Migrating a PostgreSQL database from on-prem to Azure Database for PostgreSQL with (almost) zero downtime

Photo by https://unsplash.com/@campaign_creators

Migrating a PostgreSQL database from on-premises or virtual machines can be quite a challenge, especially if the database is starting to get moderately large. The obvious way you’d think of is to use the pg_dump tool to take a backup of your database, possibly copy the dump to a new server, and run pg_restore to restore the backup to the new server.

This of course does work great, but comes with a major drawback; if the database is even moderately large (10+ gigabytes), it can take a very *very* long time (many hours) for the backup/restore cycle to finish. This means downtime for your server, as you obviously cannot have the source database running and accepting connections and queries while you’re restoring the backup to your new target database, as any data inserted into the source database while the target is restoring would obviously be lost.

There are a few ways to solve this problem, and one is using replication. PostgreSQL supports both physical and logical replication. Physical replication replicates the database as an exact byte-for-byte copy, and that means you cannot use it for replication between different PostgreSQL major server versions, eg. the source database being version 11 and the target database being version 13.

Logical replication works using a pub/sub model. The source database is the publisher and the target database is the subscriber. The target database can also simultaneously act as a publisher, allowing for a cascading replication type scenario. Typically when using logical replication you would first import a snapshot of the source database, but this is not necessary. The only thing that is required is that the source and destination schemas are the same (as in, table definitions etc.). Once the source and target database schemas match, you can start replicating, even across PostgreSQL versions.

There are a few caveats:

