Saurabh Nanda's questions

我最近在从 PG 9.4 => 11 迁移时遇到了一个奇怪的问题

我使用将生产服务器从 PG 9.4 复制到 PG 11 pglogical。一切都运行良好，除了我们发现一些复杂的查询在 PG 11 服务器上花费了数十秒，而在 9.4 服务器上花费了不到一秒。

事实证明，ANALYZE在新服务器上运行可以解决缓慢的查询问题。因此，我检查$PGDATA/pg_stat并发现它们在两台服务器上都是$PGDATA/pg_stat_tmp空的！

PG在哪里存储统计信息？（跟进问题——pglogical复制时不收集统计数据吗？）

编辑/跟进：

pg_stat_user_tables新 PG 11 服务器上的查询输出-

select analyze_count,autoanalyze_count, count(*) from pg_stat_user_tables group by 1,2;
 analyze_count | autoanalyze_count | count 
---------------+-------------------+-------
             1 |                11 |     1
             4 |                 7 |     1
             1 |                 0 |    14
             4 |                 0 |   168
(4 rows)

旧 PG 9.4 服务器（当前生产服务器）上的相同查询：

 analyze_count | autoanalyze_count | count 
---------------+-------------------+-------
             0 |                13 |     3
             0 |               226 |     1
             0 |                 1 |    31
             0 |                 5 |     6
             0 |                21 |     2
             0 |                16 |     2
             0 |               601 |     1
             0 |                 8 |     1
             0 |                20 |     1
             0 |                 4 |     2
             0 |                 9 |     2
             0 |                10 |     2
             0 |                12 |     1
             0 |                 7 |     2
             0 |                41 |     1
             0 |                 2 |    11
             0 |                 6 |     1
             0 |                 3 |     8
             0 |               731 |     1
             0 |                39 |     1
             0 |                11 |     1
             0 |                 0 |    99
             0 |                50 |     1
             0 |                66 |     1
             0 |               241 |     1
             0 |                14 |     2

以下查询会返回不同的结果有什么原因吗？

select count(id) 
from mytable 
where 
  status='active' 
  and ((textfield1 ilike '%needle%') or (textfield2 ilike '%needle%'));

对比

select count(id) 
from mytable 
where 
  status='active' 
  and ((textfield1 || textfield2) ilike '%needle%');

当我运行这些查询时，前者正在返回26而后者19。

我犯了什么愚蠢的错误？

我有以下配置片段/etc/barman.conf：

[barman]
; System user
barman_user = ubuntu

; Log location
log_file = /var/log/barman/barman.log

但是，每当轮换日志文件时，都会发生以下情况：

$ ls -l /var/log/barman/
total 860
-rw-r----- 1 barman adm         0 Dec  3 06:37 barman.log
-rw-rw-r-- 1 ubuntu ubuntu 875647 Dec  2 06:12 barman.log.1

并barman不断抛出关于此的警告/错误：

$ barman cron
2017-12-13 11:11:08,358 [11498] barman.utils WARNING: Failed opening the requested log file. Using standard error instead.

我的酒保配置是否不正确，这是酒保中的错误，还是某些操作系统/内核级安全设置导致了此行为？

编辑：拥有者/var/log/barman

$ ls -ld /var/log/barman
drwxr-x--- 2 ubuntu ubuntu 4096 Dec  3 06:37 /var/log/barman

编辑：输出barman diagnose

