Migrate from PostgreSQL on Amazon EC2 to Amazon RDS for PostgreSQL using pglogical - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resources

Migrate from PostgreSQL on Amazon EC2 to Amazon RDS for PostgreSQL using pglogical

Created by Rajesh Madiwale (AWS)

Environment: PoC or pilot

Source: Amazon EC2

Target: Amazon RDS for PostgreSQL

R Type: Replatform

Workload: Open-source

Technologies: Migration; Databases

AWS services: Amazon RDS

Summary

This pattern outlines steps for migrating a PostgreSQL database (version 9.5 and later) from Amazon Elastic Compute Cloud (Amazon EC2) to Amazon Relational Database Service (Amazon RDS) for PostgreSQL by using the PostgreSQL pglogical extension. Amazon RDS now supports the pglogical extension for PostgreSQL version 10.

Prerequisites and limitations

Prerequisites

Product versions

  • PostgreSQL version 10 and later on Amazon RDS, with the features supported on Amazon RDS (see PostgreSQL on Amazon RDS in the AWS documentation). This pattern was tested by migrating PostgreSQL 9.5 to PostgreSQL version 10 on Amazon RDS, but it also applies to later versions of PostgreSQL on Amazon RDS.

Architecture

Data migration architecture

Data migration architecture for PostgreSQL on Amazon RDS

Tools

Epics

TaskDescriptionSkills required
Create an Amazon RDS PostgreSQL DB instance.

Set up a PostgreSQL DB instance in Amazon RDS. For instructions, see the Amazon RDS for PostgreSQL documentation.

DBA
Obtain a schema dump from the source PostgreSQL database and restore it into the target PostgreSQL database.

  1. Use the pg_dump utility  with the -s option to generate a schema file from the source database.

  2. Use the psql utility with the -f option to load the schema into the target database.

DBA
Turn on logical decoding.

In the Amazon RDS DB parameter group, set the rds.logical_replication static parameter to 1. For instructions, see the Amazon RDS documentation.

DBA
Create the pglogical extension on the source and target databases.

  1. Create the pglogical extension on the source PostgreSQL database:

    psql -h <amazon-ec2-endpoint> -d target-dbname -U target-dbuser -c "create extension pglogical;"

  2. Create the pglogical extension on the target PostgreSQL database:

    psql -h <amazon-rds-endpoint> -d source-dbname -U source-dbuser -c "create extension pglogical;"
DBA
Create a publisher on the source PostgreSQL database.

To create a publisher, run:

psql -d dbname -p 5432 <<EOF
SELECT pglogical.create_node( node_name := 'provider1', dsn := 'host=<ec2-endpoint> port=5432 dbname=source-dbname user=source-dbuser' );
EOF
DBA
Create a replication set, add tables and sequences.

To create a replication set on the source PostgreSQL database, and to add tables and sequences to the replication set, run:

psql -d dbname -p 5432 <<EOF
SELECT pglogical.replication_set_add_all_tables('default', '{public}'::text[],synchronize_data := true);
EOF
DBA
Create a subscriber.

To create a subscriber on the target PostgreSQL database, run:

psql -h <rds-endpoint> -d target-dbname -U target-dbuser  <<EOF
SELECT pglogical.create_node(
    node_name := 'subscriber1',
    dsn := 'host=<rds-endpoint> port=5432 dbname=target-dbname password=postgres user=target-dbuser'
);
EOF
DBA
Create a subscription.

To create a subscription on the target PostgreSQL database, run:

psql -h <rds-endpoint> -d target -U postgres  <<EOF
SELECT pglogical.create_subscription(
 subscription_name := 'subscription1',
 replication_sets := array['default'],
    provider_dsn := 'host=<ec2-endpoint> port=5432 dbname=<source-dbname> password=<password> user=source-dbuser'
);
DBA
TaskDescriptionSkills required
Check source and target databases.

Check the source and target databases to confirm that data is being replicated successfully. You can perform basic validation by using select count(1) from the source and target tables.

DBA

Related resources