Preventing Data Loss with Postgres WAL Archiving

backuppostgresqlwrite-ahead-logging

As I understand from the docs, a WAL segment is archived when the segment file reaches some size or due to archive_timeout.

So if the database crashes, all the data from the incomplete, currently active WAL segment would be lost (assuming we don't have any access to that incomplete segment in the Postgres directory).

Do I get it right and if so, is there a way to avoid this data loss?

Best Answer

You are correct.

The way to avoid this is to use pg_receivewal, which streams WAL information from the database server and persists it in WAL segment copies immediately.

By default this is asynchronous, so you could lose some split seconds in case of a crash. It you need to avoid that, you'd have to use synchronous replication with pg_receivewal, but be warned that

  1. This makes DML statements slower, since COMMIT cannot succeed until it has heard back from pg_receivewal.

  2. you shouldn't set synchronous_commit = remote_apply, because pg_receivewal never applies the changes, it just saves them to a WAL file.

Related Question