需要一个脚本来删除较大的索引，并在环境启动后重新创建这些索引 postgresql 中的备份

Question

我正在寻找帮助，以最大限度地减少pg_basebackup实用程序所花费的时间。

我们正在使用以下命令使用实用程序pg_basbackup备份数据库。

$PGHOME/bin/pg_basebackup -p 5433 -U postgres -P -v -x --format=tar --gzip --compress=6 --pgdata=- -D /opt/backup_db

pg_basebackup不依赖于任何 postgresql 配置参数。如果我选择gzip格式，我们需要按时妥协。

我们计划按照以下步骤进行备份，以最大程度地减少数据库备份时间。如果我错了，请纠正我。

1)识别较大的索引(大小大于 256MB)并删除这些索引。由于数据库的这种大小会减小。 2)备份数据库。通过使用此备份创建一个新环境。 3)在我们创建使用备份创建的环境的环境中重新创建索引。

注意：-我们将使用备份创建一个新环境。在这种环境中，我们应用了很少的ddl和dml，并且由于我们的产品迁移，将其作为实时数据库。

我是邮政数据库的新手。你能帮我构造查询来删除和创建索引吗？

Answer 1

必要的信息在pg_indexes：

我编写了一些脚本，可用于索引维护。通常可以直接对小索引进行重新索引。有一个写锁，但它不需要太多时间。如果无法锁定，则不能使用此脚本：

psql -At -c "select 'REINDEX INDEX' || c.relname from pg_class c where relkind = 'i' and pg_table_size(c.oid) < 1024*1024*1024;" mydb | psql -S mydb

查询：

SELECT c.relname
FROM pg_class
WHERE relkind = 'i'
AND pg_table_size(c.oid) < 1024*1024*1024;

选择小于 1GB 的索引。在脚本中，我为这些索引创建了一个REINDEX INDEX命令。

索引越大通常工作量越大。 由于锁定时间太长，无法REINDEX INDEX。CREATE INDEX CONCURRENTLY命令就是解决方案。但是约束索引需要更多的魔力。可以通过原子方式执行以下操作：

alter table T1
drop constraint T1_pkey,
add constraint T1_pkey PRIMARY KEY USING INDEX T1_xx_pk2; 

几年前，我编写了脚本，该脚本对所选表进行并发重新索引：

create table commands(cmd text);
do $$
declare 
r record;
newname text;
begin
for r in 
SELECT c2.relname as indexname, i.indisprimary,
pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as indexdef,
pg_catalog.pg_get_constraintdef(con.oid, true),
c.oid::regclass as tablename
FROM pg_catalog.pg_class c,
pg_catalog.pg_class c2, pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con 
ON (conrelid = i.indrelid 
AND conindid = i.indexrelid
AND contype IN ('p','u','x'))
WHERE c.oid = 'mytable'::regclass
AND c.oid = i.indrelid AND i.indexrelid = c2.oid
loop
newname = 'fresh_' || md5(r.indexname || r.tablename);
-- inject CONCURRENTLY keyword
insert into commands
values(replace(replace(r.indexdef, 'INDEX', 'INDEX CONCURRENTLY'), r.indexname, newname) || ';');
if r.indisprimary then
insert into commands
values(format('ALTER TABLE %I DROP CONSTRAINT %I, ADD CONSTRAINT %I PRIMARY KEY USING INDEX %I;',
r.tablename, r.indexname, r.indexname, newname));
else
insert into commands
values(format('BEGIN;DROP INDEX %I;', r.indexname) || format('ALTER INDEX %I RENAME TO %I; COMMIT;', newname, r.indexname));
end if;
raise notice 'refreshed index: %', r.indexname;
end loop;
end;
$$ language plpgsql;

所有必需的命令都存储在表commands

只是几点说明：

1. 通常你应该非常小心地删除索引 - 某些应用程序可能会停止工作

2. 当 256MB 的索引有问题(非常小的索引)时，您可能可以使用 pg_dump 进行备份。 pg_dump只备份索引定义。它不备份索引内容 - 也许它是更好的解决方案，而不是pg_basebackup.

3. postgresql-9.2. 是不受支持的 PostgreSQL 版本。您应该进行升级。