Perguntas[postgresql-12](dba)

Então eu herdei esse banco de dados. No início, já havia alguns arquivos em pg_wal.

Então, depois de algumas semanas, eu conto SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';, resulta: 8182 arquivos.

Observando o carimbo de data/hora do arquivo no pg_waldiretório, vejo alguns novos arquivos sendo criados, mas o número de arquivos permanece o mesmo. Estou suspeitando que a rotação ocorre.

Então observo pg_stat_archiver:archived_count = **2**; failed_count = 0

Cerca de 3 horas depois, eu conto novamente SELECT COUNT(*) FROM pg_ls_dir('pg_wal') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';. Resultado: 8183 . Obviamente, um novo arquivo é criado.

Então eu faço pg_stat_archiver:archived_count = **3**; failed_count = 0

archived_count last_archived_wal           last_archived_time   failed_count    last_failed_wal  last_failed_time 
3              000000010000009B00000083    22-09-13 13:14:20.   0
stats_reset                          
2022-09-13 08:14:19

Este banco de dados é muito baixo, não ocupado. Às vezes ninguém acessa. Mas há replicação lógica que sincroniza cerca de 300 registros uma vez por dia com outro servidor.

Aqui estão minhas configurações:

max_wal_size : 1024 Mb
min_wal_size : 2048 Mb
wal_keep_segment : 0
wal_rotation : ON
wal_level : logical
wal_compression : ON
archive_on : ON
archive_mode : test ! -f /Archive/%f && cp %p /Archive/%f
archive_timeout : 900

Perguntas :

1. Por que a segunda contagem de pg_walaumento? Estou esperando que diminua porque o número arquivado aumenta.
2. Se wal_keep_segment = 0, por que o número de WALs pg_walnunca é 0 ? Na documentação diz:

Se wal_keep_segments for zero (o padrão), o sistema não mantém nenhum segmento extra para fins de espera,

3. Por que o número mínimo de WALs, durante o período de baixa, não = min_wal_size+ wal_keep_segment?

Por favor me esclareça.

Muito obrigado

Depois de emitir pg_basebackup -D /var/lib/postgresql/basebackup -l "backup main $(hostname) on $(date)" -P, vejo que há 2 arquivos ".backup' no pg_waldiretório. Veja . É assim que o pg_archivediretório se parece . Perguntas:

1. Por que existem 2 arquivos?
2. Quais arquivos WAL posso excluir com segurança, antes do primeiro arquivo ".backup" (tudo em cima de **96.00000028) ou antes do segundo (tudo em cima de **98.00000028) ?
3. Ou eu tenho que excluir arquivos wal (todos os arquivos acima do 00028) em pg_archive?

Obrigado

Temos um banco de dados PostgreSQL que cresceu significativamente em tamanho recentemente, de cerca de 340 GB para 571 GB nos últimos dois meses, e não estamos rastreando nenhuma mudança significativa no comportamento do usuário durante esse período. Nosso principal DBA fez algumas recomendações, sendo sua principal recomendação exportar todo o banco de dados e reimportá-lo, o que de seus testes em um segundo servidor clonado de nosso primário requer cerca de 3 horas de inatividade e obtém o tamanho para apenas 300 GB.

Minhas duas principais áreas de preocupação seriam descobrir de onde vem esse crescimento significativo (usando du -h, posso pelo menos ver que está no diretório /data sem crescimento significativo no tablespace ou pg_wal) e entender como importar e exportar o banco de dados pode nos fornecer quase 300 GB de recuperação de espaço sem realmente perder nenhum dado de produção.

Estou Macusando Monterey. É um Mac M1que teve um pouco de problema usando homebrewpara instalar postgresquando comecei a trabalhar nesta máquina. Mudei para o GUI postgres.appque parecia apenas uma frente para o subjacente postgres, então não achei que muita coisa havia mudado.

De qualquer forma, o banco de dados ( Postgres 12) agora está em execução e estou vendo um problema ao tentar criar um novo banco de dados. Mesmo problema em um cliente de terceiros, bem como psql.

rich=# create database sresearch
rich-# \l
                                   List of databases
     Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
--------------+----------+----------+-------------+-------------+-----------------------
 postgres     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 rich         | rich     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 sresearch    | rich     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
              |          |          |             |             | postgres=CTc/postgres
 weather      | rich     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

rich-# \c sresearch
FATAL:  no such database: sresearch

O banco de dados não pode ser criado por algum motivo, mesmo que esteja informando que o fez. Eu não tenho ideia do porque isso está acontecendo, mas eu quero corrigi-lo.

