我正在准备旧版 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 一样好吗?我可能仍然遗漏了一个细节。
您想避免因表重写而导致长时间停机
ALTER TABLE ... SET WITHOUT OIDS
,对吗?安全的解决方案是运行
ALTER TABLE ... SET WITHOUT OIDS
并减少停机时间。你的方式比较危险,但可以避免长时间ACCESS EXCLUSIVE
锁在桌子上。您正在做的事情相当于 PostgreSQL 在提交 6d1e361852
ALTER TABLE ... SET WITHOUT OIDS
之前所做的事情,除了您忘记锁定表,没有检查依赖关系,并且没有使计划和缓存的元数据信息无效。其他会话也有此表。ACCESS EXCLUSIVE
oid
为了避免这些问题,请执行以下操作:
首先,确保没有任何内容依赖于该
oid
列:然后,执行您建议的更改,但锁定表并使所有计划无效:
最后,您可以使用 pg_repack 重写表而无需长时间
ACCESS EXCLUSIVE
锁定。作为 pg_repack 的替代方案,您可以分块更新表,以便重写各个行。确保
VACUUM
批次之间:之后,桌子应该没问题。尽管如此,还是要进行测试升级并进行良好的测试。
关于为什么
ALTER TABLE ... SET WITHOUT OIDS
重写表的一些背景信息,与以下不同ALTER TABLE ... DROP COLUMN ...
:在版本 8.4 之前,PostgreSQL 不需要对任一操作进行表重写。但有一个原因导致
ALTER TABLE ... SET WITHOUT OIDS
改写了表格;请参阅此错误报告和随后的线程中的讨论。那里提到的问题不会影响您pg_upgrade
,但如果您有一个表,其元组中物理上有 OID,而 PostgreSQL 不希望有任何 OID,则可能会出现其他微妙的问题。这就是为什么ALTER TABLE ... SET WITHOUT OIDS
要重写该表的原因。您的目录操作将使表处于类似于
ALTER TABLE ... SET WITHOUT OIDS
PostgreSQL 8.3 及更早版本中的状态。现在pg_upgrade
文档提到9.2及更高版本支持升级,在提交e469f0aaf3之前它提到支持8.4及更高版本。虽然历史文档中没有明确提及以您的方式删除 OID 的表会构成问题,但这种情况显然是意外的,根本没有经过测试并且不受支持。您将来可能会遇到有趣的问题。我当然不会为我的数据库冒这个风险。