$ barman diagnose
2017-12-15 11:27:05,588 [3812] barman.utils WARNING: Failed opening the requested log file. Using standard error instead.
{
    "global": {
        "config": {
            "barman_home": "/home/ubuntu/barman", 
            "barman_user": "ubuntu", 
            "compression": "bzip2", 
            "configuration_files_directory": "/home/ubuntu/barman.conf.d", 
            "errors_list": [], 
            "last_backup_maximum_age": "2 WEEKS", 
            "log_file": "/var/log/barman/barman.log", 
            "log_level": "INFO", 
            "minimum_redundancy": "3", 
            "path_prefix": "/home/ubuntu/pg/bin", 
            "retention_policy": "RECOVERY WINDOW OF 4 WEEKS"
        }, 
        "system_info": {
            "barman_ver": "2.3", 
            "kernel_ver": "REDACTED", 
            "python_ver": "", 
            "release": "Distributor ID:\tUbuntu\nDescription:\tUbuntu 14.04.5 LTS\nRelease:\t14.04\nCodename:\ttrusty", 
            "rsync_ver": "rsync  version 3.1.0  protocol version 31", 
            "ssh_ver": ""
        }
    }, 
    "servers": {
        "pg": {
            "backups": {}, 
            "config": {
                "active": true, 
                "archiver": false, 
                "archiver_batch_size": 0, 
                "backup_directory": "/home/ubuntu/barman/pg", 
                "backup_method": "postgres", 
                "backup_options": "concurrent_backup", 
                "bandwidth_limit": null, 
                "barman_home": "/home/ubuntu/barman", 
                "barman_lock_directory": "/home/ubuntu/barman", 
                "basebackup_retry_sleep": 30, 
                "basebackup_retry_times": 0, 
                "basebackups_directory": "/home/ubuntu/barman/pg/base", 
                "check_timeout": 30, 
                "compression": "bzip2", 
                "conninfo": "host=REDACTED user=REDACTED dbname=REDACTED", 
                "custom_compression_filter": null, 
                "custom_decompression_filter": null, 
                "description": "pg DB", 
                "disabled": false, 
                "errors_directory": "/home/ubuntu/barman/pg/errors", 
                "immediate_checkpoint": false, 
                "incoming_wals_directory": "/home/ubuntu/barman/pg/incoming", 
                "last_backup_maximum_age": "14 days", 
                "max_incoming_wals_queue": null, 
                "minimum_redundancy": 3, 
                "msg_list": [], 
                "name": "pg", 
                "network_compression": false, 
                "parallel_jobs": 1, 
                "path_prefix": "/home/ubuntu/pg/bin", 
                "post_archive_retry_script": null, 
                "post_archive_script": null, 
                "post_backup_retry_script": null, 
                "post_backup_script": null, 
                "pre_archive_retry_script": null, 
                "pre_archive_script": null, 
                "pre_backup_retry_script": null, 
                "pre_backup_script": null, 
                "recovery_options": "", 
                "retention_policy": "window 4 w", 
                "retention_policy_mode": "auto", 
                "reuse_backup": null, 
                "slot_name": "pgbackup", 
                "ssh_command": null, 
                "streaming_archiver": true, 
                "streaming_archiver_batch_size": 0, 
                "streaming_archiver_name": "barman_receive_wal", 
                "streaming_backup_name": "barman_streaming_backup", 
                "streaming_conninfo": "host=REDACTED user=REDACTED dbname=REDACTED", 
                "streaming_wals_directory": "/home/ubuntu/barman/pg/streaming", 
                "tablespace_bandwidth_limit": null, 
                "wal_retention_policy": "simple-wal 4 w", 
                "wals_directory": "/home/ubuntu/barman/pg/wals"
            }, 
            "status": {
                "config_file": "/etc/postgresql/9.4/main/postgresql.conf", 
                "connection_error": null, 
                "current_size": 6165378560.0, 
                "current_xlog": "000000010000005A0000000D", 
                "data_directory": "/var/lib/postgresql/9.4/main", 
                "hba_file": "/etc/postgresql/9.4/main/pg_hba.conf", 
                "ident_file": "/etc/postgresql/9.4/main/pg_ident.conf", 
                "is_in_recovery": false, 
                "is_superuser": true, 
                "pg_basebackup_bwlimit": true, 
                "pg_basebackup_compatible": true, 
                "pg_basebackup_installed": true, 
                "pg_basebackup_path": "/home/ubuntu/pg/bin/pg_basebackup", 
                "pg_basebackup_tbls_mapping": true, 
                "pg_basebackup_version": "9.4.0", 
                "pg_receivexlog_compatible": true, 
                "pg_receivexlog_installed": true, 
                "pg_receivexlog_path": "/home/ubuntu/pg/bin/pg_receivexlog", 
                "pg_receivexlog_supports_slots": true, 
                "pg_receivexlog_synchronous": false, 
                "pg_receivexlog_version": "9.4.0", 
                "pgespresso_installed": false, 
                "replication_slot": [
                    "pgbackup", 
                    true, 
                    "5A/D000000"
                ], 
                "replication_slot_support": true, 
                "server_txt_version": "9.4.4", 
                "streaming": true, 
                "streaming_supported": true, 
                "synchronous_standby_names": [
                    ""
                ], 
                "systemid": "6202911203760332717", 
                "timeline": 1, 
                "wal_level": "hot_standby", 
                "xlogpos": "5A/D4E6CA8"
            }, 
            "wals": {
                "last_archived_wal_per_timeline": {
                    "00000001": {
                        "compression": "bzip2", 
                        "name": "000000010000005A0000000C", 
                        "size": 1679, 
                        "time": 1513163133.925488
                    }
                }
            }
        }
    }
}
2017-12-15 11:27:07,611 [3812] barman.diagnose INFO: {
    "global": {
        "config": {
            "barman_home": "/home/ubuntu/barman", 
            "barman_user": "ubuntu", 
            "compression": "bzip2", 
            "configuration_files_directory": "/home/ubuntu/barman.conf.d", 
            "errors_list": [], 
            "last_backup_maximum_age": "2 WEEKS", 
            "log_file": "/var/log/barman/barman.log", 
            "log_level": "INFO", 
            "minimum_redundancy": "3", 
            "path_prefix": "/home/ubuntu/pg/bin", 
            "retention_policy": "RECOVERY WINDOW OF 4 WEEKS"
        }, 
        "system_info": {
            "barman_ver": "2.3", 
            "kernel_ver": "REDACTED", 
            "python_ver": "", 
            "release": "Distributor ID:\tUbuntu\nDescription:\tUbuntu 14.04.5 LTS\nRelease:\t14.04\nCodename:\ttrusty", 
            "rsync_ver": "rsync  version 3.1.0  protocol version 31", 
            "ssh_ver": ""
        }
    }, 
    "servers": {
        "pg": {
            "backups": {}, 
            "config": {
                "active": true, 
                "archiver": false, 
                "archiver_batch_size": 0, 
                "backup_directory": "/home/ubuntu/barman/pg", 
                "backup_method": "postgres", 
                "backup_options": "concurrent_backup", 
                "bandwidth_limit": null, 
                "barman_home": "/home/ubuntu/barman", 
                "barman_lock_directory": "/home/ubuntu/barman", 
                "basebackup_retry_sleep": 30, 
                "basebackup_retry_times": 0, 
                "basebackups_directory": "/home/ubuntu/barman/pg/base", 
                "check_timeout": 30, 
                "compression": "bzip2", 
                "conninfo": "host=REDACTED user=REDACTED dbname=REDACTED", 
                "custom_compression_filter": null, 
                "custom_decompression_filter": null, 
                "description": "pg DB", 
                "disabled": false, 
                "errors_directory": "/home/ubuntu/barman/pg/errors", 
                "immediate_checkpoint": false, 
                "incoming_wals_directory": "/home/ubuntu/barman/pg/incoming", 
                "last_backup_maximum_age": "14 days", 
                "max_incoming_wals_queue": null, 
                "minimum_redundancy": 3, 
                "msg_list": [], 
                "name": "pg", 
                "network_compression": false, 
                "parallel_jobs": 1, 
                "path_prefix": "/home/ubuntu/pg/bin", 
                "post_archive_retry_script": null, 
                "post_archive_script": null, 
                "post_backup_retry_script": null, 
                "post_backup_script": null, 
                "pre_archive_retry_script": null, 
                "pre_archive_script": null, 
                "pre_backup_retry_script": null, 
                "pre_backup_script": null, 
                "recovery_options": "", 
                "retention_policy": "window 4 w", 
                "retention_policy_mode": "auto", 
                "reuse_backup": null, 
                "slot_name": "pgbackup", 
                "ssh_command": null, 
                "streaming_archiver": true, 
                "streaming_archiver_batch_size": 0, 
                "streaming_archiver_name": "barman_receive_wal", 
                "streaming_backup_name": "barman_streaming_backup", 
                "streaming_conninfo": "host=REDACTED user=REDACTED dbname=REDACTED", 
                "streaming_wals_directory": "/home/ubuntu/barman/pg/streaming", 
                "tablespace_bandwidth_limit": null, 
                "wal_retention_policy": "simple-wal 4 w", 
                "wals_directory": "/home/ubuntu/barman/pg/wals"
            }, 
            "status": {
                "config_file": "/etc/postgresql/9.4/main/postgresql.conf", 
                "connection_error": null, 
                "current_size": 6165378560.0, 
                "current_xlog": "000000010000005A0000000D", 
                "data_directory": "/var/lib/postgresql/9.4/main", 
                "hba_file": "/etc/postgresql/9.4/main/pg_hba.conf", 
                "ident_file": "/etc/postgresql/9.4/main/pg_ident.conf", 
                "is_in_recovery": false, 
                "is_superuser": true, 
                "pg_basebackup_bwlimit": true, 
                "pg_basebackup_compatible": true, 
                "pg_basebackup_installed": true, 
                "pg_basebackup_path": "/home/ubuntu/pg/bin/pg_basebackup", 
                "pg_basebackup_tbls_mapping": true, 
                "pg_basebackup_version": "9.4.0", 
                "pg_receivexlog_compatible": true, 
                "pg_receivexlog_installed": true, 
                "pg_receivexlog_path": "/home/ubuntu/pg/bin/pg_receivexlog", 
                "pg_receivexlog_supports_slots": true, 
                "pg_receivexlog_synchronous": false, 
                "pg_receivexlog_version": "9.4.0", 
                "pgespresso_installed": false, 
                "replication_slot": [
                    "pgbackup", 
                    true, 
                    "5A/D000000"
                ], 
                "replication_slot_support": true, 
                "server_txt_version": "9.4.4", 
                "streaming": true, 
                "streaming_supported": true, 
                "synchronous_standby_names": [
                    ""
                ], 
                "systemid": "6202911203760332717", 
                "timeline": 1, 
                "wal_level": "hot_standby", 
                "xlogpos": "5A/D4E6CA8"
            }, 
            "wals": {
                "last_archived_wal_per_timeline": {
                    "00000001": {
                        "compression": "bzip2", 
                        "name": "000000010000005A0000000C", 
                        "size": 1679, 
                        "time": 1513163133.925488
                    }
                }
            }
        }
    }
}