Estou pensando em fazer um backup e tentar corrigir isso excluindo as duas versões e voltando homebrewpara começar novamente com Postgres 12. Não tenho certeza de como descobrir qual é a diferença entre essas duas versões ligeiramente diferentes, que são essencialmente a mesma coisa.

Como posso consertar isso? Obviamente eu preciso ser capaz de fazer novos bancos de dados.

Atualização: acabei de reinstalar a homebrewversão e tentei importar minha postgres.appexportação alternativa. Ele desligou \connect <dbname>por algum motivo, mas foi fácil contornar manualmente. Esta instalação também me deu o mesmo erro ao tentar criar um novo banco de dados. Portanto, ambas as versões estão mostrando o mesmo, mesmo de uma exportação.

Eu não tenho idéia do que tentar.

rich=# \c sres
FATAL:  no such database: sres             # Indeed there is though
Previous connection kept
rich=#

Atualizado: Mais informações ==>

rich=# \l
                                List of databases
     Name     | Owner | Encoding |   Collate   |    Ctype    | Access privileges
--------------+-------+----------+-------------+-------------+-------------------
 postgres     | rich  | UTF8     | C           | C           |
 rich         | rich  | UTF8     | C           | C           |
 sres         | rich  | UTF8     | C           | C           |
 sresearch    | rich  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
 template1    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
(6 rows)

rich=# \c sres;
FATAL:  no such database: sres
Previous connection kept
rich=# \s sresearch;
Wrote history to file "sresearch".
rich=#

Aqui está o feedback de Sequel:

Atualizada. Ainda mais do mesmo. ==>

rich=# CREATE DATABASE alpha;
CREATE DATABASE
rich=# \c alpha;
FATAL:  no such database: alpha
Previous connection kept
rich=# \l
                                List of databases
     Name     | Owner | Encoding |   Collate   |    Ctype    | Access privileges
--------------+-------+----------+-------------+-------------+-------------------
 alpha        | rich  | UTF8     | C           | C           |
 postgres     | rich  | UTF8     | C           | C           |
 rich         | rich  | UTF8     | C           | C           |
 template0    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
 template1    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
(5 rows)

rich=# DROP DATABASE alpha;
DROP DATABASE
rich=# \l
                                List of databases
     Name     | Owner | Encoding |   Collate   |    Ctype    | Access privileges
--------------+-------+----------+-------------+-------------+-------------------
 postgres     | rich  | UTF8     | C           | C           |
 rich         | rich  | UTF8     | C           | C           |
 template0    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
 template1    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
(4 rows)

rich=# CREATE DATABASE alpha;
CREATE DATABASE
rich=# \l
                                List of databases
     Name     | Owner | Encoding |   Collate   |    Ctype    | Access privileges
--------------+-------+----------+-------------+-------------+-------------------
 alpha        | rich  | UTF8     | C           | C           |
 postgres     | rich  | UTF8     | C           | C           |
 rich         | rich  | UTF8     | C           | C           |
 template0    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
 template1    | rich  | UTF8     | C           | C           | =c/rich          +
              |       |          |             |             | rich=CTc/rich
(5 rows)

rich=# \c alpha;
FATAL:  no such database: alpha
Previous connection kept
rich=#

Lendo a documentação do pg_cron vi que os exemplos só executam um comando ao agendar uma tarefa.

Em uma postagem do StackOverflow , vi que um usuário tentou executar vários VACUUM ao agendar a tarefa, mas ocorreu um erro.

Existe uma maneira de executar VACUUM em várias tabelas em sequência usando pg_cron? Existem cerca de 112 tabelas selecionadas que devem ser aspiradas, de um total de 155, então agendar uma tarefa para cada uma não é muito prático.

Ou por exemplo, excluir registros antigos de uma tabela e imediatamente ao final do processo executar o cron nas tabelas selecionadas?

Um novato aqui e pedindo sua ajuda. Eu fiz as configurações de arquivamento para WAL LEVEL = replica Archiving Mode = ON, mencionei o local para backup: /home/beekay/archive for online backup for archive_command.

não tenho certeza por que estou recebendo esse erro agora (captura de tela no tópico). Por favor ajude!

Atualmente estou trabalhando na implementação de uma segurança de 2 camadas, o que significa que temos filtragem de consulta orm em nosso código de aplicativo, bem como em nosso banco de dados. No banco de dados estou usando o Postgre Row Level Security.

