3

Is it possible to shoot a backup with the pg_basebackup utility from the slave server (where of streaming replication with the help of this utility pg_basebackup)?

I have a hot_standby server (streaming replication with pg_basebackup), and periodically run pg_basebackup from slave, but when I open my backup (base.tar.gz) I can't find any WAL files.

Do I have to set up archive_command on master (or slave, idk)?

  • 2
    What version of PostgreSQL are you using? What was the exact command you ran to perform the basebackup? – Kassandry Aug 23 '15 at 7:12
  • 1
    Did you check Cascading Replication ? – Sahap Asci Aug 23 '15 at 7:15
  • command is : sudo -u postgres /usr/bin/pg_basebackup --xlog-method=fetch --checkpoint=spread --format=t -z -Z ${COMPRESS_LEVEL} -D ${BACKUP_FOLDER} I use postgresql 9.3 version @Sahap Asci: i dont check it , but thanks aa lot for link i will learn it – Andrew Zaharov Aug 24 '15 at 7:22
  • @AndrewZaharov Are you wanting to take a standalone backup of your cluster or are you looking to do point-in-time recovery at some point? Also what is the output of tar -tf on your base.tar.gz file? – Kassandry Aug 24 '15 at 9:15
  • @Kassandry thank you for reply , i am looking for standalone backup output of tar -tf base.tar.gz is very huge (db size about 300Gbytes), but it is not contains pg_xlog/ files – Andrew Zaharov Aug 24 '15 at 10:39
1

You might want to try to set up max_wal_senders on your replica, and use --xlog-method=stream instead of --xlog-method=fetch, which would let the basebackup grab the individual WAL records as they are shipped, rather than trying to grab the WAL segments at the end of the basebackup. If you have to use --xlog-method=fetch, then you should set wal_keep_segments high enough to let you take a reasonable basebackup. checkpoint_segments x2 should be reasonable as a starting point.

As a reference, the documentation for the 9.3 version of pg_basebackup is here.

I highly recommend that you test out the backup by restoring it on another server, and running a few tests against it to make sure it comes up in a consistent state, and has a reasonable snapshot of the data that you wanted backed up.

One additional note, if you set up an archive_command under 9.3, and a restore_command in your recovery.conf, your streaming replica will be able to recover itself and continue if network conditions cause streaming replication to lag. An example and discussion of this problem and a solution is talked about here: http://evol-monkey.blogspot.com/2014/09/offsite-replication-problems-and-how-to.html

I hope this helps. =)

  • Thank you very much, your post was very helpful i made next things : - on master i set archive_command to send WALs to another backup server (and store last 5 days) - on slave server i edit recovery.conf as you commend it (when server is up , it trys to download WALs from another backup server) – Andrew Zaharov Aug 25 '15 at 8:06
  • And now when i am trying to restore backup on some staging server , it downloads WALs from backup server (like slave server for example) I did it because I'm not sure whether the right decision to increase wal_keep_segments checkpoint_segments and under heavy load I dont know is it right way , but it approached for me – Andrew Zaharov Aug 25 '15 at 8:06
  • @AndrewZaharov Increasing wal_keep_segments for your database under heavy load should be just fine, so long as you have enough disk space to keep the extra WAL segments around. And getting the extra WAL segments from a replica server is excellent, as it takes load off of the primary. Glad my suggestions helped. =) – Kassandry Aug 25 '15 at 17:06

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.