cancel
Showing results for 
Search instead for 
Did you mean: 

Moving Your EDB Postgres Database from On-Prem to an EDB Cloud Database Service Cluster

EDB Cloud Database Services (CDS) allows you the option to spin up database clusters in the cloud with different EDB Postgres versions ranging from 9.6 to 11.

 

One way of moving the data from your Ec2 instance or On-Prem instance to a CDS cluster is by using the logical backup and restore option. However, if you are planning to move your production or business critical database with minimum downtime, it’s recommended to use logical replication.

 

Using the logical replication feature is especially helpful when your intent is to move your production data to the cloud and also perform application testing. You will find that once the results are positive, it will require a minimum downtime to stop the application to let your databases sync and point the application towards the intended CDS cluster.

 

Source: Any Postgres/EDB Postgres running on Ec2 or On-Prem

Target:  CDS cluster.

 

Picture1.png

 

 

 

Picture2.png

 

 

Pre-requisites For Master/Source Database

  1. wal _level should be logical.
  2. Max_wal_sender should be set with the appropriate value.
  3. Table should have Primary/Unique Key.

 

Steps:

  1. Use Create Publication with the table on the Master server.
  2. Take a dump of the database/Table from Master.
  3. Restore the dump taken from the Master server to the Slave server.
  4. Allow the replication entry for Subscription.
  5. Create Subscription on the Slave server.
  6. Verify Logical Slot is created on the Master server and verify logical replication status.

 

A. Replicating Empty Table:

 

We will setup replication before we start loading the values in the table.

 

1.  Publication Creation:

edb=# create table migration_test(id int primary key, name char(20));

CREATE TABLE

 

CREATE PUBLICATION mypub FOR TABLE public.migration_test;

CREATE PUBLICATION

 

2. Verify Publication and Table Data Count:

 

edb=# \dRp+

                   Publication mypub

   Owner     | All tables | Inserts | Updates | Deletes

--------------+------------+---------+---------+---------

public | f          | t | t | t

Tables:

   "public.migration_test"

 

edb=# select count(*) from migration_test ;

count

-------

   10

 

3. Create Subscription:

 

edb=# create subscription mysub connection 'dbname=edb port=5444 user=enterprisedb password=postgres host=13.57.210.49' publication mypub;

NOTICE:  created replication slot "mysub" on publisher

CREATE SUBSCRIPTION

 

edb=# \dRs+

                                                              List of subscriptions

  Name    | Owner     | Enabled | Publication | Synchronous commit |                                  Conninfo

-----------+--------------+---------+-------------+--------------------+------

mysub     | enterprisedb | t       | {mypub} | off          | dbname=edb port=5444 user=enterprisedb password=postgres host=13.57.210.49



Logical Replication Slot On Master:

 

slot_name |  plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn

-----------+----------+-----------+--------+----------+-----------+--------+--

mysub     | pgoutput | logical   | 15662 | edb | f         | t | 11103 | |       1236 | 0/2CF95F8 | 0/2CF9630



Table Data Count On Subscription:

edb=# select count(*) from migration_test ;

count

-------

   10

 

B. Replicating Table from a Different Schema and Existing Data:

 

We will setup replication with existing table data.

 

1.  Publication Creation:

 

edb=# create table schemaa_table (id int primary key, name bytea);

CREATE TABLE

 

     CREATE PUBLICATION mypub_sch FOR TABLE enterprisedb.schemaa_table;

     CREATE PUBLICATION

 

2. Verify Publication and Table Data Count:

 

                 Publication mypub_sch

   Owner     | All tables | Inserts | Updates | Deletes

--------------+------------+---------+---------+---------

enterprisedb | f          | t | t | t

Tables:

   "enterprisedb.schemaa_table"

 

edb=# select count(*) from schemaa_table ;

count

-------

20001

 

edb=# select * from schemaa_table limit 5;

id |                                                                                                                              name

----+-------------------------------------------------------------------------

 1 | \x6b736462676b6a73626e6c6a676e6c6a736e6c676a626b687362676b6a6e736b62676b6a626e646b6a66676e6c6a6473626b6667626b6c6a73626b626e6b6a626e6c766e6273676a6b736264666b6a626e6c646a666e62626c3b6b6e736a626b6c6a73206c6a6e627364696862676b6a736462

