3

I back Postgres DB 9.1 up using following command:

pg_basebackup -h 127.0.0.1 -U replicationuser -D /storage/test_backup -Ft -z -P

In postgresql.conf I have this set:

wal_level = hot_standby              

However, at the end of pg_basebackup run I get this warning:

NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup

I care about the backup of data from time of starting pg_basebackup only (I can afford a bit of downtime in the night, so I shut down the app server that is using this PG instance as backend DB and then start the backup). I do not need nor aim to have "continuous backup".

Question: do I really have to copy the WAL to have complete backup from the time of starting pg_basebackup command? Or is that warning designed strictly for purpose of achieving "continuous archive" functionality?

  • Is there a specific reason of using pg_basebackup instead of pg_dumpall? – dezso Sep 16 '14 at 8:07
  • Several: it's a "single app" db server but containing several databases all of them used simultaneously by the app - backing them all up at specific "checkpoint" time ensures consistency, we do not miss anything with custom stuff, plus downtime is lower - with "hot_standby" setting in PG I can shutdown appserver, start backup, and start appserver again (provided that I back up only the state from the time when app server was shut down and do not store further app-related transactions). – LetMeSOThat4U Sep 16 '14 at 8:30
10

Question: do I really have to copy the WAL to have complete backup from the time of starting pg_basebackup command?

Yes, you do.

The DB needs current WAL to start up from the base backup.

You can use pg_basebackup -X stream on 9.3 and above to copy all the required WAL along with the initial basebackup.

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.