Imanol Y.'s questions

Oi, estou executando em uma máquina centos 7, estou no processo de migrar um banco de dados postgres antigo para o novo servidor, ele tem 10 TB de dados, então estou tentando com pg_basebackup. O novo servidor tem um disco independente com armazenamento suficiente para lidar com a operação, mas não é o disco primário. Eu gostaria de definir o PGDATA neste disco de big data.

Eu segui estes passos:

1. Instale postgres-12 e postgres12-contrib

2. Monte o disco de dados na nova máquina

3. Crie um diretório pgdata vazio no disco de dados. Todos com o usuário postgres.

4. Usar/usr/bin/pg_basebackup -h IP_OF_OLD_MACHINE -D /mnt/disks/data-disk/pgdata -P -U USERNAME --wal-method=stream

Acho que pg_basebackupterminou com sucesso, os dados levaram várias horas, mas terminaram sem solicitar nenhum erro.

total 132K
-rw-------. 1 postgres postgres  230 Mar 18 12:36 backup_label
drwx------. 7 postgres postgres 4.0K Mar 18 12:43 base
-rw-------. 1 postgres postgres   30 Mar 19 00:13 current_logfiles
drwx------. 2 postgres postgres 4.0K Mar 19 00:13 global
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 log
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_commit_ts
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_dynshmem
-rw-------. 1 postgres postgres 4.8K Mar 18 12:36 pg_hba.conf
-rw-------. 1 postgres postgres 4.7K Mar 18 12:36 pg_hba.conf~
-rw-------. 1 postgres postgres 1.6K Mar 19 00:13 pg_ident.conf
drwx------. 4 postgres postgres 4.0K Mar 18 12:36 pg_logical
drwx------. 4 postgres postgres 4.0K Mar 18 12:36 pg_multixact
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_notify
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_replslot
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_serial
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_snapshots
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_stat
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_stat_tmp
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_subtrans
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_tblspc
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_twophase
-rw-------. 1 postgres postgres    3 Mar 19 08:27 PG_VERSION
drwx------. 3 postgres postgres 4.0K Mar 18 12:36 pg_wal
drwx------. 2 postgres postgres 4.0K Mar 18 12:36 pg_xact
-rw-------. 1 postgres postgres   88 Mar 18 12:36 postgresql.auto.conf
-rw-------. 1 postgres postgres  24K Mar 19 08:39 postgresql.conf

Ok, agora estou tentando inicializar um servidor postgres com esses dados, o que acabei de trazer.

Estas são minhas tentativas, olhando a documentação:

/usr/pgsql-12/bin/postgresql-12-setup initdb -D /mnt/disks/data-disk/pgdata
systemctl: invalid option -- 'D'
failed to find PGDATA setting in -D.service


/usr/pgsql-12/bin/postgresql-12-setup initdb --pgdata=/mnt/disks/data-disk/pgdata
systemctl: unrecognized option '--pgdata=/mnt/disks/data-disk/pgdata.service'
failed to find PGDATA setting in --pgdata=/mnt/disks/data-disk/pgdata.service

Existe algum erro no processo?

Atualização: como @pifor me disse, adicionei o PATH ao postgres bash_profile e comecei a reconhecer PG_DATA. O problema agora é que o initdb visa encontrar uma pasta vazia e não está vazia devido ao pg_basebackup

Acabei de excluir uma grande parte dos dados armazenados em um jsonb dentro da minha tabela de usuários (não é mais necessário) e apenas mantenho um id dentro dessa coluna. Eu esperava liberar muito espaço em disco com essa ação.

UPDATE users SET my_jsonb = '{id: xxxx }';

O problema é que quando tento ver o espaço livre no disco continua o mesmo

/dev/md125 935G 501G 387G 57%

Também a mesa

 Table                                            Size        External
 users                                          | 497 GB     | 478 GB

Minha última opção é tentar um VACUUM FULL users, mas como essa tabela é usada em ambiente de produção, não seria conveniente bloqueá-la por uma quantidade de tempo desconhecida.

Estou executando um banco de dados postgres 10.5 em uma máquina CENTOS RHEL7.

metricsé uma tabela não registrada que tem cerca de 3 milhões de linhas, acabei de executar o ALTER TABLE metrics SET LOGGEDpara manter esses dados registrados. Mas parece estar bloqueado por algum processo de autovacuum em execução.

  pid  | usename  | blocked_by |              blocked_query
-------+----------+------------+------------------------------------------
 13462 | postgres | {32090}    | ALTER TABLE sensors.metrics SET LOGGED;

    duration     |                                 query                                  |  pid
