amcheck relata o seguinte erro:
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
Como posso corrigir esse problema?
A documentação diz que isso REINDEXpode não ajudar.
Um REINDEX pode não ser eficaz na reparação da corrupção.
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?
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 postgres@pg.example.com
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
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.
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á pausadashutdownirá parar o servidor depois de atingir o alvo de recuperaçãopromotesignifica que o processo de recuperação terminará e o servidor começará a aceitar conexõespromoteparece 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?
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?