Mikko Ohtamaa's questions

Temos um PostgreSQL (com extensão TimescaleDB) em execução em um servidor Ubuntu, cuja carga tem aumentado constantemente ao longo do ano.

Gostaríamos de manter a carga sob controle. Para CPU, ou carga, gostaríamos de identificar consultas que estão causando o aumento na carga, pois algumas das consultas são provavelmente desnecessárias; a frequência pode ser diminuída, e assim por diante.

Qual seria a melhor maneira de mapear consultas e/ou aplicações PostgreSQL para uso de CPU? Especificamente, como essa não é uma consulta única, deve haver algum tipo de coleta de estatísticas por um período maior de tempo.

Atualmente, estamos usando o Datadog para monitorar o PostgreSQL e o servidor, mas ficaremos felizes em considerar quaisquer opções.

Estou executando o PostgreSQL no sistema de arquivos ZFS compactado . Uma dica mencionada é desabilitar a compactação TOAST inline do PostgreSQL porque o ZFS pode compactar melhor os dados. Isso pode ser feito definindo o armazenamento de coluna como EXTERNAL.

Eu posso fazer isso coluna por coluna com:

ALTER TABLE my_table ALTER COLUMN my_column SET STORAGE EXTERNAL;

No entanto, isso pode ser um pouco complicado, pois todo esquema precisa ter migrado para isso manualmente.

Existem maneiras fáceis de

• Definir padrão STORAGEem EXTERNALvez de MAINpara todas as colunas
• Desative a compactação TOAST de outra maneira

Encontrei a opção default_toast_compression, mas a documentação não está clara se posso desativá-la.

O PostgreSQL suporta algum tipo de índice esparso que seria útil para indexar apenas o primeiro e o último valor da tabela, com base em uma determinada chave?

Considere os seguintes dados:

CREATE TABLE samples (
   device_id int not null;
   ts timestamp not null;
   value real not null;
);

Agora, vamos supor que eu tenha milhões de dispositivos e cada um possa ter bilhões de amostras. Eu quero acessar todos esses dados - se o acesso for lento, não me importo. No entanto, estou especialmente interessado em acessar o primeiro e o último valor por tstimestamp: (device_id, value) muito, rápido. Isso para saber qual é o intervalo de amostras para um dispositivo específico.

Eu posso construir uma lógica de gatilho/aplicativo que gerencie uma tabela separada onde as informações são armazenadas:

CREATE TABLE first_last_samples (
   device_id int not null;
   first_ts timestamp not null;
   first_value real not null;
   last_ts timestamp not null;
   last_value real not null;
);

No entanto, gerenciar a lógica para armazenar em cache o primeiro/último item parece um pouco desajeitado. Estou perguntando se o PostgreSQL gerenciaria algum tipo de índice especial, talvez por extensões, o que me permitiria resolver esse problema no nível de definição de tabela/exibição/índice?

Como existem bilhões de pontos de dados, colocar todos eles no índice não parece fazer sentido se você estiver interessado apenas no primeiro e no último.

Estou tentando associar consultas lentas no PostgreSQL a aplicativos que as causam. Eu configurei o rastreamento application_name rudimentar .

Alguns aplicativos contêm várias tarefas independentes sendo executadas simultaneamente. Seria possível atualizar application_nameno meio de uma sessão para refletir a tarefa que a conexão está processando no momento? Dessa forma, identificar a fonte de consultas problemáticas seria mais fácil, pois eu poderia ler pg_stat_activitydiretamente algo legível por humanos para a causa raiz de consultas lentas.

(Alternativa, posso fazer os clientes se reconectarem com um nome diferente, mas isso é um pouco doloroso.)

Eu tenho uma instância de desenvolvedor Docker PostgreSQL (TimescaleDB) com um mapeamento de volume local do Linux.

version: '3'
services:
  dex-timeseriesdb:
    image: timescale/timescaledb:latest-pg12
    # https://stackoverflow.com/a/56754077/315168
    shm_size: 1g
    container_name: dex-timeseriesdb
    environment:
      POSTGRES_USER: postgres
    volumes:
       - $PWD/data/postgresql:/var/lib/postgresql/data

