Postgresql – How to purge old files from pg_xlogs directory

See the fine manual. If my advice conflicts with its' in any way, it's right.

1. A sync isn't a bad idea, unless your copy tool fsync()s each WAL file it writes and the directory it's in before copying the next one. An incomplete last WAL file doesn't matter much; at worst, you just delete it. Pg will generally choke on an incomplete WAL - though there's no checksumming done, so you could be really unlucky and have it try to apply garbage data that by sheer insane chance happened to look like real WAL records. In your position I'd be syncing the volume before a snapshot to make sure any unwritten dirty buffers in RAM hit the file system image on disk. A freeze would help avoid messy but non-fatal partially written WALs, so it's not a terrible idea but not vital. What's vital is to have an undamaged timeline up until the point of recovery. Personally, I write my WALs to a temporary file name and rename them to their final name only once fully copied; if you do this, you don't need to freeze.

2. Sounds correct. A live snapshot is just like doing a plug pull test on a live system with write-through caching. Your database should recover fine when restored from a live snapshot, same as after plug-pull. I'd recommend that you automate tests of restores from snapshots. (Note: A snapshot restore test is not a complete substitute for plug pull testing because it doesn't account for possible disk, raid controller, etc write caching). Not only the config files and the dump, but the database its self should be fine after your snapshot. Consider syncing the volume before the snapshot to make sure all the dump data etc has actually hit disk.
   
   2a. Might save some disk space. Little difference otherwise. You'll get to keep the snapshots a lot longer without all the churn of the live database on them.

3. Why even snapshot your code volume? A plain file level copy may well be just fine. Certainly a live snapshot should be.

4. This is not a solid backup scheme. It fails in one critical area: There is no restore testing and validation being performed. You should always test your backups on a regular basis to make sure you can really restore them.

   Personally, I recommend that you use WAL shipping, or send database dumps, to a different host, preferably one not on Amazon EC2 or at least in a different region. This host should perform automated restore tests, send reports to you of the results, and should also be checked manually.

   While your snapshots (containing dumps) will be on S3, and will be safe there, that doesn't mean they'll be accessible when you need them urgently. Amazon's durability claims are reassuring, but your data can still be safe and completely inaccessible to you during a badly timed outage of the S3 service.

Most likely what you're seeing is a huge checkpoint_segments value and long checkpoint_timeout; alternately, they might have set wal_keep_segments to a very large value if it's supposed to support streaming replication.

You can force a checkpoint with the CHECKPOINT command. This may stall the database for some time if it has accumulated a huge amount of WAL and hasn't been background-writing it. If checkpoint_completion_target is low (less than 0.8 or 0.9) then there's likely to be a big backlog of work to do at checkpoint time. Be prepared for the database to become slow and unresponsive during the checkpoint. You cannot abort a checkpoint once it begins by normal means; you can crash the database and restart it, but that just puts you back to where you were.

I'm not certain, but I have the feeling a checkpoint could also result in growth of the main database - and do so before any space is freed in the WAL, if it is at all. So a checkpoint could potentially trigger you running out of space, something that's very hard to recover from without adding more storage at least temporarily.

Now would be a very good time to get a proper backup of the database - use pg_dump -Fc dbname to dump each database, and pg_dumpall --globals-only to dump user definitions etc.

If you can afford the downtime, stop the database and take a file-system level copy of the entire data directory (the folder containing pg_xlog, pg_clog, global, base, etc). Do not do this while the server is running and do not omit any files or folders, they are all important (well, except pg_log, but it's a good idea to keep the text logs anyway).

If you'd like more specific comment on the likely cause (and so I can be more confident in my hypothesis is) you can run the following queries and paste their output into your answer (in a code-indented block) then comment so I'm notified:

SELECT version();

SELECT name, current_setting(name), source
  FROM pg_settings
  WHERE source NOT IN ('default', 'override');

It is possible that setting checkpoint_completion_target = 1 then stopping and restarting the DB might cause it to start aggressively writing out queued up WAL. It won't free any until it does a checkpoint, but you could force one once write activity slows down (as measured with sar, iostat, etc). I have not tested to see if checkpoint_completion_target affects already-written WAL when changed in a restart; consider testing this on a throwaway test PostgreSQL you initdb on another machine first.

Backups have nothing to do with WAL retention and growth; it isn't backup related.

See:

• The PostgreSQL manual on WAL
• The PostgreSQL manual on WAL configuration
• depesz on checkpoint_completion_target
• depesz on WAL