How to change data directory and port of an existing Master on Tanzu Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

Normally, changing the MASTER_DATA_DIRECTORY or the Master port requires changing some entries in the catalog tables. This article describes both methods:

1. How to make these two changes using gpinitstandby instead of issuing direct updates to the catalog.
2. Changing the port by changing gp_segment_configuration

Environment

Product Version: 6.20

Resolution

This article applies to VMware Greenplum Database (GPDB) 5.x and 6.x.

Method 1:

IMPORTANT NOTE: This process involves initializing and activating the Master a couple of times. It does require some cluster downtime.

Follow the steps mentioned below to change the data directory and port of an existing Master:

1.  Check the current master settings in gp_segment_configuration. Verify this with your environment variables and postgresql.conf values:

gpadmin=# select * from gp_segment_configuration where content = -1;
 dbid | content | role | preferred_role | mode | status | port | hostname | address |                 datadir                 
------+---------+------+----------------+------+--------+------+----------+---------+-----------------------------------------
    1 |      -1 | p    | p              | n    | u      | 3005 | gpdb2-m  | gpdb2-m | /data/master/gp_6.20.3_20220517205720-1
(1 row)

gpadmin=# show port;
 port 
------
 3005
(1 row)

[gpadmin@gpdb2-m ~]$ echo $PGPORT
3005

[gpadmin@gpdb2-m ~]$ echo $MASTER_DATA_DIRECTORY
/data/master/gp_6.20.3_20220517205720-1

In 5.x, you will have to reference the pg_filespace_entry table:

gpadmin=# select * from pg_filespace_entry where fsedbid = 1;
 fsefsoid | fsedbid |               fselocation               
----------+---------+-----------------------------------------
     3052 |       1 | /data/master/gp_5.29.1_20211122171624-1

[gpadmin@gpdb2-m ~]$ echo $MASTER_DATA_DIRECTORY
/data/master/gp_5.29.1_20211122171624-1

2. Verify the directory and port used by the postmaster process:

[gpadmin@gpdb2-m ~]$ ps -ef | grep "postgres -D" | grep -v grep
gpadmin  10951     1  0 21:37 ?        00:00:00 /usr/local/6.20.3/greenplum-db-6.20.3/bin/postgres -D /data/master/gp_6.20.3_20220517205720-1 -p 3005 -E

3 . If you have a standby Master configured currently, then remove it first:

gpinitstandby -r

4. Create a new standby Master instance with the new directory and port settings. In this example, we are changing the master data directory to /data/master/gpseg-1 and the port to 5432:

6.x:

[gpadmin@gpdb2-m ~]$ gpinitstandby -s gpdb2-1 -S /data/master/gpseg-1 -P 5432

5.x:

[gpadmin@gpdb2-m ~]$ gpinitstandby -s gpdb2-1 -F pg_system:/data/master/gpseg-1 -P 5432

5. Verify the standby Master has been created in sync with the current active Master:

gpstate -f

6.  Shutdown the Master instance only:

gpstop -am

7. SSH to gpdb2-1 (Standby Master) and check that the MASTER_DATA_DIRECTORY and PGPORT environment variables are set to the new values. Activate the newly created standby instance:

[gpadmin@gpdb2-1 ~]$ export MASTER_DATA_DIRECTORY=/data/master/gpseg-1
[gpadmin@gpdb2-1 ~]$ export PGPORT=5432
[gpadmin@gpdb2-1 ~]$ source /usr/local/greenplum-db/greenplum_path.sh 
[gpadmin@gpdb2-1 ~]$ gpactivatestandby

8. Verify the new postmaster process is running with the changed settings:

[gpadmin@gpdb2-1 ~]$ ps -ef | grep "postgres -D" | grep master| grep -v grep
gpadmin  19823     1  0 21:52 ?        00:00:00 /usr/local/6.20.3/greenplum-db-6.20.3/bin/postgres -D /data/master/gpseg-1 -p 5432 -E

9.  We now need to restore the original master with the changed MASTER_DATA_DIRECTORY and PGPORT.
To restore the original master, follow the steps in the Greenplum documentation.

10.  Finally, verify gp_segment_configuration and pg_filespace_entry.

gpadmin=# select * from pg_filespace_entry where fsedbid = 1;
 fsefsoid | fsedbid | fselocation
----------+---------+-----------------------
 3052 | 1 | /data/master/gpseg-1

gpadmin=# select dbid,content,hostname,port from gp_segment_configuration where content = -1;
 dbid | content | hostname | port
------+---------+----------+------
 1 | -1 | gpdb2-m | 5432

gpadmin=# show port;
 port
