Migrate PostgreSQL database from AWS EC2 to AWS RDS PostgreSQL via AWS DMS

By · · · · ·

 


                            
                                
                                    In this article we will see how we can migrate the PostgreSQL database hosted on AWS EC2 instance to AWS RDS database via DMS(Database Migration Service). 

 Source Database : PostgreSQL 14 database hosted on AWS EC2 UBUNTU 22.04 LTS instance.
Target  Database :  AWS RDS PostgreSQL 14.1R1

STEP 1:  Create DMS Replication Instance

            Go to AWS DMS Dashboard and click replication instance.



For Demo purpose select the below parameters and click create replication instance option. 
  • Instance Class            : dms.t3.micro
  • High Availability         : Dev or Test Workload (Single-AZ)
  • Allocated storage (GiB)   : 20
  • Virtual private cloud(VPC): Default VPC
  • Public accessible         : Yes



STEP 2:  Configure EC2 Instance to accept connection from  DMS replication instance

Login to the EC2 ubuntu machine and make the below changes in postgresql.conf file to accept remote connection.

postgres@ip-172-31-1-243:~$ nano /etc/postgresql/14/main/postgresql.conf

listen_addresses = '*' 



Make the below changes in the pg_hba.conf file to accept connection from dms instance.

postgres@ip-172-31-1-243:~$ nano /etc/postgresql/14/main/pg_hba.conf



Restart the postgresql server to make changes into effect

postgres@ip-172-31-1-243:~$ systemctl restart postgresql.service


Under EC2 security group add the ip address of the dms instance to allow connection



STEP 3: Configure Postgresql RDS instance security group to accept connection from DMS instance 

Under rds security group add the ip address of the dms instance to allow connection



Create the database in rds instance.

postgres@ip-172-31-1-243:~$ psql -U postgres -h pgrds.csvrkcow4cbn.ap-south-1.rds.amazonaws.com
psql> create database dvdrental;


STEP 4: Create source and target endpoint for the DMS 

a. Source endpoint

 Create Source endpoint for the EC2 PostgreSQL instance and test the connectivity using below parameters.

Endpoint Identifier     :  sourceendpoint  #give your own name
Source Engine           :  PostgreSQL
Server Name             : ec2-13-127-137-212.ap-south-1.compute.amazonaws.com
Database Name           : dvdrental
Port No                 : 5432
Test Endpoint Connection: Run Test




Target endpoint


 Create Target endpoint for the RDS PostgreSQL instance and test the connectivity using below parameters.

Endpoint Identifier     : targetendpoint  #give your own name
RDS Instance            : pgrds.csvrkcow4cbn.ap-south-1.rds.amazonaws.com
Target Engine           : PostgreSQL
Database Name           : dvdrental
Port No                 : 5432
Test Endpoint Connection: Run Test



STEP 5: Do Precheck of the Source PostgreSQL Database and compare it  with Target after Migration completed


  • Find size of the database
           Postgres# SELECT pg_size_pretty( pg_database_size('dvdrental') );

  • List Databases in source
            Postgres# \l+
     Postgres# SELECT datname FROM pg_database;
  • List all schemas on source
            Postgres# SELECT nspname FROM pg_catalog.pg_namespace;

  • Find different types of objects count like table, view, index, sequence on source server.
            Postgres# SELECT
n.nspname as schema_name
,CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
END as object_type
,count(1) as object_count
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','i','S','s')
GROUP BY  n.nspname,
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
END
ORDER BY n.nspname,
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
END;


  •  Listing users

        Postgres# SELECT usename AS role_name,
CASE
WHEN usesuper AND usecreatedb THEN
   CAST('superuser, create database' AS pg_catalog.text)
WHEN usesuper THEN
    CAST('superuser' AS pg_catalog.text)
WHEN usecreatedb THEN
    CAST('create database' AS pg_catalog.text)
ELSE
    CAST('' AS pg_catalog.text)
END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;

  • Find all extension installed.
        Postgres# SELECT * FROM pg_available_extensions;

STEP 6: Create Migration Task in DMS

  1. DMS
  2. Database migration tasks
  3. Create database migration task















STEP 6: Once Migration Completed perform Post check

A. Source 




B. Target 



Thus we reached the end of this article.

Hope you liked the content (:

Please provide your collaborative ideas , suggestion and valuable feedback’s.

Keep Learning ! Keep Sharing!

No comments:

Post a Comment