Sitemap
Kontakt
Links
+++ erd.trabant +++

+++ erd.trabant +++

blablabla

blablabla

Backing up PostgreSQL databases

if you need to backup different postgres servers automagicaly, or if you like to install a new version parrallel to the running version (e.g. to decrease downtime), and you need to backup and restore, i wrote a little backupscript to handle more than one connection. this script backups all databases from different servers. also backups large objects....maybe this is useful, i use it every day ,-)
But: this script only handles one host with different servers on different ports (e.g. on port 5432, 5433, 5434, ...).....

e.g. you can use this script in a cronjob (as root or user postgres)


#DB-Backup
49 0 * * * su -c /home/pgsql/scr/bckdb.pgsql postgres

bckpdb.pgsql

 


#! /bin/sh
Date=$(date "+%Y-%m-%d")
#set path to your postgres server
export PATH=/home/pgsql/bin/psql:$PATH

#give a hostname
HOST=localhost
#backupdirectory
BACKUPDIR=/home/BCK/$HOST/DBS/pgsql
#homedirectory of postgres
DBHOME=/home/pgsql
#maybe you need o store the password in an external file if this script running as root for unattended backup (cronjobs, etc)
#single-password file
PASSWORDFILE=$DBHOME/pass
#user/password file, every in a single line
USERPASSWORDFILE=$DBHOME/user-pass
#Ports to connect to server
PORTS="5432 5434 5433"
#where to save the backup-logfile
LOGFILE=/home/LOGS/bck/dbs/pgsql.$Date

echo "">$LOGFILE
for port in $PORTS; do
        export PGPORT=$port

#if you do a backup of different versions, you  maybe need this:
####################################################
#       if [ "$port" = "5433" ]; then
#               export PGLIB=/home/postgres6
#               export PGDATA=/home/postgres6/
#               export PGLIB=/home/postgres6/lib/
#               export PATH=/home/postgres6/bin:$PATH
#               export LD_LIBRARY_PATH=/home/postgres6/lib/
#       fi
#####################################################
        echo "PORT: $port" >>$LOGFILE
        DBs=$($DBHOME/bin/psql -t -c "select datname from pg_database" template1 <$PASSWORDFILE)

        for DB in $DBs; do
                #echo "============================="
                #echo "Making backup of database $DB"
                #echo "============================="
                #echo "1)  dumping"
                mkdir -p $BACKUPDIR/$DB
                #> /dev/null 2> /dev/null


                #complete backup with blobs
                ARCHIVENAME=$DB.$Date.lo.sql.tgz
                $DBHOME/bin/pg_dump -x -Ft -o -c -b $DB -u < $USERPASSWORDFILE | gzip -9 > $BACKUPDIR/$DB/$ARCHIVENAME
                echo "$DB:$ARCHIVENAME" >> $LOGFILE


                #sheme backup with createDB
                ARCHIVENAME=$DB.$Date.tplC.sql.gz
                $DBHOME/bin/pg_dump -x -s -C $DB -u < $USERPASSWORDFILE | gzip -9 > $BACKUPDIR/$DB/$ARCHIVENAME
                echo "$DB:$ARCHIVENAME" >> $LOGFILE

                #sheme backup for restore without clean
                ARCHIVENAME=$DB.$Date.tplR.sql.gz
                $DBHOME/bin/pg_dump -x -s $DB  -u < $USERPASSWORDFILE| gzip -9 > $BACKUPDIR/$DB/$ARCHIVENAME
                echo "$DB:$ARCHIVENAME" >> $LOGFILE

                #complete backup with copy
                ARCHIVENAME=$DB.$Date.CP.sql.gz
                $DBHOME/bin/pg_dump -x -c $DB -u < $USERPASSWORDFILE | gzip -9 > $BACKUPDIR/$DB/$ARCHIVENAME
                echo "$DB:$ARCHIVENAME" >> $LOGFILE

                #complete backup with inserts
                ARCHIVENAME=$DB.$Date.INS.sql.gz
                $DBHOME/bin/pg_dump -x -c -D $DB -u < $USERPASSWORDFILE | gzip -9 > $BACKUPDIR/$DB/$ARCHIVENAME
                echo "$DB:$ARCHIVENAME" >> $LOGFILE

        done;
done

NOTE:
Restore?
untar-gzip the backup-archive for scheme, inserts, or complete database...
and as always , connect the server on port xxxx, connect the existing database or create a new one and connect, create user and group if needed, and use i backup.sql for import what you need.

>>PopUP / Drucken / Print<<
blablabla