Forums PostgreSQL.fr

morista

Membre

Script bash de backup

J'essaye de faire un backup de notre base de données avec ce script https://gist.github.com/matthewlehner/3091458
J'ai des erreurs d'exécution comme :
$ sh autopgsqlbackup
autopgsqlbackup: line 261: pg_dump -U postgres $HOST $OPT $1 > $2: command not found
autopgsqlbackup: line 261: pg_dump -U postgres $HOST $OPT $1 > $2: command not found
autopgsqlbackup: line 261: pg_dump -U postgres $HOST $OPT $1 > $2: command not found
autopgsqlbackup: line 261: pg_dump -U postgres $HOST $OPT $1 > $2: command not found
autopgsqlbackup: line 261: pg_dump -U postgres $HOST $OPT $1 > $2: command not found
autopgsqlbackup: line 261: pg_dump -U postgres $HOST $OPT $1 > $2: command not found
autopgsqlbackup: line 261: pg_dump -U postgres $HOST $OPT $1 > $2: command not found
autopgsqlbackup: line 398: /*.lundi.sql.*: No such file or directory
autopgsqlbackup: line 261: pg_dump -U postgres $HOST $OPT $1 > $2: command not found
autopgsqlbackup: line 261: pg_dump -U postgres $HOST $OPT $1 > $2: command not found
autopgsqlbackup: line 261: pg_dump -U postgres $HOST $OPT $1 > $2: command not found
autopgsqlbackup: eval: line 398: unexpected EOF while looking for matching `"'
autopgsqlbackup: eval: line 399: syntax error: unexpected end of file

Pourriez vous m'aidez s'il vous plais ou bien si vous avez un autre exemplaire de script plus complet.
Merci

gleu

Administrateur

Re : Script bash de backup

Avant de pouvoir vous conseiller un script quelconque, il serait bon de savoir ce que vous voulez faire.

---

Guillaume.

morista

Membre

Re : Script bash de backup

Je veux faire des sauvegardes automatiques de la base de données de manière journalière, hebdomadaire et mensuelle.

gleu

Administrateur

Re : Script bash de backup

J'utilise pg_back (https://github.com/orgrim/pg_back) qui fonctionne très bien.

---

Guillaume.

morista

Membre

Re : Script bash de backup

voici ce que je teste en local mais y a rien sur les fichiers sauvegarder 0Ko

DST=/D/backup/dump
login=postgres
password=postgres
host=localhost
db=postgres

        if [ $db = postgres ]; then
            mkdir -p $DST/$host/$db
            datetime=$(date +%Y_%m_%d-%H_%M_%S)
            pg_dump --host=$host --username=$login --password=$password $db | gzip -c > $DST/$host/$db/$db"-"$datetime.sql.gz
           

        fi

exit 0

morista

Membre

Re : Script bash de backup

Merci je vais explorer ça

gleu

Administrateur

Re : Script bash de backup

Il n'existe pas d'option permettant de préciser un mot de passe sur la ligne de commande, ce serait une grosse faille de sécurité. Donc pas très étonnant que cela ne fonctionne pas.

---

Guillaume.

morista

Membre

Re : Script bash de backup

ah et l'option -W ? Sinon comment faire pour le mot de passe ?

Dernière modification par morista (23/08/2016 13:54:27)

gleu

Administrateur

Re : Script bash de backup

Soit la variable d'environnement PGPASSWORD (https://www.postgresql.org/docs/9.5/sta … nvars.html) soit le fichier .pgpass (https://www.postgresql.org/docs/9.5/sta … gpass.html).

---

Guillaume.

morista

Membre

Re : Script bash de backup

Merci..
Pour le script (https://github.com/orgrim/pg_back), je le teste en local avec git bach
c'est ce que j'ai
samb@DESKTOP-PB6B74G MINGW64 /D/backk/pg_back (master)
$ sh pg_back
2016-08-23 14:25:05      INFO: preparing to dump
2016-08-23 14:25:05      INFO: target directory is D:/backk

ça n'affiche pas d'erreur mais ne crée pas de fichiers de sauvegarde non plus. Est ce normal ?

morista

Membre

Re : Script bash de backup

Merci ça marche depuis que j'ai configuré le fichier pgpass. Par contre j'ai remarqué la sauvegarde avec le fichier .dump est plus lourd que celle avec le fichiers .sql

gleu

Administrateur

Re : Script bash de backup

Il rend la main après ou il reste bloqué ? avez-vous modifié la configuration du script ?

---

Guillaume.

morista

Membre

Re : Script bash de backup

ça restait bloquer mais depuis que j'ai modifier le fichier .pgpass ça marche super bien

morista

Membre

Re : Script bash de backup

J'ai un adapté votre scripte à mes besoin peut etre que ça peut vous donner d'idée pour changer la version. Merci pour votre aide

#!/bin/sh

#
# Copyright 2011-2016 Nicolas Thauvin and contributors. All rights
# reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions
# are met:
#
#  1. Redistributions of source code must retain the above copyright
#     notice, this list of conditions and the following disclaimer.
#  2. Redistributions in binary form must reproduce the above copyright
#     notice, this list of conditions and the following disclaimer in the
#     documentation and/or other materials provided with the distribution.
#
# THIS SOFTWARE IS PROVIDED BY THE AUTHORS ``AS IS'' AND ANY EXPRESS OR
# IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
# OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
# IN NO EVENT SHALL THE AUTHORS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
# INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
# (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
# LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
# (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
# THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
#

version="1.3-dev"

DOW='date +%A'                  # Day of the week e.g. Monday
DNOW='date +%u'                 # Day number of the week 1 to 7 where 1 represents Monday
DOM='date +%d'                  # Date of the Month e.g. 27
M='date +%B'                    # Month e.g January
W='date +%V'
DOWEEKLY=6

# hardcoded defaults
PGBK_CONFIG="D:/backk/pg_back/pg_back.conf"
PGBK_BACKUP_DIR=D:/backk/backup
PGBK_TIMESTAMP="%Y-%m-%d_%H-%M-%S"
PGBK_PURGE=30
PGBK_OPTS="-Fc"
PGBK_WITH_TEMPLATES="no"
PGBK_CONNDB="postgres"

if [ ! -e "$PGBK_BACKUP_DIR" ]        # Check Backup Directory exists.
    then
    mkdir -p "$PGBK_BACKUP_DIR"
fi

if [ ! -e "$PGBK_BACKUP_DIR/pg_global" ]        # Check Backup Directory exists.
    then
    mkdir -p "$PGBK_BACKUP_DIR/pg_global"
fi

if [ ! -e "$PGBK_BACKUP_DIR/daily" ]      # Check Daily Directory exists.
    then
    mkdir -p "$PGBK_BACKUP_DIR/daily"
fi

if [ ! -e "$PGBK_BACKUP_DIR/weekly" ]     # Check Weekly Directory exists.
    then
    mkdir -p "$PGBK_BACKUP_DIR/weekly"
fi

if [ ! -e "$PGBK_BACKUP_DIR/monthly" ]    # Check Monthly Directory exists.
    then
    mkdir -p "$PGBK_BACKUP_DIR/monthly"
fi

usage() {
    echo "PostgreSQL simple backup script"
    echo "usage: `basename $0` [OPTIONS] [DBNAME]..."
    echo "options:"
    echo "  -b dir        store dump files there (default: \"$PGBK_BACKUP_DIR\")"
    echo "  -c config     alternate config file (default: \"$PGBK_CONFIG\")"
    echo "  -P time       purge old backup, see find -ctime (default: \"$PGBK_PURGE\")"
    echo "  -D db1,...    list of databases to exclude"
    echo "  -t            include templates"
    echo
    echo "  -h hostname   database server host or socket directory (default: \"${PGHOST:-local socket}\")"
    echo "  -p port       database server port (default: \"$PGPORT\")"
    echo "  -U username   database user name (default: \"$PGUSER\")"
    echo "  -d db         database used for connection (default: \"$PGBK_CONNDB\")"
    echo
    echo "  -q            quiet mode"
    echo
    echo "  -V            print version"
    echo "  -?            print usage"
    echo
    exit $1
}

now() {
    echo "$(date "+%F %T %Z")"
}

error() {
    echo "$(now)  ERROR: $*" 1>&2
}

die() {
    error $*
    exit 1
}

info() {
    [ "$quiet" != "yes" ] && echo "$(now)  INFO: $*" 1>&2
}

warn() {
    echo "$(now)  WARNING: $*" 1>&2
}

args=`getopt "b:c:P:D:th:p:U:d:qV?" $*`
if [ $? -ne 0 ]
then
    usage 2
fi

set -- $args
for i in $*
do
    case "$i" in
    -b) CLI_BACKUP_DIR=$2; shift 2;;
    -c) PGBK_CONFIG=$2; shift 2;;
    -P) CLI_PURGE=$2; shift 2;;
    -D) CLI_EXCLUDE="`echo $2 | tr ',' ' '`"; shift 2;;
    -t) CLI_WITH_TEMPLATES="yes"; shift;;
    -h) CLI_HOSTNAME=$2; shift 2;;
    -p) CLI_PORT=$2; shift 2;;
    -d) CLI_CONNDB=$2; shift 2;;
    -U) CLI_USERNAME=$2; shift 2;;
    -q) quiet="yes"; shift;;
    -V) echo "pg_back version $version"; exit 0;;
    -\?) usage 1;;
    --) shift; break;;
    esac
done

CLI_DBLIST=$*

# Load configuration
if [ -f "$PGBK_CONFIG" ]; then
    . $PGBK_CONFIG
fi

# The backup directory overrides the one in the config file
if [ -n "$CLI_BACKUP_DIR" ]; then
    PGBK_BACKUP_DIR=$CLI_BACKUP_DIR
fi

# Override configuration with cli options
[ -n "$CLI_PURGE" ] && PGBK_PURGE=$CLI_PURGE
[ -n "$CLI_EXCLUDE" ] && PGBK_EXCLUDE=$CLI_EXCLUDE
[ -n "$CLI_WITH_TEMPLATES" ] && PGBK_WITH_TEMPLATES=$CLI_WITH_TEMPLATES
[ -n "$CLI_HOSTNAME" ] && PGBK_HOSTNAME=$CLI_HOSTNAME
[ -n "$CLI_PORT" ] && PGBK_PORT=$CLI_PORT
[ -n "$CLI_USERNAME" ] && PGBK_USERNAME=$CLI_USERNAME
[ -n "$CLI_DBLIST" ] && PGBK_DBLIST=$CLI_DBLIST
[ -n "$CLI_CONNDB" ] && PGBK_CONNDB=$CLI_CONNDB

# Prepare common options for pg_dump and pg_dumpall
[ -n "$PGBK_HOSTNAME" ] && OPTS="$OPTS -h $PGBK_HOSTNAME"
[ -n "$PGBK_PORT" ] && OPTS="$OPTS -p $PGBK_PORT"
[ -n "$PGBK_USERNAME" ] && OPTS="$OPTS -U $PGBK_USERNAME"

info "preparing to dump"

# Ensure there is a trailing slash in the path to the binaries
if [ -n "$PGBK_BIN" ]; then
    PGBK_BIN=$PGBK_BIN/
    # Also, it must exist
    if [ ! -d "$PGBK_BIN" ]; then
    die "$PGBK_BIN directory does not exist"
    fi
fi

# Create the backup directory if missing
if [ ! -d $PGBK_BACKUP_DIR ]; then
    info "creating directory $PGBK_BACKUP_DIR"
    mkdir -p $PGBK_BACKUP_DIR
    if [ $? != 0 ]; then
    die "could not create $PGBK_BACKUP_DIR"
    fi
fi

info "target directory is $PGBK_BACKUP_DIR"

# Check if replay pause is available
PG_HASPAUSE=`${PGBK_BIN}psql $OPTS -At -c "SELECT 1 FROM pg_proc WHERE proname='pg_xlog_replay_pause' AND pg_is_in_recovery();" $PGBK_CONNDB 2>/dev/null`
if [ $? != 0 ]; then
    info "could not check for replication control functions"
fi

# Pause replay if dumping from a slave
if [ "${PG_HASPAUSE}" = "1" ]; then
    info "pausing replication replay"
    ${PGBK_BIN}psql $OPTS -At -c "SELECT pg_xlog_replay_pause() where pg_is_in_recovery();" $PGBK_CONNDB
    if [ $? != 0 ]; then
        die "could not pause replication replay"
    fi
fi

# Prepare the list of databases to dump
if [ -z "$PGBK_DBLIST" ]; then
    info "listing databases"
    if [ "$PGBK_WITH_TEMPLATES" = "yes" ]; then
    DB_QUERY="SELECT datname FROM pg_database WHERE datallowconn;"
    else
    DB_QUERY="SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate;"
    fi

    PGBK_DBLIST=`${PGBK_BIN}psql $OPTS -At -c "$DB_QUERY" $PGBK_CONNDB`
    if [ $? != 0 ]; then
    die "could not list databases"
    fi
fi

# Dump roles and tablespaces first
info "dumping global objects"
${PGBK_BIN}pg_dumpall $OPTS -g > $PGBK_BACKUP_DIR/pg_global/pg_global_`date "+${PGBK_TIMESTAMP}"`.sql
if [ $? != 0 ]; then
    error "pg_dumpall -g failed"
    out_rc=1
fi

for db in $PGBK_DBLIST
do
    # Do not dump excluded databases
    echo $PGBK_EXCLUDE | grep -w $db >/dev/null 2>&1
    if [ $? = 0 ]; then
    continue
    fi

    # Dump
    # Monthly  Backup
    if [ "$DOM" = "1" ]; then
        info "dumping database \"$db\""
        ${PGBK_BIN}pg_dump $OPTS $PGBK_OPTS -f $PGBK_BACKUP_DIR/monthly/${db}_mounth_`date +%B`_`date "+${PGBK_TIMESTAMP}"`.dump $db
        rc=$?
        if [ $rc != 0 ]; then
            out_rc=1
            error "pg_dump of database \"$db\" failed"
        fi       

    fi
        # Weekly Backup
    if [ "$DNOW" = "6" ]; then
 
        info "dumping database weekly\"$db\""
        ${PGBK_BIN}pg_dump $OPTS $PGBK_OPTS -f $PGBK_BACKUP_DIR/weekly/${db}_week_`date +%V`_`date "+${PGBK_TIMESTAMP}"`.dump $db
        rc=$?
        if [ $rc != 0 ]; then
            out_rc=1
            error "pg_dump of database \"$db\" failed"
        fi       
       
     fi
      # Daily Backup
info "dumping database \"$db\""
        ${PGBK_BIN}pg_dump $OPTS $PGBK_OPTS -f $PGBK_BACKUP_DIR/daily/${db}_`date "+${PGBK_TIMESTAMP}"`.dump $db
        rc=$?
        if [ $rc != 0 ]; then
            out_rc=1
            error "pg_dump of database \"$db\" failed"
        fi           
   
 
done

# Resume replay if dumping from a slave
if [ "${PG_HASPAUSE}" = "1" ]; then
    info "resuming replication replay"
    ${PGBK_BIN}psql $OPTS -At -c "SELECT pg_xlog_replay_resume();" $PGBK_CONNDB
    if [ $? != 0 ]; then
        die "could not resume replication replay"
    fi
fi

[ -z "$out_rc" ] && out_rc=0

# Purge old backups, only if current backup succeeded
if [ "$out_rc" = 0 ]; then
    info "purging old backups"
    find $PGBK_BACKUP_DIR -maxdepth 1 -mtime +$PGBK_PURGE -exec rm -rf '{}' ';'
    if [ $? != 0 ]; then
    die "could not purge all old backups"
    fi
else
    warn "some dumps failed, purge of all old backup cancelled"
fi

info "done"

exit $out_rc

Dernière modification par morista (24/08/2016 11:33:06)