我一直在摸索解决以下错误，但没有取得太大进展：

$ barman switch-wal --force --archive --archive-timeout 180 pg

The WAL file 0000000100000059000000FF has been closed on server 'pg'
Waiting for the WAL file 0000000100000059000000FF from server 'pg' (max: 180 seconds)
ERROR: The WAL file 0000000100000059000000FF has not been received in 180 seconds

我之前在一次性 VPS 上配置了酒保，一切正常。但是当我尝试在我们的最终 EC2 实例上复制它时，我遇到了上面提到的问题。我已经确保了以下内容（即使我不记得在 VPS 上这样做了）：

• 来自酒吧的 SSH => pg 正在工作
• 来自 pg => barman 的 SSH 正在工作
• 'pg' 上的所有防火墙规则已被暂时禁用

这里可能是什么问题？

编辑：添加输出barman check pg

Server pg:
2017-12-08 05:56:31,841 [14559] barman.server ERROR: Check 'WAL archive' failed for server 'pg'
    WAL archive: FAILED (please make sure WAL shipping is setup)
    PostgreSQL: OK
    is_superuser: OK
    PostgreSQL streaming: OK
    wal_level: OK
2017-12-08 05:56:34,174 [14559] barman.server ERROR: Check 'replication slot' failed for server 'pg'
    replication slot: FAILED (slot 'pgbackup' not active: is 'receive-wal' running?)
    directories: OK
    retention policy settings: OK
