Início / user-27707

Tombart's questions

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

Estou tentando atualizar o cluster do PostgreSQL 12 para a versão 13, com o seguinte script:

/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

A verificação retorna:

*Clusters are compatible*

No entanto, durante a atualização real, o processo falha miseravelmente devido à pg_catalog.pg_pltemplatetabela:

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

Parece ser um problema antigo , mas o script de atualização não verifica esses modelos.

Até agora, parece que essa consulta deve retornar um resultado vazio, caso contrário, você terá problemas:

$ 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

REVOGANDO estes privilégios:

REVOKE SELECT ON "pg_catalog"."pg_pltemplate" FROM PUBLIC;
REVOKE ALL ON "pg_catalog"."pg_pltemplate" FROM postgres;

realmente não ajuda, pois a REVOKEinstrução é salva no esquema:

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;

isso pode ser verificado (também o resultado deve estar vazio) usando:

pg_dump --port 5432 --schema-only --quote-all-identifiers | grep pg_pltemplate

antes de realizar a atualização.

Alguma idéia de como se livrar da pg_catalog.pg_pltemplatemesa completamente?

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

Estou tentando configurar a replicação de streaming para um servidor Barman.

A configuração do Barman é bastante básica, estou usando o 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

Vou chamar o servidor DBpg

[pg]
description = "pg"
ssh_command = ssh [email protected]
conninfo = user=barman dbname=postgres host=pg.example.com port=5432

Configurei as regras do hba e configurei um .pgpassarquivo na barmanhome /var/lib/barman/.pgpass.

Uma conexão de teste com o pgservidor funciona bem:

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)

No entanto barman check pg, falha miseravelmente e não tenho ideia do porquê:

$ 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

Existe uma maneira melhor de depurar problemas de autenticação? O .pgpassmesmo está carregado sob barman check?

pg.example.com:5432:postgres:barman:s3cr3tP2ssw0rd
postgresql backup
Martin Hope
Tombart
Asked: 2018-01-10 05:35:34 +0800 CST
  • 5

Eu tenho um banco de dados PostgreSQL 9.6 com tráfego de alto volume. Eu corro periodicamente para pg_repackrecuperar espaço não utilizado em tabelas/índices. Em tabelas maiores, o repack às vezes falha ao completar o processo, o que resulta no uso de mais espaço em disco que o PostgreSQL relata que o banco de dados está usando.

Eu uso a seguinte consulta para relatar o tamanho de cada banco de dados:

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

isso dá uma ideia de que o espaço ocupado deve estar ao redor 750GB. No entanto, na realidade, o PostgreSQL está usando quase o dobro:

$ du -hs /var/lib/postgresql/9.6/main/base/
1.3T    /var/lib/postgresql/9.6/main/base/

Parte do problema é pgsql_tmpque isso é ocupar 349GB. Existe uma maneira segura de remover arquivos não utilizados pgsql_tmp?

349G    /var/lib/postgresql/9.6/main/base/pgsql_tmp/

Já tentei VACUUM FULLe pg_repacknas maiores mesas sem sucesso. A única maneira de se livrar do espaço em disco desperdiçado parece ser despejar tabelas no SQL e reimportar para um servidor limpo.

postgresql postgresql-9.6
Martin Hope
Tombart
Asked: 2017-01-30 06:22:54 +0800 CST
  • 4

Eu tenho um cluster PostgreSQL 9.5 com um masterservidor standbyque é replicado usando o repmgr. O mestre está configurado para manter os 4000arquivos WAL:

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

Em algum momento, o standbyservidor fica atrás do mestre (não tenho ideia de por que isso está acontecendo):

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

Obviamente, o mestre removeu o WAL necessário 0000000200001E64000000B7, mas o arquivo ainda está presente no arquivo. De qualquer forma, após standbya reinicialização, o banco de dados parece atingir um estado consistente:

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

O PostgreSQL sugere executar pg_xlog_replay_resume(), mas isso levaria à promoção de standby para master, portanto, uma situação de split-brain.

$ 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

O PostgreSQL 9.5 vem com um novo parâmetro recovery_target_actionque pode ter 3 valores em recovery.conf:

  • pause(padrão) a recuperação será pausada
  • shutdownirá parar o servidor depois de atingir o alvo de recuperação
  • promotesignifica que o processo de recuperação terminará e o servidor começará a aceitar conexões

promoteparece ser uma escolha lógica de acordo com a documentação, no entanto, leva novamente à situação de cérebro dividido:

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

A questão é como retomar o streaming WAL sem promover o modo de espera para mestre? É possível fazer isso de forma automática?

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

Estou tentando configurar uma réplica usando 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

Consegui sincronizar os bancos de dados, mas a réplica em espera não inicia:

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

Alguma ideia de como iniciar a réplica?

postgresql postgresql-9.3