Issue:
1) this parameter need --skipvalidation
2) transaction_isolation parameter setting not getting effect even after reload and restart, the parameter is stored enclosed in single quotes in postgresql.conf file, tried below did not work.
Not sure if it has something to do with (#default_transaction_isolation = 'read committed' ) parameter
[gpadmin@mdw_ipv4 ~]$ gpconfig -s transaction_isolation
Values on all segments are consistent
GUC : transaction_isolation
Master value: read committed
Segment value: read committed
[gpadmin@mdw_ipv4 ~]$
[gpadmin@mdw_ipv4 ~]$
[gpadmin@mdw_ipv4 ~]$ gpconfig -c transaction_isolation -v serializable
20210727:18:03:37:016929 gpconfig:mdw_ipv4:gpadmin-[CRITICAL]:-not a modifiable GUC: 'transaction_isolation'
not a modifiable GUC: 'transaction_isolation'
[gpadmin@mdw_ipv4 ~]$
[gpadmin@mdw_ipv4 ~]$
[gpadmin@mdw_ipv4 ~]$ gpconfig -c transaction_isolation -v serializable --skipvalidation
20210727:18:03:58:016954 gpconfig:mdw_ipv4:gpadmin-[INFO]:-completed successfully with parameters '-c transaction_isolation -v serializable --skipvalidation'
[gpadmin@mdw_ipv4 ~]$
[gpadmin@mdw_ipv4 ~]$
[gpadmin@mdw_ipv4 ~]$ gpstop -u
20210727:18:04:13:017038 gpstop:mdw_ipv4:gpadmin-[INFO]:-Starting gpstop with args: -u
20210727:18:04:13:017038 gpstop:mdw_ipv4:gpadmin-[INFO]:-Gathering information and validating the environment...
20210727:18:04:13:017038 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20210727:18:04:13:017038 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Segment details from master...
20210727:18:04:13:017038 gpstop:mdw_ipv4:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 6.16.0 build commit:5650be2b79197fed564dca8d734d10f2a76b876c'
20210727:18:04:13:017038 gpstop:mdw_ipv4:gpadmin-[INFO]:-Signalling all postmaster processes to reload
[gpadmin@mdw_ipv4 ~]$
[gpadmin@mdw_ipv4 ~]$
[gpadmin@mdw_ipv4 ~]$ gpconfig -s transaction_isolation
Values on all segments are consistent
GUC : transaction_isolation
Master value: read committed
Segment value: read committed
[gpadmin@mdw_ipv4 ~]$
[gpadmin@mdw_ipv4 ~]$ gpconfig -s transaction_isolation
Values on all segments are consistent
GUC : transaction_isolation
Master value: read committed
Segment value: read committed
[gpadmin@mdw_ipv4 ~]$
[gpadmin@mdw_ipv4 ~]$ gpstop -arf
20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Starting gpstop with args: -arf
20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Gathering information and validating the environment...
20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Segment details from master...
20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 6.16.0 build commit:5650be2b79197fed564dca8d734d10f2a76b876c'
20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Commencing Master instance shutdown with mode='fast'
20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Master segment instance directory=/data1/gpdb/master/gpseg-1
20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Attempting forceful termination of any leftover master process
20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Terminating processes for segment /data1/gpdb/master/gpseg-1
20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Stopping master standby host smdw_ipv4 mode=fast
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Successfully shutdown standby process on smdw_ipv4
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Targeting dbid [2, 8, 3, 9, 4, 10, 5, 11, 6, 12, 7, 13] for shutdown
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Commencing parallel primary segment instance shutdown, please wait...
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-0.00% of jobs completed
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-100.00% of jobs completed
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Commencing parallel mirror segment instance shutdown, please wait...
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-0.00% of jobs completed
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-100.00% of jobs completed
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-----------------------------------------------------
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:- Segments stopped successfully = 12
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:- Segments with errors during stop = 0
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-----------------------------------------------------
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Successfully shutdown 12 of 12 segment instances
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Database successfully shutdown with no errors reported
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Cleaning up leftover gpmmon process
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-No leftover gpmmon process found
20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Cleaning up leftover gpsmon processes
20210727:18:04:28:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-No leftover gpsmon processes on some hosts. not attempting forceful termination on these hosts
20210727:18:04:28:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Cleaning up leftover shared memory
20210727:18:04:28:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Restarting System...
[gpadmin@mdw_ipv4 ~]$
[gpadmin@mdw_ipv4 ~]$
[gpadmin@mdw_ipv4 ~]$ gpconfig -s transaction_isolation
Values on all segments are consistent
GUC : transaction_isolation
Master value: read committed
Segment value: read committed
Page No : 1394
Issue: 1) this parameter need --skipvalidation 2) transaction_isolation parameter setting not getting effect even after reload and restart, the parameter is stored enclosed in single quotes in postgresql.conf file, tried below did not work.
[gpadmin@mdw_ipv4 ~]$ gpconfig -s transaction_isolation Values on all segments are consistent GUC : transaction_isolation Master value: read committed Segment value: read committed [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ gpconfig -c transaction_isolation -v serializable 20210727:18:03:37:016929 gpconfig:mdw_ipv4:gpadmin-[CRITICAL]:-not a modifiable GUC: 'transaction_isolation' not a modifiable GUC: 'transaction_isolation' [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ gpconfig -c transaction_isolation -v serializable --skipvalidation 20210727:18:03:58:016954 gpconfig:mdw_ipv4:gpadmin-[INFO]:-completed successfully with parameters '-c transaction_isolation -v serializable --skipvalidation' [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ gpstop -u 20210727:18:04:13:017038 gpstop:mdw_ipv4:gpadmin-[INFO]:-Starting gpstop with args: -u 20210727:18:04:13:017038 gpstop:mdw_ipv4:gpadmin-[INFO]:-Gathering information and validating the environment... 20210727:18:04:13:017038 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20210727:18:04:13:017038 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Segment details from master... 20210727:18:04:13:017038 gpstop:mdw_ipv4:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 6.16.0 build commit:5650be2b79197fed564dca8d734d10f2a76b876c' 20210727:18:04:13:017038 gpstop:mdw_ipv4:gpadmin-[INFO]:-Signalling all postmaster processes to reload [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ gpconfig -s transaction_isolation Values on all segments are consistent GUC : transaction_isolation Master value: read committed Segment value: read committed [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ gpconfig -s transaction_isolation Values on all segments are consistent GUC : transaction_isolation Master value: read committed Segment value: read committed [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ gpstop -arf 20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Starting gpstop with args: -arf 20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Gathering information and validating the environment... 20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Segment details from master... 20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 6.16.0 build commit:5650be2b79197fed564dca8d734d10f2a76b876c' 20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Commencing Master instance shutdown with mode='fast' 20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Master segment instance directory=/data1/gpdb/master/gpseg-1 20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Attempting forceful termination of any leftover master process 20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Terminating processes for segment /data1/gpdb/master/gpseg-1 20210727:18:04:26:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Stopping master standby host smdw_ipv4 mode=fast 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Successfully shutdown standby process on smdw_ipv4 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Targeting dbid [2, 8, 3, 9, 4, 10, 5, 11, 6, 12, 7, 13] for shutdown 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Commencing parallel primary segment instance shutdown, please wait... 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-0.00% of jobs completed 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-100.00% of jobs completed 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Commencing parallel mirror segment instance shutdown, please wait... 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-0.00% of jobs completed 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-100.00% of jobs completed 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:----------------------------------------------------- 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:- Segments stopped successfully = 12 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:- Segments with errors during stop = 0 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:----------------------------------------------------- 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Successfully shutdown 12 of 12 segment instances 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Database successfully shutdown with no errors reported 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Cleaning up leftover gpmmon process 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-No leftover gpmmon process found 20210727:18:04:27:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Cleaning up leftover gpsmon processes 20210727:18:04:28:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-No leftover gpsmon processes on some hosts. not attempting forceful termination on these hosts 20210727:18:04:28:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Cleaning up leftover shared memory 20210727:18:04:28:017098 gpstop:mdw_ipv4:gpadmin-[INFO]:-Restarting System... [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ gpconfig -s transaction_isolation Values on all segments are consistent GUC : transaction_isolation Master value: read committed Segment value: read committed
[gpadmin@mdw_ipv4 ~]$ grep transaction_isolation $MASTER_DATA_DIRECTORY/postgresql.conf
default_transaction_isolation = 'read committed'
transaction_isolation=serializable [gpadmin@mdw_ipv4 ~]$
[gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ gpconfig -c transaction_isolation -v "'serializable'" --skipvalidation 20210729:03:57:37:011542 gpconfig:mdw_ipv4:gpadmin-[INFO]:-completed successfully with parameters '-c transaction_isolation -v ''"'"'serializable'"'"'' --skipvalidation' [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ gpstop -u 20210729:03:57:41:011616 gpstop:mdw_ipv4:gpadmin-[INFO]:-Starting gpstop with args: -u 20210729:03:57:41:011616 gpstop:mdw_ipv4:gpadmin-[INFO]:-Gathering information and validating the environment... 20210729:03:57:41:011616 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20210729:03:57:41:011616 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Segment details from master... 20210729:03:57:41:011616 gpstop:mdw_ipv4:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 6.16.0 build commit:5650be2b79197fed564dca8d734d10f2a76b876c' 20210729:03:57:41:011616 gpstop:mdw_ipv4:gpadmin-[INFO]:-Signalling all postmaster processes to reload [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ gpconfig -s transaction_isolation Values on all segments are consistent GUC : transaction_isolation Master value: read committed Segment value: read committed [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ grep transaction_isolation $MASTER_DATA_DIRECTORY/postgresql.conf
default_transaction_isolation = 'read committed'
transaction_isolation=serializable
transaction_isolation='serializable' [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ gpstop -arf 20210729:03:58:51:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Starting gpstop with args: -arf 20210729:03:58:51:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Gathering information and validating the environment... 20210729:03:58:51:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20210729:03:58:51:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Obtaining Segment details from master... 20210729:03:58:51:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 6.16.0 build commit:5650be2b79197fed564dca8d734d10f2a76b876c' 20210729:03:58:51:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Commencing Master instance shutdown with mode='fast' 20210729:03:58:51:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Master segment instance directory=/data1/gpdb/master/gpseg-1 20210729:03:58:51:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Attempting forceful termination of any leftover master process 20210729:03:58:51:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Terminating processes for segment /data1/gpdb/master/gpseg-1 20210729:03:58:51:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Stopping master standby host smdw_ipv4 mode=fast 20210729:03:58:52:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Successfully shutdown standby process on smdw_ipv4 20210729:03:58:52:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Targeting dbid [2, 8, 3, 9, 4, 10, 5, 11, 6, 12, 7, 13] for shutdown 20210729:03:58:52:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Commencing parallel primary segment instance shutdown, please wait... 20210729:03:58:52:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-0.00% of jobs completed 20210729:03:58:52:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-100.00% of jobs completed 20210729:03:58:52:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Commencing parallel mirror segment instance shutdown, please wait... 20210729:03:58:52:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-0.00% of jobs completed 20210729:03:58:53:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-100.00% of jobs completed 20210729:03:58:53:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:----------------------------------------------------- 20210729:03:58:53:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:- Segments stopped successfully = 12 20210729:03:58:53:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:- Segments with errors during stop = 0 20210729:03:58:53:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:----------------------------------------------------- 20210729:03:58:53:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Successfully shutdown 12 of 12 segment instances 20210729:03:58:53:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Database successfully shutdown with no errors reported 20210729:03:58:53:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Cleaning up leftover gpmmon process 20210729:03:58:53:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-No leftover gpmmon process found 20210729:03:58:53:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Cleaning up leftover gpsmon processes 20210729:03:58:53:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-No leftover gpsmon processes on some hosts. not attempting forceful termination on these hosts 20210729:03:58:53:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Cleaning up leftover shared memory 20210729:03:58:53:011744 gpstop:mdw_ipv4:gpadmin-[INFO]:-Restarting System... [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ [gpadmin@mdw_ipv4 ~]$ gpconfig -s transaction_isolation Values on all segments are consistent GUC : transaction_isolation Master value: read committed Segment value: read committed [gpadmin@mdw_ipv4 ~]$