Tag: replication
Simulating temporal tables with dblink and replication delay
On some rare occasions, I’ve been asked if Postgres supports system-versioned temporal tables – in other words, time travel on table data for your queries. As quite often with Postgres, thanks to its extensibility, I can answer: well, officially, it does not…but, you can make it work, nevertheless 🙂 Why are people even asking such […]
Setting up PostgreSQL streaming replication
There are two types of replication available in PostgreSQL at the moment: Streaming replication & Logical replication. If you are looking to set up streaming replication for PostgreSQL 13, this is the page you have been looking for. This tutorial will show you how to configure PostgreSQL replication and how to set up your database servers […]
Monitoring replication: pg_stat_replication
PostgreSQL replication (synchronous and asynchronous replication) is one of the most widespread features in the database community. Nowadays, people are building high-availability clusters or use replication to create read-only replicas to spread out the workload. What is important to note here is that if you are using replication, you must make sure that your clusters […]
Dealing with streaming replication conflicts in PostgreSQL
© Laurenz Albe 2020 Streaming replication in PostgreSQL is a well-established master-slave replication technique. It is simple to set up, stable and performs well. So many people are surprised when they learn about replication conflicts — after all, the standby server is read-only. This article describes replication conflicts and tells you how to deal with […]
Upgrading Postgres major versions using Logical Replication
By Kaarel Moppel – Some weeks ago, in the light of PostgreSQL v12 release, I wrote a general overview on various major version upgrade methods and benefits of upgrading in general – so if upgrading is a new thing for you I’d recommend to read that posting first. But this time I’m concentrating on the […]
Upgrade to a new PostgreSQL major version
Upgrade to a new major version of PostgreSQL, by Kaarel Moppel – Soon it’s that time of the year again – basically a 2nd Christmas for followers of the “blue elephant cult” if you will :). I’m, of course, referring to the upcoming release of the next PostgreSQL major version, v12. So I thought it’s […]
What hot_standby_feedback in PostgreSQL really does
Many of you out there using PostgreSQL streaming replication might wonder what this hot_standby_feedback parameter in postgresql.conf really does. Support customers keep asking this question, so it might be useful to share this knowledge with a broader audience of PostgreSQL users out there. What VACUUM does in PostgreSQL VACUUM is an essential command in PostgreSQL […]
Verifying PostgreSQL replicas
BY Kaarel Moppel – Although Postgres is a great all-around product that can’t be beaten in price-performance ratio, there’s one area where things could be a bit better. That’s in the validation of replicas, or maybe even more widely, all clusters. What’s the problem? After building a new replica there’s actually no way to tell […]
Speeding up the creation of Postgres replicas
While it’s generally well known how to create physical replicas (i.e. block level copies), using the super simple and really effortless pg_basebackup tool, not so many know what can be done, when the process is painfully slow for bigger databases. The reason for slowness can be typically linked to machine hardware (slow disks, high CPU […]
Custom replication handlers for Postgres-BDR
Although normally one should try to avoid using non-core extensions/forks of Postgres, in some rare cases it could be even worse not to use them. But as BDR project is also here to stay (as I’ve understood main functionality will be gradually integrated into Postgres core), then the following info might still be useful in […]
Number one thing to watch out for when doing Postgres Streaming Replication
On operational issues side, one thing that quite commonly floats atop when dealing with customers using Postgres, especially with smaller setups, is Streaming Replication and it’s failures. Failure here not as a bug or design failure, but more as a misunderstood “feature”, as encountered problems are mostly actually things that work as intended and with […]
Walbouncer update – a proxy for selective PostgreSQL physical replication
By Kaarel Moppel – Walbouncer was covered on the blog when it was first announced, but that was almost 2 years ago. So it’s a good time to echo it out again, especially in light of compatibility update to support PostgreSQL 9.5, addition of a helper script for bootstrapping replicas called Walbouncer-companion and making the […]