Perguntas[postgresql](dba)

Tenho o seguinte SQL que gera uma lista bem formatada dos tamanhos de todas as tabelas em um banco de dados:

SELECT
  schema_name,
  relname,
  pg_size_pretty(table_size) AS size
FROM (
       SELECT
         pg_catalog.pg_namespace.nspname           AS schema_name,
         relname,
         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
WHERE schema_name NOT LIKE 'pg_%'
ORDER BY table_size DESC;

Isso gera dados como os seguintes (alguns nomes e tamanhos foram alterados para ocultar dados, mas você entendeu a ideia):

     schema_name     |                 relname                 |    size
---------------------+-----------------------------------------+------------
 information_schema  | log                                     | 1024 kB
 information_schema  | collations                              | 1024 kB
 information_schema  | routines                                | 1024 kB
(30 rows)

Gostaria de ter os resultados de count() em cada tabela adicionados como uma quarta coluna aqui, mas não consigo descobrir como fazer isso. Estou usando o PostgreSQL 14, se isso especificamente fizer alguma diferença.

Temos um cluster de replicação do PostgreSQL 13 onde um dos standbys está configurado com recovery_min_apply_delay="x hr", o que pode ser útil em caso de corrupção de dados.

Para um cenário em que uma exclusão acidental aconteceu no servidor mestre , mas ainda não foi aplicada ao servidor em espera . Neste caso, estou tentando extrair os dados do servidor em espera removendo o atraso de repetição do WAL e adicionando recovery_target_timeum ponto antes da exclusão acontecer.

Até este ponto, tive sucesso, mas depois que terminei de puxar os dados do servidor standby para o master (estou pensando em postgres_fdw), preciso retomar a replicação. Mas quando executo pg_wal_replay_resume(), ele está criando uma nova linha do tempo e não está mais no cluster de replicação.

Existe alguma maneira de reproduzir o WAL além de usar recovery_target_time?

Dando um exemplo do que estou tentando alcançar,

Digamos que recovery_min_apply_delay=2hr , uma exclusão acidental aconteceu às 03:50 AM, neste momento transações confirmadas em standby são transações que aconteceram em ou antes de 01:50 AM. Se eu esperar mais 2hr e às 05:49 AM se eu conseguir pausar a repetição do WAL em standby, poderei ver um instantâneo dos dados que estão "logo antes" de DELETE. Então usarei postgres_fdw para extrair dados do standby.

Como eu consigo "logo antes" de uma forma precisa e sistemática? Também seria capaz de retomar o replay do WAL.

Estou praticando Point in Time Recovery (PITR) no PostgreSQL 13 contra exclusão acidental de dados. Como o PostgreSQL oferece vários parâmetros para executar PITR, estou um pouco confuso escolhendo entre Transaction XID e LSN.

Se uma DELETEdeclaração acidental removesse um grande número de linhas e eu obtivesse o correspondente XIDa partir LSNdo dump do WAL, executar o PITR produziria o mesmo resultado, independentemente de eu usar o XID ou o LSN?

Existe o risco de perder outras transações confirmadas se eu restaurar usando o XID?

Notei que para uma tabela (ou visualização ou visualização materializada) no PostgreSQL, há uma linha em pg_depend vinculando a linha pg_class da tabela com a linha pg_namespace do seu esquema.

No entanto, estou pensando se há algum caso em que uma linha em pg_depend pode vincular a linha pg_class a uma linha pg_namespace de um esquema no qual ela não está.

Isso está no contexto de Como posso listar as tabelas em um esquema sem uma varredura sequencial de pg_class?, onde estou tentando encontrar uma consulta eficiente para encontrar as tabelas em um esquema e me pergunto se tenho que lidar com esse caso.

O PostgreSQL tem uma OR REPLACEvariante " " (como em " CREATE OR REPLACE ____") para tabelas, funções, visualizações, regras, etc. No entanto, ele não tem uma para tipos.

Eu poderia ir registrar um bug, mas imagino que essa foi uma decisão deliberada dos mantenedores. Minha pergunta é: por quê?

Em outras palavras, o que há de diferente nos tipos que os faz não precisar de um OR REPLACE(mesmo que tabelas, funções, etc. precisem)?

PS Acabei de descobrir que ele tem um DROP TYPE IF EXISTS... o que só me confunde mais. Realmente parece que CREATE TYPEfoi escolhido para não ter um IF EXISTS, e eu simplesmente não vejo o porquê.

No PostgreSQL, eu gostaria de listar as tabelas em um esquema. Eu tenho essa consulta que faz isso para o esquema público, por exemplo:

SELECT
  relname
FROM
  pg_class
WHERE
  relnamespace = 'public'::regnamespace
  AND relkind = 'r';

No entanto, no meu sistema isso leva ~400-500ms (e às vezes pode levar muito mais tempo). Suspeito que isso seja porque pg_class tem mais de 5 milhões de linhas e tem quase 5gb de tamanho.

Olhando para a explicação e análise da consulta, vejo isto:

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..356932.33 rows=14 width=64) (actual time=2.190..418.126 rows=120 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on pg_class  (cost=0.00..355930.92 rows=6 width=64) (actual time=5.318..375.222 rows=40 loops=3)
         Filter: ((relnamespace = '2200'::oid) AND (relkind = 'r'::"char"))
         Rows Removed by Filter: 1792453
 Planning Time: 0.070 ms
 Execution Time: 418.162 ms

e vejo que tem uma varredura sequencial em pg_class. Olhando para os índices em pg_class, isso não é surpreendente - não há índice na coluna relnamespace (pelo menos, nenhum índice que tenha relnamespace como sua primeira coluna).

Então, como posso evitar a varredura sequencial em pg_class ao tentar encontrar as tabelas em um esquema? Isso é possível?

O Postgresql 17 adicionou um novo recurso para fazer backups incrementais. Quero usar isso para fazer backups incrementais diários, no entanto, estou lutando para encontrar um bom método para automatização, pois o build in pg_basebackup --incrementalsempre precisa de um caminho para o último backup incremental. Fornecer o caminho é fácil para a operação normal, no entanto, não quero implementar o tratamento de erros para todos os casos em que algo pode dar errado.

Também dei uma olhada no barman, que tem suporte para backups incrementais em nível de bloco, porém não preciso emparelhá-lo com o arquivamento WAL (que não consegui desativar) e parece que ainda preciso acionar manualmente o backup de qualquer maneira. O pgBackRest parece não ter suporte algum para esses backups.

Talvez haja uma alternativa melhor do que usar o backup incremental integrado, mas ela parece atender quase perfeitamente às minhas necessidades:

• Grande banco de dados (>1 TB) e espaço de backup limitado
• Não é possível desligar o banco de dados
• Não há grande problema se um dia de dados for perdido

Existe alguma ferramenta que esteja faltando e que possa resolver meu problema?

Talvez essa seja uma pergunta muito básica, mas quero ter certeza. Estou atualizando um cluster RDS com uma implantação azul-verde. Eu estava verificando o cluster, seguindo as diretrizes da AWS, e vi um monte de conexões ativas.

psql -d postgres -c “SELECT * FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval ‘2 minutes’”mostra cerca de 60 consultas ativas.

Em uma das nossas primeiras etapas de atualização, temos que reiniciar a instância para uma alteração de grupo de parâmetros. Gostaria de saber se isso eliminará todas essas conexões ativas. A maioria delas é idle, e quase todas são SELECTinstruções, não DDL ou INSERT/UPDATEconsultas. Estou preocupado que, se as conexões não forem eliminadas, isso afetará o processo de replicação de alguma forma.

usar pg_upgrade quando você instalou a versão 'antiga' e a versão 'nova' em um sistema é bem direto. Eu tentei encontrar uma maneira de usar pg_upgrade com contêineres docker. Isso é um pouco mais complicado porque você precisa do 'antigo datadir', do 'antigo bindir' e do 'novo datadir' e do 'novo bindir' da versão 'antiga' e da 'nova' do postgres que você quer atualizar e atualizar para. Como os 'diretórios antigos' não estão presentes no contêiner docker da versão 'nova', você tem que montá-los no contêiner 'novo'.

Mas como o pg_upgrade parece esperar não apenas o bindir e o datadir 'antigos', mas também as bibliotecas 'antigas' (das quais o postgres depende) da versão mais antiga, você também precisa montá-las no contêiner da nova versão.

Então acabei executando um 'old-version-container', copiando o bindir, datadir e lib dir para o host docker local e montando-os no 'new-version-container'

ao vir do postgres-12, isso significa copiar o conteúdo de /usr/lib/ para o host docker local e remontá-lo para o 'new-version-container'

então montei o 'old-libdir' em /12-bindir/ e fiz

ldd /12-bindir/postgres para descobrir de quais libs o postgres depende. Depois de copiar o 'old-libs' para /usr/lib no 'new-version-container' todas as dependências puderam ser encontradas e eu consegui usar o pg_upgrade e realmente atualizar um banco de dados de (ou seja, postgres-12-alpine para postgres:15.10-bookworm)

então... funcionou... Então... a verdadeira questão é: porque funciona... devo fazer assim ou estou esquecendo de algo e fazendo algo estúpido ou bobo? Fico feliz com cada dica e opinião

Tenho um banco de dados PostgreSQL 16 configurado com a seguinte configuração:

-- DB Version: 16
-- OS Type: linux
-- DB Type: oltp
-- Total Memory (RAM): 64 GB
-- CPUs num: 32
-- Connections num: 200
-- Data Storage: ssd

show effective_cache_size; --48 GB
show effective_io_concurrency; --200
show huge_pages; --try
show maintenance_work_mem; --4GB
show max_connections; --200
show max_parallel_maintenance_workers; --4
show max_parallel_workers; --32
show max_parallel_workers_per_gather; --4
show max_wal_size; --8GB
show max_worker_processes; --32
show min_wal_size; --2GB
show parallel_setup_cost; --0.1
show random_page_cost; --1.1
show shared_buffers; --16GB
show wal_buffers; --16MB
show work_mem; --256MB

Estou executando uma CREATE TABLEconsulta com múltiplas junções. Às vezes, a consulta é executada em paralelo, mas outras vezes não. Quero garantir que a consulta seja executada em paralelo ou, pelo menos, aumentar as chances de ela ser executada em paralelo para aquela sessão de transação específica. Também é bom se os trabalhadores paralelos atribuídos forem menos do que o normal, porque sem trabalhadores paralelos a consulta é muito lenta.

A configuração abaixo funcionou melhor para mim por algum tempo, mas causou um erro semelhante a este problema .

ALTER SYSTEM SET maintenance_work_mem = '8GB';
ALTER SYSTEM SET work_mem = '2GB';
ALTER SYSTEM SET effective_io_concurrency = '400';
ALTER SYSTEM SET max_parallel_workers_per_gather = '16';
ALTER SYSTEM SET wal_buffers = '1GB';

Estou anexando um plano de consulta selecionado para referência: plano de consulta .

Quero maximizar a utilização da CPU (atualmente menos de 10%) e a utilização da RAM (atualmente menos de 30%).

Estou ciente de que force_parallel_modefoi descontinuado. Como posso obter execução paralela consistente para minhas consultas no PostgreSQL?