We use Postgres 9.6, with 10+TB, with a multi-node cluster setup, managed by Patroni. The WAL archives and backups are managed by home grown tool pgrsync.

The archive_command was initially set to cp %p /archives/%f. There is a background job (pgrsync) that pushes the archives to S3 periodically. The volume of WAL archives was higher (avg around 200 WAL files/min, with the peak being 500/min). The cp also adds to the Disk IO bandwidth, which is precious for us in a cloud environment.

We are looking to optimise this in the application. Also, I noticed that in pg_xlog folder that several files were hard link to other WAL files. (This part is not understood fully, how could Postgres internally have one WAL archive being a link to another -- it is unlikely that so many transactions could be repeated exactly after some time).

Anyway, as an optimisation exercise, we set the archive_command to ln %p /archives/%f. This reduces the disk IO, we are just adding one more link to the same file. When we are done copying to S3, the link is removed and the OS manages deleting the actual file, when Postgres also frees it. Looks good on paper. Except one problem: If Postgres writes to the same file (with the same inode) after completing the archive_command, then we are in a mess. Please refer postgres: WAL ends before end of online backup where we are seeing random WAL corruption and we don't know if using ln caused this.

Question: Is it safe to use ln (hardlink) instead of cp in archive_command?

WAL files are default 16MB. We have a plan to increase it, but I suspect it will reduce the number of files, but the size and IO will likely remain the same. Not likely to help, isn't it?

Backup WAL from a standby node is a good idea - or the home grown consumer for archiving. Or even simply scp to another server, to reduce disk write IO on Postgres server. Since anything could be standby or master at any time, managing who will actually push files could be slightly complex. So, I would either go for archiving consumer or scp to non-Postgres server.

Reg not invoking pgrsync directly, We did this initially. pgrsync would then have to handle each file individually in-series (compress and push to S3). This was slowing down the ability to handle 500 WAL files (or 500*16MB WAL bytes). Copying to another location gives pgrsync the ability to push files in parallel and thus the ability to handle 500 WAL files per min. I was avoiding the need for another server (rsync / scp to), but it looks like that is a better approach now.

  • Some thoughts: 1. How large are your WAL files? 500 WAL files per minute leads me to believe your files are much too small. The default is 16MB but you should bump that up significantly. Are you getting checkpoint warnings? 2. Can't you backup your WAL from a standby node? 3. Replication slots could be used to keep WAL from being recycled (see postgresql.org/docs/9.6/wal-configuration.html for more info about this). 4. You might be able to use a home-grown replication "consumer" for archiving -- but this is similar to backing up off of a standby. – Colin 't Hart Nov 14 '20 at 9:29
  • Re recycling WAL files: Postgres does this presumably to avoid having to create new files and allocate new blocks on disk. So you need to guarantee that Postgres won't reuse that WAL file till after you've finished archiving it. There's no reason you have to copy files to another place on the same server; we rsync our WAL to another server (in the archive command). Why can't you just invoke your pgrsync there? Then you're using Postgres to manage your WAL files rather than doing it yourself -- you're trying to reinvent the wheel here. – Colin 't Hart Nov 14 '20 at 9:34
  • 1
    I believe many of your performance problems will be greatly reduced if you switch to larger WAL files. I would use 1GB in your case. Are you using rsync? Over ssh? Have you done anything to mitigate connection time — such as using a persistent ssh connection (check the manual for ControlMaster). In addition, you have a huge overhead by having such small WAL files that need to be synced individually. Increase your WAL file size to something like 1GB. – Colin 't Hart Nov 15 '20 at 11:53
  • Thanks @Colin'tHart. Will consider increasing WAL files size. – rsmoorthy Nov 16 '20 at 8:09
  • @Colin'tHart Just FYI: It seems postgres 9.6 only supports WAL file sizes upto 64MB. The "./configure" option for --with-wal-segsize accepts only upto 64MB. In later versions, it does support higher values. – rsmoorthy Nov 16 '20 at 14:55

No, that is not an option and will lead to trouble.

The first and most obvious reason is that the WAL segments exists only once, even if there is a hard link to it in another directory. If anything happens to the disk with the file, you have no copy and cannot recover.

The second reason is more subtle and will lead to data corruption. When a WAL segment is archived and older than the latest checkpoint, it is not necessarily deleted. If PostgreSQL determines that it needs new WAL segments (max_wal_size or – in older versions – checkpoint_segments has not been exceeded and there is much data modification activity going on), it will recycle the WAL segment rather than deleting it.

If that happens, and PostgreSQL starts writing to the recycled WAL segment before you have managed to copy it to safety, the original WAL entries will be overwritten and are lost. If you recover using such a WAL segment, PostgreSQL will notice that there are unexpected data in the WAL and will stop recovering.

  • 1
    As I mentioned in my comments, the recycling won't happen as long as there's an active replication slot needing it. The asker is basically trying to reinvent the wheel: Postgres will already do what he wants if he copies the WAL file directly to another server in the archive command. In addition, I believe many of his performance problems will go away if he switches to a much larger WAL size: 16MB files are just silly when maintaining a 10+TB database. – Colin 't Hart Nov 15 '20 at 11:52
  • @Colin'tHart Yes, but OP didn't say anything about having replication slots. Rather than doing something complicated like that, why not write the data to S3 in archive_command? – Laurenz Albe Nov 15 '20 at 12:25
  • Thanks @LaurenzAlbe. It seemed a good idea to me (using ln), but I can see the point about possible data corruption. – rsmoorthy Nov 16 '20 at 8:13
  • You could set wal_keep_segments high to delay the recycling, but it is not a guarantee - if your sync is delayed for whatever reason, you might still hot the problem. – Laurenz Albe Nov 16 '20 at 8:15
  • 2
    Yes, I got that. wal_keep_segments is low now, But having understood it now, no point in pursuing that. Doing rsync / cp to another server and then archive to S3 is a saner option. – rsmoorthy Nov 16 '20 at 8:49

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.