Postgresql – How to correctly use %p and %f in postgresql.conf to make the Write Ahead Log

postgresqlwrite-ahead-logging

I am attempting to modify postgresql.conf (Postgre SQL 10.0 under Ubuntu 18.04 VPS) to setup the Write Ahead Log (WAL).

AIUI, the commands should be:

wal_level = replica             # minimal, replica, or logical
archive_mode = on               # enables archiving; off, on, or always
                                # (change requires restart)
archive_command = 'cp %p /var/lib/postgresql/10/main/pg_wal/%f'          
archive_timeout = 600           # force a logfile segment switch after this
                                # number of seconds; 0 disables

AIUI, %p references the database file (to be archived) and %f references the filename that will contain the copy of said database file.

In this case: I am saying to copy every 5 minutes the database file to the /pg_wal directory.

However, this is clearly not correct as the error log files shows:

cp: 'pg_wal/000000010000000000000002' and '/var/lib/postgresql/10/main/pg_wal/000000010000000000000002' are the same file
2020-02-16 21:01:05.857 UTC [20707] LOG:  archive command failed with exit code 1
2020-02-16 21:01:05.857 UTC [20707] DETAIL:  The failed archive command was: cp pg_wal/000000010000000000000002 /var/lib/postgresql/10/main/pg_wal/000000010000000000000002

So I am not sure how %p and/or %f are supposed to be used. If someone could present the syntax, I would be most grateful.

Best Answer

You need to copy the WAL segment somewhere else, not to its original location.

“Somewhere else” in this context means different storage on a different physical machine. So cp is only a good method of archival if the destination is an NFS mount or similar. Otherwise, scp would be better.

You have to make as sure as you can that any trouble that befalls the original database won't affect the WAL archives.

It is also a good idea to test if the cp would overwrite anything, like the example in the documentation does. Otherwise, if two clusters archive to the same directory by mistake, you could end up with data loss.

Related Question