-----------------+------------------------------------------------------------------------+-------
 02:03:08.131365 | autovacuum: VACUUM pg_toast.pg_toast_315873283 (to prevent wraparound) | 32090

Minhas configurações de autovacuum para uma RAM de 32 núcleos e 98gigs:

 autovacuum_max_workers
------------------------
 12
 autovacuum_vacuum_cost_limit
------------------------------
 -1    
 autovacuum_vacuum_cost_delay
------------------------------
 20ms  
 vacuum_cost_page_hit
----------------------
 1
 vacuum_cost_page_miss
-----------------------
 10
 vacuum_cost_page_dirty
------------------------
 20

Posso fazer algo para evitar o bloqueio ou corrigir esse bloqueio sem encerrar o processo de alteração da tabela que está logando os dados?

Estou tentando despejar uma tabela com 50M de registros em um arquivo, e meu objetivo é reduzir o tempo em que esta ação é executada. Eu costumo usar o COPY metrics TO 'metrics.csv' DELIMITER ',' CSV;Isso pode levar uma hora nos melhores casos. Também estou interessado em exportar os dados em algum formato simples (evite usar pd_dumpdiretórios).

Uma das ideias é de alguma forma acessar essa tabela por uma condição ou cursor que divida a tabela inteira em pedaços de tamanhos iguais, para que você possa realizar por exemplo 2 consultas de cópia ao mesmo tempo reduzindo o tempo pela metade.

Exemplo:

COPY (SELECT * FROM metrics WHERE id < 25000000) TO 'metrics_1.csv' DELIMITER ',' CSV;
COPY (SELECT * FROM metrics WHERE id >= 25000000) TO 'metrics_2.csv' DELIMITER ',' CSV;

Os índices parciais criados nessas condições podem ajudar?

Alguma idéia de uma boa maneira de implementar esses despejos de cópia parcial de uma tabela? Existe alguma outra solução para despejar esta tabela mais rapidamente?

Postgresql 11 / 100 GB de RAM / 20 núcleos.

Após alguma paralelização com COPYlimites de E/S não parece ser o gargalo.

Migrei um banco de dados promovendo a réplica de leitura em um servidor com mais capacidade, esse processo funcionou sem problemas. Mas estou tentando criar uma réplica de leitura agora a partir deste servidor promovido para ter uma configuração semelhante à que eu tinha antes. O problema é que quando tento iniciar o servidor Postgres isso acontece.

● postgresql-10.service - PostgreSQL 10 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-10.service; disabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since mar 2018-10-16 07:07:42 UTC; 10s ago
   Docs: https://www.postgresql.org/docs/10/static/
   Process: 22746 ExecStart=/usr/pgsql-10/bin/postmaster -D ${PGDATA} 
   (code=exited, status=2)
   Process: 22740 ExecStartPre=/usr/pgsql-10/bin/postgresql-10-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 22746 (code=exited, status=2)

Vou descrever meu processo o mais preciso possível, estou rodando no CentOS RHEL 7.

Primeiro eu instalo o Postgres 10:

rpm -Uvh https://yum.postgresql.org/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm
yum install postgresql10-server postgresql10
yum install postgresql10-contrib
su - postgres
/usr/pgsql-10/bin/initdb
systemctl start postgresql-10.service

Isso funciona, o servidor está rodando então a instalação funcionou, vamos começar com a replicação. Eu paro o serviço e continuo com isso:

 rm -r /var/lib/pgsql/10/data
 su - postgres
 /usr/pgsql-10/bin/pg_basebackup -h <database host ip> -D /var/lib/pgsql/10/data/ -P -U replicate

replicateé o usuário com privilégios de replicação no banco de dados principal.

O pg_basebackupfinaliza corretamente, então renomeio o recovery.donepara recovery.conf.

