Postgresql – How archive command works in postgres

postgresqlreplication

Just wondering how does this postgres wal archive command works in replication:

rsync -z -a --contimeout=120 --partial-dir=.rsync-partial %p db-slave.sr1.net::primary-wal-archive-share/%f

what does this %p and %f mean here?

Best Answer

from docs:

The local shell command to execute to archive a completed WAL file segment. Any %p in the string is replaced by the path name of the file to archive, and any %f is replaced by only the file name. (The path name is relative to the working directory of the server, i.e., the cluster's data directory.) Use %% to embed an actual % character in the command. It is important for the command to return a zero exit status only if it succeeds

(emphasis mine)

Also take a look at setting WAL archiving

Related Solutions

Postgresql, write archive file over mount, secure against simultaneous access

Neither NFS nor cp will protect you from partial write. The usual way of mitigating problems with write atomicity is to use a temporary file and a mv after copying (or writing) is complete. You might use something like:

    test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/tmp/%f && \
    mv /mnt/server/archivedir/tmp/%f /mnt/server/archivedir/%f

Postgresql – Postgres WAL logs— point in time recovery with (time) gaps in the logs

PostgreSQL does not care when the WAL logs are generated, it just reads them sequentially on restore/recovery.

If the WAL logs were properly sent after the restart of the database, they should be restored as well.

However, if there is even a single WAL file missing for some reason (in this example, I'd guess that'd be either the last segment before the shutdown or one or more of the first ones from after the startup), which is typically either an incorrectly configured archive_command (missing error checks for example) or simply a broken archive server, the restore process cannot go past that point.

Best Answer

Related Solutions

Postgresql, write archive file over mount, secure against simultaneous access

Postgresql – Postgres WAL logs— point in time recovery with (time) gaps in the logs

Related Question