amcheck报告以下错误：
select bt_index_parent_check(34465);
ERROR: item order invariant violated for index "admin_access_rights_pkey"
DETAIL: Lower index tid=(282,71) (points to heap tid=(201,46)) higher index tid=(282,72) (points to heap tid=(197,38)) page lsn=396C/DDF2FD00
我该如何修复这个问题？
文档表明这REINDEX可能没有帮助。
REINDEX 可能无法有效修复损坏。
我正在尝试使用以下脚本将 PostgreSQL 12 集群升级到版本 13：
/usr/lib/postgresql/13/bin/pg_upgrade --check \
--old-datadir=/var/lib/postgresql/12/main \
--new-datadir=/var/lib/postgresql/13/main \
--old-bindir=/usr/lib/postgresql/12/bin \
--new-bindir=/usr/lib/postgresql/13/bin \
--old-options=' -c config_file=/etc/postgresql/12/main/postgresql.conf' \
--new-options=' -c config_file=/etc/postgresql/13/main/postgresql.conf' \
--old-port=5432 \
--new-port=5433
支票返回：
*Clusters are compatible*
然而，在实际升级过程中，由于pg_catalog.pg_pltemplate表：
pg_restore: creating ACL "pg_catalog.TABLE "pg_pltemplate""
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 17728; 0 0 ACL TABLE "pg_pltemplate" postgres
pg_restore: error: could not execute query: ERROR: relation "pg_catalog.pg_pltemplate" does not exist
这似乎是一个老问题，但是升级脚本不会检查这些模板。
到目前为止，这个查询似乎应该返回一个空结果，否则你会遇到麻烦：
$ psql -c "SELECT * FROM information_schema.role_table_grants WHERE table_name='pg_pltemplate';"
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
----------+----------+---------------+--------------+---------------+----------------+--------------+----------------
postgres | postgres | postgres | pg_catalog | pg_pltemplate | TRIGGER | YES | NO
postgres | postgres | postgres | pg_catalog | pg_pltemplate | REFERENCES | YES | NO
postgres | postgres | postgres | pg_catalog | pg_pltemplate | TRUNCATE | YES | NO
postgres | postgres | postgres | pg_catalog | pg_pltemplate | DELETE | YES | NO
postgres | postgres | postgres | pg_catalog | pg_pltemplate | UPDATE | YES | NO
postgres | postgres | postgres | pg_catalog | pg_pltemplate | SELECT | YES | YES
postgres | postgres | postgres | pg_catalog | pg_pltemplate | INSERT | YES | NO
postgres | PUBLIC | postgres | pg_catalog | pg_pltemplate | SELECT | NO | YES
撤销这些特权：
REVOKE SELECT ON "pg_catalog"."pg_pltemplate" FROM PUBLIC;
REVOKE ALL ON "pg_catalog"."pg_pltemplate" FROM postgres;
REVOKE由于语句被保存到架构中，因此并没有真正的帮助：
pg_restore: error: could not execute query: ERROR: relation "pg_catalog.pg_pltemplate" does not exist
Command was: REVOKE ALL ON TABLE "pg_catalog"."pg_pltemplate" FROM "postgres";
REVOKE SELECT ON TABLE "pg_catalog"."pg_pltemplate" FROM PUBLIC;
可以使用以下方法检查（结果也应该为空）：
pg_dump --port 5432 --schema-only --quote-all-identifiers | grep pg_pltemplate
在执行升级之前。
任何想法如何完全摆脱pg_catalog.pg_pltemplate桌子？
我正在尝试将流复制设置到 Barman 服务器。
Barman 配置非常基本，我使用的是backup_method = postgres.
[barman]
barman_home = /var/lib/barman
barman_user = barman
log_file = /var/log/barman/barman.log
log_level = DEBUG
archiver = true
backup_method = postgres
backup_options = concurrent_backup
compression = gzip
minimum_redundancy = 1
retention_policy = RECOVERY WINDOW OF 7 DAYS
retention_policy_mode = auto
streaming_archiver = true
wal_retention_policy = main
configuration_files_directory = /etc/barman.conf.d
我会打电话给数据库服务器pg
[pg]
description = "pg"
ssh_command = ssh [email protected]
conninfo = user=barman dbname=postgres host=pg.example.com port=5432
我已经配置了 hba 规则并在's home设置了一个.pgpass文件。barman/var/lib/barman/.pgpass
与服务器的测试连接pg工作正常：
barman@b01:~$ psql -c 'SELECT version()' -U barman -h pg.example.com postgres
version
----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 10.7 (Debian 10.7-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
(1 row)
但是barman check pg失败得很惨，我不知道为什么：
$ barman check pg
Server pg:
WAL archive: FAILED (please make sure WAL shipping is setup)
PostgreSQL: OK
is_superuser: OK
PostgreSQL streaming: FAILED (fe_sendauth: no password supplied)
wal_level: OK
directories: OK
retention policy settings: OK
backup maximum age: OK (no last_backup_maximum_age provided)
compression settings: OK
failed backups: OK (there are 0 failed backups)
minimum redundancy requirements: FAILED (have 0 backups, expected at least 1)
pg_basebackup: OK
pg_basebackup compatible: FAILED (PostgreSQL version: None, pg_basebackup version: 11.2-1.pgdg90+1))
pg_basebackup supports tablespaces mapping: OK
archive_mode: OK
archive_command: FAILED (please set it accordingly to documentation)
pg_receivexlog: OK
pg_receivexlog compatible: FAILED (PostgreSQL version: None, pg_receivexlog version: 11.2-1.pgdg90+1))
receive-wal running: FAILED (See the Barman log file for more details)
archiver errors: OK
有没有更好的方法来调试身份验证问题？是.pgpass偶数加载下barman check吗？
pg.example.com:5432:postgres:barman:s3cr3tP2ssw0rd
我有一个流量很大的 PostgreSQL 9.6 数据库。我定期运行pg_repack回收表/索引中未使用的空间。在较大的表上，重新打包有时无法完成导致使用 PostgreSQL 报告数据库正在使用的更多磁盘空间的过程。
我使用以下查询来报告每个数据库的大小：
SELECT schema_name,
pg_size_pretty(sum(table_size)::bigint),
(sum(table_size) / pg_database_size(current_database())) * 100 as pct
FROM (
SELECT pg_catalog.pg_namespace.nspname as schema_name,
pg_relation_size(pg_catalog.pg_class.oid) as table_size
FROM pg_catalog.pg_class
JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY pct DESC;
schema_name | pg_size_pretty | pct
--------------------+----------------+------------------------------------
production | 605 GB | 62.70818987165323895600
dev | 116 GB | 12.05199834243206743500
pg_toast | 12 GB | 1.26824870382580753200
staging | 12 GB | 1.26031018275065892500
test | 1497 MB | 0.15143744784303601600
pg_catalog | 26 MB | 0.002621403693008641646300
public | 624 kB | 0.000061661486144352849300
information_schema | 96 kB | 0.000009486382483746592200
repack | 0 bytes | 0.00000000000000000000000000000000
这给出了一个想法，占用的空间应该在750GB. 然而实际上 PostgreSQL 的使用量几乎是原来的两倍：
$ du -hs /var/lib/postgresql/9.6/main/base/
1.3T /var/lib/postgresql/9.6/main/base/
问题的一部分是pgsql_tmp，那就是占领349GB。有没有一种安全的方法可以从中删除未使用的文件pgsql_tmp？
349G /var/lib/postgresql/9.6/main/base/pgsql_tmp/
我已经尝试过VACUUM FULL并pg_repack在最大的桌子上没有任何成功。如何摆脱浪费的磁盘空间的唯一方法似乎是将表转储到 SQL 并重新导入到干净的服务器中。
我有一个 PostgreSQL 9.5 集群master和standby使用repmgr. Master 配置为保留4000WAL 文件：
wal_level = logical
hot_standby = on
archive_command = 'test ! -f /mnt/share/psql/archive/psql/%f && cp %p /mnt/share/psql/archive/psql/%f'
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
wal_keep_segments = 4000
在某些时候，standby服务器落后于主服务器（我不知道为什么会发生这种情况）：
2017-01-28 23:49:24 UTC ERROR: current transaction is aborted, commands ignored until end of transaction block
2017-01-28 23:49:24 UTC STATEMENT:
2017-01-28 23:51:10 UTC LOG: invalid magic number 0000 in log segment 0000000200001E5A000000C8, offset 5201920
2017-01-28 23:51:10 UTC LOG: started streaming WAL from primary at 1E5A/C8000000 on timeline 2
2017-01-29 00:04:59 UTC FATAL: could not send data to WAL stream: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
2017-01-29 00:16:10 UTC LOG: invalid magic number 0000 in log segment 0000000200001E5B000000C5, offset 5242880
2017-01-29 00:16:10 UTC LOG: started streaming WAL from primary at 1E5B/C5000000 on timeline 2
2017-01-29 00:50:07 UTC FATAL: could not send data to WAL stream: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
2017-01-29 00:50:07 UTC LOG: invalid magic number 0000 in log segment 0000000200001E5D0000000A, offset 5373952
2017-01-29 00:50:22 UTC LOG: started streaming WAL from primary at 1E5D/A000000 on timeline 2
2017-01-29 03:29:35 UTC FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 0000000200001E64000000B7 has already been removed
显然 master 删除了需要的 WAL 0000000200001E64000000B7，但该文件仍然存在于存档中。无论如何，standby重新启动数据库后似乎达到一致状态：
...
2017-01-29 13:45:35 UTC LOG: restored log file "0000000200001E64000000B1" from archive
2017-01-29 13:45:36 UTC LOG: restored log file "0000000200001E64000000B2" from archive
2017-01-29 13:45:36 UTC LOG: restored log file "0000000200001E64000000B3" from archive
2017-01-29 13:45:36 UTC LOG: restored log file "0000000200001E64000000B4" from archive
2017-01-29 13:45:37 UTC LOG: restored log file "0000000200001E64000000B5" from archive
2017-01-29 13:45:37 UTC LOG: restored log file "0000000200001E64000000B6" from archive
2017-01-29 13:45:37 UTC LOG: restored log file "0000000200001E64000000B7" from archive
2017-01-29 13:45:38 UTC LOG: consistent recovery state reached at 1E64/B7DFFD78
2017-01-29 13:45:38 UTC LOG: recovery stopping after reaching consistency
2017-01-29 13:45:38 UTC LOG: recovery has paused
2017-01-29 13:45:38 UTC HINT: Execute pg_xlog_replay_resume() to continue.
2017-01-29 13:45:38 UTC LOG: database system is ready to accept read only connections
PostgreSQL 建议执行pg_xlog_replay_resume()，但这会导致将standby 提升为master，从而出现脑裂的情况。
$ repmgr cluster show
Role | Name | Upstream | Connection String
----------+-------------------------|-------------------------|-----------------------------------------
* master | psql01a | | host=psql01a user=repmgr
standby | psql01b | psql01a | host=psql01b user=repmgr
recovery.conf：
restore_command = 'cp /mnt/share/psql/archive/psql/%f %p'
recovery_target_inclusive = true
recovery_target = 'immediate'
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'user=repmgr port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres host=psql01a application_name=psql01b password=ZDIzNjk2OTM2MWYyNjNiYzk5ZDVhMWIw'
recovery_min_apply_delay = 0
PostgreSQL 9.5 带有一个新参数recovery_target_action，它可以有 3个值recovery.conf：
pause（默认）恢复将被暂停shutdown达到恢复目标后将停止服务器promote表示恢复过程将完成，服务器将开始接受连接promote根据文档，这似乎是合乎逻辑的选择，但它再次导致脑裂情况：
2017-01-29 19:31:27 UTC LOG: consistent recovery state reached at 1E64/B7DFFD78
2017-01-29 19:31:27 UTC LOG: recovery stopping after reaching consistency
2017-01-29 19:31:27 UTC LOG: redo done at 1E64/B7DFFD78
2017-01-29 19:31:27 UTC LOG: last completed transaction was at log time 2017-01-29 00:32:06.442239+00
2017-01-29 19:31:27 UTC LOG: database system is ready to accept read only connections
cp: cannot stat '/mnt/share/psql/archive/psql/00000003.history': No such file or directory
2017-01-29 19:31:27 UTC LOG: selected new timeline ID: 3
cp: cannot stat '/mnt/share/psql/archive/psql/00000002.history': No such file or directory
2017-01-29 19:31:28 UTC LOG: archive recovery complete
$ repmgr cluster show
Role | Name | Upstream | Connection String
----------+-------------------------|-------------------------|-----------------------------------------
* master | psql01a | | host=psql01a user=repmgr
* master | psql01b | psql01a | host=psql01b user=repmgr
问题是如何在不将备用提升为主控的情况下恢复 WAL 流式传输？是否可以自动执行此操作？
我正在尝试使用以下方法设置副本repmgr：
repmgr -D /var/lib/postgresql/9.3/main -p 5432 -U repmgr -R postgres \
--verbose standby clone psql.master.example.com
repmgr --verbose standby register
我已设法同步数据库，但备用副本无法启动：
postgres@psql01a:~$ /usr/lib/postgresql/9.3/bin/postgres --single -D /var/lib/postgresql/9.3/main -P -d 1
2016-04-18 14:02:05 UTC [30048]: [1-1] user=,db=,client= LOG: database system was shut down in recovery at 2016-04-18 14:00:51 UTC
2016-04-18 14:02:05 UTC [30048]: [2-1] user=,db=,client= LOG: entering standby mode
2016-04-18 14:02:05 UTC [30048]: [3-1] user=,db=,client= DEBUG: checkpoint record is at 27B5/BA68B550
2016-04-18 14:02:05 UTC [30048]: [4-1] user=,db=,client= DEBUG: redo record is at 27B5/B3626B20; shutdown FALSE
2016-04-18 14:02:05 UTC [30048]: [5-1] user=,db=,client= DEBUG: next transaction ID: 0/2281005353; next OID: 230242292
2016-04-18 14:02:05 UTC [30048]: [6-1] user=,db=,client= DEBUG: next MultiXactId: 879585; next MultiXactOffset: 1823275
2016-04-18 14:02:05 UTC [30048]: [7-1] user=,db=,client= DEBUG: oldest unfrozen transaction ID: 2094018845, in database 134461654
2016-04-18 14:02:05 UTC [30048]: [8-1] user=,db=,client= DEBUG: oldest MultiXactId: 1, in database 16546
2016-04-18 14:02:05 UTC [30048]: [9-1] user=,db=,client= DEBUG: transaction ID wrap limit is 4241502492, limited by database with OID 134461654
2016-04-18 14:02:05 UTC [30048]: [10-1] user=,db=,client= DEBUG: MultiXactId wrap limit is 2147483648, limited by database with OID 16546
2016-04-18 14:02:05 UTC [30048]: [11-1] user=,db=,client= DEBUG: resetting unlogged relations: cleanup 1 init 0
2016-04-18 14:02:05 UTC [30048]: [12-1] user=,db=,client= DEBUG: initializing for hot standby
2016-04-18 14:02:05 UTC [30048]: [13-1] user=,db=,client= LOG: redo starts at 27B5/B3626B20
2016-04-18 14:02:05 UTC [30048]: [14-1] user=,db=,client= DEBUG: recovery snapshots are now enabled
2016-04-18 14:02:05 UTC [30048]: [15-1] user=,db=,client= CONTEXT: xlog redo running xacts: nextXid 2281009749 latestCompletedXid 2281009746 oldestRunningXid 2281009747; 2 xacts: 2281009748 2281009747
2016-04-18 14:02:05 UTC [30048]: [16-1] user=,db=,client= PANIC: btree_xlog_delete_get_latestRemovedXid: cannot operate with inconsistent data
2016-04-18 14:02:05 UTC [30048]: [17-1] user=,db=,client= CONTEXT: xlog redo delete: index 1663/16546/215742765; iblk 363218, heap 1663/16546/215740352;
Aborted
知道如何启动副本吗？