drwx------. 6 postgres postgres   54 oct 16 06:45 base
-rw-------. 1 postgres postgres   30 oct 16 06:55 current_logfiles
drwx------. 2 postgres postgres 4,0K oct 16 06:45 global
drwx------. 2 postgres postgres  188 oct 16 06:53 log
drwx------. 2 postgres postgres    6 oct 16 06:45 pg_commit_ts
drwx------. 2 postgres postgres    6 oct 16 06:45 pg_dynshmem
-rw-------. 1 postgres postgres 4,5K oct 16 06:55 pg_hba.conf
-rw-------. 1 postgres postgres 1,6K oct 16 06:55 pg_ident.conf
drwx------. 4 postgres postgres   68 oct 16 06:53 pg_logical
drwx------. 4 postgres postgres   36 oct 16 06:45 pg_multixact
drwx------. 2 postgres postgres   18 oct 16 06:45 pg_notify
drwx------. 2 postgres postgres    6 oct 16 06:53 pg_replslot
drwx------. 2 postgres postgres    6 oct 16 06:45 pg_serial
drwx------. 2 postgres postgres    6 oct 16 06:45 pg_snapshots
drwx------. 2 postgres postgres    6 oct 16 06:53 pg_stat
drwx------. 2 postgres postgres    6 oct 16 06:53 pg_stat_tmp
drwx------. 2 postgres postgres    6 oct 16 06:45 pg_subtrans
drwx------. 2 postgres postgres    6 oct 16 06:53 pg_tblspc
drwx------. 2 postgres postgres    6 oct 16 06:45 pg_twophase
-rw-------. 1 postgres postgres    3 oct 16 06:55 PG_VERSION
drwx------. 3 postgres postgres 4,0K oct 16 06:45 pg_wal
drwx------. 2 postgres postgres 4,0K oct 16 06:53 pg_xact
-rw-------. 1 postgres postgres   88 oct 16 06:55 postgresql.auto.conf
-rw-------. 1 postgres postgres  23K oct 16 06:55 postgresql.conf
-rw-------. 1 postgres postgres   58 oct 16 06:55 postmaster.opts
-rw-r--r--. 1 postgres postgres  154 oct 16 06:53 recovery.conf

Mas ao correr systemctl start postgresql-10.service, eu recebo

O trabalho para postgresql-10.service falhou porque o processo de controle foi encerrado com código de erro. Consulte "systemctl status postgresql-10.service" e "journalctl -xe" para obter detalhes.

Não consegui encontrar mais pistas do que esta única mensagem:

postgresql-10.service: main process exited, code=exited, status=1/FAILURE

o que estou perdendo? Já tentei rodar com o padrão postgres.confe pg_hba.confevitar erros de configuração mas também não funcionou.

journalctlSaída completa :

oct 17 09:15:11 database-replica systemd[1]: Starting PostgreSQL 10 database server...
-- Subject: Unit postgresql-10.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql-10.service has begun starting up.
oct 17 09:15:11 database-replica postmaster[19514]: 2018-10-17 09:15:11.834 UTC [19514] LOG:  listening on IPv6 address "::1", port 5432
oct 17 09:15:11 database-replica postmaster[19514]: 2018-10-17 09:15:11.834 UTC [19514] LOG:  listening on IPv4 address "127.0.0.1", port 5432
oct 17 09:15:11 database-replica postmaster[19514]: 2018-10-17 09:15:11.836 UTC [19514] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
oct 17 09:15:11 database-replica postmaster[19514]: 2018-10-17 09:15:11.840 UTC [19514] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
oct 17 09:15:11 database-replica postmaster[19514]: 2018-10-17 09:15:11.865 UTC [19514] LOG:  redirecting log output to logging collector process
oct 17 09:15:11 database-replica postmaster[19514]: 2018-10-17 09:15:11.865 UTC [19514] HINT:  Future log output will appear in directory "log".
oct 17 09:15:11 database-replica systemd[1]: postgresql-10.service: main process exited, code=exited, status=1/FAILURE
oct 17 09:15:11 database-replica systemd[1]: Failed to start PostgreSQL 10 database server.
-- Subject: Unit postgresql-10.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit postgresql-10.service has failed.
--
-- The result is failed.
oct 17 09:15:11 database-replica systemd[1]: Unit postgresql-10.service entered failed state.
oct 17 09:15:11 database-replica systemd[1]: postgresql-10.service failed.
oct 17 09:15:11 database-replica polkitd[676]: Unregistered Authentication Agent for unix-process:19502:16930756 (system bus name :1.160, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, locale es_ES.UTF-8) (disconnected from bus)

Estou usando o postgres 10.1 e a configuração do Datestyle é a seguinte

 DateStyle
 -----------
 ISO, MDY

estou com esse erro

ERRO: valor do campo de data/hora fora do intervalo: "1535673858" Dica: Talvez você precise de uma configuração de "estilo de data" diferente.

Ao executar a seguinte instrução de inserção:

INSERT INTO ex (taken_ats) VALUES('{ 1535673858 , 1535678856}')

Esquema:

CREATE TABLE ex (
    taken_ats TIMESTAMP WITHOUT TIME ZONE[]
)

Tenho um SELECTque fica muito lento ao usar o <operador procuro correções ou soluções alternativas para realizar esta operação:

