PostgreSQL Disaster Recovery¶

Backup (Exporting DB)¶

Requirements¶

Preliminary Note¶

The PostgreSQL-Database-Cluster is saved periodically in background by rsnapshot and the backup_psql.sh-script.

The backup is located in /var/cache/rsnapshot/ where several subdirectories exist.

cd /var/cache/rsnapshot
ls

will give you an output like

daily.0   hourly.10  hourly.14  hourly.18  hourly.21  hourly.4  hourly.8
daily.1   hourly.11  hourly.15  hourly.19  hourly.22  hourly.5  hourly.9
hourly.0  hourly.12  hourly.16  hourly.2   hourly.23  hourly.6
hourly.1  hourly.13  hourly.17  hourly.20  hourly.3   hourly.7

hourly.0 is the most recent hourly backup, while hourly.1 is one hour before hourly.0 and so on. Once a day hourly.23 is copied to daily.0 and the former daily.0 is moved to daily.1 and so on.

if you want to save just the most recent backup go for hourly.0. The following example shows how to retrieve the backup with PSFTP [[vbip:software:remoteaccess|Remote Access for Windows machines]]

1. find out what your local working directory is
2. open connection to server in this example 192.168.2.167
   1. if you connect to the server the first time, you can check the fingerprint and continue by storing the fingerprint locally by answering y
   2. or by not storing the fingerprint and answering n
   3. or pressing <enter> if you don't trust this server
   4. login as: root
   5. provide your password
3. change to the remote rsnapshot directory
4. change to the database-backup directory
5. list the files
6. download all files
   1. pg_dumpall.sql
   2. pg_hba.conf
   3. pg_ident.conf
   4. postgresql.conf
7. close connection
8. close terminal

lpwd
open 192.168.2.167
cd /var/cache/rsnapshot/hourly.0
cd localhost/postgres
ls
get pg_dumpall.sql
get pg_hba.conf
get pg_ident.conf
get postgresql.conf
close
bye

Restore (Importing DB)¶

Importing the complete DB involves

• upload the DB-dump
• drop the current DB
• reloading a complete DB-dump

The following example shows how to retrieve the backup with PSFTP [[vbip:software:remoteaccess|Remote Access for Windows machines]]

1. find out what your local working directory is
2. change your local working directory to the local directory containing the pg_dumpall.sql file
3. open connection to server in this example 192.168.2.167
   1. if you connect to the server the first time, you can check the fingerprint and continue by storing the fingerprint locally by answering y
   2. or by not storing the fingerprint and answering n
   3. or pressing <enter> if you don't trust this server
   4. login as: root
   5. provide your password
4. change to the remote postgres-data directory
5. upload the dump file
   1. pg_dumpall.sql
6. close connection
7. close terminal

lpwd
lcd c:\psql.bak\
open 192.168.2.167
cd /var/lib/pgsql/9.1/data

put pg_dumpall.sql
close
bye

if applicable drop the expired current database via remote shell

cd /var/lib/pgsql/9.1/data
su postgres
dropdb JANUS_11002
exit

restore the dump

cd /var/lib/pgsql/9.1/data
chown postgres:postgres pg_dumpall.sql
su postgres
psql -f pg_dumpall.sql postgres