Em um mundo ideal, todas as consultas de filtragem criadas no código do aplicativo são perfeitas e as políticas de segurança em nível de linha nunca removem entradas. No entanto, se acontecer de filtrar um resultado, isso significa que temos um erro em nossa filtragem no código do aplicativo que deve ser corrigido. Eu gostaria de poder registrar isso.

É possível configurar o Postgresql para que eu possa ser notificado/registrar essas ocorrências?

Tenho uma aplicação que depende totalmente dos OIDs do PostgreSQL para seu funcionamento. Ele está atualmente no PostgreSQL 9.6.1, mas estou procurando atualizar para o 13. Existe um mecanismo recomendado/padrão (usando gatilhos?) para recriar a funcionalidade OID em versões mais recentes?

Em segundo plano, o motivo pelo qual o aplicativo usa OIDs é porque o aplicativo não sabe nada sobre o esquema de banco de dados e deve ser usado em qualquer esquema de banco de dados aleatório, usando engenharia reversa para navegar. Por esse motivo, uma sugestão do tipo "apenas adicione uma chave primária" não funcionará para mim. Atualmente, o aplicativo está sendo executado em 6 bancos de dados separados, cada um com seu próprio esquema.

Plano de fundo adicional

Praticamente todos os aplicativos entendem seu esquema de banco de dados, por exemplo, um aplicativo de folha de pagamento entende a tabela de funcionários, que talvez tenha uma chave primária de emp_id.

Meu aplicativo é diferente.

Meu aplicativo gera automaticamente um aplicativo CRUD para qualquer banco de dados PostgreSQL aleatório para o qual ele seja apontado. Como tal, não faz suposições sobre chaves primárias, nomes de tabelas, nomes de colunas, etc. A única suposição que faz é que o banco de dados para o qual foi apontado possui um OID exclusivo para cada linha em cada tabela. Como as coisas estão atualmente, eu uso este aplicativo em cinco bancos de dados separados e distintos, e tenho feito isso desde 2007.

Então eu entendo que, na ausência de um OID gerado pelo sistema, eu provavelmente preciso criar uma coluna de usuário, chamada OID definida como uma sequência. O objetivo da pergunta é perguntar se existe uma solução canônica para aplicativos que usam o OID anterior à v12. Suponha que o aplicativo não possa ser alterado, portanto, qualquer solução deve ser totalmente implementada dentro do banco de dados.

Eu tenho uma hipertabela com escala de tempo assim:

create table logs
(
    time         timestamp not null,
    partitionkey text      not null,
    ip           inet,
    raw          text,
    transformed  double precision
);

E indexa da seguinte forma:

create index logs_time_idx
    on logs (time desc);

create unique index logs_partitionkey_time_uindex
    on logs (partitionkey asc, time desc);

Quando executo esta consulta, leva 20 minutos para ser concluída:

SELECT * FROM data.logs 
WHERE partitionkey LIKE '%m.60.05482730' 
AND time > NOW() - INTERVAL '3 days'

Mas quando eu executo este, leva 2 segundos:

SELECT * FROM data.logs 
WHERE partitionkey LIKE '865617033605366.m.60.05482730'
AND time > NOW() - INTERVAL '3 days'

Tentei indexar apenas as chaves de partição para ajudar a consulta curinga a encontrar valores correspondentes, mas isso não teve efeito.

-- created this index later to try and fix the slow wildcard query
create index logs_partitionkey_index
    on logs (partitionkey);

Explique o plano para consulta curinga:

Gather  (cost=1000.57..525711.89 rows=1219 width=81)
  Workers Planned: 2
  ->  Parallel Custom Scan (ChunkAppend) on logs  (cost=0.57..524589.99 rows=509 width=82)
        Chunks excluded during startup: 2
        ->  Parallel Index Scan using _hyper_2_10_chunk_logs_time_idx on _hyper_2_10_chunk  (cost=0.57..263956.91 rows=255 width=81)
              Index Cond: ("time" > (now() - '3 days'::interval))
              Filter: (partitionkey ~~ '%m.60.05482730'::text)
        ->  Parallel Index Scan using _hyper_2_9_chunk_logs_time_idx on _hyper_2_9_chunk  (cost=0.57..260629.72 rows=252 width=83)
              Index Cond: ("time" > (now() - '3 days'::interval))
              Filter: (partitionkey ~~ '%m.60.05482730'::text)
JIT:
  Functions: 8
  Options: Inlining true, Optimization true, Expressions true, Deforming true

Explique para o valor específico da chave de partição:

