我正在准备旧版 PostgreSQL 9.5 数据库进行升级。
我需要从表中删除 OID,而不需要长时间锁定表。我已经制定了一个似乎有效的策略,但我希望专家们能够参与进来,因为我计划对系统表进行一些通常不被允许的操作。
在运行 pg_repack 之前,我执行以下修改:
mydata=# update pg_class set relhasoids = false where oid = 'some_schema.a_very_large_table_with_oids'::regclass;
UPDATE 1
mydata=# delete from pg_attribute where attrelid = 'some_schema.a_very_large_table_with_oids'::regclass and attname = 'oid';
DELETE 1
mydata=# \d+ some_schema.a_very_large_table_with_oids;
Table "some_schema.a_very_large_table_with_oids"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------+-----------+----------+--------------+-------------
k | text | not null | extended | |
v | text | | extended | |
Indexes:
"a_very_large_table_with_oids_pkey" PRIMARY KEY, btree (k)
mydata=# select oid,* from some_schema.a_very_large_table_with_oids;
ERROR: column "oid" does not exist
到目前为止,一切都很好。我可以插入更新和删除行,但磁盘上的表结构没有改变。我需要解决这个问题。
因此,完成这些修改后,我使用 vanilla pg_repack 重新打包表。这会将数据复制到磁盘上的新表中,无需任何 oid。
在开始对生产数据执行这些在线操作之前,我希望有专家来关注这个过程,因为这是关键任务。
在非常大的表上,我们将在很长一段时间内处于这种不确定状态,其中 pg_class 和 pg_attribute 将进行强制修改,而 pg_repack 会发挥其魔力。
如果插入/更新/删除似乎正常工作,有什么需要担心的吗?
当 pg_repack 运行时,我在表上的插入/更新/删除似乎工作正常。
我还在附有烤面包桌的桌子上尝试过这个。乍一看,一切似乎都井然有序。
这是一个破坏性操作,我没有使用规范的ALTER TABLE ... SET WITHOUT OIDS
. 如果我遗漏了一些细节(例如在备份恢复或复制过程中),我想确保我不会给自己带来问题。
请指教。
更新:
Laurenz 的出色回答给了我另外两个我没有想到的东西——即依赖性检查和缓存/计划失效。将这些纳入计划非常重要。执行短时间的vacuum来清除陈旧的列是一个很好的后备位置,但是根据我在pg_repack中看到的情况,我认为这会完成同样的事情......来自pg_repack的日志公开了复制所涉及的所有SQL表和交换定义:
mydata=# begin;
BEGIN
mydata=# ALTER TABLE perm.permission_cache SET WITHOUT OIDS;
^CCancel request sent
ERROR: canceling statement due to user request
mydata=# rollback;
ROLLBACK
mydata=# \d+ perm.permission_cache
Table "perm.permission_cache"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+--------------------------+---------------------------------------------------------+----------+--------------+-------------
id | integer | not null default nextval('perm.cache_id_seq'::regclass) | plain | |
company_uuid | uniqueidentifier | not null | plain | |
user_uuid | uniqueidentifier | not null | plain | |
value | boolean | not null | plain | |
cache_date | timestamp with time zone | not null default now() | plain | |
token_name | character varying(255) | | extended | |
Indexes:
"cache_id_pkey" PRIMARY KEY, btree (id)
"permission_cache_user_token_idx" UNIQUE, btree (user_uuid, token_name)
Foreign-key constraints:
"company_uuid_fkey" FOREIGN KEY (company_uuid) REFERENCES company_table(company_uuid) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
"user_uuid_fkey" FOREIGN KEY (user_uuid) REFERENCES user_table(user_uuid) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED
Has OIDs: yes
mydata=# \e
BEGIN
ALTER TABLE
UPDATE 1
DELETE 1
COMMIT
mydata=# \q
$ pg_repack -h 127.0.0.1 -d mydata -Upostgres -t 'perm.permission_cache' -e
LOG: (query) SET search_path TO pg_catalog, pg_temp, public
LOG: (query) SET search_path TO pg_catalog, pg_temp, public
LOG: (query) select repack.version(), repack.version_sql()
LOG: (query) SET statement_timeout = 0
LOG: (query) SET search_path = pg_catalog, pg_temp, public
LOG: (query) SET client_min_messages = warning
LOG: (query) SELECT t.*, coalesce(v.tablespace, t.tablespace_orig) as tablespace_dest FROM repack.tables t, (VALUES (quote_ident($1::text))) as v (tablespace) WHERE (relid = $2::regclass) ORDER BY t.relname, t.schemaname
LOG: (param:0) = (null)
LOG: (param:1) = perm.permission_cache
INFO: repacking table "perm.permission_cache"
LOG: (query) SELECT pg_try_advisory_lock($1, CAST(-2147483648 + $2::bigint AS integer))
LOG: (param:0) = 16185446
LOG: (param:1) = 1046889774
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE perm.permission_cache IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = $1 AND NOT indisvalid
LOG: (param:0) = 1046889774
LOG: (query) SELECT indexrelid, repack.repack_indexdef(indexrelid, indrelid, $2, FALSE) FROM pg_index WHERE indrelid = $1 AND indisvalid
LOG: (param:0) = 1046889774
LOG: (param:1) = (null)
LOG: (query) SELECT repack.conflicted_triggers($1)
LOG: (param:0) = 1046889774
LOG: (query) CREATE TYPE repack.pk_1046889774 AS (id integer)
LOG: (query) CREATE TABLE repack.log_1046889774 (id bigserial PRIMARY KEY, pk repack.pk_1046889774, row perm.permission_cache)
LOG: (query) CREATE TRIGGER repack_trigger AFTER INSERT OR DELETE OR UPDATE ON perm.permission_cache FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('INSERT INTO repack.log_1046889774(pk, row) VALUES( CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.id)::repack.pk_1046889774) END, $2)')
LOG: (query) ALTER TABLE perm.permission_cache ENABLE ALWAYS TRIGGER repack_trigger
LOG: (query) SELECT repack.disable_autovacuum('repack.log_1046889774')
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SELECT pg_backend_pid()
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'relation' AND granted = false AND relation = 1046889774 AND mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL SERIALIZABLE
LOG: (query) SELECT set_config('work_mem', current_setting('maintenance_work_mem'), true)
LOG: (query) SELECT coalesce(array_agg(l.virtualtransaction), '{}') FROM pg_locks AS l LEFT JOIN pg_stat_activity AS a ON l.pid = a.pid LEFT JOIN pg_database AS d ON a.datid = d.oid WHERE l.locktype = 'virtualxid' AND l.pid NOT IN (pg_backend_pid(), $1) AND (l.virtualxid, l.virtualtransaction) <> ('1/1', '-1/0') AND (a.application_name IS NULL OR a.application_name <> $2) AND a.query !~* E'^\\s*vacuum\\s+' AND a.query !~ E'^autovacuum: ' AND ((d.datname IS NULL OR d.datname = current_database()) OR l.database = 0)
LOG: (param:0) = 11918
LOG: (param:1) = pg_repack
LOG: (query) DELETE FROM repack.log_1046889774
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'relation' AND granted = false AND relation = 1046889774 AND mode = 'AccessExclusiveLock' AND pid <> pg_backend_pid()
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE perm.permission_cache IN ACCESS SHARE MODE
LOG: (query) RESET statement_timeout
LOG: (query) CREATE TABLE repack.table_1046889774 WITH (oids = false) TABLESPACE pg_default AS SELECT id,company_uuid,user_uuid,NULL::integer AS "........pg.dropped.4........",value,cache_date,token_name FROM ONLY perm.permission_cache WITH NO DATA
LOG: (query) INSERT INTO repack.table_1046889774 SELECT id,company_uuid,user_uuid,NULL::integer AS "........pg.dropped.4........",value,cache_date,token_name FROM ONLY perm.permission_cache
LOG: (query) ALTER TABLE repack.table_1046889774 DROP COLUMN "........pg.dropped.4........"
LOG: (query) SELECT repack.disable_autovacuum('repack.table_1046889774')
LOG: (query) COMMIT
LOG: (query) CREATE UNIQUE INDEX index_1046889779 ON repack.table_1046889774 USING btree (id)
LOG: (query) CREATE UNIQUE INDEX index_1050932923 ON repack.table_1046889774 USING btree (user_uuid, token_name)
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG: (param:0) = SELECT * FROM repack.log_1046889774 ORDER BY id LIMIT $1
LOG: (param:1) = INSERT INTO repack.table_1046889774 VALUES ($1.*)
LOG: (param:2) = DELETE FROM repack.table_1046889774 WHERE (id) = ($1.id)
LOG: (param:3) = UPDATE repack.table_1046889774 SET (id, company_uuid, user_uuid, value, cache_date, token_name) = ($2.id, $2.company_uuid, $2.user_uuid, $2.value, $2.cache_date, $2.token_name) WHERE (id) = ($1.id)
LOG: (param:4) = DELETE FROM repack.log_1046889774 WHERE id IN (
LOG: (param:5) = 1000
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG: (param:0) = SELECT * FROM repack.log_1046889774 ORDER BY id LIMIT $1
LOG: (param:1) = INSERT INTO repack.table_1046889774 VALUES ($1.*)
LOG: (param:2) = DELETE FROM repack.table_1046889774 WHERE (id) = ($1.id)
LOG: (param:3) = UPDATE repack.table_1046889774 SET (id, company_uuid, user_uuid, value, cache_date, token_name) = ($2.id, $2.company_uuid, $2.user_uuid, $2.value, $2.cache_date, $2.token_name) WHERE (id) = ($1.id)
LOG: (param:4) = DELETE FROM repack.log_1046889774 WHERE id IN (
LOG: (param:5) = 1000
LOG: (query) SELECT pid FROM pg_locks WHERE locktype = 'virtualxid' AND pid <> pg_backend_pid() AND virtualtransaction = ANY($1)
LOG: (param:0) = {}
LOG: (query) SAVEPOINT repack_sp1
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE perm.permission_cache IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)
LOG: (param:0) = SELECT * FROM repack.log_1046889774 ORDER BY id LIMIT $1
LOG: (param:1) = INSERT INTO repack.table_1046889774 VALUES ($1.*)
LOG: (param:2) = DELETE FROM repack.table_1046889774 WHERE (id) = ($1.id)
LOG: (param:3) = UPDATE repack.table_1046889774 SET (id, company_uuid, user_uuid, value, cache_date, token_name) = ($2.id, $2.company_uuid, $2.user_uuid, $2.value, $2.cache_date, $2.token_name) WHERE (id) = ($1.id)
LOG: (param:4) = DELETE FROM repack.log_1046889774 WHERE id IN (
LOG: (param:5) = 0
LOG: (query) SELECT repack.repack_swap($1)
LOG: (param:0) = 1046889774
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SAVEPOINT repack_sp1
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE perm.permission_cache IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT repack.repack_drop($1, $2)
LOG: (param:0) = 1046889774
LOG: (param:1) = 4
LOG: (query) COMMIT
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) ANALYZE perm.permission_cache
LOG: (query) COMMIT
LOG: (query) SELECT pg_advisory_unlock($1, CAST(-2147483648 + $2::bigint AS integer))
LOG: (param:0) = 16185446
LOG: (param:1) = 1046889774
看起来 OID 并不像 Laurenz 猜测的那样在副本中幸存下来。对我来说,使用 pg_repack 是首选,因为它可以在无人值守的情况下运行。在没有 OID 的情况下重写磁盘上的表,这不是与增量 VACUUM 一样好吗?我可能仍然遗漏了一个细节。