Luciano Andress Martini's questions

É seguro sincronizar a replicação do PostgreSQL usando o parâmetro rsync -u?

Por exemplo, se minha replicação foi perdida por muito tempo devido a um problema de conexão (arquivo WAL já excluído, por exemplo), sempre preciso rsync todos os arquivos do meu banco de dados novamente do mestre para a réplica.

Então, estou usando algo como (ou quase) isso (escravo interno):

systemctl stop postgresql@11-slave 
psql -h 10.0.0.MASTER -Atc "select pg_start_backup('clone', true);"
rsync -avz [email protected]:/var/lib/postgresql/11/main /var/lib/postgresql/11/  
systemctl start postgresql@11-slave 
psql -h 10.0.0.MASTER -Atc "select pg_stop_backup('clone', true);"

A solução acima está funcionando muito bem e foi testada várias vezes sem perda de dados.

Mas fico tentado a tentar restaurar a replicação usando algo mais rápido (perigoso?) como:

systemctl stop postgresql@11-slave 
psql -h 10.0.0.MASTER -Atc "select pg_start_backup('clone', true);"
rsync -uavz [email protected]:/var/lib/postgresql/11/main/ /var/lib/postgresql/11/  
systemctl start postgresql@11-slave 
psql -h 10.0.0.MASTER -Atc "select pg_stop_backup('clone', true);"

É seguro usar o parâmetro -u no rsync (update) em vez de recopiar todos os arquivos ou devo continuar copiando todos os arquivos quando algo acontecer com minha replicação?

Eu tenho uma tabela enorme que não consigo excluir as linhas, apenas atualizar as colunas que estão armazenando dados enormes em base64, que devo atualizar para null para tentar liberar espaço.

Então programei um script que é capaz de colocar todas as imagens em base64 esperando que o espaço seja liberado após o Vacuum!

As imagens estão definidas como null , vacuo executado, mas a tabela ainda está exatamente do mesmo tamanho, e tenho certeza que o espaço deve ser liberado imediatamente, então o que estou fazendo de errado?

O vácuo completo será capaz de liberar espaço de dados varchar enormes que atualizei para nulo? (Porque precisarei trancar a mesa se fizer isso e preciso ter certeza)

O tamanho do despejo diminuiu 10 vezes, portanto, espero um comportamento semelhante no tamanho do banco de dados.

É possível ter várias instâncias do PostgreSQL para uma pasta PGDATA, compartilhada em um armazenamento, por exemplo?

E se não for, existe algum tipo de RDBMS capaz de algo assim?

Corrija-me se estou usando o termo correto: "afetado globalmente", quero dizer, se eu aumentar o valor da sequência, ele será aumentado para todas as outras transações em execução sem a necessidade de confirmação....

Eu sei que as sequências têm esse comportamento, mas minha dúvida é se existe uma lista com alguns outros papéis que são afetados globalmente, se eu atualizar dentro de uma transação?

Por exemplo, se eu definir alter table mytable enable trigger all; isso afetará o comportamento do gatilho apenas na transação local ou em outras transações, observe que isso é apenas um exemplo, estou pensando em muitas outras coisas que preciso ter cuidado com isso.

Estou fazendo a replicação postgresql emitindo:

No mestre :

>select pg_start_backup('clone', true);
>\q 
#tar -cz main -f main.tar.gz

(Explicação: main é o diretório de dados no meu sistema /var/lib/postgresql/9.6/main)

Então eu transfiro main.tar.gz para o slave ....

Sobre o escravo :

--Uncompressing main.tar.gz.... 
#tar -zxf main.tar.gz 
--Copying previously created recovery.conf 
#cp recovery.conf main/
--And then just starts the slave
#service postgresql start

No mestre :

--And stoping backup mode on the master:
>select pg_stop_backup(); 

O que recebo é que a replicação está funcionando bem e realmente funciona bem.

Mas estou com dúvida após ler a documentação oficial que após a cópia devo copiar o pg_xlog em uma etapa adicional... https://wiki.postgresql.org/wiki/Binary_Replication_Tutorial

