Preparing PostgreSQL for migration with Database Migration Service

Quick example time:

Note that these numbers are for the DMS load only. If you already have these values set for other reasons, you need to take that into account. For example, if you’ve got max_worker_processes set to 8 to handle higher parallel querying, then you may want to add more on top to accommodate the replication to avoid impacting performance.

Case 1: You’re just doing a migration and immediately promoting the Cloud SQL instance. There aren’t any other replicas setup on the source, and you only have a single database you’re migrating over. Then you’d want to set the values to:

# Technically we only need 1 for Cloud SQL subscriber and the default is

# set to 10, so you could just leave it alone. This is just illustrating

# that you could set it lower without any issues

max_replication_slots = 3

# Equal to max_replication_slots + 1 because we'll only have one

# replica connected to the source instance

max_wal_senders = 4

# Technically we only need 1 here because we're only bringing over

# one database, but always a good practice to have one as a buffer

# just in case there's an issue so it doesn't rely on

# only the one processor.

max_worker_processes = 2

Case 2: You have a setup where your on prem local instance is already set up with 5 replication slots to handle other replication you have in place, and there are 4 databases you want to migrate to the Cloud, you would want to set the variables up like:

# 5 for existing subscribers + 4 for each of source databases since pglogical

# requires 1 slot for each database

max_replication_slots = 9

# Equal to max_replication_slots + 6 because say we have 5 existing replicas,

# and we'll be adding one more replica for DMS doing the migration

max_wal_senders = 15

# 4 databases we're migrating, plus and extra as a buffer just in case

max_worker_processes = 5

Once you have your variables all set, if you changed them in the config file, now’s the time you need to restart your PostgreSQL instance.

You can verify it worked by logging into the instance and running CREATE EXTENSION pglogical on one of the databases you’re planning on replicating over. As long as it works, you’ll need to connect to every database you want to be replicating and run that command on each one. And while you’re there on each database, you need to grant the user that you specified in the Define a source step creating the migration certain privileges. These grants need to happen on each database you’re replicating as well as the postgres database:

# on all schemas (aside from the information schema and schemas starting with “pg_”) on each database to migrate, including pglogical

GRANT USAGE on SCHEMA <SCHEMA> to <USER>

# on all databases to get replication information from source databases.

GRANT SELECT on ALL TABLES in SCHEMA pglogical to <USER>

# on all schemas (aside from the information schema and schemas starting with “pg_”) on each database to migrate, including pglogical

GRANT SELECT on ALL TABLES in SCHEMA <SCHEMA> to <USER>

# on all schemas (aside from the information schema and schemas starting with “pg_”) on each database to migrate, including pglogical

GRANT SELECT on ALL SEQUENCES in SCHEMA <SCHEMA> to <USER>

# We’re not handling it in this blog post, but if you happen to be trying to replicate

# from RDS, it would be GRANT rds_replication TO USER.

ALTER USER USER WITH REPLICATION

If your source database is earlier than version 9.6, there’s an extra step to follow because before that, PostgreSQL didn’t have replication delay monitoring by default. This is needed because DMS uses this to be able to watch if replication lag becomes too high. I’m not going to cover it in detail here since all versions before 9.6 are currently end of life, but if you need to do this, there’s information on what you need to do here.

Congratulations! Your PostgreSQL instance and database(s) are fully configured and ready for DMS! Another nicety of DMS, is when you’re all configured and ready to go, there’s a connectivity/configuration test in the UI that will tell you if everything is configured correctly or not before you hit the final “do it” button.

Remember I mentioned that I cover a lot of the nitty gritty details around connectivity between your source database and the Cloud SQL instance in the blog post I linked at the top of this post. It covers MySQL there, so I’ll add a pitfall I ran into with PostgreSQL here before I leave you.

Be sure to remember if you haven’t already, to enable your database to listen and accept connections from non-localhost locations. Two pieces to this, one, you need to change the listen_address variable in your postgresql.conf file. It defaults to localhost, which might work depending on how you’re managing connection to the database from your application, but won’t work for the migration. You also need to modify the pg_hba.conf file to grant your user for the migration access to your local database from the Cloud. If you don’t do either of these, DMS is really good about giving you clear error messages from the PostgreSQL instance telling you that you messed up. Ask me how I know.

And there we have it. Everything in one place to get you ready to go bringing your PostgreSQL database into Cloud SQL. If you have any questions, suggestions or complaints, please reach out to me on Twitter, my DMs are open! Thanks for reading.