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 dont know if using “ln” caused this.
Question: Is it safe to use “ln” (hardlink) instead of “cp” in archive_command?