Postgres WAL Files
tl;dr — you only need the archive_command in your postgres.conf file if you need the WAL files to perform a full or point-in-time recovery of your database; it is not required for replication.
Ran into some issues with our current database setup at MyEducator. We currently run Postgres 9.2 on two separate instances in a master/slave configuration with the slave on hot standby in the event the master stops working. Getting it configured correctly got us looking at a few different how-to guides, but we eventually got it working!
Time passed — as it often does — and one day our application stopped working on account of the fact the our master database server had run out of disk space! Realizing our database wasn’t anywhere close to filling the entire disk itself, we searched around and found that the directory that stores our archived WAL (write ahead log) files was stuffed to the gills. We emptied the directory and everything went back to normal.
Fast forward to today and our solution to the problem has continued to be, “Just delete the archived WAL files to free up space again. Somebody should look into that when they have some time.” Well, I finally decided I had some time.
After reading and re-reading the Postgres documentation and a small, almost one-sided discussion on StackExchange, I came to the realization that we had pulled bits and pieces from different guides and come up with our own setup that was doing one and a half jobs instead of the one we thought it was.
From the docs:
At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/ subdirectory of the cluster’s data directory. The log records every change made to the database’s data files. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by “replaying” the log entries made since the last checkpoint.
So the WAL files are there in case of a crash. Got it.
Turns out we can also use them to keep our hot standby (almost) up-to-date as a failover:
Transaction Log Shipping
Warm and hot standby servers can be kept current by reading a stream of write-ahead log (WAL) records. If the main server fails, the standby contains almost all of the data of the main server, and can be quickly made the new master database server.
WAL files can also server another purpose:
[T]he existence of the log makes it possible to use a[nother] strategy for backing up databases: we can combine a file-system-level backup with backup of the WAL files. If recovery is needed, we restore the file system backup and then replay from the backed-up WAL files to bring the system to a current state.
WAL files can be used for:
- Replicating data
- Recovering data
As part of our initial replication setup we had included the following in our postgres.conf file:
archive_command = 'cp %p /var/lib/postgresql/9.2/archive/%f'This command archives WAL files before they are recycled so that they can be used in a continous-archiving solution for restoring a database.
Well, turns out we didn’t actually need this command because we’re not backing up the right way to use them anyways:
Note: pg_dump and pg_dumpall do not produce file-system-level backups and cannot be used as part of a continuous-archiving solution
So, the files that have been building up and crashing our application aren’t actually needed in our current setup. The only thing they’re good for is causing problems that I can write a blog post about.
We removed the archive_command from our configuration file and all is well. Maybe we’ll need them again when we start using the appropriate file-system-level backups, but for now we don’t!
