PostgreSQL: Migrate from DigitalOcean Managed DB to AWS Aurora

9 min readFeb 5, 2025

Given a recent challenge, I discovered, and many colleagues pointed out that AWS has a video on YouTube about using their Database Migration Service to move a PostgreSQL database from DigitalOcean’s managed DB service (DBaaS) to RDS. In my experience, the video didn’t work. I found a solution which does.

Bear in mind that my experience is singular. Also, be aware that my experience managing PostgreSQL (or anything, really) is entirely brute force. I’m grateful for your feedback.

The scenario

First off, I need to clarify that we are running PostgreSQL 16.

AWS Database Migration Services (DMS) requires us to set up a replication instance, which is basically an intermediate instance of PostgreSQL, to act as a bridge between our source database (DBaaS) and our target (AWS Aurora).

Note that this replication instance must be able to connect to both the source and target database instances.

If my understanding is correct, DMS then tries to set up logical replication to copy data from source to target.

The problem: superuser access

Logical replication — or at least setting up a publication for all tables in a database — is not supported on DigitalOcean’s DBaaS. I’m not sure if DMS is trying to use built-in logical replication, or whether it’s trying to use an extension, or whether there’s even a difference at this point, but it doesn’t matter: DMS tries to use superuser privileges and it gets shut down.

The lynchpin: aiven-extras

Thanks go out to the folks at aiven.io. I’ll send a shout-out specifically for their aiven-extras project, hosted on Github. What it does is allow you to set up publications for all tables without superuser access.

Our solution, then, is to replicate our data from a server where we don’t have superuser access to one where we do, then to replicate from that to AWS RDS using native PostgreSQL tools.

In this solution, we’ll use aiven-extras twice: once on the DBaaS instance, and once on a PostgreSQL server we’ll install ourselves on a generic AWS EC2 instance.

On the generic EC2 instance, we’ll also set up a native publication. We will use that to copy the database a second time to RDS. Our solution *looks* like AWS DMS, but it’s *not* AWS DMS.

Some notes: you’ll need to refresh your materialized views after using your new database. You will also need to reset your sequences. See this link and this link, respectively.

Let’s do it!

This is a big job. I assume you know what you’re doing with AWS and Linux, and that you can adapt while following along.

PostgreSQL on EC2

We’ll spin up a generic AWS EC2 instance. I chose an i7ie.2xlarge, which is a somewhat expensive EC2 instance that has NVMe-attached storage directly attached. An EBS-backed instance is probably fine, but make sure you have enough disk space to do the job. Bear in mind that you’re going to need enough space not only for your database, but for PostgreSQL Write-Ahead Logs, as well. I recommend doubling the amount of disk space you’ll need for your database storage.

While launching my instance, I attached an IAM instance profile that has AWS’s AmazonSSMManagedInstanceCore policy attached to it. This allows me to be lazy and use Session Manager to connect to my instance, in lieu of SSH. Regardless of how you log into your EC2 instance, I recommend running commands in tmux or screen, so your shells are long-lived.

The most important thing to be aware of while launching the instance is that it will need to connect to the DigitalOcean instance, and the Aurora Instance will need to be able to connect to it. However you do this is up to you, but the overall takeaway is that you need to open TCP port 5432 to your EC2 instance via its attached security group.

I used Ubuntu 24.04.

Once the EC2 instance is created, log into it and install PostgreSQL, the pglogical extension, aiven-extras, and prerequisites:

# Remember that I'm using an NVMe-backed instance.  This step is optional:
sudo mdadm --create md0 --level=1 --raid-devices=2 /dev/nvme1n1 /dev/nvme2n1
sudo mkfs.ext4 -L postgres /dev/md/md0
sudo mkdir /data
sudo cp /etc/fstab{,.bak}
sudo cat >> /etc/fstab <<EOF
LABEL=postgres /data ext4 defaults,noatime 0 1
EOF
sudo mount /data
sudo mkdir /data/postgresql

# Install postgresql
sudo apt update
sudo apt install postgresql-16 postgresql-16-pglogical postgresql-client-16 postgresql-server-dev-16

# Install aiven-extras
sudo apt install gcc make
cd /tmp
git clone https://github.com/aiven/aiven-extras.git
cd aiven-extras
make && sudo make install

# Optional; see above
sudo chown postgres:postgres /data/postgresql
sudo chmod 0700 /data/postgresql
sudo pg_dropcluster 16 main --stop
sudo pg_createcluster -d /data/postgresql 16 main

# Not optional: adjust postgresql
sudo systemctl stop postgresql.service
sudo systemctl enable postgresql.service

# 100 replication slots is a lot!
# 20GB WAL segments are huge!
# I'm using brute force.
sudo cat > /etc/postgresql/16/main/conf.d/local.conf <<EOF
wal_level = 'logical'
track_commit_timestamp = on
max_worker_processes = 10
max_replication_slots = 100
max_wal_senders = 10
max_wal_size = 20480
shared_preload_libraries = 'pglogical'
listen_addresses = '*'
EOF