Mas eu nunca faço isso e nunca tive um problema - faço muitos testes, como contar todas as linhas da tabela, tanto no mestre quanto no escravo e muito mais, mas o que de pior pode acontecer?

Aumentar o valor default_statistics_target pode tornar seu banco de dados mais rápido, especialmente após a análise....

Lendo este artigo, vejo que https://discuss.pivotal.io/hc/en-us/articles/201581033-default-statistics-target-Explained

(...) em resumo e em termos básicos, este parâmetro controla a forma como as estatísticas são coletadas, sendo o valor 1 a estatística menos estimada/precisa e o valor 1000 a estatística mais precisa, obviamente com gasto de tempo/recursos ( CPU , memória etc ) / espaço . Normalmente, o valor padrão é suficiente para obter um plano preciso, mas se você tiver uma distribuição de dados complexa/ou uma coluna for referenciada na consulta com bastante frequência, definir um valor mais alto pode ajudar a obter estatísticas melhores na tabela e, portanto, uma melhor plano para o otimizador executar.

É uma boa explicação, mas, por exemplo, se eu definir default_statistics_target= 1000, o que 1000 realmente significa? São 1000 kilobytes de estatísticas sendo geradas? ou talvez sejam 1000 linhas das tabelas analisadas? Talvez seja 1000 colunas? ou talvez 1000 segundos para cada análise...

Então, minha pergunta é como esse número está realmente afetando a análise ou o planejador de consultas? Óbvio eu entendo que default_statistics_target = 1000 vai ter mais tempo que 100, para rodar analise, e que 1000 vai gerar estatísticas melhores...

Oi, eu sou capaz de iniciar a replicação de streaming com o postgresql, copiando os arquivos do banco de dados com este comando:

select pg_start_backup('clone', true); 

Isso mesmo, mas o problema é que eu preciso copiar todos os arquivos de banco de dados em /var/lib/postgresql/data de um servidor para outro. Esses arquivos são muito grandes por causa dos índices, por exemplo.

Quero saber se é possível fazer o mesmo usando um dump simples e restaurando ele no slave, o que é menor e mais rápido para transferir na rede, vai funcionar? E o que devo fazer para trabalhar?

Eu tenho duas funções em PL/pgSQL:

A função 1 chama a função 2, e a função 2 precisa processar uma tabela temporária criada na função 1, ou seja, essa tabela temporária precisa ser global no contexto da função 1 (que tem a função 2 dentro dela).

FUNCTION1 
   | CREATE TEMPORARY TABLE
   | DO SOME SPECIFIC STUFF...
   | FUNCTION2
         | DO SOME GENERIC STUFF
         | INSERT SOME DATA INTO THE TEMPORARY TABLE  
         | (Uhh!! Ohh! it hurts, table not visible)
   | RAISED ON ERROR

Existe uma maneira de passar a tabela temporária criada na função 1 para esta subfunção?

Eu preciso disso porque estou trabalhando com uma função muito grande que processa muitos dados em uma tabela temporária, antes de inserir todos os big data, mas quando tento transformar em uma forma mais modular, as subfunções simplesmente não conseguem ver o tabela temporária).

Está funcionando até agora (em uma grande função), mas estou fazendo CTRL+C CTRL+V para criar novas funções semelhantes, pois muitos códigos podem ser repetidos dentro dela. Se eu puder usar subfunções, esse problema desaparecerá, pois posso usar esse código várias vezes.

Decidimos migrar para 9.6 após algumas pesquisas sobre consultas paralelas, então trouxemos duas máquinas idênticas com exatamente o mesmo hardware, kernel e outras com PostgreSQL 9.1 e 9.6 respectivamente.

As versões 9.1 e 9.6 possuem a mesma configuração, exceto a configuração paralela que possui 5 workers paralelos no PostgreSQL 9.6.

Os resultados não são os que esperamos. No início, parece melhor que 9.1 para consultas simples comoselect * from something where big_string_column ilike '%test%';

