Pivotal Knowledge Base

Follow

How to change primary segment ports in HAWQ

Environment

Product Version
HAWQ 1.3.x

Purpose

A user would like to change the segment listening ports without having to reinstall HAWQ. This article outlines the steps to do so. 

Cause

By default, the segment port range is 40000 and increments by one for each segment installed on the server. In some cases, you may not have the foresight to predict that this port range will conflict with an existing or future application.

Procedure

In this example, we will change the starting primary port range from 40000 to 42000.

  1. Stop the database
    gpstop -af
  2. Start the master in master only mode using "-m" switch
    gpstart -am
  3. Connect to the master in utility mode
    PGOPTIONS='-c gp_session_role=utility' psql
  4. In the database prompt, make a backup of the gp_segment_configuration table
    gpadmin=# copy ( select * from gp_segment_configuration ) to '/home/gpadmin/gp_segment_configuration.backup';
  5. You must enable allow_system_table_modsguc in order to make changes to the gp_segment_configuration table
    gpadmin=# set allow_system_table_mods = 'dml';
  6. Update the port ranges for all primary segments gp_segment_configuration table. Please note, the query to update the port ranges will only work in this specific example. In many use cases, these changes may not be as straight forward and this example should only be used as a guideline and you should never run this query without full confidence about the outcome.
  7. Select the gp_segment_configuration table so we can see how it looks before and after the update.
    gpadmin=# select * from gp_segment_configuration
    gpadmin-# ;
     dbid | content | role | preferred_role | mode | status | port  |    hostname    |    address     | replication_port | san_mounts
    ------+---------+------+----------------+------+--------+-------+----------------+----------------+------------------+------------
        1 |      -1 | p    | p              | s    | u      |  5432 | hdm1.phd.local | hdm1.phd.local |                  |
        2 |       0 | p    | p              | s    | u      | 40000 | hdw1.phd.local | hdw1.phd.local |                  |
        4 |       2 | p    | p              | s    | u      | 40000 | hdw2.phd.local | hdw2.phd.local |                  |
        6 |       4 | p    | p              | s    | u      | 40000 | hdw3.phd.local | hdw3.phd.local |                  |
        3 |       1 | p    | p              | s    | u      | 40001 | hdw1.phd.local | hdw1.phd.local |                  |
        5 |       3 | p    | p              | s    | u      | 40001 | hdw2.phd.local | hdw2.phd.local |                  |
        7 |       5 | p    | p              | s    | u      | 40001 | hdw3.phd.local | hdw3.phd.local |                  |
  8. Start a new transaction and update the gp_segment_configuration table using your own customized query, here is an example. Please note "-1" is the master instance content ID, which iswhy exclude it from the update query"
    gpadmin=# BEGIN;
    gpadmin=# update gp_segment_configuration set port = (port + 2000) where content != -1;
  9. Verify the changes are ok before committing the transaction
    gpadmin=# select * from gp_segment_configuration                                                                                                                                                                                                                                                         ;
     dbid | content | role | preferred_role | mode | status | port  |    hostname    |    address     | replication_port | san_mounts
    ------+---------+------+----------------+------+--------+-------+----------------+----------------+------------------+------------
        1 |      -1 | p    | p              | s    | u      |  5432 | hdm1.phd.local | hdm1.phd.local |                  |
        2 |       0 | p    | p              | s    | u      | 42000 | hdw1.phd.local | hdw1.phd.local |                  |
        4 |       2 | p    | p              | s    | u      | 42000 | hdw2.phd.local | hdw2.phd.local |                  |
        6 |       4 | p    | p              | s    | u      | 42000 | hdw3.phd.local | hdw3.phd.local |                  |
        3 |       1 | p    | p              | s    | u      | 42001 | hdw1.phd.local | hdw1.phd.local |                  |
        5 |       3 | p    | p              | s    | u      | 42001 | hdw2.phd.local | hdw2.phd.local |                  |
        7 |       5 | p    | p              | s    | u      | 42001 | hdw3.phd.local | hdw3.phd.local |                  |
  10. Once you have reviewed the changes and are satisfied then commit the transaction
    gpadmin=# commit;
  11. Update the postgresql.conf in all of the segment data directories so the port numbers match what is in the gp_segment_configuration table. you can get segment data directories list using this query
    gpadmin=# select conf.hostname, ent.fselocation from gp_segment_configuration conf JOIN pg_filespace_entry ent ON ent.fsedbid = conf.dbid where ent.fselocation not like 'hdfs%' and conf.content != -1 order by 1;
        hostname    |        fselocation
    ----------------+---------------------------
     hdw1.phd.local | /data/hawq/primary/gpseg0
     hdw1.phd.local | /data/hawq/primary/gpseg1
     hdw2.phd.local | /data/hawq/primary/gpseg2
     hdw2.phd.local | /data/hawq/primary/gpseg3
     hdw3.phd.local | /data/hawq/primary/gpseg5
     hdw3.phd.local | /data/hawq/primary/gpseg4
    (6 rows)
  12. Example of how to change gpseg0 on hdw1 by changing the port flag in /data/hawq/primary/gpseg0/postgresql.conf
     
     cp /data/hawq/primary/gpseg0/postgresql.conf  /data/hawq/primary/gpseg0/postgresql.`date +%m%d%y.backup
     
     Change from:
     port=40000 ##port = 5432                                # sets the database listener port for
     
     Change To:
     port=42000 ##port = 5432                                # sets the database listener port for
  13. Complete the port changes for all segments
  14. Stop the master instance
    gpstop -am
  15. Start the database and resume operations
    gpstart -a

Backout procedure

  1. Stop the entire database
    gpstop -af
  2. Start database in master only mode
    gpstart -am
  3. Connect to database in utility mode
    PGOPTIONS='-c gp_session_role=utility' psql
  4. Enable DML editing
    gpadmin=# set allow_system_table_mods = 'dml';
  5. Create another backup of the gp_segment_configuration table
    gpadmin=# copy ( select * from gp_segment_configuration ) to '/home/gpadmin/gp_segment_configuration.4200.backup';
  6. Start transaction and delete all rows in gp_segment_configuration table
    BEGIN;
    gpadmin=# delete from gp_segment_configuration;
  7. Load table data from backup
    copy gp_segment_configuration from '/home/gpadmin/gp_segment_configuration.backup';
  8. Confirm everything looks ok before committing
    gpadmin=# select * from gp_segment_configuration
    gpadmin-# ;
     dbid | content | role | preferred_role | mode | status | port  |    hostname    |    address     | replication_port | san_mounts
    ------+---------+------+----------------+------+--------+-------+----------------+----------------+------------------+------------
        1 |      -1 | p    | p              | s    | u      |  5432 | hdm1.phd.local | hdm1.phd.local |                  |
        2 |       0 | p    | p              | s    | u      | 40000 | hdw1.phd.local | hdw1.phd.local |                  |
        4 |       2 | p    | p              | s    | u      | 40000 | hdw2.phd.local | hdw2.phd.local |                  |
        6 |       4 | p    | p              | s    | u      | 40000 | hdw3.phd.local | hdw3.phd.local |                  |
        3 |       1 | p    | p              | s    | u      | 40001 | hdw1.phd.local | hdw1.phd.local |                  |
        5 |       3 | p    | p              | s    | u      | 40001 | hdw2.phd.local | hdw2.phd.local |                  |
        7 |       5 | p    | p              | s    | u      | 40001 | hdw3.phd.local | hdw3.phd.local |                  |
  9. Commit the transaction
    gpadmin = commit;
  10. Make sure all the segment postgresql.conf port parameter settings is reverted as well
  11. Stop and restart the database
    gpstop -am
    gpstart -a

 

Comments

Powered by Zendesk