sudo cat >> /etc/postgresql/16/main/pg_hba.conf <<EOF
host    all             postgres        0.0.0.0/0               scram-sha-256
EOF

sudo systemctl restart postgresql.service

sudo -u postgres psql template1
ALTER USER postgres with encrypted password 'test';

At this point, you should have a PostgreSQL server running, with the required extensions to transfer data through itself.

RDS

Run some AWS CLI commands to create a database cluster parameter group:

aws rds create-db-cluster-parameter-group \
 --db-cluster-parameter-group-name test-pg16-logical-replication-enabled \
 --db-parameter-group-family aurora-postgresql16 \
 --description "Test PostgreSQL logical replication" 

aws rds modify-db-cluster-parameter-group \
 --db-cluster-parameter-group-name test-pg16-logical-replication-enabled \
 --parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot" \
              "ParameterName=max_replication_slots,ParameterValue=30,ApplyMethod=immediate" \
              "ParameterName=log_replication_commands,ParameterValue=true,ApplyMethod=immediate" \
              "ParameterName=shared_preload_libraries,ParameterValue=\"pg_stat_statements,pglogical\",ApplyMethod=pending-reboot"

You’ll probably need to create a database subnet group, etc. I make no assumptions about your network layout.

Create an Aurora PostgreSQL-compatible RDS cluster. I used:

Version 16.6
db.r7i.2xlarge
No replica
DB cluster parameter group: test-pg16-logical-replication-enabled

The most important element of spinning up an RDS cluster that can replicate is assigning the parameter group we created, above, to it. We have to enable rds.logical_replication, and add pglogical to the shared_preload_libraries setting. See this document for more details.

DigitalOcean

I actually have no guidance, here. We are deprecating the DigitalOcean account that I’m operating in. I can say that the only thing I changed was to add my NAT gateways’ Elastic IP addresses in my VPC to the whitelist for the database resource in DigitalOcean.

Select (or load) a database

We were working with a pre-existing database that is 700GB in size. Perhaps you need to load a sample database for testing; try pagila?

Copy the schema

Our first step is to pg_dump the schema:

mkdir schema;
pg_dump \
 -h some-database.b.db.ondigitalocean.com \
 -p 25060 \
 -U doadmin \
 -W \
 --format=d \
 --file=schema \
 --compress=none \
 --schema-only \
 --clean

We restore the schema to our PostgreSQL server on EC2:

psql \
 -h my-ec2-instance \
 -p 5432 \
 -U postgres \
 -W \
 template1
create database MY_DB;

pg_restore \
 -h my-ec2-instance \
 -p 5432 \
 -U postgres \
 -d MY_DB \
 -W \
 --format=d \
 --no-owner \
 --no-privileges \
 --clean \
 --if-exists \
 schema

# NOTE: the database we are working with has tables without primary keys.  
# These will cause problems with with CREATE PUBLICATION statements
# when we set up logical replication!

# For testing purposes, you can add unique primary keys to tables before
# creating publications and subscriptions.

# psql -h my-ec2-instance -p 5432 -u postgres -d MY_DB
#
# select tab.table_schema,
#        tab.table_name
# from information_schema.tables tab
# left join information_schema.table_constraints tco
#           on tab.table_schema = tco.table_schema
#           and tab.table_name = tco.table_name
#           and tco.constraint_type = 'PRIMARY KEY'
# where tab.table_type = 'BASE TABLE'
#       and tab.table_schema not in ('pg_catalog', 'information_schema')
#       and tco.constraint_name is null
# order by table_schema,
#          table_name;
# alter table MY_TABLE add id uuid default gen_random_uuid() primary key;
# ...

Next step: Publish/Subscribe with pglogical

