GreenPlum删除节点
查看数据库当前状态
[gpadmin@server04 greenplum]$ gpstate
20191106:23:08:35:006912 gpstate:server04:gpadmin-[INFO]:-Starting gpstate with args:
20191106:23:08:36:006912 gpstate:server04:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 5.21.3 build commit:3d56d131a82082e5f1c69ecee50177f6484ed6a4'
20191106:23:08:36:006912 gpstate:server04:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.3.23 (Greenplum Database 5.21.3 build commit:3d56d131a82082e5f1c69ecee50177f6484ed6a4) on x86_64-pc-linux-gnu, compiled by GCC gcc (GCC) 6.2.0, 64-bit compiled on Aug 29 2019 17:19:21'
20191106:23:08:36:006912 gpstate:server04:gpadmin-[INFO]:-Obtaining Segment details from master...
20191106:23:08:36:006912 gpstate:server04:gpadmin-[INFO]:-Gathering data from segments...
..
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:-Greenplum instance status summary
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:-----------------------------------------------------
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Master instance = Active
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Master standby = No master standby configured
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Total segment instance count from metadata = 15
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:-----------------------------------------------------
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Primary Segment Status
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:-----------------------------------------------------
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Total primary segments = 15
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Total primary segment valid (at master) = 15
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Total primary segment failures (at master) = 0
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Total number of postmaster.pid files missing = 0
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Total number of postmaster.pid files found = 15
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Total number of postmaster.pid PIDs missing = 0
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Total number of postmaster.pid PIDs found = 15
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Total number of /tmp lock files missing = 0
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Total number of /tmp lock files found = 15
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Total number postmaster processes missing = 0
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Total number postmaster processes found = 15
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:-----------------------------------------------------
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Mirror Segment Status
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:-----------------------------------------------------
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:- Mirrors not configured on this array
20191106:23:08:38:006912 gpstate:server04:gpadmin-[INFO]:-----------------------------------------------------
关闭当前数据库
[gpadmin@server04 greenplum]$ gpstop -M fast
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:-Starting gpstop with args: -M fast
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:-Gathering information and validating the environment...
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:-Obtaining Segment details from master...
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 5.21.3 build commit:3d56d131a82082e5f1c69ecee50177f6484ed6a4'
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:---------------------------------------------
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:-Master instance parameters
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:---------------------------------------------
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- Master Greenplum instance process active PID = 16688
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- Database = template1
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- Master port = 5432
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- Master directory = /data1/greenplum/data/master/gpseg-1
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- Shutdown mode = fast
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- Timeout = 120
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- Shutdown Master standby host = Off
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:---------------------------------------------
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:-Segment instances that will be shutdown:
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:---------------------------------------------
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- Host Datadir Port Status
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server04.novalocal /data1/greenplum/data/primary/gpseg0 6000 u
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server04.novalocal /data1/greenplum/data/primary/gpseg1 6001 u
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server04.novalocal /data1/greenplum/data/primary/gpseg2 6002 u
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server12.novalocal /data1/greenplum/data/primary/gpseg3 6000 u
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server12.novalocal /data1/greenplum/data/primary/gpseg4 6001 u
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server12.novalocal /data1/greenplum/data/primary/gpseg5 6002 u
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server14.novalocal /data1/greenplum/data/primary/gpseg6 6000 u
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server14.novalocal /data1/greenplum/data/primary/gpseg7 6001 u
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server14.novalocal /data1/greenplum/data/primary/gpseg8 6002 u
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server16.novalocal /data1/greenplum/data/primary/gpseg9 6000 u
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server16.novalocal /data1/greenplum/data/primary/gpseg10 6001 u
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server16.novalocal /data1/greenplum/data/primary/gpseg11 6002 u
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server04.novalocal /data1/greenplum/data/primary/gpseg12 6003 u
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server04.novalocal /data1/greenplum/data/primary/gpseg13 6004 u
20191106:23:10:58:008777 gpstop:server04:gpadmin-[INFO]:- server04.novalocal /data1/greenplum/data/primary/gpseg14 6005 u
Continue with Greenplum instance shutdown Yy|Nn (default=N):
> y
20191106:23:10:59:008777 gpstop:server04:gpadmin-[INFO]:-There are 5 connections to the database
20191106:23:10:59:008777 gpstop:server04:gpadmin-[INFO]:-Commencing Master instance shutdown with mode='fast'
20191106:23:10:59:008777 gpstop:server04:gpadmin-[INFO]:-Master host=172.16.0.4
20191106:23:10:59:008777 gpstop:server04:gpadmin-[INFO]:-Detected 5 connections to database
20191106:23:10:59:008777 gpstop:server04:gpadmin-[INFO]:-Switching to WAIT mode
20191106:23:10:59:008777 gpstop:server04:gpadmin-[INFO]:-Will wait for shutdown to complete, this may take some time if
20191106:23:10:59:008777 gpstop:server04:gpadmin-[INFO]:-there are a large number of active complex transactions, please wait...
20191106:23:10:59:008777 gpstop:server04:gpadmin-[INFO]:-Commencing Master instance shutdown with mode=fast
20191106:23:10:59:008777 gpstop:server04:gpadmin-[INFO]:-Master segment instance directory=/data1/greenplum/data/master/gpseg-1
20191106:23:11:00:008777 gpstop:server04:gpadmin-[INFO]:-Attempting forceful termination of any leftover master process
20191106:23:11:00:008777 gpstop:server04:gpadmin-[INFO]:-Terminating processes for segment /data1/greenplum/data/master/gpseg-1
20191106:23:11:00:008777 gpstop:server04:gpadmin-[INFO]:-No standby master host configured
20191106:23:11:00:008777 gpstop:server04:gpadmin-[INFO]:-Targeting dbid [2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 16, 17] for shutdown
20191106:23:11:00:008777 gpstop:server04:gpadmin-[INFO]:-Commencing parallel segment instance shutdown, please wait...
20191106:23:11:00:008777 gpstop:server04:gpadmin-[INFO]:-0.00% of jobs completed
20191106:23:11:03:008777 gpstop:server04:gpadmin-[INFO]:-100.00% of jobs completed
20191106:23:11:03:008777 gpstop:server04:gpadmin-[INFO]:-----------------------------------------------------
20191106:23:11:03:008777 gpstop:server04:gpadmin-[INFO]:- Segments stopped successfully = 15
20191106:23:11:03:008777 gpstop:server04:gpadmin-[INFO]:- Segments with errors during stop = 0
20191106:23:11:03:008777 gpstop:server04:gpadmin-[INFO]:-----------------------------------------------------
20191106:23:11:03:008777 gpstop:server04:gpadmin-[INFO]:-Successfully shutdown 15 of 15 segment instances
20191106:23:11:03:008777 gpstop:server04:gpadmin-[INFO]:-Database successfully shutdown with no errors reported
20191106:23:11:03:008777 gpstop:server04:gpadmin-[INFO]:-Cleaning up leftover gpmmon process
20191106:23:11:03:008777 gpstop:server04:gpadmin-[INFO]:-No leftover gpmmon process found
20191106:23:11:03:008777 gpstop:server04:gpadmin-[INFO]:-Cleaning up leftover gpsmon processes
20191106:23:11:03:008777 gpstop:server04:gpadmin-[INFO]:-No leftover gpsmon processes on some hosts. not attempting forceful termination on these hosts
20191106:23:11:03:008777 gpstop:server04:gpadmin-[INFO]:-Cleaning up leftover shared memory
以管理模式启动数据库
[gpadmin@server04 greenplum]$ gpstart -m
20191106:23:12:11:009614 gpstart:server04:gpadmin-[INFO]:-Starting gpstart with args: -m
20191106:23:12:11:009614 gpstart:server04:gpadmin-[INFO]:-Gathering information and validating the environment...
20191106:23:12:11:009614 gpstart:server04:gpadmin-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 5.21.3 build commit:3d56d131a82082e5f1c69ecee50177f6484ed6a4'
20191106:23:12:11:009614 gpstart:server04:gpadmin-[INFO]:-Greenplum Catalog Version: '301705051'
20191106:23:12:11:009614 gpstart:server04:gpadmin-[INFO]:-Master-only start requested in configuration without a standby master.
Continue with master-only startup Yy|Nn (default=N):
> y
20191106:23:12:13:009614 gpstart:server04:gpadmin-[INFO]:-Starting Master instance in admin mode
20191106:23:12:15:009614 gpstart:server04:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20191106:23:12:15:009614 gpstart:server04:gpadmin-[INFO]:-Obtaining Segment details from master...
20191106:23:12:15:009614 gpstart:server04:gpadmin-[INFO]:-Setting new master era
20191106:23:12:15:009614 gpstart:server04:gpadmin-[INFO]:-Master Started...
登录管理数据库
PGOPTIONS="-c gp_session_role=utility" psql -d postgres
删除segment
select * from gp_segment_configuration;
postgres=# select * from gp_segment_configuration;
dbid | content | role | preferred_role | mode | status | port | hostname | address | replication_port
------+---------+------+----------------+------+--------+------+------------------+------------+------------------
2 | 0 | p | p | s | u | 6000 | server04.novalocal | sdw0 |
5 | 3 | p | p | s | u | 6000 | server12.novalocal | sdw1 |
8 | 6 | p | p | s | u | 6000 | server14.novalocal | sdw2 |
11 | 9 | p | p | s | u | 6000 | server16.novalocal | sdw3 |
3 | 1 | p | p | s | u | 6001 | server04.novalocal | sdw0 |
6 | 4 | p | p | s | u | 6001 | server12.novalocal | sdw1 |
9 | 7 | p | p | s | u | 6001 | server14.novalocal | sdw2 |
12 | 10 | p | p | s | u | 6001 | server16.novalocal | sdw3 |
4 | 2 | p | p | s | u | 6002 | server04.novalocal | sdw0 |
7 | 5 | p | p | s | u | 6002 | server12.novalocal | sdw1 |
10 | 8 | p | p | s | u | 6002 | server14.novalocal | sdw2 |
13 | 11 | p | p | s | u | 6002 | server16.novalocal | sdw3 |
15 | 12 | p | p | s | u | 6003 | server04.novalocal | sdw0 |
16 | 13 | p | p | s | u | 6004 | server04.novalocal | sdw0 |
17 | 14 | p | p | s | u | 6005 | server04.novalocal | sdw0 |
1 | -1 | p | p | s | u | 5432 | 172.16.0.4 | 172.16.0.4 |
(16 rows)
postgres=# select * from gp_segment_configuration where hostname='server04.novalocal';
dbid | content | role | preferred_role | mode | status | port | hostname | address | replication_port
------+---------+------+----------------+------+--------+------+------------------+---------+------------------
2 | 0 | p | p | s | u | 6000 | server04.novalocal | sdw0 |
3 | 1 | p | p | s | u | 6001 | server04.novalocal | sdw0 |
4 | 2 | p | p | s | u | 6002 | server04.novalocal | sdw0 |
15 | 12 | p | p | s | u | 6003 | server04.novalocal | sdw0 |
16 | 13 | p | p | s | u | 6004 | server04.novalocal | sdw0 |
17 | 14 | p | p | s | u | 6005 | server04.novalocal | sdw0 |
(6 rows)
postgres=# select * from gp_segment_configuration where hostname!='server04.novalocal';
dbid | content | role | preferred_role | mode | status | port | hostname | address | replication_port
------+---------+------+----------------+------+--------+------+------------------+------------+------------------
5 | 3 | p | p | s | u | 6000 | server12.novalocal | sdw1 |
8 | 6 | p | p | s | u | 6000 | server14.novalocal | sdw2 |
11 | 9 | p | p | s | u | 6000 | server16.novalocal | sdw3 |
6 | 4 | p | p | s | u | 6001 | server12.novalocal | sdw1 |
9 | 7 | p | p | s | u | 6001 | server14.novalocal | sdw2 |
12 | 10 | p | p | s | u | 6001 | server16.novalocal | sdw3 |
7 | 5 | p | p | s | u | 6002 | server12.novalocal | sdw1 |
10 | 8 | p | p | s | u | 6002 | server14.novalocal | sdw2 |
13 | 11 | p | p | s | u | 6002 | server16.novalocal | sdw3 |
1 | -1 | p | p | s | u | 5432 | 172.16.0.4 | 172.16.0.4 |
postgres=# select * from pg_filespace_entry ;
fsefsoid | fsedbid | fselocation
----------+---------+---------------------------------------
3052 | 2 | /data1/greenplum/data/primary/gpseg0
3052 | 5 | /data1/greenplum/data/primary/gpseg3
3052 | 8 | /data1/greenplum/data/primary/gpseg6
3052 | 11 | /data1/greenplum/data/primary/gpseg9
3052 | 3 | /data1/greenplum/data/primary/gpseg1
3052 | 6 | /data1/greenplum/data/primary/gpseg4
3052 | 9 | /data1/greenplum/data/primary/gpseg7
3052 | 12 | /data1/greenplum/data/primary/gpseg10
3052 | 4 | /data1/greenplum/data/primary/gpseg2
3052 | 7 | /data1/greenplum/data/primary/gpseg5
3052 | 10 | /data1/greenplum/data/primary/gpseg8
3052 | 13 | /data1/greenplum/data/primary/gpseg11
3052 | 15 | /data1/greenplum/data/primary/gpseg12
3052 | 16 | /data1/greenplum/data/primary/gpseg13
3052 | 17 | /data1/greenplum/data/primary/gpseg14
3052 | 1 | /data1/greenplum/data/master/gpseg-1
设置操作权限
set allow_system_table_mods='dml';
删除节点
delete from gp_segment_configuration where dbid in (2,3,4,15,16,17);
delete from pg_filespace_entry where fsedbid in (2,3,4,15,16,17);
postgres=# select * from gp_segment_configuration;
dbid | content | role | preferred_role | mode | status | port | hostname | address | replication_port
------+---------+------+----------------+------+--------+------+------------------+------------+------------------
5 | 3 | p | p | s | u | 6000 | server12.novalocal | sdw1 |
8 | 6 | p | p | s | u | 6000 | server14.novalocal | sdw2 |
11 | 9 | p | p | s | u | 6000 | server16.novalocal | sdw3 |
6 | 4 | p | p | s | u | 6001 | server12.novalocal | sdw1 |
9 | 7 | p | p | s | u | 6001 | server14.novalocal | sdw2 |
12 | 10 | p | p | s | u | 6001 | server16.novalocal | sdw3 |
7 | 5 | p | p | s | u | 6002 | server12.novalocal | sdw1 |
10 | 8 | p | p | s | u | 6002 | server14.novalocal | sdw2 |
13 | 11 | p | p | s | u | 6002 | server16.novalocal | sdw3 |
1 | -1 | p | p | s | u | 5432 | 172.16.0.4 | 172.16.0.4 |
gpstart报错
gpstop -m
[gpadmin@server04 greenplum]$ gpstart
20191106:23:33:24:019409 gpstart:server04:gpadmin-[INFO]:-Starting gpstart with args:
20191106:23:33:24:019409 gpstart:server04:gpadmin-[INFO]:-Gathering information and validating the environment...
20191106:23:33:25:019409 gpstart:server04:gpadmin-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 5.21.3 build commit:3d56d131a82082e5f1c69ecee50177f6484ed6a4'
20191106:23:33:25:019409 gpstart:server04:gpadmin-[INFO]:-Greenplum Catalog Version: '301705051'
20191106:23:33:25:019409 gpstart:server04:gpadmin-[INFO]:-Starting Master instance in admin mode
20191106:23:33:26:019409 gpstart:server04:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20191106:23:33:26:019409 gpstart:server04:gpadmin-[INFO]:-Obtaining Segment details from master...
20191106:23:33:26:019409 gpstart:server04:gpadmin-[INFO]:-Setting new master era
20191106:23:33:26:019409 gpstart:server04:gpadmin-[INFO]:-Master Started...
20191106:23:33:26:019409 gpstart:server04:gpadmin-[CRITICAL]:-gpstart failed. (Reason=''NoneType' object has no attribute 'hostname'') exiting...
联系节点
postgres=# update gp_segment_configuration set content=content-3 where content!=-1;
ERROR: operator does not exist: smallint !=- integer
LINE 1: ...gment_configuration set content=content-3 where content!=-1;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
postgres=# update gp_segment_configuration set content=content-3 where dbid!=1;
UPDATE 9
postgres=# select * from gp_segment_configuration;
dbid | content | role | preferred_role | mode | status | port | hostname | address | replication_port
------+---------+------+----------------+------+--------+------+------------------+------------+------------------
1 | -1 | p | p | s | u | 5432 | 172.16.0.4 | 172.16.0.4 |
5 | 0 | p | p | s | u | 6000 | server12.novalocal | sdw1 |
8 | 3 | p | p | s | u | 6000 | server14.novalocal | sdw2 |
11 | 6 | p | p | s | u | 6000 | server16.novalocal | sdw3 |
6 | 1 | p | p | s | u | 6001 | server12.novalocal | sdw1 |
9 | 4 | p | p | s | u | 6001 | server14.novalocal | sdw2 |
12 | 7 | p | p | s | u | 6001 | server16.novalocal | sdw3 |
7 | 2 | p | p | s | u | 6002 | server12.novalocal | sdw1 |
10 | 5 | p | p | s | u | 6002 | server14.novalocal | sdw2 |
13 | 8 | p | p | s | u | 6002 | server16.novalocal | sdw3 |
重启节点
gpstop -m
gpstart