Saurabh Nanda's questions

Recentemente, encontrei um problema estranho ao migrar do PG 9.4 => 11

Eu repliquei o servidor de produção do PG 9.4 para o PG 11 usando pglogical. Tudo funcionou bem, exceto que descobrimos que algumas consultas complexas demoravam dezenas de segundos no servidor PG 11, enquanto no servidor 9.4 demoravam menos de um segundo.

Acontece que a execução ANALYZEno novo servidor corrigiu as consultas lentas. Então, eu verifiquei $PGDATA/pg_state $PGDATA/pg_stat_tmpdescobri que eles estavam vazios em ambos os servidores!

Onde o PG armazena as estatísticas? (Pergunta de acompanhamento - não pglogicalcoleta estatísticas enquanto está replicando?)

Editar/acompanhar:

Saída da consulta no pg_stat_user_tablesnovo servidor 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)

Mesma consulta no servidor PG 9.4 antigo (servidor de produção atual):

 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

Existe algum motivo para as consultas a seguir retornarem resultados diferentes?

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

vs

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

Quando estou executando essas consultas, a primeira está retornando 26e a segunda 19.

Que erro idiota estou cometendo?

Eu tenho o seguinte trecho de configuração no meu /etc/barman.conf:

[barman]
; System user
barman_user = ubuntu

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

No entanto, sempre que o arquivo de log é girado, isso é o que acontece:

$ 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

E barmancontinua lançando avisos/erros sobre isso:

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

A configuração do meu barman está incorreta, isso é um bug no barman ou alguma configuração de segurança no nível do sistema operacional/kernel está causando esse comportamento?

Editar: Proprietário de/var/log/barman

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

Editar: Saída debarman 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
                    }
                }
            }
        }
    }
}

Estou coçando a cabeça para resolver o seguinte erro, mas não fiz muito progresso:

$ 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

Eu já havia configurado o barman em um VPS descartável e tudo funcionou bem. Mas quando tentei replicar isso em nossa instância final do EC2, encontrei o problema mencionado acima. Assegurei o seguinte (mesmo que não me lembre de fazer isso no VPS):

• SSH do barman => pg está funcionando
• SSH de pg => barman está funcionando
• Todas as regras de firewall em 'pg' foram desabilitadas, temporariamente

Qual poderia ser o problema aqui?

Edit: Adicionando a saída debarman 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

Editar: Adicionado saída debarman 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.

Editar: arquivos de configuração --

; --------------------
; 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

Recentemente notamos que várias de nossas migrações Rails acabam travando/congelando nosso app+DB em produção. A investigação preliminar revela que isso provavelmente se deve ao acesso simultâneo pelo aplicativo e à migração em uma tabela com leituras muito altas.

Faria sentido explorar uma configuração PG replicada (talvez mestre-escravo), onde todas as gravações e migrações são executadas no mestre e todas as leituras de alto volume são executadas no escravo?

Como o PG se comporta quando uma instrução ALTER TABLE é replicada para um escravo? O escravo também adquire os mesmos bloqueios de tabela? A replicação resolverá os problemas que estamos enfrentando atualmente?

Estou tentando obter uma lista de restrições de chave estrangeira no seguinte formulário:

pk_table | pk_col | fk_table | fk_col

sem usar pg_get_constraintdef(o que me forçaria a recorrer à manipulação de strings).

Existe alguma razão pela qual a consulta a seguir não funciona?

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'

Erro:

ERROR:  relation "sql_implementation_info" does not exist

Minha hipótese era que o PG está ficando confuso devido à presença de vários namespaces, então tentei restringir o conjunto de resultados aos meus esquemas conhecidos, ou seja b2b, public, , mas isso ainda não funciona.

Eu tenho uma tabela que está armazenando dados de reserva usando as colunas starts_at& ends_atSempre que estou consultando a tabela para encontrar reservas sobrepostas, tenho a opção de usar uma das seguintes consultas:

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

Ou

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

Eu tenho índices B-Tree regulares nas colunas starts_ate , portanto, a primeira consulta sempre os usa. ends_atNo entanto, a menos que eu defina um índice GiST funcional no tsrange, a segunda consulta faz uma varredura completa.

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

Minha dúvida é, conforme a tabela cresce, qual índice vai ser mais rápido? Provavelmente, a resposta é óbvia olhando para o plano de execução da consulta, mas não sou muito versado na leitura da EXPLAIN ANALYZEsaída.