ERROR: permission denied: "gp_segment_configuration" is a system catalog

Post date: Oct 04, 2012 5:4:16 PM

One of oor DBA Reported that after changing the Port using the method described in the Greenplum official document.

Changing the Master Port

Greenplum Database comes configured with the default master port of 5432. Administrators should follow this process to change the master port number. Perform these steps on your Greenplum master host.

1.Shut down your Greenplum system in fast mode, interrupting and rolling back any active transactions. There cannot be active queries in the system when the system configuration is being altered.

$ gpstop -M fast

2.Edit the postgresql.conf file of the master. For example:

$ vi $MASTER_DATA_DIRECTORY/postgresql.conf

3.Change the following parameter to the new port number. For example if the new port is 54321:

port=54321

Note: If you have a standby master host, you must also edit the $MASTER_DATA_DIRECTORY/postgresql.conf file on your standby master host. After you are finished, return to your primary master host to complete the process.

4.Restart your Greenplum Database system.

$ gpstart

5.Start a psql client session using the new port and edit the gp_segment_configuration system catalog to change the master port. For example, if the new port is 54321 and the old port is 5432:

psql -p 54321 -c "UPDATE gp_segment_configuration SET port=54321 WHERE port=5432"

6.Make sure that any client programs you are using are updated to connect to the new master port. For example, if using psql update the $PGPORT environment variable in the profile of your Greenplum superuser (gpadmin).

After this when he ran

gpadmin=# select * from gp_segment_configuration where port = 5432;

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

1 | -1 | p | p | s | u | 5432 | mdw | mdw | |

194 | -1 | m | m | s | u | 5432 | smdw | smdw | |

(2 rows)

[gpadmin@mdw scripts]$ psql -p 54321 -c "UPDATE gp_segment_configuration SET port=54321 WHERE port=5432"

ERROR: permission denied: "gp_segment_configuration" is a system catalog.

To solve this issue.

GP 4.2 version has some behavior change on the control of catalog modification. In order to update the

Starting the Master in Maintenance Mode

There may be cases where you want to start only the master. This is called maintenance mode. In this mode, you can do things such as connect to a database on the master instance only in utility mode and edit settings in the system catalog, without affecting user data on the segment instances.

To start the master in utility mode

1. Stop the greenplum database system.

gpstop -M fast

2. To start the master in maintenance utility mode

Run gpstart using the -m option:

$ gpstart -m

$PGOPTIONS='-c gp_session_role=utility' psql

3.Now update the port

$psql -p 1587 -c "UPDATE gp_segment_configuration SET port=1587 WHERE port=5432"

4.After completing your administrative tasks, Stop a master instance that was started in maintenance mode:

$gpstop -m

5. start the master in normal mode.

$ gpstart

Warning: Incorrect use of maintenance mode connections can put the system into an inconsistent state. This operation should only be performed by Technical Support.