Vous n'êtes pas identifié(e).
- Contributions : Récentes | Sans réponse
Pages : 1
#1 Re : Général » Script bash de backup » 23/08/2016 17:19:04
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
#2 Re : Général » Script bash de backup » 23/08/2016 16:22:02
ça restait bloquer mais depuis que j'ai modifier le fichier .pgpass ça marche super bien
#3 Re : Général » Script bash de backup » 23/08/2016 15:41:52
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
#4 Re : Général » Script bash de backup » 23/08/2016 14:40:24
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 ?
#5 Re : Général » Script bash de backup » 23/08/2016 13:34:34
ah et l'option -W ? Sinon comment faire pour le mot de passe ?
#6 Re : Général » Script bash de backup » 23/08/2016 12:26:17
Merci je vais explorer ça
#7 Re : Général » Script bash de backup » 23/08/2016 12:24:33
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
#8 Re : Général » Script bash de backup » 23/08/2016 10:00:25
Je veux faire des sauvegardes automatiques de la base de données de manière journalière, hebdomadaire et mensuelle.
#9 Général » Script bash de backup » 22/08/2016 17:01:15
- morista
- Réponses : 13
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
#10 Re : Général » Backup » 03/08/2016 16:13:17
#11 Re : Général » Backup » 03/08/2016 12:25:20
Je veux définir une stratégie de backup puis l'automatisé de manière quotidienne, hebdomadaire et mensuel. Non je fais pas de réplication
#12 Re : Général » Backup » 03/08/2016 11:59:53
Merci !!! Et qu'est ce qui est mieux entre c'est trois approches ?
24.1. Sauvegarde SQL
24.2. Sauvegarde de niveau système de fichiers
24.3. Archivage continu et récupération d'un instantané (PITR)
#13 Re : Général » Backup » 03/08/2016 11:49:20
Vous voulez dire des crons ?
#14 Général » Backup » 03/08/2016 11:22:07
- morista
- Réponses : 8
Salut,
Qui sait quel est le meilleur moyen d'automatiser ces backup ?
#15 Re : Général » RLS Row Level Security » 03/08/2016 11:20:26
ok Merci.
#16 Général » RLS Row Level Security » 29/07/2016 11:35:19
- morista
- Réponses : 2
Bonjour,
Qui a une idée ou un article à me proposer sur l'impact qu'a le ROW LEVEL SECURITY
sur la performance de la base de données. J'aimerai l'implémenté.
Merci!!
#17 Re : Général » Trigger for Update » 28/07/2016 12:21:43
Merci pour ta réponse. Oui je l'ai finalement avec NEW et OLD
IF (TG_OP = 'UPDATE') THEN
IF (NEW.valid_to != OLD.valid_to) THEN
INSERT INTO audit VALUES(uuid_generate_v4(), ...);
ELSIF (NEW.rate != OLD.rate) THEN
INSERT INTO audit VALUES(...);
ELSIF(NEW.workflow_state != OLD.workflow_state) THEN
INSERT INTO audit VALUES(...
END IF;
#18 Général » Trigger for Update » 27/07/2016 12:57:16
- morista
- Réponses : 2
Bonjour,
J'ai une table audite que je souhaite gérer avec un trigger.
Pour les Insertions et les Suppression c'est déjà. Par contre
pour chaque Update je veux enregistrer en meme temps
la valeur modifier et son ancien valeur. Can you help me please ??
Voici ma fonction
CREATE OR REPLACE FUNCTION "log_mission" () RETURNS trigger
VOLATILE
AS $dbvis$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO audit VALUES(uuid_generate_v4(), OLD.account_id, current_timestamp, 'Mission', 'Suppresion', '', '', OLD.account_id);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO audit VALUES(uuid_generate_v4(), NEW.account_id, current_timestamp, 'Mission', 'Modification', '', '', NEW.account_id);
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO audit VALUES(uuid_generate_v4(), NEW.account_id, current_timestamp, 'Mission', 'Création', '', '', NEW.account_id);
END IF;
RETURN NEW;
END;
$dbvis$ LANGUAGE plpgsql
Pages : 1