Após o desligamento não limpo, a instância não inicia mais com FATAL: xlog flush request 0/2CEFA910 is not satisfied --- flushed only to 0/1B48258erro:

dex-timeseriesdb    |
dex-timeseriesdb    | PostgreSQL Database directory appears to contain a database; Skipping initialization
dex-timeseriesdb    |
dex-timeseriesdb    | 2021-06-13 18:50:47.330 UTC [1] LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
dex-timeseriesdb    | 2021-06-13 18:50:47.330 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
dex-timeseriesdb    | 2021-06-13 18:50:47.330 UTC [1] LOG:  listening on IPv6 address "::", port 5432
dex-timeseriesdb    | 2021-06-13 18:50:47.336 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
dex-timeseriesdb    | 2021-06-13 18:50:47.486 UTC [21] LOG:  database system shutdown was interrupted; last known up at 2021-06-13 18:47:35 UTC
dex-timeseriesdb    | 2021-06-13 18:50:49.629 UTC [21] LOG:  database system was not properly shut down; automatic recovery in progress
dex-timeseriesdb    | 2021-06-13 18:50:49.645 UTC [21] LOG:  redo starts at 0/1B46C68
dex-timeseriesdb    | 2021-06-13 18:50:49.648 UTC [21] LOG:  invalid record length at 0/1B48258: wanted 24, got 0
dex-timeseriesdb    | 2021-06-13 18:50:49.648 UTC [21] LOG:  redo done at 0/1B48220
dex-timeseriesdb    | 2021-06-13 18:50:49.697 UTC [21] LOG:  request to flush past end of generated WAL; request 0/2CEFA910, currpos 0/1B48258
dex-timeseriesdb    | 2021-06-13 18:50:49.697 UTC [21] CONTEXT:  writing block 0 of relation base/13455/16573_vm
dex-timeseriesdb    | 2021-06-13 18:50:49.697 UTC [21] FATAL:  xlog flush request 0/2CEFA910 is not satisfied --- flushed only to 0/1B48258
dex-timeseriesdb    | 2021-06-13 18:50:49.697 UTC [21] CONTEXT:  writing block 0 of relation base/13455/16573_vm
dex-timeseriesdb    | 2021-06-13 18:50:49.701 UTC [1] LOG:  startup process (PID 21) exited with exit code 1
dex-timeseriesdb    | 2021-06-13 18:50:49.701 UTC [1] LOG:  aborting startup due to startup process failure
dex-timeseriesdb    | 2021-06-13 18:50:49.744 UTC [1] LOG:  database system is shut down

Provavelmente, isso é corrupção de dados devido ao desligamento não limpo do Docker.

Não há nada importante no banco de dados. No entanto, ainda gostaria de saber se é possível recuperar o banco de dados em situações como essa, em vez de reconstruí-lo do zero ou restaurar a partir de um backup.

Eu testei que o mapeamento de volume é gravável dentro da instância do Docker usando shell, então isso não deve ser um problema.

Veja também pergunta semelhante sobre FATAL: solicitação de liberação de xlog, mas erro um pouco diferente .

Estou tentando restaurar um pg_dumpbackup binário com um nome de banco de dados diferente que estava no dump original. Apesar de dar --dbnameargumento de linha de comando, pg_restoretenta restaurar tudo sob o nome original do banco de dados - o comportamento que eu particularmente quero evitar.

createdb mydb

pg_restore --dbname=mydb --exit-on-error -Fc database.bak

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8; 2615 16396 SCHEMA topology u9o2f4j0
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "topology" already exists
    Command was: CREATE SCHEMA topology;

Como posso fazer pg_restorepara restaurar o banco de dados com um nome diferente?

pg_dump --version
pg_dump (PostgreSQL) 9.5.5

pg_restore --version
pg_restore (PostgreSQL) 9.6.1