EXPLAIN (ANALYZE) 
SELECT * 
FROM "users" 
WHERE (engagement_level(social) < 1) 
    AND (social_peemv(social) < 33.333333333333336) 
    AND (array['United Kingdom'] <@ mixed_frequent_locations(location)) 
    AND (is_visible(social, flags) = TRUE) 
ORDER BY "users"."created_at" ASC 
LIMIT 12 OFFSET 0;
                                                                                                               QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    ---------------------------
 Limit  (cost=0.43..18572.10 rows=12 width=860) (actual time=5658.037..175582.743 rows=12 loops=1)
   ->  Index Scan using created_at_idx on users  (cost=0.43..6244724.16 rows=4035 width=860) (actual time=5658.035..175582.735 rows=12 loops=1)
         Filter: (is_visible(social, flags) AND (engagement_level(social) < 1) AND (social_peemv(social) < '33.3333333333333'::double precision) AND ('{"United Kingdom"}'::text[] <@ mixed_frequent_locations(location)))
         Rows Removed by Filter: 2816798
 Planning time: 1.573 ms
 Execution time: 175583.373 ms
(6 rows)

EXPLAIN (ANALYZE) 
SELECT * 
FROM "users" 
WHERE (engagement_level(social) < 1) 
  AND (social_peemv(social) = 33.3333) 
  AND (array['United Kingdom'] <@ mixed_frequent_locations(location)) 
  AND (is_visible(social, flags) = TRUE)
ORDER BY "users"."created_at" ASC
LIMIT 12 OFFSET 0;
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=380.04..380.31 rows=1 width=863) (actual time=0.051..0.051 rows=0 loops=1)
   ->  Result  (cost=380.04..380.31 rows=1 width=863) (actual time=0.050..0.050 rows=0 loops=1)
         ->  Sort  (cost=380.04..380.05 rows=1 width=896) (actual time=0.049..0.049 rows=0 loops=1)
               Sort Key: created_at
               Sort Method: quicksort  Memory: 25kB
               ->  Index Scan using idx_in_social_peemv on users  (cost=0.43..380.03 rows=1 width=896) (actual time=0.044..0.044 rows=0 loops=1)
                     Index Cond: (social_peemv(social) = '33.3333'::double precision)
                     Filter: (is_visible(social, flags) AND (engagement_level(social) < 1) AND ('{"United Kingdom"}'::text[] <@ mixed_frequent_locations(location)))
 Planning time: 0.459 ms
 Execution time: 0.095 ms

No primeiro caso não Index Condé aplicado e o tempo de execução cresce para175583.373 ms

O índice:

 CREATE INDEX idx_in_social_peemv ON users USING BTREE ( social_peemv(social) ) ;
 CREATE INDEX mixed_frequent_locations_idx on users USING GIN ( mixed_frequent_locations(location) ) ;
 CREATE INDEX created_at_idx ON users USING btree (created_at)
 CREATE INDEX idx_in_social_follower_count_and_created_at ON users USING btree (social_follower_count(social) DESC, created_at)
 CREATE INDEX idx_in_egagagement_level_and_created_at ON users USING btree (engagement_level(social), creat
ed_at)

A mesa:

CREATE TABLE users (
    id SERIAL PRIMARY KEY NOT NULL,
    name TEXT,
    bio TEXT,
    social jsonb,
    flags array,
    location jsonb,
    search_field ts_vector,
    created_at TIMESTAMP WITHOUT TIMEZONE,
    udpated_at TIMESTAMP WITHOUT TIMEZONE
);

Versão do Postgres: 10

Toda a condição corresponde a 20 registros de um total de 3669284

Cada condição corresponde:

(engagement_level(social) < 1)= 801176

(social_peemv(social) < 33.333333333333336)= 1621516

(array['United Kingdom'] <@ mixed_frequent_locations(location))= 91625

(is_visible(social, flags) = TRUE)= 3333733

Como sugerido por @jjanes, tentei remover o LIMITe OFFSETe o plano de consulta mudou para um BitMap Heap Scan:

EXPLAIN (ANALYZE)
SELECT *
FROM "users"
WHERE (engagement_level(social) < 1)
     AND (social_peemv(social) < 33.333333333333336)
     AND (array['United Kingdom'] <@ mixed_frequent_locations(location))
     AND (is_visible(social, flags) = TRUE)
