Backup standby database using LVM snapshot

Question

Objective: Backup standby database (synchronous streaming replication) using LVM snapshot.

When running pg_start_backup:

postgres=# SELECT pg_start_backup('label');
ERROR:  recovery is in progress
HINT:  WAL control functions cannot be executed during recovery.

Is it possible to copy backup_label and tablespace_map from the primary database's cluster directory into the standby database's cluster directory in order to perform the LVN snapshot backup of the standby cluster directory?

Answer 1

To do that on the standby server you need to configure it like a master server to enable the cascading replication[1].

To enable it, you need to change the follow parameters:

• wal_level to archive or hot_standby
• If plan to use streamming replication:
  • max_wal_senders to 2
• If you plan to use wal archiving:
  • archive_mode to on
  • archive_command to some place

Then restart your standby database.

When you configure that, you can run the pg_start_backup function without a problem.

To do what you need (backup with LVM) you must:

PLEASE test this steps on a test machine to avoid any issues on your production database server.

1. run pg_start_backup:

   psql -U postgres -c "SELECT pg_start_backup('my lvm backup');"
   

2. make the lvm snapshot[2] (and export it to a file[3]), eg:

   ## Repeat that action for each tablespace
   ## TODO: Review max grow size for your snapshot
   lvcreate --size 10G --snapshot --name pgdata_backup_snapshot /path/to/pgdata/dir
   ## export the LVM to a image (with the `dd` command): 
   ### TODO: Replace the `vg_name` by correct one
   dd if=/dev/vg_name/pgdata_backup_snapshot of=/path/to/pgdata_backup.dd
   ## now remove the snapshot
   lvremove /dev/vg_name/pgdata_backup_snapshot
   

3. run pg_stop_backup function:

   psql -U postgres -c "SELECT pg_stop_backup();"
   

Hope it helps.

References:

1. https://www.postgresql.org/docs/current/static/warm-standby.html#CASCADING-REPLICATION
2. http://www.tecmint.com/take-snapshot-of-logical-volume-and-restore-in-lvm/
3. https://www.howtoforge.com/linux_lvm_snapshots_p2
4. http://www.tldp.org/HOWTO/LVM-HOWTO/snapshots_backup.html

Answer 2

BACKUP STEPS

On Primary Database

1. Make sure primary db has archive_mode = on in base/postgresql.conf
2. select pg_start_backup(‘backup_label’);

On Standby Database

3. backup the database using:
   rsync –avr /var/lib/pgsql95/* /tmp/pgsql95/

4. Transfer files generate by pg_start_backup():
   rsync -avr postgres@xxxxx:/var/lib/pgsql95/base/tablespace_map /tmp/pgsql95/base

   rsync -avr postgres@xxxxx:/var/lib/pgsql95/base/backup_label /tmp/pgsql95/base

On Primary Database

6. select pg_stop_backup();

7. Transfer archive WAL files

   rsync –avr /var/lib/pgsql95/archivedir/* \ postgres@xxxxx:/tmp/pgsql95/archivedir

RECOVERY STEPS

8. Edit recovery.conf in database cluster which is to be restored

recovery_target_timeline = 'latest'

restore_command = 'cp -f /tmp/archivedir/%f %p </dev/null'

9. Edit postgresql.conf
10. pg_ctl –D /tmp/pgsql95/base start

Answer 3

I decided to "roll my own", eventually I spent quite much more work on it than expected, so I decided to publish my script:

https://gitlab.com/tobixen/pglvmbackup

One of the biggest complications for me was that the backup should be taken from the slave server, then it's required to do the pg_start_backup in a "non-exclusive backup mode", which again means we cannot simply run a simple "pg_start_backup()" using pgsql ... we need to hold that session until the backup is done, which adds a bit of complexity when sticking to bash. I ended up using the bash concept "coproc" for this purpose.

I see the other suggested solutions here is to run "pg_start_backup()" on the master server rather than slave server, I'm not 100% confident that this will have the intended effect.

Usage of pg_start_backup and pg_stop_backup may arguably be moot when doing an lvm snapshot, but I'd like to be on the safe side.