1备份数据库
[gpadmin@hadoop02 ~]$ gp_dump --gp-d=/home/gpadmin/backup tutorial 20160928:14:41:34|gp_dump-[INFO]:-Read params: <empty> 20160928:14:41:34|gp_dump-[INFO]:-Command line options analyzed. 20160928:14:41:34|gp_dump-[INFO]:-Connecting to master database on host localhost port 5432 database tutorial. 20160928:14:41:34|gp_dump-[INFO]:-Reading Greenplum Database configuration info from master database. 20160928:14:41:34|gp_dump-[INFO]:-Preparing to dump the following segments: 20160928:14:41:34|gp_dump-[INFO]:-Segment 1 (dbid 3) 20160928:14:41:34|gp_dump-[INFO]:-Segment 0 (dbid 2) 20160928:14:41:34|gp_dump-[INFO]:-Master (dbid 1) 20160928:14:41:34|gp_dump-[INFO]:-Starting a transaction on master database tutorial. 20160928:14:41:34|gp_dump-[INFO]:-Getting a lock on pg_class in database tutorial. 20160928:14:41:34|GetTimestampKey-[INFO]:-Timestamp key is generated as it is not provided by the user. 20160928:14:41:34|gp_dump-[INFO]:-About to spin off 3 threads with timestamp key 20160928144134 20160928:14:41:34|gp_dump-[INFO]:-Creating thread to backup dbid 3: host hadoop07 port 40000 database tutorial 20160928:14:41:34|gp_dump-[INFO]:-Creating thread to backup dbid 2: host hadoop04 port 40000 database tutorial 20160928:14:41:34|gp_dump-[INFO]:-Creating thread to backup dbid 1: host hadoop02 port 5432 database tutorial 20160928:14:41:34|gp_dump-[INFO]:-Waiting for remote gp_dump_agent processes to start transactions in serializable isolation level 20160928:14:41:34|gp_dump-[INFO]:-Listening for messages from server on dbid 1 connection 20160928:14:41:34|gp_dump-[INFO]:-Listening for messages from server on dbid 2 connection 20160928:14:41:34|gp_dump-[INFO]:-Listening for messages from server on dbid 3 connection 20160928:14:41:34|gp_dump-[INFO]:-Successfully launched Greenplum Database backup on dbid 2 server 20160928:14:41:34|gp_dump-[INFO]:-Successfully launched Greenplum Database backup on dbid 3 server 20160928:14:41:36|gp_dump-[INFO]:-Successfully launched Greenplum Database backup on dbid 1 server 20160928:14:41:36|gp_dump-[INFO]:-backup succeeded for dbid 1 on host hadoop02 20160928:14:41:36|gp_dump-[INFO]:-All remote gp_dump_agent processes have began transactions in serializable isolation level 20160928:14:41:36|gp_dump-[INFO]:-Waiting for remote gp_dump_agent processes to obtain local locks on dumpable objects 20160928:14:41:36|gp_dump-[INFO]:-All remote gp_dump_agent processes have obtains the necessary locks 20160928:14:41:36|gp_dump-[INFO]:-Committing transaction on the master database,thereby releasing locks. 20160928:14:41:36|gp_dump-[INFO]:-Waiting for all remote gp_dump_agent programs to finish. 20160928:14:42:00|gp_dump-[INFO]:-backup succeeded for dbid 3 on host hadoop07 20160928:14:42:00|gp_dump-[INFO]:-backup succeeded for dbid 2 on host hadoop04 20160928:14:42:00|gp_dump-[INFO]:-All remote gp_dump_agent programs are finished. 20160928:14:42:00|gp_dump-[INFO]:-Report results also written to /data/master/gpseg-1/gp_dump_20160928144134.rpt. Greenplum Database Backup Report Timestamp Key: 20160928144134 gp_dump Command Line: --gp-d=/home/gpadmin/backup tutorial Pass through Command Line Options: None Compression Program: None Backup Type: Full Individual Results segment 1 (dbid 3) Host hadoop07 Port 40000 Database tutorial BackupFile /home/gpadmin/backup/gp_dump_0_3_20160928144134: Succeeded segment 0 (dbid 2) Host hadoop04 Port 40000 Database tutorial BackupFile /home/gpadmin/backup/gp_dump_0_2_20160928144134: Succeeded Master (dbid 1) Host hadoop02 Port 5432 Database tutorial BackupFile /home/gpadmin/backup/gp_dump_1_1_20160928144134: Succeeded Master (dbid 1) Host hadoop02 Port 5432 Database tutorial BackupFile /home/gpadmin/backup/gp_dump_1_1_20160928144134_post_data: Succeeded gp_dump utility finished successfully.
2关闭数据库
[gpadmin@hadoop02 ~]$ gpstop -M fast -a 20160928:14:43:39:002897 gpstop:hadoop02:gpadmin-[INFO]:-Starting gpstop with args: -M fast -a 20160928:14:43:39:002897 gpstop:hadoop02:gpadmin-[INFO]:-Gathering information and validating the environment... 20160928:14:43:39:002897 gpstop:hadoop02:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20160928:14:43:39:002897 gpstop:hadoop02:gpadmin-[INFO]:-Obtaining Segment details from master... 20160928:14:43:39:002897 gpstop:hadoop02:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 4.3.8.1 build 1' 20160928:14:43:39:002897 gpstop:hadoop02:gpadmin-[INFO]:-There are 0 connections to the database 20160928:14:43:39:002897 gpstop:hadoop02:gpadmin-[INFO]:-Commencing Master instance shutdown with mode='fast' 20160928:14:43:39:002897 gpstop:hadoop02:gpadmin-[INFO]:-Master host=hadoop02 20160928:14:43:39:002897 gpstop:hadoop02:gpadmin-[INFO]:-Detected 0 connections to database 20160928:14:43:39:002897 gpstop:hadoop02:gpadmin-[INFO]:-Using standard WAIT mode of 120 seconds 20160928:14:43:39:002897 gpstop:hadoop02:gpadmin-[INFO]:-Commencing Master instance shutdown with mode=fast 20160928:14:43:39:002897 gpstop:hadoop02:gpadmin-[INFO]:-Master segment instance directory=/data/master/gpseg-1 20160928:14:43:40:002897 gpstop:hadoop02:gpadmin-[INFO]:-Attempting forceful termination of any leftover master process 20160928:14:43:40:002897 gpstop:hadoop02:gpadmin-[INFO]:-Terminating processes for segment /data/master/gpseg-1 20160928:14:43:40:002897 gpstop:hadoop02:gpadmin-[INFO]:-Stopping master standby host hadoop03 mode=fast 20160928:14:43:41:002897 gpstop:hadoop02:gpadmin-[INFO]:-Successfully shutdown standby process on hadoop03 20160928:14:43:41:002897 gpstop:hadoop02:gpadmin-[INFO]:-Commencing parallel segment instance shutdown,please wait... 20160928:14:43:41:002897 gpstop:hadoop02:gpadmin-[INFO]:-0.00% of jobs completed 20160928:14:43:51:002897 gpstop:hadoop02:gpadmin-[INFO]:-100.00% of jobs completed 20160928:14:43:51:002897 gpstop:hadoop02:gpadmin-[INFO]:----------------------------------------------------- 20160928:14:43:51:002897 gpstop:hadoop02:gpadmin-[INFO]:- Segments stopped successfully = 2 20160928:14:43:51:002897 gpstop:hadoop02:gpadmin-[INFO]:- Segments with errors during stop = 0 20160928:14:43:51:002897 gpstop:hadoop02:gpadmin-[INFO]:----------------------------------------------------- 20160928:14:43:51:002897 gpstop:hadoop02:gpadmin-[INFO]:-Successfully shutdown 2 of 2 segment instances 20160928:14:43:51:002897 gpstop:hadoop02:gpadmin-[INFO]:-Database successfully shutdown with no errors reported 20160928:14:43:51:002897 gpstop:hadoop02:gpadmin-[INFO]:-Cleaning up leftover gpmmon process 20160928:14:43:51:002897 gpstop:hadoop02:gpadmin-[INFO]:-No leftover gpmmon process found 20160928:14:43:51:002897 gpstop:hadoop02:gpadmin-[INFO]:-Cleaning up leftover gpsmon processes 20160928:14:43:51:002897 gpstop:hadoop02:gpadmin-[INFO]:-No leftover gpsmon processes on some hosts. not attempting forceful termination on these hosts 20160928:14:43:51:002897 gpstop:hadoop02:gpadmin-[INFO]:-Cleaning up leftover shared memory
3master模式启动数据库
[gpadmin@hadoop02 ~]$ gpstart -m 20160928:14:45:01:003022 gpstart:hadoop02:gpadmin-[INFO]:-Starting gpstart with args: -m 20160928:14:45:01:003022 gpstart:hadoop02:gpadmin-[INFO]:-Gathering information and validating the environment... 20160928:14:45:01:003022 gpstart:hadoop02:gpadmin-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 4.3.8.1 build 1' 20160928:14:45:01:003022 gpstart:hadoop02:gpadmin-[INFO]:-Greenplum Catalog Version: '201310150' 20160928:14:45:01:003022 gpstart:hadoop02:gpadmin-[WARNING]:-**************************************************************************** 20160928:14:45:01:003022 gpstart:hadoop02:gpadmin-[WARNING]:-Master-only start requested in a configuration with a standby master. 20160928:14:45:01:003022 gpstart:hadoop02:gpadmin-[WARNING]:-This is advisable only under the direct supervision of Greenplum support. 20160928:14:45:01:003022 gpstart:hadoop02:gpadmin-[WARNING]:-This mode of operation is not supported in a production environment and 20160928:14:45:01:003022 gpstart:hadoop02:gpadmin-[WARNING]:-may lead to a split-brain condition and possible unrecoverable data loss. 20160928:14:45:01:003022 gpstart:hadoop02:gpadmin-[WARNING]:-**************************************************************************** Continue with master-only startup Yy|Nn (default=N): > y 20160928:14:45:03:003022 gpstart:hadoop02:gpadmin-[INFO]:-Starting Master instance in admin mode 20160928:14:45:04:003022 gpstart:hadoop02:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20160928:14:45:04:003022 gpstart:hadoop02:gpadmin-[INFO]:-Obtaining Segment details from master... 20160928:14:45:04:003022 gpstart:hadoop02:gpadmin-[INFO]:-Setting new master era 20160928:14:45:04:003022 gpstart:hadoop02:gpadmin-[INFO]:-Master Started...
4进入管理模式修改元数据
[gpadmin@hadoop02 ~]$ PGOPTIONS="-c gp_session_role=utility" psql psql (8.2.15) Type "help" for help. 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 | hadoop02 | hadoop02 | | 2 | 0 | p | p | s | u | 40000 | hadoop04 | hadoop04 | | 3 | 1 | p | p | s | u | 40000 | hadoop07 | hadoop07 | | 4 | -1 | m | m | s | u | 5432 | hadoop03 | hadoop03 | | (4 rows) gpadmin=# set allow_system_table_mods='dml'; --获取修改系统表的权限 SET gpadmin=# delete from gp_segment_configuration where dbid=3; DELETE 1 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 | hadoop02 | hadoop02 | | 2 | 0 | p | p | s | u | 40000 | hadoop04 | hadoop04 | | 4 | -1 | m | m | s | u | 5432 | hadoop03 | hadoop03 | | (3 rows) gpadmin=# select * from pg_filespace_entry; fsefsoid | fsedbid | fselocation ----------+---------+---------------------- 3052 | 1 | /data/master/gpseg-1 3052 | 2 | /data/primary/gpseg0 3052 | 3 | /data/primary/gpseg1 3052 | 4 | /data/master/gpseg-1 (4 rows) gpadmin=# delete from pg_filespace_entry where fsedbid=3; DELETE 1 gpadmin=# select * from pg_filespace_entry; fsefsoid | fsedbid | fselocation ----------+---------+---------------------- 3052 | 1 | /data/master/gpseg-1 3052 | 2 | /data/primary/gpseg0 3052 | 4 | /data/master/gpseg-1 (3 rows)
5集群模式启动数据库
5.1关闭master模式启动的gp
[gpadmin@hadoop02 ~]$ gpstop -m 20160928:14:52:12:003181 gpstop:hadoop02:gpadmin-[INFO]:-Starting gpstop with args: -m 20160928:14:52:12:003181 gpstop:hadoop02:gpadmin-[INFO]:-Gathering information and validating the environment... 20160928:14:52:12:003181 gpstop:hadoop02:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20160928:14:52:12:003181 gpstop:hadoop02:gpadmin-[INFO]:-Obtaining Segment details from master... 20160928:14:52:12:003181 gpstop:hadoop02:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 4.3.8.1 build 1' 20160928:14:52:12:003181 gpstop:hadoop02:gpadmin-[INFO]:-There are 0 connections to the database 20160928:14:52:12:003181 gpstop:hadoop02:gpadmin-[INFO]:-Commencing Master instance shutdown with mode='smart' 20160928:14:52:12:003181 gpstop:hadoop02:gpadmin-[INFO]:-Master host=hadoop02 20160928:14:52:12:003181 gpstop:hadoop02:gpadmin-[INFO]:-Commencing Master instance shutdown with mode=smart 20160928:14:52:12:003181 gpstop:hadoop02:gpadmin-[INFO]:-Master segment instance directory=/data/master/gpseg-1 20160928:14:52:13:003181 gpstop:hadoop02:gpadmin-[INFO]:-Attempting forceful termination of any leftover master process 20160928:14:52:13:003181 gpstop:hadoop02:gpadmin-[INFO]:-Terminating processes for segment /data/master/gpseg-1
5.2启动数据库
[gpadmin@hadoop02 ~]$ gpstart -a 20160928:14:52:53:003210 gpstart:hadoop02:gpadmin-[INFO]:-Starting gpstart with args: -a 20160928:14:52:53:003210 gpstart:hadoop02:gpadmin-[INFO]:-Gathering information and validating the environment... 20160928:14:52:53:003210 gpstart:hadoop02:gpadmin-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 4.3.8.1 build 1' 20160928:14:52:53:003210 gpstart:hadoop02:gpadmin-[INFO]:-Greenplum Catalog Version: '201310150' 20160928:14:52:53:003210 gpstart:hadoop02:gpadmin-[INFO]:-Starting Master instance in admin mode 20160928:14:52:54:003210 gpstart:hadoop02:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20160928:14:52:54:003210 gpstart:hadoop02:gpadmin-[INFO]:-Obtaining Segment details from master... 20160928:14:52:54:003210 gpstart:hadoop02:gpadmin-[INFO]:-Setting new master era 20160928:14:52:54:003210 gpstart:hadoop02:gpadmin-[INFO]:-Master Started... 20160928:14:52:54:003210 gpstart:hadoop02:gpadmin-[INFO]:-Shutting down master 20160928:14:52:55:003210 gpstart:hadoop02:gpadmin-[INFO]:-Commencing parallel segment instance startup,please wait... .. 20160928:14:52:57:003210 gpstart:hadoop02:gpadmin-[INFO]:-Process results... 20160928:14:52:57:003210 gpstart:hadoop02:gpadmin-[INFO]:----------------------------------------------------- 20160928:14:52:57:003210 gpstart:hadoop02:gpadmin-[INFO]:- Successful segment starts = 1 20160928:14:52:57:003210 gpstart:hadoop02:gpadmin-[INFO]:- Failed segment starts = 0 20160928:14:52:57:003210 gpstart:hadoop02:gpadmin-[INFO]:- Skipped segment starts (segments are marked down in configuration) = 0 20160928:14:52:57:003210 gpstart:hadoop02:gpadmin-[INFO]:----------------------------------------------------- 20160928:14:52:57:003210 gpstart:hadoop02:gpadmin-[INFO]:- 20160928:14:52:57:003210 gpstart:hadoop02:gpadmin-[INFO]:-Successfully started 1 of 1 segment instances 20160928:14:52:57:003210 gpstart:hadoop02:gpadmin-[INFO]:----------------------------------------------------- 20160928:14:52:57:003210 gpstart:hadoop02:gpadmin-[INFO]:-Starting Master instance hadoop02 directory /data/master/gpseg-1 20160928:14:52:58:003210 gpstart:hadoop02:gpadmin-[INFO]:-Command pg_ctl reports Master hadoop02 instance active 20160928:14:52:59:003210 gpstart:hadoop02:gpadmin-[INFO]:-Starting standby master 20160928:14:52:59:003210 gpstart:hadoop02:gpadmin-[INFO]:-Checking if standby master is running on host: hadoop03 in directory: /data/master/gpseg-1 20160928:14:52:59:003210 gpstart:hadoop02:gpadmin-[INFO]:-Database successfully started
6验证节点是否正确删除
[gpadmin@hadoop02 ~]$ gpstate -s 20160928:14:54:01:003347 gpstate:hadoop02:gpadmin-[INFO]:-Starting gpstate with args: -s 20160928:14:54:01:003347 gpstate:hadoop02:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.8.1 build 1' 20160928:14:54:01:003347 gpstate:hadoop02:gpadmin-[INFO]:-master Greenplum Version: 'Postgresql 8.2.15 (Greenplum Database 4.3.8.1 build 1) on x86_64-unknown-linux-gnu,compiled by GCC gcc (GCC) 4.4.2 compiled on Apr 20 2016 08:08:56' 20160928:14:54:01:003347 gpstate:hadoop02:gpadmin-[INFO]:-Obtaining Segment details from master... 20160928:14:54:01:003347 gpstate:hadoop02:gpadmin-[INFO]:-Gathering data from segments... . 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:----------------------------------------------------- 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:--Master Configuration & Status 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:----------------------------------------------------- 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Master host = hadoop02 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Master postgres process ID = 3266 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Master data directory = /data/master/gpseg-1 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Master port = 5432 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Master current role = dispatch 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Greenplum initsystem version = 4.3.8.1 build 1 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Greenplum current version = Postgresql 8.2.15 (Greenplum Database 4.3.8.1 build 1) on x86_64-unknown-linux-gnu,compiled by GCC gcc (GCC) 4.4.2 compiled on Apr 20 2016 08:08:56 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Postgres version = 8.2.15 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Master standby = hadoop03 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Standby master state = Standby host passive 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:----------------------------------------------------- 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:-Segment Instance Status Report 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:----------------------------------------------------- 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Segment Info 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Hostname = hadoop04 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Address = hadoop04 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Datadir = /data/primary/gpseg0 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Port = 40000 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Status 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- PID = 1489 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Configuration reports status as = Up 20160928:14:54:02:003347 gpstate:hadoop02:gpadmin-[INFO]:- Database status = Up
7恢复数据
[gpadmin@hadoop07 backup]$ pwd
/home/gpadmin/backup
[gpadmin@hadoop07 backup]$ ls
gp_dump_0_3_20160928144134 gp_dump_status_0_3_20160928144134
恢复删除节点上的数据，只需将删除掉的节点上的数据重分布即可，gp_dump_0_3_20160928144134是保存在hadoop07 上的，先将其传到那hadoop02上，再执行以下命令。
[gpadmin@hadoop02 ~]$ psql tutorial -f gp_dump_0_3_20160928144134
SET
SET
SET
SET
SET
SET
SET
SET
setval
--------
1
(1 row)
至此节点删除完成
原文链接：https://www.f2er.com/postgresql/194345.html