Back to articles list
- 6 minutes read

PostgreSQL Database Replication

Motivation

The main idea behind any type of database replication is to be able to tell your database to report to someone (usually to another database) via any of the mechanisms described below whenever changes to your main database have been made. Those changes will then be processed and applied to this second database, and if everything goes well, you will end up having an up to date replica of your main database running somewhere else. This can be useful for many reasons.

Common Applications

High availability and load balancing are two typical uses for replication.

High Availability

High availability refers to the ability to have an updated copy of your main database at all times. In case of failure of your main server, you could always switch over to your backup, which is known to be up to date. The procedure of promoting a replica into a main database is commonly known as failover, or failover procedure, and there are many ways to accomplish this. The decision as to which mechanism to use will always depend on your specific needs.

The fail over-procedure normally consists of two basic steps. First, to re-establish a physical connection between the client and the backup database, and secondly, promoting the backup database into a new master. The specific procedures for achieving this will depend on the final replication method that is used.

Load Balancing

Load balancing refers to the ability to distribute the load -queries- between the main database and the replicas. Having a single read-write main database, with multiple read-only replicas, is a common setup and a good practice. With this configuration you can have all your read-only queries run against the replicas instead of the main database, effectively decreasing its workload.

Products

PostgreSQL users have many alternatives when it comes to replication. The following products, however, have always been able to cope with all my replication needs.

PostgreSQL Log File Shipping Replication

Since version 9.0, PostgreSQL has been providing a native replication solution. This solution consists of the storage and later playback of WAL logs. WAL stands for Write Ahead Log and refers to internal log files that contain information on past and future disk operations. These log files basically hold a list of actions to be performed and a status describing if they have been finished or not. In the event of a system crash, some of the operations present in the WAL may not have been tagged as complete. This information is useful for the database to know exactly where it left, which operations were left unfinished and more importantly come up with a strategy to recover from that failure. To achieve this, the database will try to reconstruct its last steps, or at least to rollback the changes up to the closest certain, stable, point.

WAL logs have been there way before replication, but only as of version 9.0 have they started to be transmittable, and re-playable by other instances. These log files, replayed on another database, would effectively move the second database to the same state as the original. Not only that, but once you have these log files, you can also do point-in-time recoveries, just by re-playing only to a certain moment, since you do not need to replay the whole file.

The only things you have to configure on both servers is where the log files will be stored, which is normally a shared folder, and maybe some other minor configuration values, but that is basically it. For more information on postgres file shipping replication, please go here.

Slony-I

This replication engine has been the de facto replication solution until log shipping was introduced in version 9.0. Why would I need any other solution if I can do all that with what is offered out of the box with PostgreSQL, you may ask. Well, the thing is that with PostgreSQL log shipping replication (at least up to the writing of this article) you still don't have much control over what gets replicated and what does not. You can replicate your whole database. Or not. But there is no middle ground. So, unless you are trying to replicate your full database (which may be exactly what you want), you will need to find some other alternative, and this is where Slony-I comes into play.

We’ve already covered a basic Slony-I setup in another article. You can go here for more details on its architecture and a step by step guide on how to configure it.

pgPool

pgPool is a product mainly focused on providing connection pooling, but it can also offer replication and load balancing services. It basically works as a middleware between the clients and the different servers. From the client’s point of view, you are just connecting to a single database. From the server’s point of view, all connections are made from pgPool. In the middle, pgPool decides to which of the many servers it has access to, it will plug your client’s connection.

Considering that all queries go through pgPool, it has all the knowledge it needs to replicate data on the fly. That is, imagine you have two backed nodes, one configured as a master, the other one as a slave. Every time a client inserts a new row in a table, the row can also be inserted at the slave. In the same manner, once two nodes are known to be replicated, a select could be theoretically fetched from any of them. And in the event of a failure of the master node, this could be easily detected by pgPool and automatically and transparently promote the slave as the new master, and redirect clients to go to this new server, without even disconnecting the client, all the work would be done internally by pgPool. The slave will start to service all the incoming connections, until the master node is ready again. It can also work in combination with file shipping replication, letting PostgreSQL handle the replication, while pgPool can be configured to perform a customizable failover procedure. More information on pgPool can be found here.

Which One Should I Use?

After introducing all these alternatives, you may be facing the big question: which one is the best and which one should I use. And I’m sorry to say that there is no generic answer for that. It will greatly depend on your specific needs. But I think I’ve said that already, haven’t I? =)

go to top