DokuWiki » Infrastructure » Disaster Recovery

h1. PostgreSQL Disaster Recovery

h2. Backup (Exporting DB)

h3. Requirements

h3. 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.

<pre><code class="bash">

cd /var/cache/rsnapshot

ls

</code></pre>

will give you an output like

<pre><code class="bash">

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

</code></pre>

@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]]

# find out what your local working directory is

# open connection to server in this example @192.168.2.167@

## if you connect to the server the first time, you can check the fingerprint and continue by storing the fingerprint locally by answering @y@

## *or* by not storing the fingerprint and answering @n@

## *or* pressing @<enter>@ if you don't trust this server

## login as: @root@

## provide your password

# change to the remote @rsnapshot@ directory

# change to the database-backup directory

# list the files

# download all files

## pg_dumpall.sql

## pg_hba.conf

## pg_ident.conf

## postgresql.conf

# close connection

# close terminal

<pre><code class="bash">

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

</code></pre>

h2. 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]]

# find out what your local working directory is

# change your local working directory to the local directory containing the @pg_dumpall.sql@ file

# open connection to server in this example @192.168.2.167@

## if you connect to the server the first time, you can check the fingerprint and continue by storing the fingerprint locally by answering @y@

## *or* by not storing the fingerprint and answering @n@

## *or* pressing @<enter>@ if you don't trust this server

## login as: @root@

## provide your password

# change to the remote @postgres-data@ directory

# upload the dump file

## pg_dumpall.sql

# close connection

# close terminal

<pre><code class="bash">

lpwd

lcd c:\psql.bak\

open 192.168.2.167

cd /var/lib/pgsql/9.1/data

put pg_dumpall.sql

close

bye

</code></pre>

if applicable drop the expired current database via remote shell

<pre><code class="bash">

cd /var/lib/pgsql/9.1/data

su postgres

dropdb JANUS_11002

exit

</code></pre>

restore the dump

<pre><code class="bash">

cd /var/lib/pgsql/9.1/data

chown postgres:postgres pg_dumpall.sql

su postgres

psql -f pg_dumpall.sql postgres

</code></pre>