合 GreenPlum清理pg_xlog目录释放空间
Tags： GreenPlum清理pg_resetxlog
👉 本文共约267个字，系统预计阅读时间或需2分钟。
示例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 | [gpadmin@gpdb6261 ~]$ du -sh /opt/greenplum/data/primary/gpseg0 /opt/greenplum/data/primary/gpseg1 3.6G /opt/greenplum/data/primary/gpseg0 3.6G /opt/greenplum/data/primary/gpseg1 [gpadmin@gpdb6261 ~]$ [gpadmin@gpdb6261 ~]$ pg_controldata /opt/greenplum/data/primary/gpseg0 | grep -E "Latest checkpoint's NextXID|Latest checkpoint's NextOID" Latest checkpoint's NextXID: 0/4493 Latest checkpoint's NextOID: 33516 [gpadmin@gpdb6261 ~]$ pg_controldata /opt/greenplum/data/primary/gpseg1 | grep -E "Latest checkpoint's NextXID|Latest checkpoint's NextOID" Latest checkpoint's NextXID: 0/4532 Latest checkpoint's NextOID: 26006 [gpadmin@gpdb6261 ~]$ pg_resetxlog -o 33516 -x 4493 -f /opt/greenplum/data/primary/gpseg0 pg_resetxlog: lock file "postmaster.pid" exists Is a server running? If not, delete the lock file and try again. [gpadmin@gpdb6261 ~]$ [gpadmin@gpdb6261 ~]$ gpstop -M fast -a 20240103:15:57:33:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Starting gpstop with args: -M fast -a 20240103:15:57:33:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Gathering information and validating the environment... 20240103:15:57:33:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20240103:15:57:33:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Obtaining Segment details from master... 20240103:15:57:34:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 6.26.1 build commit:0ed93c8b0cd523fc85d49b245fe901c063cf3a0b' 20240103:15:57:34:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Commencing Master instance shutdown with mode='fast' 20240103:15:57:34:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Master segment instance directory=/opt/greenplum/data/master/gpseg-1 20240103:15:57:34:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Attempting forceful termination of any leftover master process 20240103:15:57:34:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Terminating processes for segment /opt/greenplum/data/master/gpseg-1 20240103:15:57:37:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Stopping master standby host gpdb6261 mode=fast 20240103:15:57:37:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Successfully shutdown standby process on gpdb6261 20240103:15:57:37:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Targeting dbid [2, 3] for shutdown 20240103:15:57:37:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Commencing parallel segment instance shutdown, please wait... 20240103:15:57:37:042023 gpstop:gpdb6261:gpadmin-[INFO]:-0.00% of jobs completed 20240103:15:57:39:042023 gpstop:gpdb6261:gpadmin-[INFO]:-100.00% of jobs completed 20240103:15:57:39:042023 gpstop:gpdb6261:gpadmin-[INFO]:----------------------------------------------------- 20240103:15:57:39:042023 gpstop:gpdb6261:gpadmin-[INFO]:- Segments stopped successfully = 2 20240103:15:57:39:042023 gpstop:gpdb6261:gpadmin-[INFO]:- Segments with errors during stop = 0 20240103:15:57:39:042023 gpstop:gpdb6261:gpadmin-[INFO]:----------------------------------------------------- 20240103:15:57:39:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Successfully shutdown 2 of 2 segment instances 20240103:15:57:39:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Database successfully shutdown with no errors reported 20240103:15:57:39:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Cleaning up leftover gpmmon process 20240103:15:57:39:042023 gpstop:gpdb6261:gpadmin-[INFO]:-No leftover gpmmon process found 20240103:15:57:39:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Cleaning up leftover gpsmon processes 20240103:15:57:39:042023 gpstop:gpdb6261:gpadmin-[INFO]:-No leftover gpsmon processes on some hosts. not attempting forceful termination on these hosts 20240103:15:57:39:042023 gpstop:gpdb6261:gpadmin-[INFO]:-Cleaning up leftover shared memory [gpadmin@gpdb6261 ~]$ pg_resetxlog -o 33516 -x 4493 -f /opt/greenplum/data/primary/gpseg0 WARNING: Do not use this on Greenplum. pg_resetxlog might cause data loss and render system irrecoverable. Do you wish to proceed? [yes/no] yes Transaction log reset [gpadmin@gpdb6261 ~]$ du -sh /opt/greenplum/data/primary/gpseg0 /opt/greenplum/data/primary/gpseg1 1.9G /opt/greenplum/data/primary/gpseg0 3.6G /opt/greenplum/data/primary/gpseg1 [gpadmin@gpdb6261 pg_xlog]$ pg_resetxlog -o 26006 -x 4532 -f /opt/greenplum/data/primary/gpseg1 WARNING: Do not use this on Greenplum. pg_resetxlog might cause data loss and render system irrecoverable. Do you wish to proceed? [yes/no] yes Transaction log reset [gpadmin@gpdb6261 pg_xlog]$ ll total 65536 -rw------- 1 gpadmin gpadmin 67108864 Jan 3 15:58 000000010000000000000021 drwx------ 2 gpadmin gpadmin 6 Dec 19 09:33 archive_status [gpadmin@gpdb6261 pg_xlog]$ du -sh /opt/greenplum/data/primary/gpseg0 /opt/greenplum/data/primary/gpseg1 1.9G /opt/greenplum/data/primary/gpseg0 1.9G /opt/greenplum/data/primary/gpseg1 [gpadmin@gpdb6261 pg_xlog]$ du -sh /opt/greenplum/data/primary/gpseg0/pg_xlog /opt/greenplum/data/primary/gpseg1/pg_xlog 64M /opt/greenplum/data/primary/gpseg0/pg_xlog 64M /opt/greenplum/data/primary/gpseg1/pg_xlog [gpadmin@gpdb6261 pg_xlog]$ gpstart -a 20240103:15:59:25:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Starting gpstart with args: -a 20240103:15:59:25:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Gathering information and validating the environment... 20240103:15:59:25:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Greenplum Binary Version: 'postgres (Greenplum Database) 6.26.1 build commit:0ed93c8b0cd523fc85d49b245fe901c063cf3a0b' 20240103:15:59:25:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Greenplum Catalog Version: '301908232' 20240103:15:59:25:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Starting Master instance in admin mode 20240103:15:59:25:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20240103:15:59:25:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Obtaining Segment details from master... 20240103:15:59:25:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Setting new master era 20240103:15:59:25:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Master Started... 20240103:15:59:25:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Shutting down master 20240103:15:59:27:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Commencing parallel segment instance startup, please wait... 20240103:15:59:28:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Process results... 20240103:15:59:28:043894 gpstart:gpdb6261:gpadmin-[INFO]:----------------------------------------------------- 20240103:15:59:28:043894 gpstart:gpdb6261:gpadmin-[INFO]:- Successful segment starts = 2 20240103:15:59:28:043894 gpstart:gpdb6261:gpadmin-[INFO]:- Failed segment starts = 0 20240103:15:59:28:043894 gpstart:gpdb6261:gpadmin-[INFO]:- Skipped segment starts (segments are marked down in configuration) = 0 20240103:15:59:28:043894 gpstart:gpdb6261:gpadmin-[INFO]:----------------------------------------------------- 20240103:15:59:28:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Successfully started 2 of 2 segment instances 20240103:15:59:28:043894 gpstart:gpdb6261:gpadmin-[INFO]:----------------------------------------------------- 20240103:15:59:28:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Starting Master instance gpdb6261 directory /opt/greenplum/data/master/gpseg-1 20240103:15:59:28:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Command pg_ctl reports Master gpdb6261 instance active 20240103:15:59:28:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Connecting to dbname='template1' connect_timeout=15 20240103:15:59:28:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Starting standby master 20240103:15:59:28:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Checking if standby master is running on host: gpdb6261 in directory: /opt/greenplum/data/master_standby/gpseg-1 20240103:15:59:29:043894 gpstart:gpdb6261:gpadmin-[INFO]:-Database successfully started [gpadmin@gpdb6261 pg_xlog]$ gpstate -e 20240103:15:59:36:044617 gpstate:gpdb6261:gpadmin-[INFO]:-Starting gpstate with args: -e 20240103:15:59:36:044617 gpstate:gpdb6261:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.26.1 build commit:0ed93c8b0cd523fc85d49b245fe901c063cf3a0b' 20240103:15:59:36:044617 gpstate:gpdb6261:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.26.1 build commit:0ed93c8b0cd523fc85d49b245fe901c063cf3a0b) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Dec 6 2023 03:38:31' 20240103:15:59:36:044617 gpstate:gpdb6261:gpadmin-[INFO]:-Obtaining Segment details from master... 20240103:15:59:36:044617 gpstate:gpdb6261:gpadmin-[INFO]:-Physical mirroring is not configured [gpadmin@gpdb6261 pg_xlog]$ gpstate -m 20240103:15:59:40:044661 gpstate:gpdb6261:gpadmin-[INFO]:-Starting gpstate with args: -m 20240103:15:59:40:044661 gpstate:gpdb6261:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 6.26.1 build commit:0ed93c8b0cd523fc85d49b245fe901c063cf3a0b' 20240103:15:59:40:044661 gpstate:gpdb6261:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 9.4.26 (Greenplum Database 6.26.1 build commit:0ed93c8b0cd523fc85d49b245fe901c063cf3a0b) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Dec 6 2023 03:38:31' 20240103:15:59:40:044661 gpstate:gpdb6261:gpadmin-[INFO]:-Obtaining Segment details from master... 20240103:15:59:40:044661 gpstate:gpdb6261:gpadmin-[WARNING]:-------------------------------------------------------------- 20240103:15:59:40:044661 gpstate:gpdb6261:gpadmin-[WARNING]:-Mirror not used 20240103:15:59:40:044661 gpstate:gpdb6261:gpadmin-[WARNING]:-------------------------------------------------------------- [gpadmin@gpdb6261 pg_xlog]$ psql psql (9.4.26) Type "help" for help. postgres=# create database db2; CREATE DATABASE postgres=# \q [gpadmin@gpdb6261 pg_xlog]$ du -sh /opt/greenplum/data/primary/gpseg0/pg_xlog /opt/greenplum/data/primary/gpseg1/pg_xlog 64M /opt/greenplum/data/primary/gpseg0/pg_xlog 64M /opt/greenplum/data/primary/gpseg1/pg_xlog [gpadmin@gpdb6261 pg_xlog]$ |
总结
方法1、可以使用如下步骤来清理pg_xlog目录：
1 2 3 4 5 | gpstop -M fast -a pg_controldata /opt/greenplum/data/primary/gpseg0 | grep -E "Latest checkpoint's NextXID|Latest checkpoint's NextOID" pg_resetxlog -o 16395 -x 735 -f /opt/greenplum/data/primary/gpseg0 # -o 参数为查询到的NextOID，-x 参数为查询到的NextXID，-f 参数为对应的数据存储目录 |
方法2、修改max_slot_wal_keep_size参数
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