2017-12-08 05:56:34,175 [14559] barman.server ERROR: Check 'backup maximum age' failed for server 'pg'
    backup maximum age: FAILED (interval provided: 14 days, latest backup age: No available backups)
    compression settings: OK
    failed backups: OK (there are 0 failed backups)
2017-12-08 05:56:34,175 [14559] barman.server ERROR: Check 'minimum redundancy requirements' failed for server 'pg'
    minimum redundancy requirements: FAILED (have 0 backups, expected at least 3)
    pg_basebackup: OK
    pg_basebackup compatible: OK
    pg_basebackup supports tablespaces mapping: OK
    pg_receivexlog: OK
    pg_receivexlog compatible: OK
2017-12-08 05:56:34,367 [14559] barman.server ERROR: Check 'receive-wal running' failed for server 'pg'
    receive-wal running: FAILED (See the Barman log file for more details)
    archiver errors: OK

编辑：添加的输出barman receive-wal pg

$ sudo barman receive-wal pg
2017-12-10 01:48:01,129 [25410] barman.utils WARNING: Failed opening the requested log file. Using standard error instead.
Starting receive-wal for server pg
2017-12-10 01:48:01,138 [25410] barman.server INFO: Starting receive-wal for server pg
2017-12-10 01:48:03,087 [25410] barman.wal_archiver INFO: Activating WAL archiving through streaming protocol
pg: pg_receivexlog: starting log streaming at 5A/A000000 (timeline 1)
2017-12-10 01:48:03,391 [25410] barman.command_wrappers INFO: pg: pg_receivexlog: starting log streaming at 5A/A000000 (timeline 1)

