主页 / user-27707

Tombart's questions

Martin Hope
Tombart
Asked: 2022-01-16 11:16:06 +0800 CST
  • 1

我正在尝试使用以下脚本将 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桌子？

postgresql upgrade
Martin Hope
Tombart
Asked: 2019-02-27 01:40:34 +0800 CST
  • 1

我正在尝试将流复制设置到 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 backup
Martin Hope
Tombart
Asked: 2018-01-10 05:35:34 +0800 CST
  • 5

我有一个流量很大的 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 postgresql-9.6
Martin Hope
Tombart
Asked: 2017-01-30 06:22:54 +0800 CST
  • 4

我有一个 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 流式传输？是否可以自动执行此操作？

postgresql replication
Martin Hope
Tombart
Asked: 2016-04-19 06:10:07 +0800 CST
  • 1

我正在尝试使用以下方法设置副本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

知道如何启动副本吗？

postgresql postgresql-9.3