2

I need some help understanding the how archived WAL files are used and when I can clean them up.

Currently we have a master database replicating to a slave, but every so often our master database server runs out of disk space because the files archived using archive_command build up and we have to manually remove them. I feel like we're missing something if we have to manually remove old archived WAL files because there are too many that the file system runs out of disk space...

Having said that, I'm not sure I understand the replication process enough to start deleting files and be confident that I'm not screwing anything up. Does our slave use the archived files created by archive_command or does it rely only on the files in $PG_DATA/pg_xlog directory to maintain a consistent state with the master?

If that's the case, and we're doing a pg_dump of our master every six hours, could I update our backup script to also clear out all archive files that exist at the start of the pg_dump (once the pg_dump is complete of course)? from the docs:

Note: pg_dump and pg_dumpall do not produce file-system-level backups and cannot be used as part of a continuous-archiving solution. Such dumps are logical and do not contain enough information to be used by WAL replay.

I just want to make sure that however we clean up the archived files, we still have what we need for replication to continue as well as be able to perform a PITR in since the last base backup.

Our archive command is cp %p /var/lib/postgresql/9.2/archive/%f and here is our recovery.conf file from the slave:

standby_mode = 'on'
primary_conninfo = 'host=<ip-address> port=<port> user=rep password=<password> sslmode=require'
trigger_file = '/tmp/postgresql.trigger.5432'
  • Can you please add your archive_command and the contents of your recovery.conf on your slave to the question. That will help greatly in answering your question. – Kassandry Jan 22 '16 at 0:34
  • It would seem that I've mixed to different topics together here... The replication relies on the WAL files in pg_xlog/ but the archived WAL files are related to backup and restoration. Also, since we're currently using pg_dump for our database backups, the point about keeping archived WAL files is moot because they can't be played on top of a restored pg_dump file. – ngoue Jan 22 '16 at 7:01
  • 1
    If anybody is interested I blogged about our mistake! medium.com/@jordanthomasg/… – ngoue Jan 22 '16 at 7:50
4

WAL Files are a record of all the transactions that have taken place .. but that's it - a delta between state before and state after, essentially. Which means you have to have a BASE to start off of before you can apply any WAL Files.

If you clear out WAL archives without creating a new BASE, then you will be in trouble when you try to restore your database (via PITR) from them.

If you are going to delete WAL files, then you need to make sure you have a valid base to build from again afterward.

Read the documentation on WAL archiving for more information.

In terms of streaming replication, I believe that only uses files located in pg_xlog... (files are not archived before they are sent, IIRC)

If that's the case, and we're doing a pg_dump of our master every six hours, could I update our backup script to also clear out all archive files that exist at the start of the pg_dump (once the pg_dump is complete of course)?

Not entirely ... pg_dump creates a file that contains the information of your whole database that can be given to pg_restore... For PITR, you actually need to create a file-level "backup" by copying your database files and WAL files (after issuing a pg_start_backup)

  • Currently going back through the documentation... Looks like pg_dump does not create an adequate backup for the purposes of continuous archiving and point-in-time recovery. – ngoue Jan 22 '16 at 6:47
  • The more I'm looking through this issue, I believe you are correct that replication only relies on the files in pg_xlog/. – ngoue Jan 22 '16 at 6:58
  • Perhaps update the end of your answer to account for the documentation that clearly states pg_dump does not produce a backup compatible with WAL archives. – ngoue Jan 22 '16 at 7:45
  • Ah yes - sorry. I got pg_dump mixed up with the backup process. – Joishi Bodio Jan 22 '16 at 17:15

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.