An expressive oil painting of an elephant jumping between two planets

Intro

In a company’s lifetime, growth also means being able to easily migrate between different systems. However, this can be challenging when dealing with stateful systems like PostgreSQL.

In Treatwell we migrated from bare-metal servers to AWS cloud infrastructure, as detailed in a previous story:

During that migration, we followed a lift-and-shift approach, changing the base Kubernetes platform while keeping the same structure and machine performance.
This did not involve migrating to cloud-managed services.

After this initial challenge, we gradually migrated to cloud-managed services, achieving a “stateless” EKS cluster — a critical step for our blue/green strategy during any cluster upgrade.

During this process, the main PostgreSQL cluster became the most delicate stateful workload to move due to its critical role in our platform and its substantial size.

Disclaimer: To use the built-in logical replication as explained in this article, PostgreSQL ≥ 10 is required on the source system.

Context

Let’s briefly look at the context, explaining both source and destination systems.

Source K8S PostgreSQL

The source system is a PostgreSQL cluster running on Kubernetes, with one primary and two read-only replicas.

It operates in an AWS EKS cluster, and each instance runs on a r6i.8xlarge node, using EBS disks with io2 class on the primary and gp3 class on readers (this storage setup has some limitations, but it’s a good balance between costs and performances).

PostgreSQL is running version 14.5.

Destination Aurora PostgreSQL

After much research, we chose Aurora over RDS because it would better fit our needs.

We selected the most similar database instance, db.r6g.8xlarge, with the nearest PostgreSQL version, 14.8.

We opted for Aurora I/O Optimised to skip the storage topic and save costs compared to provisioned IOPS.

Intra-system communication

Both the source and destination systems run in the same AWS account, requiring minimal tweaks to Security Groups for proper communication.

If direct communication is not possible, an alternative is discussed in the article about how we used VPNs to migrate other systems to AWS.

Migration possibilities

For a PostgreSQL — PostgreSQL migration in RDS/Aurora, AWS provides several options:

• pg_dump & pg_restore
• Managed tools such as AWS DMS
• Hybrid approach combining the two options above

Due to the database size and the importance of our product to provide optimal uptime, we discarded thepg_dump & pg_restoreoption as it would result in more than 10 hours of downtime.
Although we explored AWS DMS, encountering multiple issues and an extensive limitations list led us to consider another approach.

The AWS DMS system uses the PostgreSQL CDC (change data capture) feature provided by native logical replication. So, we explored using the built-in logical replication for the entire process, with fewer restrictions than AWS DMS.
All of them are easily workaround-able, such as:

• DDL commands are not replicated: we will block those changes in our codebase for the migration period
• Sequence data is not replicated: we will align all available sequences just before switching to the new database
• Large objects are not replicated: we don’t use them

DMS uses it only to keep the data up to date, but the Logical Replication allows to do a full data migration from scratch, keeping the right constraints on tables and keeping monitored the process for the entire duration.

Execution

In this section, we’ll cover each migration step as we followed them.

Setting right parameters

The logical replication requires wal_level = 'logical' set on the source system.
To speed up the initial data migration, we increased some settings related to replication itself: max_replication_slots = 15, max_wal_senders = 16 and max_worker_processes = 14.

These settings can be checked at runtime with:

SELECT name, setting 
  FROM pg_settings 
WHERE name IN (
      'wal_level', 
      'max_worker_processes', 
      'max_replication_slots', 
      'max_wal_senders'
);

Since a database migration is not something easy and it obviously brings risks, It’s recommended to mirror the source PostgreSQL configuration in the destination system.

Ensure connectivity

Within the same K8S Namespace as the source Postgres instance, a new deployment (toolbox) was created, and connectivity between the two systems was checked.

Expose the source database to Aurora

The source database needs to be exposed and directly accessible from the target cluster. We created a specific Kubernetes service for this migration:

apiVersion: v1
kind: Service
metadata:
  name: database-nodeport-service
  namespace: database-namespace
spec:
  selector:
    postgres/cluster: database
    postgres/role: primary
  ports:
    - name: p5432
      protocol: TCP
      port: 5432
      targetPort: 5432
      nodePort: 30432
  type: NodePort
  sessionAffinity: None
  externalTrafficPolicy: Cluster
  internalTrafficPolicy: Cluster

We then edited the AWS security group, allowing Aurora to reach the source Postgres instance in Kubernetes.
This can be done by adding an Inbound Rule for the 30432 port, with a meaningful description such as “Allow Aurora to access K8S PG instance”:

aws ec2 authorize-security-group-ingress \
--group-id sg-1234567890abcdef0 \
--ip-permissions '[
{
  "IpProtocol": "tcp", 
  "FromPort": 30432, 
  "ToPort": 30432, 
  "IpRanges": [
    {
      "CidrIp": "sg-018a76217e6911189", 
      "Allow Aurora to access K8S PG instance"
    }
  ]
}]'

We then used this command from the toolbox pod to check the connectivity (where 10.x.x.x is the node IP, in our case the EC2 instance):

psql -U postgres -h 10.x.x.x -p 30432

Destination database creation

The Aurora cluster was already up & running on AWS, so we created the required database:

psql -U postgres -h 10.x.x.x -p 30432 -c "CREATE DATABASE database_name";

