How to change a segment port number in gp_segment_configuration (alternate method)
Article ID: 382981
Updated On:
Products
Issue/Introduction
There are instances where ports used by Greenplum are taken by other applications or services. This can cause problems such as segments down and cannot start, gprecoverseg hanging and etc. To resolve the port conflicts, you will need to change the primary or mirror segment ports by changing the port number in the gp_segment_configuration.
Environment
6.23.0
Cause
Ports 5000 and 5001 used by Greenplum were taken by another service/application.
Resolution
1. Stop gpdb instance.
gpstop -a -i
2. Start gpdb in master only mode.
gpstart -m
[gpadmin@627gpdb-m ~]$ gpstart -m
20241129:18:12:23:002344 gpstart:627gpdb-m:gpadmin-[INFO]:-Starting gpstart with args: -m
20241129:18:12:23:002344 gpstart:627gpdb-m:gpadmin-[INFO]:-Gathering information and validating the environment...
20241129:18:12:23:002344 gpstart:627gpdb-m:gpadmin-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 6.27.3 build commit:fbdb2d07da47b0f13cb166baba1d8c47d92059f2'
20241129:18:12:23:002344 gpstart:627gpdb-m:gpadmin-[INFO]:-Greenplum Catalog Version: '301908232'
20241129:18:12:23:002344 gpstart:627gpdb-m:gpadmin-[WARNING]:-****************************************************************************
20241129:18:12:23:002344 gpstart:627gpdb-m:gpadmin-[WARNING]:-Master-only start requested. If a standby master is configured, this command
20241129:18:12:23:002344 gpstart:627gpdb-m:gpadmin-[WARNING]:-may lead to a split-brain condition and possible unrecoverable data loss.
20241129:18:12:23:002344 gpstart:627gpdb-m:gpadmin-[WARNING]:-Maintenance mode should only be used with direction from Greenplum Support.
20241129:18:12:23:002344 gpstart:627gpdb-m:gpadmin-[WARNING]:-****************************************************************************
3. Log into db using utility mode. Identify the segments needing the port change.
[gpadmin@627gpdb-m ~]$ PGOPTIONS='-c gp_session_role=utility' psql
psql (9.4.26)
Type "help" for help.
gpadmin=# select * from gp_segment_configuration ;
dbid | content | role | preferred_role | mode | status | port | hostname | address | datadir
------+---------+------+----------------+------+--------+-------+-----------+-----------+------------------------------------------------------
7 | 1 | m | m | s | u | 35001 | 627gpdb-2 | 627gpdb-2 | /data/mirror/gp_greenplum-db-6.27.0_202404111543261
9 | 3 | m | m | s | u | 35001 | 627gpdb-1 | 627gpdb-1 | /data/mirror/gp_greenplum-db-6.27.0_202404111543263
4. Enable system_table_mods then change the segment port number.
gpadmin=# set allow_system_table_mods=true;
SET
gpadmin=# UPDATE gp_segment_configuration
SET port = 36001
WHERE dbid = 9;
UPDATE 1
gpadmin=#
5. Verify the segment port numbers changed from 35001 to 36001.
gpadmin=# select * from gp_segment_configuration where port = 36001;
dbid | content | role | preferred_role | mode | status | port | hostname | address | datadir
------+---------+------+----------------+------+--------+-------+-----------+-----------+-----------------------------------------------------
7 | 1 | m | m | s | u | 36001 | 627gpdb-2 | 627gpdb-2 | /data/mirror/gp_greenplum-db-6.27.0_202404111543261
9 | 3 | m | m | s | u | 36001 | 627gpdb-1 | 627gpdb-1 | /data/mirror/gp_greenplum-db-6.27.0_202404111543263
6. Stop gpdb to exit master only mode, then start gpdb in normal mode.
gpstop -a -i
gpstart -a
7. After gpdb has started, log into the db and confirm the port number have changed.
[gpadmin@627gpdb-m ~]$ psql
psql (9.4.26)
Type "help" for help.
gpadmin=# select * from gp_segment_configuration where port = 36001;
dbid | content | role | preferred_role | mode | status | port | hostname | address | datadir
------+---------+------+----------------+------+--------+-------+-----------+-----------+-----------------------------------------------------
7 | 1 | m | m | n | u | 36001 | 627gpdb-2 | 627gpdb-2 | /data/mirror/gp_greenplum-db-6.27.0_202404111543261
9 | 3 | m | m | n | u | 36001 | 627gpdb-1 | 627gpdb-1 | /data/mirror/gp_greenplum-db-6.27.0_202404111543263
Feedback
