0

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?

2

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
1

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

  1. backup the database using:
    rsync –avr /var/lib/pgsql95/* /tmp/pgsql95/
  2. 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

  1. select pg_stop_backup();
  2. Transfer archive WAL files

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

RECOVERY STEPS

  1. 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'

  1. Edit postgresql.conf
  2. pg_ctl –D /tmp/pgsql95/base start
  • 1
    I might be missing something, but where is the LVM snapshot in this list of steps? Also, in step 9, what has to be changed? – dezso Aug 1 '16 at 15:40
  • Yes it is missing. I just tested the "theory", as the snapshots are taken by a different team. Replace the backing up of the database cluster and the archive WAL files (which in the above example use rsync) with the actual LVM snapshot commands. I will ask the infrastructure team to provide the commands and post the update. – Stelios Aug 1 '16 at 16:46
  • Changes in postgresql.conf are not necessary and depend on whether the database environment has any changes to it. In my demo I had to change the database cluster base directory (from /var/lib/pgsql95 to /tmp/pgsql95) as I restore the database in a different directory; and the port (from 5432 to 5433). – Stelios Aug 1 '16 at 16:46
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.