Mas quando as consultas começam a ficar mais complexas, o PostgreSQL 9.1 começou a ganhar e por isso estou procurando ajuda. Tentamos desabilitar a consulta paralela, mas não adiantou. Não posso dar mais detalhes sobre a estrutura do banco de dados (desculpe), mas vai me ajudar se alguém já passou por algo parecido e resolveu.

A consulta (ofuscada) que executei em ambos:

SELECT case when lbipro = 1 then 'PEM'
            when lbipro = 2 then 'CRE'
            when lbipro = 3 then 'COM'
            when lbipro = 4 then 'REC'
            when lbipro = 6 then 'DES'
            END pnom, 
            lbipro, 
            sum(lbival) as lim,
            sum(coalesce(CASE WHEN lbisal > 0 THEN lbisal END,0)) dsp,
            sum(coalesce(CASE WHEN lbisal <= 0 THEN lbisal END,0)) exc,
            sum(coalesce(obiave,0)) as tom
FROM "lbi"
LEFT JOIN (SELECT sum(obiave) as obiave, obimat, obipro, obiscr
           FROM   obi
           WHERE  obicop = '022017'
           GROUP BY obimat, obipro, obiscr) as oo 
ON        oo.obimat = lbimat 
and       oo.obipro = lbipro
WHERE     lbicon = '5' 
AND       lbicop = '022017'
GROUP BY  "lbipro"
ORDER BY  "lbipro" ASC;