------
 5432

Method 2:

If you only want to change the port, you can modify postgresql.conf file and update gp_segment_configuration as follows:

NOTE: Since this is a requires a catalog change, please contact VMware Greenplum Support to assist with this.

In this example, we will show how to modify the port for Master and Standby Master if it already exists.

1. Verify current port setting:

gpadmin=# select * from gp_segment_configuration where content = -1;
 dbid | content | role | preferred_role | mode | status | port | hostname | address |                 datadir                 
------+---------+------+----------------+------+--------+------+----------+---------+-----------------------------------------
    1 |      -1 | p    | p              | n    | u      | 3006 | gpdb2-m  | gpdb2-m | /data/master/gp_6.20.3_20220519223315-1
   10 |      -1 | m    | m              | s    | u      | 3006 | gpdb2-1  | gpdb2-1 | /data/master/gp_6.20.3_20220519223315-1

2.  gpstop -aM fast 

3. Edit the port in $MASTER_DATA_DIRECTORY/postgresql.conf 

vi $MASTER_DATA_DIRECTORY/postgresql.conf 
port=5432

4. Update PGPORT environment variable to the new port.

5.  gpstart -a

6. Login using the new port and make the change to gp_segment_configuration:

[gpadmin@gpdb2-m ~]$ psql
psql (9.4.26)
Type "help" for help.
gpadmin=# BEGIN;
BEGIN
gpadmin=# set allow_system_table_mods=true;
SET
gpadmin=# UPDATE gp_segment_configuration SET port=5432 WHERE port=3006 and content = -1;
UPDATE 2

gpadmin=# select * from gp_segment_configuration where content = -1;
 dbid | content | role | preferred_role | mode | status | port | hostname | address |                 datadir                 
------+---------+------+----------------+------+--------+------+----------+---------+-----------------------------------------
    1 |      -1 | p    | p              | n    | u      | 5432 | gpdb2-m  | gpdb2-m | /data/master/gp_6.20.3_20220519223315-1
   10 |      -1 | m    | m              | s    | u      | 5432 | gpdb2-1  | gpdb2-1 | /data/master/gp_6.20.3_20220519223315-1

gpadmin=# COMMIT;
​​​​​​​COMMIT

7.  On the Standby Master, modify $MASTER_DATA_DIRECTORY/recovery.conf

primary_conninfo = 'user=#### host=gpdb2-m port=3006 sslmode=prefer sslcompression=1 krbsrvname=postgres application_name=gp_walreceiver'

to

primary_conninfo = 'user=#### host=gpdb2-m port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres application_name=gp_walreceiver'

8.  On the master, gpstate -f will show the standby master is in unknown status:

[gpadmin@gpdb2-m ~]$ gpstate -f
20220519:22:50:40:024087 gpstate:gpdb2-m:gpadmin-[INFO]:-Starting gpstate with args: -f
20220519:22:50:40:024087 gpstate:gpdb2-m:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.20.3 build commit:24b949d2585cdbe8a157062fb756013e7c2874ab'
20220519:22:50:40:024087 gpstate:gpdb2-m:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.20.3 build commit:24b949d2585cdbe8a157062fb756013e7c2874ab) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Apr  6 2022 19:59:52'
20220519:22:50:40:024087 gpstate:gpdb2-m:gpadmin-[INFO]:-Obtaining Segment details from master...
20220519:22:50:41:024087 gpstate:gpdb2-m:gpadmin-[INFO]:-Standby master details
20220519:22:50:41:024087 gpstate:gpdb2-m:gpadmin-[INFO]:-----------------------
20220519:22:50:41:024087 gpstate:gpdb2-m:gpadmin-[INFO]:-   Standby address          = gpdb2-1
20220519:22:50:41:024087 gpstate:gpdb2-m:gpadmin-[INFO]:-   Standby data directory   = /data/master/gp_6.20.3_20220519223315-1
20220519:22:50:41:024087 gpstate:gpdb2-m:gpadmin-[INFO]:-   Standby port             = 5432
20220519:22:50:41:024087 gpstate:gpdb2-m:gpadmin-[WARNING]:-Standby PID              = 29712                                     No socket connection or lock file (/tmp/.s.PGSQL.5432.lock) found for port 5432   <<<<<<<<
20220519:22:50:41:024087 gpstate:gpdb2-m:gpadmin-[WARNING]:-Standby status           = Status could not be determined            <<<<<<<<

After doing step 7, we need to start the standby master again:

[gpadmin@gpdb2-m ~]$ gpinitstandby -n

The Master and Standby Master will now be in sync with the new port number.