676b6a6273646e6c6a6667

 2 | \x736b64666c6a736462666b6a626b6a7364626e676c6e6c6b73646e676c6b6a6e62736b6a20626c6a6e73646c6a67626b6a6c7364626b6a67626e6c73646b6e676c6b73646c62676c6b736462676c207362676c7362676c6a6e73646c6a6b676c6b6e736c6b6e676c206e6c6a7362646c6a676b

 3 | \x736b64666c6a736462666b6a626b6a7364626e676c6e6c6b73646e676c6b6a6e62736b6a20626c6a6e73646c6a67626b6a6c7364626b6a67626e6c73646b6e676c6b73646c62676c6b736462676c207362676c7362676c6a6e73646c6a6b676c6b6e736c6b6e676c206e6c6a7362646c6a676b

 4 | \x736b64666c6a736462666b6a626b6a7364626e676c6e6c6b73646e676c6b6a6e62736b6a20626c6a6e73646c6a67626b6a6c7364626b6a67626e6c73646b6e676c6b73646c62676c6b736462676c207362676c7362676c6a6e73646c6a6b676c6b6e736c6b6e676c206e6c6a7362646c6a676b

 5 | \x736b64666c6a736462666b6a626b6a7364626e676c6e6c6b73646e676c6b6a6e62736b6a20626c6a6e73646c6a67626b6a6c7364626b6a67626e6c73646b6e676c6b73646c62676c6b736462676c207362676c7362676c6a6e73646c6a6b676c6b6e736c6b6e676c206e6c6a7362646c6a676b

(5 rows)

 

3. Create Subscription:

 

Note: Before creating Subscription, verify the respective schema is present on the Subscription Server.

 

edb=# create subscription mysub_sch connection 'dbname=edb port=5444 user=enterprisedb password=postgres host=13.57.210.49' publication mypub_sch with (copy_data);

NOTICE:  created replication slot "mysub_sch" on publisher

CREATE SUBSCRIPTION

 

edb=# \dRs+

                                                              List of subscriptions

  Name    | Owner     | Enabled | Publication | Synchronous commit |                                  Conninfo

-----------+--------------+---------+-------------+--------------------+------

mysub_sch | enterprisedb | t       | {mypub_sch} | off | dbname=edb port=5444 user=enterprisedb password=postgres host=13.57.210.49

 

Logical Replication Slot On Master:

 

slot_name |  plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn

-----------+----------+-----------+--------+----------+-----------+--------+--

mysub_sch | pgoutput | logical   | 15662 | edb | f | t      | 11181 | | 1236 | 0/2CF95F8   | 0/2CF9630

 

Table Data Count On Subscription:

 

edb=# select count(*) from schemaa_table ;

count

-------

20001

 

edb=# select * from schemaa_table limit 5;

id |                                                                                                                              name

----+-------------------------------------------------------------------------

 1 | \x6b736462676b6a73626e6c6a676e6c6a736e6c676a626b687362676b6a6e736b62676b6a626e646b6a66676e6c6a6473626b6667626b6c6a73626b626e6b6a626e6c766e6273676a6b736264666b6a626e6c646a666e62626c3b6b6e736a626b6c6a73206c6a6e627364696862676b6a736462

676b6a6273646e6c6a6667

 2 | \x736b64666c6a736462666b6a626b6a7364626e676c6e6c6b73646e676c6b6a6e62736b6a20626c6a6e73646c6a67626b6a6c7364626b6a67626e6c73646b6e676c6b73646c62676c6b736462676c207362676c7362676c6a6e73646c6a6b676c6b6e736c6b6e676c206e6c6a7362646c6a676b

 3 | \x736b64666c6a736462666b6a626b6a7364626e676c6e6c6b73646e676c6b6a6e62736b6a20626c6a6e73646c6a67626b6a6c7364626b6a67626e6c73646b6e676c6b73646c62676c6b736462676c207362676c7362676c6a6e73646c6a6b676c6b6e736c6b6e676c206e6c6a7362646c6a676b

 4 | \x736b64666c6a736462666b6a626b6a7364626e676c6e6c6b73646e676c6b6a6e62736b6a20626c6a6e73646c6a67626b6a6c7364626b6a67626e6c73646b6e676c6b73646c62676c6b736462676c207362676c7362676c6a6e73646c6a6b676c6b6e736c6b6e676c206e6c6a7362646c6a676b

 5 | \x736b64666c6a736462666b6a626b6a7364626e676c6e6c6b73646e676c6b6a6e62736b6a20626c6a6e73646c6a67626b6a6c7364626b6a67626e6c73646b6e676c6b73646c62676c6b736462676c207362676c7362676c6a6e73646c6a6b676c6b6e736c6b6e676c206e6c6a7362646c6a676b

(5 rows)

 

C. Adding Table Into Publication:

 

alter publication mypub add table only public.testing;

migration=# \dRp+

                 Publication mypub

 Owner   | All tables | Inserts | Updates | Deletes

----------+------------+---------+---------+---------

postgres | f          | t | t | t

Tables:

   "public.migration_test"

   "public.testing"

 

D. Refreshing Subscription:

Alter subscription mysub refresh publication mypub with (copy_data);

Alter subscription mysub refresh publication;

 

I hope this is helpful. If you have any questions or comments, please feel free to send an email to cds-help@enterprisedb.com.

 

 

 

Version history
Revision #:
2 of 2
Last update:
‎04-18-2019 04:13 PM
Updated by:
 
Labels (1)
Contributors