O plano gerado para 9.1 (explicar analisar):

 Sort  (cost=231411.25..231411.26 rows=1 width=46) (actual time=2389.223..2389.223 rows=3 loops=1)
   Sort Key: lbi.lbipro
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=231411.22..231411.24 rows=1 width=46) (actual time=2389.209..2389.214 rows=3 loops=1)
         ->  Hash Right Join  (cost=193708.93..223165.41 rows=471189 width=46) (actual time=1698.385..1950.493 rows=458094 lo$
               Hash Cond: (((obi.obimat)::text = (lbi.lbimat)::text) AND (obi.obipro = lbi.lbipro))
               ->  HashAggregate  (cost=66042.93..66261.10 rows=21817 width=55) (actual time=470.412..536.613 rows=125480 loo$
                     ->  Seq Scan on obi  (cost=0.00..63861.29 rows=218164 width=55) (actual time=0.137..352.910 rows=215512 $
                           Filter: ((obicop)::text = '022017'::text)
               ->  Hash  (cost=120598.17..120598.17 rows=471189 width=32) (actual time=1227.654..1227.654 rows=458094 loops=1)
                     Buckets: 65536  Batches: 1  Memory Usage: 29292kB
                     ->  Seq Scan on lbi  (cost=0.00..120598.17 rows=471189 width=32) (actual time=5.109..1093.313 rows=45809$
                           Filter: ((lbicon = 5) AND ((lbicop)::text = '022017'::text))
 Total runtime: 2389.494 ms

O plano gerado para 9.6 (sim, é para a mesma consulta):

 GroupAggregate  (cost=244410.91..256931.05 rows=3 width=164) (actual time=7070.306..7547.298 rows=3 loops=1)
   Group Key: lbi.lbipro
   ->  Merge Left Join  (cost=244410.91..248891.54 rows=459395 width=46) (actual time=6789.774..7290.870 rows=458094 loops=1)
         Merge Cond: ((lbi.lbipro = oo.obipro) AND ((lbi.lbimat)::text = (oo.obiave)::text))
         ->  Sort  (cost=163803.11..164951.59 rows=459395 width=32) (actual time=5102.693..5220.945 rows=458094 loops=1)
               Sort Key: lbi.lbipro, lbi.lbimat
               Sort Method: quicksort  Memory: 48077kB
               ->  Seq Scan on lbi  (cost=0.00..120598.44 rows=459395 width=32) (actual time=6.580..1341.290 rows=458094 loop$
                     Filter: ((lbicon = 5) AND ((lbicop)::text = '022017'::text))
                     Rows Removed by Filter: 3518238
         ->  Sort  (cost=80607.80..80945.86 rows=135222 width=54) (actual time=1687.073..1709.739 rows=125480 loops=1)
               Sort Key: oo.obipro, oo.obiave
               Sort Method: quicksort  Memory: 12876kB
               ->  Subquery Scan on oo  (cost=66041.03..69083.53 rows=135222 width=54) (actual time=695.702..803.308 rows=125$
                     ->  HashAggregate  (cost=66041.03..67731.31 rows=135222 width=81) (actual time=695.701..794.818 rows=125$
                           Group Key: obi.obiave, obi.obipro, obi.obiscr
                           ->  Seq Scan on obi  (cost=0.00..63861.41 rows=217962 width=55) (actual time=0.193..541.695 rows=2$
                                 Filter: ((obicop)::text = '022017'::text)
                                 Rows Removed by Filter: 1728890
 Planning time: 1.528 ms
 Execution time: 7555.976 ms

No PostgreSQL 9.1 o tempo de execução da consulta é de cerca de 1 ou 2 segundos. Para 9,6 é mais de 8 ou 9 segundos. Eu verifiquei a configuração muitas vezes e eles são idênticos agora. Eu removi a consulta paralela, mas ainda é mais lenta que 9.1. Eu até tentei penalizar 9,1 reduzindo work_meme shared_buffersou aumentando em 9,6, mas 9,1 ainda ganha. Eu corri vacuum analyzemuitas vezes.

Fiz o teste com o 9.4 e está agindo como 9.1. Eu suspeito que o 9.5 dará os mesmos resultados, e há algum bug de desempenho no 9.6. Eu larguei o banco de dados e o criei novamente em 9.6 - ainda é lento. O pior é que muitas consultas estão funcionando mais lentamente no 9.6 e algumas são mais rápidas.

Essa diferença é causada por algum tipo de otimização que eu possa desabilitar?

A versão específica é 9.6.2.

Eu tenho uma tabela retornando o seguinte conjunto de resultados:

  mydate    | startstop
------------+----------
 2018-02-07 | start
 2018-02-14 | stop
 2017-02-06 | start
 2017-02-12 | stop
 2016-02-05 | start 
 2016-02-12 | stop 

Preciso saber se minha data atual está em um dos intervalos, por exemplo se eu consultar a tabela a partir da data atual '2017-02-07', preciso obter 'TRUE'.

Eu sei que parece tão simples, mas não é ser simples!

O melhor que encontrei é este:

       select true 
        where '2017-02-06'>=
         (select mydate from mytable where starstop='start' order by id limit 1) 
        and '2017-02-06' <= 
         (select mydate from mytable where startstop='stop' order by id limit 1);

Funciona retornando TRUEse a data estiver em um de cada intervalo, mas somente se a tabela não tiver intervalos no futuro, e como você pode ver minha tabela tem intervalos no futuro.

Nota: O sistema de gerenciamento de banco de dados é o PostgreSQL 9.1

Quero saber os riscos da pg_start_backup()função de controle.

Por exemplo, o que acontecerá se alguém reinicializar o servidor ou se houver uma queda de energia durante pg_start_backupa operação do?

O que acontecerá se algo der errado com o postgresprocesso e simplesmente vazar?

O que acontecerá se o disco rígido ficar cheio com esta opção habilitada?

O que é esse erro? Vejo que a replicação está funcionando bem, mas não tenho certeza se os dados estão corrompidos ou não.

Posso ignorar com segurança esse erro?

Eu quero saber se os índices são replicados em uma replicação binária no postgresql.

E se for, como posso desativar esse comportamento?

Portanto, se o escravo se tornar mestre, posso começar a indexá-lo usando a reindexação?

Olá, estou seguindo este tutorial sempre que preciso configurar uma replicação binária, na verdade estou usando o segundo método chamado: "Iniciando a replicação com apenas uma reinicialização rápida do mestre" encontrado aqui: https://wiki.postgresql.org /wiki/Binary_Replication_Tutorial

Isso funciona bem.

Em um ambiente de teste, tentei usar tar em vez deste rsync:

 rsync -av --exclude pg_xlog --exclude postgresql.conf --exclude postgresql.pid \ 
     data/*  192.168.0.2:/var/lib/postgresql/data/

Assim:

tar -cz data >data.tar.gz

Gerar o arquivo .tar com os dados e descompactá-lo no slave, quão ruim isso pode ser para o banco de dados?

Nos logs, o escravo mostra que se conectou com sucesso ao mestre.

Estamos executando um PostgreSQL 9.1.20 com um banco de dados de cerca de 50 GB.

1. Todas as coisas funcionam bem.
2. Depois de alguns dias, valores inesperados estranhos aparecem em colunas numéricas, como valores negativos ou maiores em lugares inesperados.
3. A parte estranha: se executarmos um pg_dumpdo mesmo banco de dados "corrompido" e depois restaurá-lo (no mesmo servidor), os dados ficarão ok. Agora as mesmas consultas retornam valores diferentes!

Por que o dump do banco de dados tem valores diferentes quando estamos tirando o dump exatamente do banco de dados "corrompido"? (O dump corrigiu os valores de nossa percepção!)

Isso pode estar relacionado a algum tipo de transação quebrada ou problema no disco rígido? Observe que reiniciar o banco de dados não resolve o problema.

Qual pode ser a causa por trás desse problema?

Conforme solicitado nos comentários alguns exemplos de dados:

    #psql 
    \c base
    select * from salXXXX where sal_id=2323; 
    sal_id | sal_XXXX | XXXXXX | XXXXXX | XXXXX | XXXXX | XXXXX 
--------+-----------+---------------+--------------------+------------+-----------------------+------------
   2323 |     -30.43 | 42586501      |                  6 |         13 | f                     | 

    \quit
    pg_dump -h 127.0.0.1 -p 5432 --user=postgres >backup.sql
    psql 
    drop database base;
    create database base;
    \c base 
    \i backup.sql
    (no errors found here) 
    select * from salXXXX where sal_id=2323; 
    sal_id | sal_XXXX | XXXXXX | XXXXXX | XXXXX | XXXXX | XXXXX 
--------+-----------+---------------+--------------------+------------+-----------------------+------------
   2323 |    245.43 | 42586501      |                  6 |         13 | f                     | 

É difícil imitá-lo, raramente ocorre, mas quando ocorre é só fazer um dump, é uma solução ridícula, mas funciona.

Este é apenas um exemplo, muitos outros valores errados começam a aparecer, mesmo que reprocessemos os dados. Mas se o banco de dados for descarregado e restaurado, os valores estarão no intervalo esperado novamente.

Qual é a diferença entre:

vacuum;

e

vacuum analyze; 

Eu li o postgresqlmanual, mas isso ainda não está 100% claro para mim.

Preciso executar os dois ou um deles é suficiente?

Eu sei que CREATE INDEX CONCURRENTLYé mais lento, mas quanto tempo vai demorar para terminar em comparação com o tradicional CREATE INDEX? 40% a mais ou muito mais do que isso?

Estou criando um índice em uma tabela com 10000 registros, e em uma coluna com 15 caracteres. Leva 10 minutos usando o tradicional CREATE INDEX.

Eu sei que é muito difícil responder isso, mas existe uma maneira de estimar?

Tenho 4 tabelas relacionadas assim (é um exemplo):

Company:
ID
Name
CNPJ

Department:
ID
Name
Code
ID_Company 

Classification:
ID
Name
Code
ID_Company

Workers:
Id 
Name
Code
ID_Classification
ID_Department

Suponha que eu tenha um classificationcom id = 20, id_company = 1. E uma departmentque tem id_company = 2(que representa outra empresa).

Isso permitirá a criação de um trabalhador que seja de duas empresas, pois a classificação e o departamento estão vinculados à empresa separadamente. Não quero que isso aconteça, então acho que tenho um problema com meus relacionamentos e não sei como resolver.