How to migrate from Open Source (PGDG) Postgres to VMware Postgres

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article covers how to migrate from Open Source (PostgreSQL Global Development Group (PGDG)) Postgres to VMware Postgres.

Note: You cannot do a simple binary replacement. 

These steps apply to version 12.x and above.
The example referred to in this article is on PGDG 12.7.1 and VMware Postgres 12.6.1.

Resolution

PGDG Postgres on a single server

Prerequisites

The following prerequisites are required before moving forward:

• Need at least two hosts - one for the existing PGDG Postgres instance (source) and one for the new VMware Postgres instance (target).
• Superuser access.
• Tablespaces - If you have additional tablespaces, create the paths to the tablespaces on the target server.
• Ensure all the extensions are matched on both instances of Postgres.

We will be leveraging a warm standby to create the new VMware Postgres instance.

OSS Postgres Server (192.168.99.103 version 12.7.1)

1. Create a replication user. You can use the Postgres user but it is best practice to create a separate replication user:

postgres=# CREATE ROLE repuser WITH REPLICATION PASSWORD 'repuser' LOGIN; -- Choose an appropriate password for the user
CREATE ROLE

2. Modify pg_hba.conf to allow a regular connection from the new server as well as a replication entry. In this example the IP address of the new server is 192.168.99.104

For example:

host    replication     repuser         192.168.99.104/32        md5

3. Modify postgresql.conf to add the following parameters to create a warm standby and to activate a synchronous standby:

wal_level=hot_standby
max_wal_senders=5
wal_keep_segments=32
listen_addresses = '*'
cluster_name = 'replica'
synchronous_standby_names='replica'

Note: Ensure max_replication_slots is set to at least 2. The default value is 10.

4. Restart Postgres to ensure all changes take effect. Transactions in the source server will wait indefinitely unless a cancel is issued or the standby is created. Until then, it will show the following warning for any write:

postgres=# create table kevin3(id int);
^CCancel request sent
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
CREATE TABLE

VMware Postgres Server (192.168.99.104 version 12.6.1)

1. Install VMware Postgres as root:

yum install -y vmware-postgres-12.6-1.el7.x86_64.rpm

Note: Do not run initdb

2. As Postgres user, run pg_basebackup:

-bash-4.2$ pg_basebackup --pgdata=$PGDATA --write-recovery-conf --slot=migrator --create-slot --host=192.168.99.103 --username=repuser --progress
Password: 
25334/25334 kB (100%), 1/1 tablespace

3. Start Postgres:

postgres -D $PGDATA > /var/lib/pgsql/data/logfile 2>&1 &
[1] 16463

4. The synchronous streaming replication should work now. You can check the following things on the primary server (source):

postgres=# SELECT slot_name, slot_type, active FROM pg_replication_slots;
 slot_name | slot_type | active 
-----------+-----------+--------
 migrator  | physical  | t
(1 row)

postgres=# 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_stat
e |          reply_time           
-------+----------+---------+------------------+----------------+-----------------+-------------+------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------------+-----------------+-----------------+---------------+----------
--+-------------------------------
 17489 |    16384 | repuser | replica          | 192.168.99.104 |                 |       52162 | 2021-05-18 22:53:46.20284+00 |              | streaming | 0/B010ED8 | 0/B010ED8 | 0/B010ED8 | 0/B010ED8  | 00:00:00.026783 | 00:00:00.027636 | 00:00:00.028246 |             1 | sync     
  | 2021-05-18 22:53:46.230722+00
(1 row)

postgres=# create table kevin4(id int);
CREATE TABLE

5. If the test table is replicated to the standby, the next step is to shutdown Postgres on the source server.

6. Once it is shutdown, you will need to promote the standby (target):

-bash-4.2$ pg_ctl promote -D $PGDATA
waiting for server to promote.... done

7. Remove the replication entries in postgresql.conf.

8. Restart Postgres.

9. Create a test table. If it's successful, then you have successfully migrated over to VMware Postgres!