Re:Approaches to PostgreSQL Replication and Backup
by koolba on Saturday February 24, 2018 @06:57AM
(#16452905)
Definitely the third one and possibly the first one depending on what’s being backed up. It seems to imply it’s just a logical pg_dump backup going to the offsite store but doesn’t clarify.
The second option is at the physical block layer. It’s not specific to any one DB but is a bit of a blunt instrument (works everywhere but lacks finesse).
The third option would involve base backups being pushed off site as well as archived WAL logs. The replicas would restore the base backups, then catch up via WAL logs. Finally you can directly link up to the master to have a live standby for read only queries. This is the approach used by the WAL-E and WAL-G backup/restore tools and is the one I generally recommend as it has the most flexibility and resilience.
The second option is at the physical block layer. It’s not specific to any one DB but is a bit of a blunt instrument (works everywhere but lacks finesse).
The third option would involve base backups being pushed off site as well as archived WAL logs. The replicas would restore the base backups, then catch up via WAL logs. Finally you can directly link up to the master to have a live standby for read only queries. This is the approach used by the WAL-E and WAL-G backup/restore tools and is the one I generally recommend as it has the most flexibility and resilience.