Migrating PostgreSQL standalone instance to Aurora (Amazon RDS)

It turned out to be a bit more complicated…
  1. The source database server has to be configured properly for replication
  2. DMS will not migrate your schema one to one, leaving out all the indices, column defaults, stored procedures, etc
  3. Auto incremental indices have to be fixed after migration
VPC security group have gotten very loose

#1 Let’s start with source database configuration

First things first: source database has to accept remote connections and be configured for upcoming replication task. I’ve created a new DB user named repuser with superuser role and replication privilege:

sudo -u postgres createuser -U postgres repuser -P --replication --superuser
# Amazon DMS to Aurora
host mydb_prod repuser 0.0.0.0/0 md5 # mydb_prod - source database
host replication repuser 0.0.0.0/0 md5
  • wal_level = logical
  • max_replication_slots = 5
  • max_wal_senders = 5
  • wal_sender_timeout = 0

…now to AWS console for Aurora cluster

OK. My source database (old production database) is set up and ready for DMS to take its’ data and load to Aurora cloud. Let’s jump to AWS straight to RDS section and create an instance of Aurora or cluster if you wish. Nothing fancy here, just follow the instructions. Putting a few screenshots for completeness:

Amazon Aurora Engine
DB instance details with Multi A-Z deployment

…replication instance, source and target endpoints in DMS

Alright! Now to DMS section in AWS. That’s where the job will be done. Four items to be created here:

  1. Replication instance — cloud computing power to handle the data migration, and ongoing replication.
  2. Source endpoint — connection settings for source database (that’s my production database connection)
  3. Target endpoint — connection settings for target database (that’s my newly created Aurora cluster)
  4. Task — the process that orchestrates endpoints and replication instance to get job done
dms.t2.medium took about 5 hours to migrate 15Gbs of data
Source endpoint reply on configuration of repuser in #1
Target endpoint is simple: check Select RDS instance and choose RDS instance from drop down
DMS task is the heart of data migration

#2 DMS will not transfer your schema 1 to 1, neither indices!

A bit later after another “successful” migration, I’ve noticed column defaults in Aurora did not match the ones I’ve had in my original database. More than that, my sequences and indices were gone! Here my solution for that — in the Task settings:

  1. Uncheck “Start task on create”
  2. Set “Target table preparation mode” to Truncate
  3. Set “Stop task after full load completeness” to Stop before applying cached changes.
Target table preparation mode = Truncate AND Stop task after full load completeness = Stop Before
  1. Change your config/database.yml with Aurora endpoint and credentials
  2. Run bin/rails db:create — to create database on Aurora
  3. Rub bin/rails db:schema:load — to load schema with indices to Aurora

#3 Fix broken auto-incremental sequences

Great! About 5 hours later since I’ve started my DMS Task for production database migration to Aurora the “Full load” step has completed, and now the whole process is paused and waiting for me to fix the auto-incremental indices. That’s a common problem when copying or recreating a database.

SELECT 'SELECT SETVAL(' ||
quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) ||
', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' ||
quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
FROM pg_class AS S,
pg_depend AS D,
pg_class AS T,
pg_attribute AS C,
pg_tables AS PGT
WHERE S.relkind = 'S'
AND S.oid = D.objid
AND D.refobjid = T.oid
AND D.refobjid = C.attrelid
AND D.refobjsubid = C.attnum
AND T.relname = PGT.tablename
ORDER BY S.relname;
psql -hAURORA_ENPOINT -UAURORA_USER -Atq -f reset-seq.sql -o temp.sql -dDATABASE_NAME
psql -f temp.sql -hAURORA_ENPOINT -UAURORA_USER -dDATABASE_NAME

FINAL: Update web server end-point and hot-restart web server

Wait for DMS Task to by fully synced. Once it is synced, update you config or ENV variables of web server to point to Aurora RDS, and hot-restart your web server.

CDCLatencyTarget
SELECT * FROM pg_replication_slots; # List replication slotsSELECT pg_drop_replication_slot('slot name'); # Close replication slot (they are not cleaned up automaticallypg_dump -hHOST_URL -UDATABASE_USER -dDATABASE_SCHEMA -F c -b -v -f production.dumppg_restore -hHOST_URL --clean --no-acl --no-owner -dLOCAL_DATABASE_SCHEMA -v production.dump

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store