Eu tenho uma tabela mostrada abaixo.
CREATE TABLE tbl (sl_no int, username text, petname1 text, petname2 text);
INSERT INTO tbl VALUES
(1, 'A', 'XYZ', 'ABC')
, (2, 'B', 'RTS', 'DEF')
, (3, 'C', 'QWE', 'GHI')
, (4, 'D', 'HGD', 'JKL')
;
Como faço para converter uma única coluna em várias entradas de linha? neste resultado:
1, 'A', 'XYZ'
1, 'A', 'ABC'
2, 'B', 'RTS'
2, 'B', 'DEF'
3, 'C', 'QWE'
3, 'C', 'GHI'
4, 'D', 'HGD'
4, 'D', 'JKL'
usando postgresql 11.3 se possível
um dos meus servidores PostgreSQL 11 estava lançando um erro estranho e se recusou a iniciar corretamente a replicação de streaming após um backup de base:
FATAL: encerrando o processo walreceiver devido ao comando do administrador
Tentei habilitar o maior detalhamento do log (Debug5), mas isso não forneceu mais informações sobre o motivo pelo qual ele continuava morrendo:
2021-07-29 15:33:30.862 UTC [39315] DEBUG: postgres: PostmasterMain: initial environment dump:
2021-07-29 15:33:30.862 UTC [39315] DEBUG: -----------------------------------------
2021-07-29 15:33:30.862 UTC [39315] DEBUG: PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
2021-07-29 15:33:30.862 UTC [39315] DEBUG: PG_GRANDPARENT_PID=39310
2021-07-29 15:33:30.862 UTC [39315] DEBUG: PGLOCALEDIR=/usr/share/locale
2021-07-29 15:33:30.862 UTC [39315] DEBUG: PGSYSCONFDIR=/etc/postgresql-common
2021-07-29 15:33:30.862 UTC [39315] DEBUG: LANG=en_US.UTF-8
2021-07-29 15:33:30.862 UTC [39315] DEBUG: PWD=/
2021-07-29 15:33:30.863 UTC [39315] DEBUG: PGDATA=/var/lib/postgresql/11/replica
2021-07-29 15:33:30.863 UTC [39315] DEBUG: LC_COLLATE=en_US.UTF-8
2021-07-29 15:33:30.863 UTC [39315] DEBUG: LC_CTYPE=en_US.UTF-8
2021-07-29 15:33:30.863 UTC [39315] DEBUG: LC_MESSAGES=en_US.UTF-8
2021-07-29 15:33:30.863 UTC [39315] DEBUG: LC_MONETARY=C
2021-07-29 15:33:30.863 UTC [39315] DEBUG: LC_NUMERIC=C
2021-07-29 15:33:30.863 UTC [39315] DEBUG: LC_TIME=C
2021-07-29 15:33:30.863 UTC [39315] DEBUG: -----------------------------------------
2021-07-29 15:33:30.867 UTC [39315] DEBUG: registering background worker "logical replication launcher"
2021-07-29 15:33:30.868 UTC [39315] LOG: listening on IPv6 address "::1", port 5433
2021-07-29 15:33:30.868 UTC [39315] LOG: listening on IPv4 address "127.0.0.1", port 5433
2021-07-29 15:33:30.868 UTC [39315] LOG: listening on IPv4 address "*snip*", port 5433
2021-07-29 15:33:30.868 UTC [39315] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2021-07-29 15:33:30.869 UTC [39315] DEBUG: invoking IpcMemoryCreate(size=156459008)
2021-07-29 15:33:30.869 UTC [39315] DEBUG: mmap(157286400) with MAP_HUGETLB failed, huge pages disabled: Cannot allocate memory
2021-07-29 15:33:30.885 UTC [39315] DEBUG: SlruScanDirectory invoking callback on pg_notify/0000
2021-07-29 15:33:30.885 UTC [39315] DEBUG: removing file "pg_notify/0000"
2021-07-29 15:33:30.885 UTC [39315] DEBUG: dynamic shared memory system will support 588 segments
2021-07-29 15:33:30.885 UTC [39315] DEBUG: created dynamic shared memory control segment 1597797971 (14128 bytes)
2021-07-29 15:33:30.887 UTC [39315] DEBUG: max_safe_fds = 982, usable_fds = 1000, already_open = 8
2021-07-29 15:33:30.889 UTC [39316] LOG: database system was shut down in recovery at 2021-07-29 15:31:23 UTC
2021-07-29 15:33:30.890 UTC [39316] DEBUG: standby_mode = 'on'
2021-07-29 15:33:30.890 UTC [39316] DEBUG: primary_conninfo = '*snip*'
2021-07-29 15:33:30.890 UTC [39316] DEBUG: recovery_target_timeline = latest
2021-07-29 15:33:30.890 UTC [39316] LOG: entering standby mode
2021-07-29 15:33:30.890 UTC [39316] LOG: invalid resource manager ID 172 at 235/450000D0
2021-07-29 15:33:30.890 UTC [39316] DEBUG: switched WAL source from archive to stream after failure
2021-07-29 15:33:30.891 UTC [39317] DEBUG: find_in_dynamic_libpath: trying "/usr/lib/postgresql/11/lib/libpqwalreceiver"
2021-07-29 15:33:30.905 UTC [39317] DEBUG: find_in_dynamic_libpath: trying "/usr/lib/postgresql/11/lib/libpqwalreceiver.so"
2021-07-29 15:33:30.918 UTC [39317] LOG: started streaming WAL from primary at 235/45000000 on timeline 1
2021-07-29 15:33:30.920 UTC [39317] DEBUG: sendtime 2021-07-29 15:33:30.917704+00 receipttime 2021-07-29 15:33:30.920718+00 replication apply delay (N/A) transfer latency 3 ms
2021-07-29 15:33:30.920 UTC [39317] DEBUG: sending write 235/45020000 flush 0/0 apply 0/0
2021-07-29 15:33:30.921 UTC [39317] DEBUG: sending write 235/45020000 flush 235/45020000 apply 0/0
2021-07-29 15:33:30.921 UTC [39316] LOG: invalid resource manager ID 172 at 235/450000D0
2021-07-29 15:33:30.921 UTC [39317] FATAL: terminating walreceiver process due to administrator command
2021-07-29 15:33:30.921 UTC [39317] DEBUG: shmem_exit(1): 1 before_shmem_exit callbacks to make
2021-07-29 15:33:30.921 UTC [39317] DEBUG: shmem_exit(1): 5 on_shmem_exit callbacks to make
2021-07-29 15:33:30.921 UTC [39317] DEBUG: proc_exit(1): 2 callbacks to make
2021-07-29 15:33:30.921 UTC [39317] DEBUG: exit(1)
2021-07-29 15:33:30.921 UTC [39316] DEBUG: switched WAL source from stream to archive after failure
A única preocupação foram as mensagens de nível LOG como
LOG: ID do gerenciador de recursos inválido 172 em 235/450000D0
Descobriu-se, no entanto, que estas são apenas a maneira do Postgres de dizer "Chegou ao fim das estruturas WAL válidas" e as mensagens de nível de LOG podem ser ignoradas com segurança.
Eu tentei remover os logs WAL existentes (de datadir/pg_wal/), pensando que talvez uma corrupção de arquivo estivesse em jogo, o servidor ainda não iniciaria a replicação. A única solução foi fazer um backup de base completamente novo.
Minha pergunta - é terminating xyz process due to administrator commanduma mensagem padrão que o Postgres fornece quando um de seus processos morre de maneira não padrão?
Haveria mais opções de depuração em casos como este? Nem mesmo o mais alto detalhamento de log forneceu muita informação útil.
Eu segui com sucesso as instruções de instalação do Postgres 11 no Centos 7 encontradas aqui . Consigo criar esquemas/tabelas com sucesso e inserir dados no banco de dados pela porta 5432 sem problemas.
Agora gostaria de alterar a porta padrão para 5332 (ou outra coisa, não importa), e fiz o seguinte:
vi /var/lib/pgsql/11/data/postgresql.conf
Mudou a linha:
#port = 5432 # (change requires restart)
se parecer:
port = 5332 # (change requires restart)
Em seguida, reiniciei o serviço postgres fazendo:
systemctl restart postgresql-11.service
Então eu mudo para o usuário postgres:
su - postgres
psql
Estou recebendo a seguinte mensagem de erro:
sql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Então, aparentemente, há outra referência da porta 5432 em algum lugar, mas não consigo encontrá-la.
Para sua informação, se eu desfazer a alteração que fiz acima (no arquivo /var/lib/pgsql/11/data/postgresql.conf), tudo funcionará perfeitamente novamente na porta padrão 5432.
O que mais preciso fazer para que o postgres seja executado em uma porta diferente?
Ι tenho a seguinte visão:
CREATE OR REPLACE VIEW public.my_view as
SELECT
col1,
col11
from
table_a JOIN table_b on table_a.id=table_b.a_id
join
(
select id,col56 from table_c where col56 > 3
) as filtered_c on table_a.id=filtered_c.a_id;
tabela_a é:
id: SERIAL PK
col1: INTEGER
tabela_b é:
id: SERIAL PK
col11: INTEGER
a_id FK table_a
tabela_c é:
id: SERIAL PK
col56 INTEGER
a_id FK table_a
Como vi no wiki do postgresql , posso inserir valores nas visualizações que correspondem às tabelas apropriadas. Mas no meu caso se eu inserir:
INSERT INTO my_view VALUES (7,88);
Em qual tabela os valores serão inseridos? Até onde eu sei, as visualizações são tabelas somente leitura.
Olhando para a documentação do PostgreSQL não consegui encontrar nada no timenow(). No entanto, se eu chamar a função, ela funciona.
Então, qual é a diferença entre now()e timenow()?
Eu vou adivinhar que now()é baseado na transação enquanto timenow()é hora do sistema operacional do servidor ou estou completamente fora?
Abaixo está a definição gerada automaticamente pelo DataGrip. Todas as funções do sistema (por exemplo sum) todas têm missing source codee como você pode ver faz parte do pg_catalogesquema.
O título conta a história, mas como posso rastrear a causa de um pico de memória no Postgres 11.10? Como você pode ver na captura de tela abaixo, houve um pico de memória de até 32 GB, o que fez com que o OOM iniciasse e matasse várias conexões. De acordo com /var/log/postgresql/postgresql-11-main.logo sistema de banco de dados entrou em modo de recuperação ao mesmo tempo e perdemos um pouco de dados também.
Suspeito que o número de conexões juntamente com instruções de inserção muito grandes possa ter algo a ver com isso, mas precisa de uma maneira de provar (ou refutar) a hipótese.
Eu tenho uma tabela onde cada entrada é um nó e a tabela contém as conexões diretas de cada nó com outros nós. Estou procurando criar uma exibição com uma coluna para cada nó contendo todos os nós da cadeia, não apenas os nós aos quais o próprio nó está conectado.
Um exemplo seria gerar a coluna Nodes in Chain a partir das duas primeiras colunas da tabela a seguir:
CREATE TABLE example
(
node text,
connections text[],
nodes_in_chain text[]
)
INSERT INTO example VALUES
('a', ARRAY['a','b'], null),
('b', ARRAY['a','b','c','d'], null),
('c', ARRAY['b','c'], null),
('d', ARRAY['b','d'], null),
('e', ARRAY['e','f'], null),
('f', ARRAY['e','f'], null);
Node Connections Nodes in Chain
"a" "{a,b}" "{a,b,c,d}"
"b" "{a,b,c,d}" "{a,b,c,d}"
"c" "{b,c}" "{a,b,c,d}"
"d" "{b,d}" "{a,b,c,d}"
"e" "{e,f}" "{e,f}"
"f" "{e,f}" "{e,f}"
Essa é uma pequena versão simplificada do problema real. Se eu puder resolver o exemplo, a tabela completa não deve ser problema.
Os dados desta tabela podem ser visualizados da seguinte forma:
Eu olhei em vários métodos diferentes para resolver este problema. Eu olhei para CTEs recursivos, mas não consegui fazê-los funcionar.
Cada nó está conectado a si mesmo atualmente no banco de dados. Não é um problema remover a conexão para si mesmo no banco de dados, se necessário.
Provavelmente antecedentes desnecessários para o problema :
A origem desse problema vem da tentativa de identificar veículos no trânsito. O banco de dados original contém a localização e a velocidade dos veículos a cada passo de tempo t em uma determinada área. O objetivo é determinar o tempo gasto em um semáforo. Para solucionar este problema foi identificada uma área de parada para o semáforo. Cada veículo nesta área com uma velocidade abaixo de um determinado limite é considerado como aguardando o semáforo. Devido a uma longa fila de fila, os veículos podem, no entanto, fazer fila fora desta área. Portanto, é feita uma linha de tráfego ("Cadeia de Nós") com todos os veículos que estão a uma certa distância uns dos outros e possuem uma velocidade baixa abaixo. Partida de um veículo dentro da área de fila identificada. O problema faz parte da pesquisa científica sobre os tempos de táxi das aeronaves.
Primeiro realizei o cálculo para identificar veículos em uma área com Python e pandas. No entanto, o código demorou 10x mais para ser executado, o que o tornou proibitivo para o projeto. O código era muito simples sem loops introduzidos manualmente e, portanto, não poderia ser acelerado (acredito). Também vou comparar a velocidade de execução do algoritmo de enfileiramento em Python versus PostgreSQL.
Estou executando o PostgreSQL 11.5. Eu tenho uma tabela simples de registros.
create table register(
id serial primary key,
name text not null
);
Esta tabela é pequena (~6000 linhas), leitura relativamente pesada (100s consultas/s), quase sem gravação. Eu queria adicionar uma chave estrangeira auto-referencial:
alter table register
add column leader_id integer references register(id);
Dado o tamanho da mesa, presumi que essa seria uma mudança bastante mundana. Ele funcionou bem localmente e no servidor de teste. No entanto, quando eu o executei na produção, a tabela travou. Os logs mostram ALTER e várias instruções SELECT levando ~ 10m para serem concluídas.
2020-08-04 00:01:15 UTC:10.0.2.101(59588):postgres@db:[21609]:LOG: could not receive data from client: Connection reset by peer
2020-08-04 00:02:39 UTC:10.0.2.101(59558):postgres@db:[1795]:LOG: could not receive data from client: Connection reset by peer
2020-08-04 00:02:39 UTC:10.0.2.101(59558):postgres@db:[1795]:LOG: unexpected EOF on client connection with an open transaction
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:LOG: duration: 456741.453 ms execute <unnamed>: alter table register
add column leader_id integer references register(id);
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:LOG: could not send data to client: Broken pipe
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:FATAL: connection to client lost
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:LOG: duration: 456635.839 ms statement: SELECT register.deleted_at IS NOT NULL AS deleted, register.client_id AS register_client_id
FROM register
WHERE register.id = 123 AND register.account_id = '22781BD1-F37A-4ACE-9A3D-CBF3464AFB43'::uuid
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:LOG: could not send data to client: Connection timed out
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:FATAL: connection to client lost
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:LOG: duration: 456656.956 ms statement: SELECT register.deleted_at IS NOT NULL AS deleted, register.client_id AS register_client_id
FROM register
WHERE register.id = 234 AND register.account_id = 'A6D8395C-63E8-40A8-A0AE-4F19B1DA5509'::uuid
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:LOG: could not send data to client: Connection timed out
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:FATAL: connection to client lost
O que está acontecendo aqui que está causando o travamento da mesa? Como posso trabalhar com segurança com uma chave estrangeira auto-referenciada?
De acordo com a documentação do PostgreSQL, posso usar o pg_waldump para exibir uma renderização legível por humanos do log de gravação antecipada de um cluster de banco de dados PostgreSQL.
Tenho instalação limpa do servidor PostgreSQL-11 no Ubuntu 18.04, mas não tenho este comando:
pg_waldump: comando não encontrado
Abaixo lista de utilitários pg presentes:
pg_archivecleanup pg_conftool pg_dumpall pg_recvlogical pg_virtualenv
pg_basebackup pg_createcluster pg_isready pg_renamecluster
pg_buildext pg_ctlcluster pg_lsclusters pg_restore
pg_config pg_dropcluster pg_receivewal pg_updatedicts
pg_config.libpq-dev pg_dump pg_receivexlog pg_upgradecluster
O que preciso fazer para usar este comando no meu servidor
Eu tenho uma tabela, vamos chamá-la de pai :
CREATE TABLE parent (
id TEXT PRIMARY KEY,
name TEXT,
description TEXT,
-- other parent attributes
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);
Ele tem várias tabelas filhas, digamos child_1 , child_2 , ... child_n . Todos eles se parecem com:
CREATE TABLE child_n (
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
parent_id TEXT NOT NULL REFERENCES parent (id) ON DELETE CASCADE,
-- other columns of child_n attributes
);
Algumas dessas crianças podem até ter suas próprias tabelas filhas.
Quando atualizo uma linha no pai, muitas vezes também quero substituir todos os seus descendentes (ou seja: seus filhos, seus filhos e assim por diante recursivamente), pois eles são relevantes apenas para a versão mais antiga dessa linha pai.
Meu plano era confiar nisso REFERENCES parent (id) ON DELETE CASCADE, mas isso só funciona se eu realmente excluir o pai. Eu sei que posso excluir e inserir em uma transação, mas isso torna a preservação dos valores das colunas (como created_at) mais complicada.
Existe uma maneira de acionar a exclusão em cascata de filhos sem realmente excluir a linha pai?