• Logical replication is only supported from PostgreSQL version 10 onwards, so if you have really old PostgreSQL installations you need to upgrade those first. I will write a short blog post on this too and link it here.
• The schema and DDL commands are not replicated, so it is up to you to keep the schemas in sync. This is perhaps not a big problem while migrating to an Azure Database for PostgreSQL server, as you should probably halt development for the duration of the migration anyway and not push big changes to your system unless absolutely necessary. The obvious way to avoid getting errors when schemas are out of sync is to run the DDL commands on the target database before you run them on the source database.
• Sequence data is not replicated, and this is a big one to keep in mind. The sequence-backed id/”auto-increment” column data is of course replicated, but not the nextval()/setval() -calls on the sequences themselves. This means the sequences will be stuck at whatever value they had when initialized (eg. the value they had at the snapshot moment, or 0 if they were created form a schema-only dump, and stay like that until you manually update them. This isn’t an issue if you’re using the target database as a read-only replica to do load-balancing, but if you intend to migrate the database and start using the target database as the main database, you must set the sequence values, and this will most likely require a few minutes of downtime, but is not a big deal with a bit of careful planning.
• Large Objects (LOBs) are not replicated. There is no workaround for this, other than storing the objects in normal tables (eg. as a byte[] column).
• TRUNCATE commands are not replicated on PostgreSQL 10. From PostgreSQL 11 onwards they are supported, but care need to be taken when cascade truncating tables with foreign keys; if the truncated table has a foreign key to a table that is not in the subscription, the truncate command will fail.
• Up to and including PostgreSQL 12 only base tables are replicated. That means partitioned tables, materialized views or foreign tables are not replicated. PostgreSQL 13 supports replicating partitioned tables, but not materialized views nor foreign tables. For anything that is NOT a normal table, please consult the PostgreSQL documentation.

A few more words about the subscriber: Like we established, the subscriber is the “receiving” end of the replication. One subscriber can be subscribed to multiple publishers, even on the same database, so care must be taken not to subscribe to overlapping publications. The pretty neat side effect of this is that you can, if you wish, consolidate data from multiple different databases into one by just subscribing to multiple different publishers!

Now that we have the most of that faff dealt with, let’s dive in!

First we need to configure some stuff on the publisher side. We’ll need to create our replication user that the subscriber connects as.

db=# CREATE ROLE replicator WITH LOGIN PASSWORD 'seekrit_pass' REPLICATION;
CREATE ROLE

We then need to give access to the user. If you group your tables using schemas, you must also grant access to the schemas themselves.

db=# GRANT USAGE ON SCHEMA schema1 TO replicator;
GRANT
db=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO replicator;
GRANT
db=# GRANT SELECT ON ALL TABLES IN SCHEMA schema1 TO replicator;
GRANT

Open up postgresql.conf (for me /etc/postgresql/13/main/postgres.conf).

In it you need to configure the WAL (Write-Ahead Log) level. There are three levels; minimal, replica and logical. Minimal only includes information that’s required to recover from a database crash, replica — which is the default — includes enough information to support WAL archiving and replication and logical adds information that’s required for logical replication.

We also need to configure PostgreSQL to listen to other addresses than localhost if it isn’t already, and set some replication related settings. Here are the settings you need to change:

listen_addresses = '*'    # Addresses to listen on. "*" listens on
                          # all available addresses. You can set
                          # this to a specific address if you wish
wal_level = logical
max_wal_senders = 10      # Sets the max number of WAL sender 
                          # processes. Needs to be set to the max
                          # number of subscriber processes plus 
                          #a few more.
                          # Defaults to 10
max_replication_slots = 5 # The max number of replication slots, 
                          # which means the maximum number of
                          # subscriptions

You also need to allow remote access from the subscriber database for the replicator user, so open up pg_hba.conf and add something like

host  all  replicator  <azure_pg_db_ip>/32  md5

This allows the user “replicator” to connect to any database from the Azure Database for PostgreSQL server. Note that you’ll also need to add any firewall rules you might need to allow traffic from the server to port 5432 (or whatever port you decide to run PostgreSQL on). You will need to restart the server to reflect the changes to WAL levels in postgresql.conf.

I will assume you already have your Azure Database for PostgreSQL up and running. If not, it’s just a case of hitting “Create resource” in the Azure portal, searching for “Azure Database for PostgreSQL”, hit “Create” and pick a plan that suits your needs. Once it is up and running we need to configure a few things.

In the “Networking”-section, add the public IP of the machine running your publisher database to the list of allowed IPs. This is just to make it easier to transfer the initial schema dump.

Click on “Server parameters”, and check the following settings (you can search using the filter bar):

• max_replication_slots. Make sure it is set to at least the number of subscriptions you will use.
• max_logical_replication_workers. Make sure it is set to at least the number of subscriptions plus a few more for table synchronization workers.
• max_worker_processes: Make sure it is at least one more than max_logical_replication_workers, as they are taken from max_worker_processes.

If you wish to make the new server also be a publisher so you can set up eg. read replication later, make sure you check the wal_level, max_wal_senders and max_replication_slots too.

To get the dump from your source database, run

$ pg_dump -O -s -h <host> -p <port> -U <username> <database> > dump.sql

This will dump the structure of the specified database without Owner set (you can omit this if you have created the same users on the target database). To import the schema in the target database, run something like

$ createdb -U <yourazuredbuser> -h <yourdbservername>.postgres.database.azure.com <yourdatabasename>
$ psql -U <yourazuredbuser> -h <yourdbservername>.postgres.database.azure.com <yourdatabasename> < dump.sql

Once the schema has been imported, on the publisher database, run

db=# CREATE PUBLICATION publication1 FOR ALL TABLES;

You will need to be a database superuser to create publications that automatically publishes all tables.

On the Azure Database for PostgreSQL server, run

db=# CREATE SUBSCRIPTION subscription1 CONNECTION 'host=<yourpublisherdbip> dbname=db user=replicator password=seekrit_pass'

To confirm everything is running ok, we can query some system tables. On the publisher side, run

db=# SELECT * FROM pg_publication;

You should get back a row with the publication name, owner, and some fields indicating what we are publishing, like “puballtables”, “pubinsert”, “pubupdate”, “pubdelete” and so on. On the Azure side, you can run

db=# SELECT * FROM pg_subscription_rel;

You’ll get back a list of rows with cryptic column names, “srsubid”, “srrelid”, “srsubstate”, “srsublsn”.

srsubid | srrelid | srsubstate |   srsublsn
---------+---------+------------+--------------
  213958 |  210258 | r          | 140/DA766E20
  213958 |  210075 | r          | 140/DA767750
  213958 |  211105 | r          | 140/DA766E90
  213958 |  209628 | r          | 140/DA766F00
  213958 |  209721 | r          | 140/DA766F70

srsubid references the subscription, srrelid references the relation (table etc), srsubstate indicates the status of the subscription and srsublsn indicates the last Write-Ahead Log location that was recorded. Status can be “i” for initialize, “d” for data is being copied, “s” for synchronized and “r” for ready. The LSN field will be null for states other than s or r.

Once you are confident all data has been replicated from the publisher to the subscriber (you can double check this with eg. pg_wal_lsn_diff(), see the documentation for further info) you can set your clients to connect to the new server, drop the subscription, UPDATE ANY SEQUENCES THAT MIGHT NEED TO BE UPDATED USING EG. SELECT setval(‘my_sequence’, (SELECT MAX(id)+100 FROM mytable)) OR SIMILAR, lean back and grab a cold drink and pat yourself on the back for a job well done!

Final words

Migrating a large PostgreSQL database from on-premises (or VM) to Azure Database for PostgreSQL may initially feel like a daunting task, but with a bit of careful planning it is actually not too bad. And keep in mind that this exact technique can be used to upgrade between major version updates, especially if you don’t have access to the underlying VM and can run pg_upgrade.

As always, remember to take backups, do a few dry runs on backup data instead of live data, and, well, I can’t be held responsible if your database explodes. I’ve done this exact procedure and it worked for me, but YMMV and I may have a typo somewhere or forgotten something.