Quickly implement vacuum full for system tables in PostgreSQL

Vacuum full locks the table and is very inefficient. In practice, vacuum cannot be used to narrow down the pg_class, resulting in a long downtime.
In fact, the simplest way to implement vacuum full is to copy a table again, create table B as select * from a; then use table B instead of table.
Since pg_class is the basis of all tables, we cannot replace it even if we copy it. In this way, we can replace the underlying data file in another way. Because pg_class has a system column, oid, we cannot directly copy this column, So we adopt a roundabout method.
1. create a new table with oid, create table cxf with oids as select * from pg_class limit 0;
Create the same index as pg_class in this table, because if we replace the underlying data file with the old index file, it will be messy.
2. Export the entire pg_class into text using the copy command (using the with oids parameter) to export the oid
3. Then, run the copy with oids command to save the data file to the table created in step 1.
4. Stop the database (Write All cached data to a file)
5. Replace the underlying data file and index file
6. Restart the database.

 

Using this method, the data file size of pg_class is changed from 1.4G to 63 M, and pg_attribute is changed from 1.5G to 602 M, achieving the vacuum full effect. The specific steps are as follows:

1. Check the table and index information about pg_class.
Aligputf8 = # select oid, relname, relfilenode from pg_class where relname like '% pg_class % ';
Oid | relname | relfilenode
---------- + ---------------------------- + -------------
1259 | pg_class | 1259
2662 | pg_class_oid_index | 15687137
2663 | pg_class_relname_nsp_index | 15687138

2. view these files in the database base directory.
Www.bkjia.com @ bkjia:/home/gpadmin/cxf/aligp-1/base/16384> ll-h 1259 1259.*15687137 15687137.*15687138 15687138 .*
Ls: 15687137. *: No such file or directory
Ls: 15687138. *: No such file or directory
-Rw ------- 1 gpadmin 1.0G Dec 11 1259
-Rw ------- 1 gpadmin 395 M Dec 11 1259.1
-Rw ------- 1 gpadmin 20 M Dec 11 20:16 15687137
-Rw ------- 1 gpadmin 83 M Dec 11 20:16 15687138

3. Create a table with the same structure as pg_class. The with oids must be added during table creation.
Aligputf8 = # create table cxf with oids as select * from pg_class limit 0;
SELECT 0
Aligputf8 = # create index cxf_pg_class_oid_index on cxf (oid );
CREATE INDEX
Aligputf8 = # create index cxf_pg_class_relname_nsp_index on cxf (relname, relnamespace );
CREATE INDEX
Create an index. when starting the database, he will go to pg_class and search for records through the index. Therefore, we need to re-create the index and overwrite the underlying file together.
Oid | relname | relfilenode
---------- + -------------------------------- + -------------
19317362 | cxf | 19317362
19317367 | cxf_pg_class_oid_index | 19317367
19317368 | cxf_pg_class_relname_nsp_index | 19317368
(3 rows)

We can see that the field information of the two tables is consistent with the field content.
Aligputf8 = # select count (*) from pg_attribute where attrelid = 19317362;
Count
-------
38
(1 row)

Aligputf8 = # select count (*) from pg_attribute where attrelid = 1259;
Count
-------
38
(1 row)

4. view the current data volume of pg_class
Aligputf8 = # select count (*) from pg_class;
Count
--------
331799
(1 row)

5. Export pg_class to a file and then import it to cxf.
Aligputf8 = # copy pg_class to '/tmp/pg_class_cxf' with null as ''delimiter E'/5' oids;
COPY 1, 331799
Aligputf8 = # copy cxf from '/tmp/pg_class_cxf' with null as ''delimiter E'/5' oids;
COPY 1, 331799

6. shut down the database, back up the existing pg_class data file and index file to avoid exceptions, and then replace the underlying data file (the database must be closed if the database is not closed, the copied information may not have been flushed to the hard disk. It may be a problem to overwrite the original file. I tried it before. As a result, the pg_class table cannot be found due to data loss, the entire database is unavailable ).
$ GPHOME/bin/pg_ctl-w-D/home/gpadmin/cxf/aligp-1/-o "-E-I-p 5132 -- silent-mode = true" stop
Www.bkjia.com @ bkjia:/home/gpadmin/cxf/aligp-1/base/16384> ll-h 19317362 19317367 19317368
-Rw ------- 1 gpadmin 63 M Dec 11 20:39 19317362
-Rw ------- 1 gpadmin 9.8 M Dec 11 19317367
-Rw ------- 1 gpadmin 47 M Dec 11 20:39 19317368

Www.bkjia.com @ bkjia:/home/gpadmin/cxf/aligp-1/base/16384> mv 1259 1259.bak
Www.bkjia.com @ bkjia:/home/gpadmin/cxf/aligp-1/base/16384> mv 1259.1 1259.1.bak
Www.bkjia.com @ bkjia:/home/gpadmin/cxf/aligp-1/base/16384> mv 15687137 15687137.bak
Www.bkjia.com @ bkjia:/home/gpadmin/cxf/aligp-1/base/16384> mv 15687138 15687138.bak
Www.bkjia.com @ bkjia:/home/gpadmin/cxf/aligp-1/base/16384> cp 19317362 1259
Www.bkjia.com @ bkjia:/home/gpadmin/cxf/aligp-1/base/16384> cp 19317367 15687137
Www.bkjia.com @ bkjia:/home/gpadmin/cxf/aligp-1/base/16384> cp 19317368 15687138
Www.bkjia.com @ bkjia:/home/gpadmin/cxf/aligp-1/base/16384>

7. Restart the database and verify
Www.bkjia.com @ bkjia:/home/gpadmin/cxf/aligp-1/base/16384> PGOPTIONS = "-c gp_session_role = utility" psql-E
Psql (8.2.13)
Type "help" for help.

Aligputf8 = # select count (*) from pg_class;
Count
--------
331799
(1 row)

Aligputf8 = # explain select * from pg_class where oid = 1259;
QUERY PLAN
Bytes ---------------------------------------------------------------------------------------
Index Scan using pg_class_oid_index on pg_class (cost = 0. 00 .. 200.58 rows = 1 width = 268)
Index Cond: oid = 1259: oid
(2 rows)

8. Use the same method for pg_attribute. In this case, insert directly. You do not need to copy the data to an external table because the table has no oid.
Data Volume changed from 602 GB to MB