6 ms·
Approaches to PostgreSQL Replication and Backup
- cuu508 9y agoSorry for the dumb question, but which of the 3 approaches is pg_basebackup? My understanding was/is that with pg_basebackup the new replica would only need to replay state changes since the last checkpoint, not "the entire state from the primary node—from the beginning of time". What am I missing?
- koolba 9y agoDefinitely 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.
- colanderman 9y agoA fourth option, for those in physical datacenters who do not need to run active/active, is of course to eschew network replication entirely in favor of a RAIDed JBOD shared between two or more servers. If you have the hardware, this is often the easiest to configure, and the most efficient. EDIT: I should clarify that, like the other HA solutions in the article, of course you need to couple this with a backup solution, like periodic dumps to S3, such as the article suggests.
- colanderman 9y agoThose downvoting, care to give a technical reason why I am wrong?
- koolba 9y agoThe short answer is that RAID is not a backup solution.
- colanderman 9y agoI didn't say it was? Nor is replication. But thanks for the reply, I've edited to clarify that disk sharing is an alternative to the three HA solutions in the article, not the one backup solution (which in all examples is the mundane "copy to S3").
- rosser 9y agoThe Fine Article is, indeed, light on meaningful approaches "backup", despite its prominent presence in its title.
- colanderman 9y agoIt's also light on approaches to HA (hence my – apparentely misunderstood – addition to the list). Postgres's documentation itself is much more thorough: https://www.postgresql.org/docs/10/static/different-replication-solutions.html https://www.postgresql.org/docs/10/static/different-replicat... And gives a little more insight into backup options too: https://www.postgresql.org/docs/10/static/backup.html https://www.postgresql.org/docs/10/static/backup.html
- rosser 9y agoIt also doesn't manage failover. Shared storage is a totally viable approach. (I've worked as a PostgreSQL DBA for over a decade and have built multiple shared storage-based HA environments for multiple employers.) If you mis-manage failover, you will destroy your data. This makes proper backup even more important.
- colanderman 9y agoNor does Postgres replication manage failover. Either way you need a 3rd party tool. Yep, botched failovers are bad. Of course, a botched failover between replicated DBs is no walk in the park either. In many such cases you'll still want to pull backups anyway, if there's no clear way in your domain to merge two "authoritative" databases.
- skewedlines 9y agoHmm, just a quick clarification for the first approach. It says that the replay operation may then introduce significant load on the primary node. Is this because when new nodes are added, the logs have to be dumped to the new slave nodes from the beginning, and the IO to do so is expensive, possibly interfering with normal operation of the master node?
- mixmastamyk 9y agoThat’s how I read it.