pgrepup – upgrade PostgreSQL using logical replication
pgrepup is a tool written in Python for upgrading a PostgreSQL cluster to a new major version using logical replication and pglogical extension.
pgrepup simplifies the setup of 2nd Quadrant’s pglogical extension giving hints for configuring correctly source and destination pgsql clusters.
The supported versions of PostgreSQL are 9.4, 9.5 and 9.6.
pgrepup requires both a source and a destination PostgreSQL cluster.
The clusters can have been installed into the same server or on different hosts.
pgrepup doesn’t need to be installed on the clusters’ server.
It can be safely executed from a remote host that can access both pgsql clusters. In this case, it’s recommended that SSL is enabled in pg_hba.conf of both clusters because pgsql credentials are sent over the network.
pip install pgrepup
All versions of Python >= 2.7 are supported.
A pgsql cluster can be replicated and upgraded using pgrepup following these four steps:
pgrepup config : a simple wizard asks the basic configuration parameters needed by pgrepup
- Source and Destination database cluster
- Directory where to store temporary files
pgrepup check : various checks are performed both in Source and Destination cluster
- if a check fails, pgrepup outputs a hint for helping you to configure each cluster
- pgrepup setup : if the checks are all ok, this setup installs and configure pglogical in both pgsql clusters
- pgrepup start : start the replication process
After the start command, you can monitor the replication process using the command pgrepup status
The output of the status command displays an entry for each database of the source cluster along with the status reported by pglogical extension.
The status can be one of the following three values:
- initializing : pglogical is copying data from source to destination cluster
- replicating: pglogical is using pgsql logical replication to replicate and upgrade new data changed into the source cluster
- down: replication is down, check the PostgreSQL log in both clusters
After issuing the start command all databases will be in the
initializing status. During this phase pglogical background workers are executing the SQL dump of the source cluster, so it can take a while to complete.
When the dump is completed, each database status will change to replicating as the data is progressively copied from the source cluster.
When the replication is working fine, you can switch your application to the destination cluster at any moment.
Just follow these steps:
- stop your application connecting to the source cluster
- ensure no more connections are made to the source cluster
- stop replication using pgrepup stop command
- change the DSN in your application (or in your connection pooler) and point to the destination cluster
- start your application
- upgrade done! 🙂
Caveats and limits
pgrepup is still experimental. Please feel free to open an issue on github if you encounter problems.
DDL commands issued in a source cluster database are not replicated to the destination cluster. This is a limit of how pgsql logical replication works.
Use the pglogical.replicate_ddl_command SQL function on the source database in order to replicate the DDL on the destination cluster.
Be aware that, at the moment, pgrepup doesn’t handle the automatic subscription of newly created tables added using
The recommended procedure is to re-start the replication process using the stop, setup and start commands.
A solution is in the works and will be available in the next release of pgrepup.
Sequences are replicated between source and destination cluster. When the stop command is given, pgrepup uses pglogical function to do a final synchronization of each sequence value.
The pglogical function adds an artificial +1000 value to the actual sequence value: see this discussion on pglogical mailing list on google groups.
High number of databases
After issuing a start command, pglogical background workers start all simultaneously to dump the data of the source database into the destination database.
This can generate very high cpu/disk load on both clusters depending on the number of databases to replicate.
A feature that enables to limit the number of databases that are dumped concurrently is in the works.
pgrepup is licensed using GPL-3 license. Source code is available at project page on github: https://github.com/rtshome/pgrepup
Contributions are welcome!
Categories: PGDay2016, Planet Php, Planet PostgreSQL, PostgreSQL, Talks