We set up pglogical *first*. I had issues otherwise (see https://postgrespro.com/list/thread-id/2725810). While the issues aren’t insurmountable, it’s just easier to work our way backwards from the most downstream point to our source database.

We’ll repeat the steps to upload our schema to RDS:

psql \
 -h my-rds-instance \
 -p 5432 \
 -U postgres \
 -W \
 template1
create database MY_DB;

pg_restore \
 -h my-rds-instance \
 -p 5432 \
 -U postgres \
 -d MY_DB \
 -W \
 --format=d \
 --no-owner \
 --no-privileges \
 --clean \
 --if-exists \
 schema

We add the pglogical extension to the EC2 instance. We create a node, then create a publication.

psql -h ip-10-204-121-41.ec2.internal -U postgres -p 5432 -W -d MY_DB
# maybe not required:
create extension if not exists pglogical;
select pglogical.create_node(node_name := 'ec2', dsn := 'host=ip-10-204-121-41.ec2.internal port=5432 dbname=MY_DB user=postgres');
# definitely required:
create publication pglogical_alltables_2025020401 for all tables;

On the RDS instance, we do the same: enable the pglogical extension. Create a node. But instead of a publication, we create a subscription.

psql -h my-rds-instance.us-east-1.rds.amazonaws.com -U postgres -p 5432 -W -d MY_DB
# maybe not required
create extension if not exists pglogical cascade;
select pglogical.create_node(node_name := 'rds', dsn := 'host=my-rds-instance.us-east-1.rds.amazonaws.com port=5432 sslmode=require dbname=MY_DB user=postgres password=XXX');
# definitely required
create subscription pglogical_alltables_2025020401 connection 'host=ip-10-204-121-41.ec2.internal port=5432 sslmode=require user=postgres password=XXX dbname=MY_DB' publication pglogical_alltables_2025020401;

Connect to the EC2 instance and run select * from pg_stat_replication;

psql -h localhost -p 5432 -U postres -d MY_DB
select * from pg_stat_replication;

pid  | usesysid | usename  |            application_name             |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   |  sent_lsn   |  write_lsn  |  flush_lsn  | replay_lsn  | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time
------+----------+----------+-----------------------------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+-------------+-------------+-------------+-------------+-----------+-----------+------------+---------------+------------+-------------------------------
 9309 |       10 | postgres | pglogical_alltables_2025020401          | 10.204.122.31 |                 |       21386 | 2025-02-04 23:56:16.461253+00 |              | streaming | 61/5BE53FD0 | 61/5BE53FD0 | 61/5BE53FD0 | 61/5BE53FD0 |           |           |            |             0 | async      | 2025-02-05 00:04:47.402679+00
(3 rows)

Replication is running.

Enable replication with aiven-extras

DigitalOcean’s Managed DB service supports the aiven-extras extension, so let’s enable that and create a publication for all tables:

psql \
 -h some-database.b.db.ondigitalocean.com \
 -p 25060 \
 -U doadmin \
 -W \
 MY_DB

create extension if not exists aiven_extras;
select * from aiven_extras.pg_create_publication_for_all_tables('alltables_2025020401', ('INSERT,UPDATE,DELETE'));

On the EC2 instance, let’s also enable aiven-extras and create a subscription to the publication we just created:

psql \
 -h my-ec2-instance \
 -p 5432 \
 -U postgres \
 -W \
 MY_DB

create extension aiven_extras cascade;
select * from aiven_extras.pg_create_subscription('alltables_2025020401', 'host=some-database.b.db.ondigitalocean.com port=25060 user=doadmin password=XXX sslmode=require dbname=MY_DB', 'alltables_2025020401', 'slot', TRUE, TRUE);

Assuming that your EC2 instance can connect to the DigitalOcean instance, replication should start.

Testing/troubleshooting

I have a lot of work to do here. Many of the aiven_extras functions return empty results, even if there’s replication activity going on, but we can use the following PostgreSQL views:

pg_stat_replication

pg_subscription

pg_replication

pg_replication_slots

I’ve found that the postgresql server logs, as well as select * from pg_stat_activity can also be helpful. Running iftop also clued me in, because I could see when network activity dropped.

Validation

After replication catches up, I found it’s helpful to count rows in tables on both the source and the target:

sudo apt install colordiff
cat > rowcount.sql <<EOF
WITH    tbl AS (
 SELECT Table_Schema, Table_Name
 FROM   information_schema.Tables
 WHERE  Table_Name NOT LIKE 'pg_%'
        AND Table_Schema IN ('public')
)
SELECT  Table_Schema AS Schema_Name
,       Table_Name
,       (xpath('/row/c/text()', query_to_xml(format(
          'SELECT count(*) AS c FROM %I.%I', Table_Schema, Table_Name
        ), FALSE, TRUE, '')))[1]::text::int AS Records_Count
FROM    tbl
ORDER   BY Table_Name;
EOF

psql \
 -h some-database.b.db.ondigitalocean.com \
 -p 25060 \
 -U doadmin \
 -W \
 -f rowcount.sql
 MY_DB > orig.txt

psql \
 -h my-rds-instance.us-east-1.rds.amazonaws.com \
 -U postgres \
 -p 5432 \
 -W \
 -f rowcount.sql \
 MY_DB > copied.txt

diff --unified orig.txt copied.txt | colordiff

Additional sources

AWS’s Database Migration guide: https://docs.aws.amazon.com/dms/latest/sbs/chap-manageddatabases.postgresql-rds-postgresql-full-load-publisher.html

AWS’s Prescriptive Guidance on PostgreSQL: https://docs.aws.amazon.com/prescriptive-guidance/latest/tuning-postgresql-parameters/replication-parameters.html

Caveats / Feedback Sought

This article is brand new. I’ll update it while I check up on replication progress and I’ll sort out the need for the pglogical extension if I get time.

I’m pretty sure I haven’t captured roles that need to be recreated on the target database. Be aware of those.

[UPDATE: it’s not possible to dump roles from a DigitalOcean managed DB, either. At least not with the passwords. I’ve had to run pg_dumpall --roles-only --no-role-passwords and I’ll have to alter role passwords later.]

[UPDATE 2: I’m reversing the order of operations for setting up pglogical and aiven-extras. It’s best to have the replication set up downstream, and work your way up. See https://postgrespro.com/list/thread-id/2725810.]

[UPDATE 3: You’ll need to refresh your materialized views after using your new database. You will also need to reset your sequences. See this link and this link, respectively.]

I’m sure there might be corner cases. Please feel free to drop comments!