The database is empty, and since logical replication won’t replicate the schema, we did it manually. From the toolbox pod, we copied the current schema:

pg_dump database \
  -U postgres \
  -h local-database-service \
  --schema-only > schema.sql

and uploaded it into Aurora:

psql -U postgres -h new-database.example.org -d database < schema.sql

Logical replication, publication and subscription

With the two systems ready to communicate, we created a publication on the source database:

psql -U postgres \
     -h local-database-service \
     -d database \
     -c "CREATE PUBLICATION my_publication1 FOR ALL TABLES;

Next, we moved to the destination database, creating the relative subscription:

psql -U postgres \
     -h new-database.exaple.org \
     -d database \
     -c "CREATE SUBSCRIPTION my_subscription1 
          CONNECTION 'host=10.x.x.x 
                    port=30432 
                    dbname=database 
                    user={user} 
                    password={PASSWORD}' 
          PUBLICATION my_publication1;"

This creates a subscription to the recently created publication, along with a replication slot. If everything went well, the result of the query should inform you with a message similar to: NOTICE: created replication slot "my_subscription1" on publisher.

Please note that the crucial flag copy_data is enabled by default, so replication will automatically start copying all the data to the destination.

Check replication status

Once the subscription was created, we started checking the replication status. Here are a couple of useful queries:

-- CHECK REPLICATION SLOTS (on source)
SELECT * FROM pg_stat_replication WHERE application_name != 'walreceiver';

-- CHECK REPLICATION STATE (on the destination)
-- srsubstate code: i = initialize, d = data is being copied, f = finished table copy, s = synchronized, r = ready (normal replication)
SELECT srsubstate, COUNT(1) 
FROM pg_catalog.pg_subscription_rel subs 
JOIN pg_catalog.pg_class relmap ON subs.srrelid = relmap.oid 
GROUP BY 1;
-- ENSURE ALL TABLES ARE IN 'r' STATE
SELECT subs.*,relmap.relname 
FROM pg_catalog.pg_subscription_rel subs 
JOIN pg_catalog.pg_class relmap ON subs.srrelid = relmap.oid
WHERE srsubstate <> 'r';

Switch to the new PostgreSQL

When the logical replica finished moving data and was synchronized with the primary instance (check the queries above!), we were ready to do the actual migration.

Before it, it’s advisable to ANALYZE the most important tables to let PostgreSQL be ready to start with up-to-date statistics:

-- CHECK ANALYZE STATUS
SELECT schemaname, relname, last_autoanalyze, last_analyze 
FROM pg_stat_all_tables 
WHERE schemaname = 'public' 
ORDER BY 3 ASC;

-- FORCE ANALYZE IN A SINGLE TABLE
ANALYZE my_table;

During this process, we also switched the traffic for read-only replicas to Aurora, as they didn’t require additional work to use them.

As mentioned earlier, as a limitation of logical replication, sequences are not replicated. So, a script is needed to align them. This command will prepare a SQL query to do it:

psql -Atq -U postgres -h new-database.example.com -d database -c "SELECT 
    'SELECT SETVAL(' ||
       quote_literal(quote_ident(sequence_namespace.nspname) || '.' || quote_ident(class_sequence.relname)) ||
       ', COALESCE(MAX(' ||quote_ident(pg_attribute.attname)|| '), 1) ) FROM ' ||
       quote_ident(table_namespace.nspname)|| '.'||quote_ident(class_table.relname)|| ';'
FROM pg_depend 
    INNER JOIN pg_class AS class_sequence
        ON class_sequence.oid = pg_depend.objid 
            AND class_sequence.relkind = 'S'
    INNER JOIN pg_class AS class_table
        ON class_table.oid = pg_depend.refobjid
    INNER JOIN pg_attribute 
        ON pg_attribute.attrelid = class_table.oid
            AND pg_depend.refobjsubid = pg_attribute.attnum
    INNER JOIN pg_namespace as table_namespace
        ON table_namespace.oid = class_table.relnamespace
    INNER JOIN pg_namespace AS sequence_namespace
        ON sequence_namespace.oid = class_sequence.relnamespace
ORDER BY sequence_namespace.nspname, class_sequence.relname;" -o set_sequencies.sql

At this point, we shut down the PGBouncer connected to the old primary instance.
The downtime started.
First, we waited a little bit to let all updates be propagated by the logical replication.

Then, we aligned the sequences on Aurora, using the script we created earlier:

psql -U postgres \
     -h new-database.exaple.org \
     -d database \
     -f set_sequencies.sql

To check that sequences are properly aligned, here are some useful SQL commands:

SELECT last_value FROM my_table_id_seq;
SELECT max(id) FROM my_table;

If everything is fine, the new database is ready to serve the traffic.

We switched the PGBouncer to the new database endpoint, and the system was up again.

We then dropped the logical replication subscription:

psql -U postgres \
     -h new-database.example.org \
     -d database 
     -c "DROP SUBSCRIPTION my_subscription1;"

In our case, the downtime lasted 4 minutes during the night, only a few web requests were lost, and failed asynchronous jobs were re-enqueued.

Clean up

After the migration, we shut down the old database cluster. It was as if it never happened to our platform.

Credits

A huge thank you to Alessandro Rizzo, who helped me in this adventure (and in all the pasts!).