patternsqlMinor
Is it possible to prevent data loss with Postgres WAL archiving?
Viewed 0 times
preventlosspostgreswitharchivingpossiblewaldata
Problem
As I understand from the docs, a WAL segment is archived when the segment file reaches some size or due to
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?
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?
Solution
You are correct.
The way to avoid this is to use
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
-
This makes DML statements slower, since
-
you shouldn't set
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-
This makes DML statements slower, since
COMMIT cannot succeed until it has heard back from pg_receivewal.-
you shouldn't set
synchronous_commit = remote_apply, because pg_receivewal never applies the changes, it just saves them to a WAL file.Context
StackExchange Database Administrators Q#245513, answer score: 2
Revisions (0)
No revisions yet.