ORDER BY "users"."created_at" ASC;
                                                                                  QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=77641.99..77652.36 rows=4148 width=1393) (actual time=1195.544..1195.546 rows=20 loops=1)
   Sort Key: created_at
   Sort Method: quicksort  Memory: 59kB
   ->  Bitmap Heap Scan on users  (cost=18046.48..77392.73 rows=4148 width=1393) (actual time=227.471..1195.481 rows=20 loops=1)
         Recheck Cond: (('{"United Kingdom"}'::text[] <@ mixed_frequent_locations(location)) AND (engagement_level(social) < 1))
         Filter: (is_visible(social, flags) AND (social_peemv(social) < '33.3333333333333'::double precision))
         Rows Removed by Filter: 19444
         Heap Blocks: exact=19238
         ->  BitmapAnd  (cost=18046.48..18046.48 rows=28415 width=0) (actual time=218.484..218.484 rows=0 loops=1)
               ->  Bitmap Index Scan on mixed_frequent_locations_idx  (cost=0.00..1356.36 rows=128634 width=0) (actual time=44.794..44.794 rows=108076 loops=1)
                     Index Cond: ('{"United Kingdom"}'::text[] <@ mixed_frequent_locations(location))
               ->  Bitmap Index Scan on idx_in_egagagement_level_and_created_at  (cost=0.00..16687.80 rows=1156662 width=0) (actual time=163.368..163.368 rows=801189 loops=1)
                     Index Cond: (engagement_level(social) < 1)
 Planning time: 3.326 ms
 Execution time: 1197.242 ms

Todas as condições aplicadas, exceto as is_visible, são dadas pelo usuário

Contexto:

PostgreSQL 10, com 3667438 registros na tabela de usuários, a tabela de usuários possui um JSONB chamado social, geralmente utilizamos uma estratégia de indexação de saídas de funções computadas, assim podemos agregar informações em um único índice. A saída da engagement(social)função é um tipo numérico de precisão dupla.

Problema:

A cláusula problemática é ORDER BY engagement(social) DESC NULLS LAST, também há um índice btree idx_in_social_engagement with DESC NULLS LASTanexado a esses dados.

Consulta rápida:

EXPLAIN ANALYZE
SELECT  "users".* FROM "users"
WHERE (follower_count(social) < 500000)
AND (engagement(social) > 0.03)
AND (engagement(social) < 0.25)
AND (peemv(social) < 533)
ORDER BY "users"."created_at" ASC
LIMIT 12 OFFSET 0;

Limit  (cost=0.43..52.25 rows=12 width=1333) (actual time=0.113..1.625 
rows=12 loops=1)
   ->  Index Scan using created_at_idx on users  (cost=0.43..7027711.55 rows=1627352 width=1333) (actual time=0.112..1.623 rows=12 loops=1)
         Filter: ((follower_count(social) < 500000) AND (engagement(social) > '0.03'::double precision) AND (engagement(social) <  '0.25'::double precision) AND (peemv(social) > '0'::double precision) AND (peemv(social) < '533'::double precision))
         Rows Removed by Filter: 8
 Planning time: 0.324 ms
 Execution time: 1.639 ms

Consulta lenta:

EXPLAIN ANALYZE 
SELECT  "users".* FROM "users" 
WHERE (follower_count(social) < 500000) 
AND (engagement(social) > 0.03) 
AND (engagement(social) < 0.25) 
AND (peemv(social) > 0.0) 
AND (peemv(social) < 533) 
ORDER BY engagement(social) DESC NULLS LAST, "users"."created_at" ASC 
LIMIT 12 OFFSET 0;

Limit  (cost=2884438.00..2884438.03 rows=12 width=1341) (actual time=68011.728..68011.730 rows=12 loops=1)
->  Sort  (cost=2884438.00..2888506.38 rows=1627352 width=1341) (actual time=68011.727..68011.728 rows=12 loops=1)
        Sort Key: (engagement(social)) DESC NULLS LAST, created_at
        Sort Method: top-N heapsort  Memory: 45kB
        ->  Index Scan using idx_in_social_engagement on users  (cost=0.43..2847131.26 rows=1627352 width=1341) (actual time=0.082..67019.102 rows=1360633 loops=1)
            Index Cond: ((engagement(social) > '0.03'::double precision) AND (engagement(social) < '0.25'::double precision))
            Filter: ((follower_count(social) < 500000) AND (peemv(social) > '0'::double precision) AND (peemv(social) < '533'::double precision))
            Rows Removed by Filter: 85580
Planning time: 0.312 ms
Execution time: 68011.752 ms

O select vai com * porque preciso de todos os dados armazenados em cada linha.

Atualizar:

CREATE INDEX idx_in_social_engagement on influencers USING BTREE ( engagement(social) DESC NULLS LAST)

Definição exata do índice