^CSIGINT received. Terminate gracefully.
2017-12-10 01:50:42,512 [25410] barman.wal_archiver INFO: SIGINT received. Terminate gracefully.

编辑：配置文件——

; --------------------
; Contents of /etc/barman.conf after removing all commented lines.
; --------------------

[barman]
barman_user = ubuntu
path_prefix = /home/ubuntu/pg/bin
configuration_files_directory = /home/ubuntu/barman.conf.d
barman_home = /home/ubuntu/barman
log_file = /var/log/barman/barman.log
log_level = INFO
compression = bzip2
retention_policy = RECOVERY WINDOW OF 1 MONTH
last_backup_maximum_age = 2 WEEKS
minimum_redundancy = 3
retention_policy = RECOVERY WINDOW OF 4 WEEKS

/home/ubuntu/barman.conf.d/pg.conf：

; --------------------
; Contents of /home/ubuntu/barman.conf.d/pg.conf after removing all commented lines.
; --------------------

[pg]
description =  "Postgres DB"
conninfo = host=REDACTED user=REDACTED dbname=REDACTED
backup_method = postgres
archiver = off
streaming_conninfo =  host=REDACTED user=REDACTED dbname=REDACTED
streaming_archiver = on
slot_name = pgbackup

最近我们注意到我们的一些 Rails 迁移最终导致生产中的应用程序+数据库死锁/冻结。初步调查，这可能是由于应用程序的并发访问以及在读取非常高的表上的迁移。

探索复制的 PG 设置（也许是主从）是否有意义，其中所有写入和迁移都针对主服务器执行，而所有大容量读取都针对从服务器执行？

当一个 ALTER TABLE 语句被复制到一个 slave 时，PG 是如何表现的？从站是否也获取相同的表锁？复制能解决我们目前面临的问题吗？

我正在尝试以下列形式获取外键约束列表：

pk_table | pk_col | fk_table | fk_col

不使用pg_get_constraintdef（这将迫使我诉诸字符串操作）。

以下查询不起作用有什么原因吗？

SELECT
  x.conname,
  x.contype,
  x.conrelid,
  x.confrelid,
  pk.tablename,
  fk.tablename
FROM pg_catalog.pg_constraint x 
INNER JOIN pg_catalog.pg_tables pk ON x.conrelid!=0 AND x.conrelid=(pk.tablename :: regclass :: oid)
INNER JOIN pg_catalog.pg_tables fk ON x.confrelid!=0 AND x.confrelid=(fk.tablename :: regclass :: oid)
WHERE
  x.connamespace in (SELECT oid FROM pg_catalog.pg_namespace n WHERE n.nspname in ('b2b', 'public'))
  AND x.contype='f'

错误：

ERROR:  relation "sql_implementation_info" does not exist

我的假设是 PG 由于存在多个命名空间而变得混乱，所以我尝试将结果集限制为我已知的模式，即b2b, public，但这仍然不起作用。

我有一个使用列存储预订数据的表starts_at&ends_at每当我查询表以查找重叠预订时，我都可以选择使用以下查询之一：

SELECT * FROM reservations
WHERE starts_at < '2014-01-03 00:00:00'
AND   ends_at   >='2014-01-01 00:00:00';

或者

SELECT * FROM reservations
WHERE tsrange(starts_at, ends_at) && ('2014-01-01 00:00:00', '2014-01-03 00:00:00')

我在starts_at和ends_at列上有常规的 B-Tree 索引，因此第一个查询总是使用它们。但是，除非我在 tsrange 上定义功能性 GiST 索引，否则第二个查询会进行完整扫描。

create index tsrange_idx on reservations using gist(tsrange(starts_at, ends_at)); 

我的问题是，随着表的增长，哪个索引会更快？从查询执行计划来看，答案可能很明显，但我并不精通阅读EXPLAIN ANALYZE输出。