PostgreSQL对象重组工具【pg_reorg】
时间:2022-03-13 23:16
Description:
pg_reorg is an utility program toreorganize tables in PostgreSQL databases. Unlike clusterdb, it doesn‘t blockany selections and updates during reorganization. You can choose one of thefollowing methods to reorganize.
-
重组过程中会增大I/O压力,执行重组时应避开系统繁忙的时间段;
-
重组过程中需要创建一些临时对象,所以执行重组时应确保有足够的磁盘空间。
99549.960 ms
3、限制
Temp tables
pg_reorg cannot reorganize temp tables.
GiST indexes
pg_reorg cannot reorganize tables usingGiST indexes.
DDL commands
You cannot do DDL commands except VACUUMand ANALYZE during pg_reorg. In many cases pg_reorg will fail and rollback collectly,but there are some cases which may result in data-corruption .
TRUNCATE
TRUNCATE islost. Deleted rows still exist after pg_reorg.
CREATE INDEX
It causes indexcorruptions.
ALTER TABLE ... ADD COLUMN
It causes lostof data. Newly added columns are initialized with NULLs.
ALTER TABLE ... ALTER COLUMN TYPE
It causes datacorruptions.
ALTER TABLE ... SET TABLESPACE
It causes datacorruptions by wrong relfilenode.
注意:
附
LOG: statement: SET statement_timeout = 0
LOG: statement: SET search_path = pg_catalog, pg_temp, public
LOG: statement: SET client_min_messages = warning
LOG: execute <unnamed>: SELECT * FROM reorg.tables WHERE relid =$1::regclass
DETAIL: parameters: $1 = ‘t1‘
LOG: statement: BEGIN ISOLATION LEVEL READ COMMITTED
LOG: statement: SET LOCAL statement_timeout = 100
LOG: statement: LOCK TABLE t1 IN ACCESS EXCLUSIVE MODE
LOG: statement: RESET statement_timeout
LOG: execute <unnamed>: SELECT reorg.conflicted_triggers($1)
DETAIL: parameters: $1 = ‘16843‘
LOG: statement: CREATE TYPE reorg.pk_16843 AS (id integer)
LOG: statement: CREATE TABLE reorg.log_16843 (id bigserial PRIMARY KEY, pkreorg.pk_16843, row t1)
LOG: statement: CREATE TRIGGER z_reorg_trigger BEFORE INSERT OR DELETE ORUPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE reorg.reorg_trigger(‘INSERT INTOreorg.log_16843(pk, row) VALUES( CASE WHEN $1 IS NULL THEN NULL ELSE(ROW($1.id)::reorg.pk_16843) END, $2)‘)
LOG: statement: SELECT reorg.disable_autovacuum(‘reorg.log_16843‘)
LOG: statement: COMMIT
LOG: statement: BEGIN ISOLATION LEVEL SERIALIZABLE
LOG: statement: SELECT set_config(‘work_mem‘,current_setting(‘maintenance_work_mem‘), true)
LOG: statement: SET LOCAL synchronize_seqscans = off
LOG: statement: SELECT reorg.array_accum(virtualtransaction) FROM pg_locksWHERE locktype = ‘virtualxid‘ AND pid <> pg_backend_pid() AND(virtualxid, virtualtransaction) <> (‘1/1‘, ‘-1/0‘)
LOG: statement: DELETE FROM reorg.log_16843
LOG: statement: CREATE TABLE reorg.table_16843 WITH (oids=false) TABLESPACEpg_default AS SELECT id,name FROM ONLY t1
LOG: statement: SELECT reorg.disable_autovacuum(‘reorg.table_16843‘)
LOG: statement: COMMIT
LOG: execute <unnamed>: SELECT indexrelid,reorg.reorg_indexdef(indexrelid, indrelid), indisvalid,pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = $1
DETAIL: parameters: $1 = ‘16843‘
LOG: statement: CREATE UNIQUE INDEX index_16849 ON reorg.table_16843 USINGbtree (id)
LOG: execute <unnamed>: SELECT reorg.reorg_apply($1, $2, $3, $4, $5,$6)
DETAIL: parameters: $1 = ‘SELECT * FROM reorg.log_16843 ORDER BY id LIMIT $1‘,$2 = ‘INSERT INTO reorg.table_16843 VALUES ($1.*)‘, $3 = ‘DELETE FROMreorg.table_16843 WHERE (id) = ($1.id)‘, $4 = ‘UPDATE reorg.table_16843 SET(id, name) = ($2.id, $2.name) WHERE (id) = ($1.id)‘, $5 = ‘DELETE FROMreorg.log_16843 WHERE id = $1‘, $6 = ‘1000‘
LOG: execute <unnamed>: SELECT pid FROM pg_locks WHERE locktype =‘virtualxid‘ AND pid <> pg_backend_pid() AND virtualtransaction = ANY($1)
DETAIL: parameters: $1 = ‘{}‘
LOG: statement: BEGIN ISOLATION LEVEL READ COMMITTED
LOG: statement: SET LOCAL statement_timeout = 100
LOG: statement: LOCK TABLE t1 IN ACCESS EXCLUSIVE MODE
LOG: statement: RESET statement_timeout
LOG: execute <unnamed>: SELECT reorg.reorg_apply($1, $2, $3, $4, $5,$6)
DETAIL: parameters: $1 = ‘SELECT * FROM reorg.log_16843 ORDER BY id LIMIT $1‘,$2 = ‘INSERT INTO reorg.table_16843 VALUES ($1.*)‘, $3 = ‘DELETE FROMreorg.table_16843 WHERE (id) = ($1.id)‘, $4 = ‘UPDATE reorg.table_16843 SET(id, name) = ($2.id, $2.name) WHERE (id) = ($1.id)‘, $5 = ‘DELETE FROMreorg.log_16843 WHERE id = $1‘, $6 = ‘0‘
LOG: execute <unnamed>: SELECT reorg.reorg_swap($1)
DETAIL: parameters: $1 = ‘16843‘
LOG: statement: COMMIT
LOG: statement: BEGIN ISOLATION LEVEL READ COMMITTED
LOG: execute <unnamed>: SELECT reorg.reorg_drop($1)
DETAIL: parameters: $1 = ‘16843‘
LOG: statement: COMMIT
LOG: statement: BEGIN ISOLATION LEVEL READ COMMITTED
LOG: statement: ANALYZE t1
LOG: statement: COMMIT