Custom Scan (ChunkAppend) on logs  (cost=0.44..903.08 rows=790 width=82)
  Chunks excluded during startup: 2
  ->  Index Scan using _hyper_2_9_chunk_logs_partitionkey_time_uindex on _hyper_2_9_chunk  (cost=0.57..447.44 rows=392 width=83)
        Index Cond: ((partitionkey = '865617033605366.m.60.05482730'::text) AND ("time" > (now() - '3 days'::interval)))
        Filter: (partitionkey ~~ '865617033605366.m.60.05482730'::text)
  ->  Index Scan using _hyper_2_10_chunk_logs_partitionkey_time_uindex on _hyper_2_10_chunk  (cost=0.57..452.27 rows=396 width=81)
        Index Cond: ((partitionkey = '865617033605366.m.60.05482730'::text) AND ("time" > (now() - '3 days'::interval)))
        Filter: (partitionkey ~~ '865617033605366.m.60.05482730'::text)

O TimescaleDB não consegue fazer consultas curinga (%) ou eu perco um índice?

Eu tenho uma tabela com colunas: id, antena_id, latitude, longitude. Existem dois índices compostos em (antenna_id, latitude) e (antenna_id, longitude). Quando eu faço um max(latitude) para um id de antena específico, a velocidade é aceitável, mas fazer um min e max para latitude e longitude ao mesmo tempo é muito lento.

Usando PostgreSQL 12.3

EXPLAIN (analyze, buffers, format text) 
SELECT max(latitude) 
FROM packets 
WHERE antenna_id IN (1,2)

Finalize Aggregate  (cost=443017.21..443017.22 rows=1 width=32) (actual time=4373.679..4373.679 rows=1 loops=1)
  Buffers: shared hit=10812 read=16887
  ->  Gather  (cost=443017.10..443017.21 rows=1 width=32) (actual time=4373.412..4389.032 rows=2 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        Buffers: shared hit=10812 read=16887
        ->  Partial Aggregate  (cost=442017.10..442017.11 rows=1 width=32) (actual time=4313.576..4313.577 rows=1 loops=2)
              Buffers: shared hit=10809 read=16887
              ->  Parallel Index Only Scan using idx_packets_antenna_id_latitude on packets  (cost=0.57..433527.51 rows=3395835 width=7) (actual time=0.375..3435.488 rows=2201866 loops=2)
                    Index Cond: (antenna_id = ANY ('{1,2}'::integer[]))
                    Heap Fetches: 0
                    Buffers: shared hit=10809 read=16887
Planning Time: 5.992 ms
JIT:
  Functions: 8
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 6.236 ms, Inlining 0.000 ms, Optimization 1.549 ms, Emission 32.058 ms, Total 39.842 ms
Execution Time: 4706.406 ms

A explicação parece quase idêntica para max(longitude), min(latitude) e min(longitude) por conta própria. A velocidade é aceitável.

SELECT max(latitude), max(longitude), min(latitude), min(longitude) 
FROM packets 
WHERE antenna_id IN (1,2)

[2021-03-06 09:28:30] 1 row retrieved starting from 1 in 5 m 35 s 907 ms (execution: 5 m 35 s 869 ms, fetching: 38 ms)

Finalize Aggregate  (cost=3677020.18..3677020.19 rows=1 width=128)
  ->  Gather  (cost=3677020.06..3677020.17 rows=1 width=128)
        Workers Planned: 1
        ->  Partial Aggregate  (cost=3676020.06..3676020.07 rows=1 width=128)
              ->  Parallel Seq Scan on packets  (cost=0.00..3642080.76 rows=3393930 width=14)
                    Filter: (antenna_id = ANY ('{1,2}'::integer[]))
JIT:
  Functions: 7
  Options: Inlining true, Optimization true, Expressions true, Deforming true

EXPLAIN (analyze, buffers, format text) 
SELECT max(latitude), max(longitude), min(latitude), min(longitude) 
FROM packets 
WHERE antenna_id IN (1,2)

está em execução há 24 horas e ainda não está pronto

Índices

create index idx_packets_antenna_id_time
    on packets (antenna_id, time);

create index idx_packets_antenna_id_longitude
    on packets (antenna_id, longitude);

create index idx_packets_device_id_time
    on packets (device_id, time);

create index idx_packets_antenna_id_latitude
    on packets (antenna_id, latitude);

Estatísticas de dados

select count(*) from packets
136758098

select count(distinct (antenna_id)) from packets
17558

select antenna_id, count(*) as records 
from packets 
where antenna_id in (1,2) 
group by antenna_id 
order by records desc

1,4361049
2,42683

Pergunta

Por que a segunda consulta que faz o min e max no campo de latitude e longitude não usa os índices? E como posso reescrever a consulta para que seja mais rápida?