Niels Kristian's questions

Estou me perguntando: se eu começar a fazer um pg_dump de um banco de dados muito grande (levaria horas), que ainda está executando ativamente recebendo gravações, quais são os últimos dados que vão para o backup? É isso:

1. Os dados como estavam, no momento em que o comando pg_dump foi iniciado.
2. As últimas alterações encontradas em algum ponto em que estava despejando esse registro individual.
3. Algo mais.

Pergunta de bônus: se estou tentando despejar um banco de dados como parte da tentativa de resgatá-lo de dados corrompidos, fará alguma diferença se eu usar o directoryformato ou o customformato?

Depois de ser mordido pelo bug mencionado nas notas de lançamento do 9.3.4, percebi que uma parte muito pequena dos meus dados está corrompida, após fazer um failover. O bug se mostrou como linhas duplicadas, com a mesma chave primária. Agora posso facilmente excluir alguns dados do meu banco de dados, se isso for necessário para corrigir meu problema, então acabei de excluir todas as duplicatas. Depois de fazer isso, decidi escrever um script que elimina/recria todas as restrições (Pkeys e Fkeys), a fim de validar que nenhum dos meus dados esteja corrompido. Isso funcionou bem, até que esbarrei no seguinte erro, ao criar uma das chaves estrangeiras:

ERROR:  failed to find parent tuple for heap-only tuple at (1192248,5) in table "fruits"
CONTEXT:  SQL statement "ALTER TABLE "pm"."fruits" ADD CONSTRAINT "fruits_pkey" PRIMARY KEY (id)"

O que é, e como resolvo?

Parece que fui mordido pelo primeiro bug descrito nas notas de lançamento do postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html

Agora tenho, por exemplo, chaves primárias duplicadas. Qual é a melhor abordagem para fazer um loop de nova verificação de todas as minhas restrições (pkeys, fkeys) -> corrigir problemas -> verificar novamente, para garantir que meus dados estejam corretos?

ATUALIZAR

Decidi seguir uma abordagem para corrigir o problema, excluindo todas as restrições e, em seguida, recriando-as usando o seguinte método http://blog.hagander.net/archives/131-Automatically-dropping-and-creating-constraints.html . No entanto, estou preso em uma mensagem de erro, tentando recriar uma pkey:

ERROR:  failed to find parent tuple for heap-only tuple at (1192248,5) in table "fruits"
CONTEXT:  SQL statement "ALTER TABLE "pm"."fruits" ADD CONSTRAINT "fruits_pkey" PRIMARY KEY (id)"

O que isso significa e como faço para corrigir isso (posso viver com a exclusão, se for uma opção)?

Outra pergunta: meus dados seriam realmente bons, se eu apenas me livrasse de todas as linhas duplicadas excluindo-as e, em seguida, fizesse um pg_dump e restaurasse o banco de dados desse despejo. Isso reconstruiria a estrutura de dados - certo?

Para minha grande (e preocupante) surpresa, acabei de perceber que minha tabela com milhões de linhas, tem um punhado de linhas, onde a chave primária (ID) é uma duplicata! Não entendo como isso pode acontecer e como posso evitar isso no futuro?

A coluna que contém a chave primária está, e sempre esteve, sujeita a umaCONSTRAINT fruits_pkey PRIMARY KEY(id);

Estou executando o postgreql 9.3.4 no ubuntu.

ATUALIZAR

@Mat: o tipo de dados éinteger

@ypercube: Sim, select count(*) from (select count(*) from fruits group by id having count(*) > 1) as t1retorna 41.

@Craig: Sim, já fiz um failover antes e meu escravo é, na verdade, 9.3.3

Eu tenho uma configuração de replicação de streaming usando postgresql 9.3. Tenho dois servidores, o mestre (servidor_1) e o escravo (servidor_2).

Outro dia fiz um failover no meu slave (server_2). Agora, o servidor_2 está sendo executado como mestre há alguns dias e agora configurei meu mestre principal (servidor_1) novamente e agora ele é executado como mestre. Eu gostaria de evitar a reinstalação do server_2 antes de transformá-lo em um escravo do server_1 novamente. Como eu posso fazer isso? Quais diretórios devo excluir, etc, para ter um servidor "limpo" que eu possa usar como escravo novamente?

Parece (mas não posso dizer com certeza) que depois de adicionar uma coluna indexada postgis (USING gist) ao meu banco de dados na semana passada, tenho visto muitos desses erros:

ERROR: tuple offset out of range: 0

Os erros ocorrem em todos os tipos de consultas, inclusive algumas que não usam essas colunas e, portanto, não deveriam usar esse índice, certo?

De qualquer forma, minha pergunta é: o que significa essa mensagem de erro e pode ter algo a ver com o postgis ou estou fora do caminho?

Estou no postgresql 9.3 e postgis 2.1

Eu tenho uma configuração de espera ativa de replicação de streaming, que parece estar funcionando bem. Posso fazer login, executar selects e o resultado parece estar atualizado com o mestre. No entanto, no log do standby, vejo muitos destes:

cp: cannot stat `/mnt/wal_drive/wals/0000000100004BA800000070': No such file or directory
2014-02-28 16:00:37 CET LOG:  record with zero length at 4BA8/70DD79D0
2014-02-28 16:00:37 CET LOG:  started streaming WAL from primary at 4BA8/70000000 on timeline 1
2014-02-28 16:02:59 CET FATAL:  could not receive data from WAL stream: SSL error: sslv3 alert unexpected message

O que cada um deles significa e devo me preocupar com algum deles?

PS. No mestre, vejo alguns deles, que espero estar relacionados à coisa do SSL:

2014-02-28 16:10:33 CET LOG:  SSL renegotiation failure
2014-02-28 16:10:33 CET LOG:  SSL error: unexpected record

Estou executando o postgresql 9.3 no Ubuntu 12.04

EDITAR

Em relação aos logs do WAL tenho a seguinte configuração:

Tanto no meu mestre quanto no meu escravo, montei um armazenamento de rede fstabusando um cifscomando. O diretório é alterado para pertencer ao usuário postgres.

No meu master tenho as seguintes configurações no postgresql.conf:

archive_mode = on
archive_command = 'rsync -a %p /mnt/wal_drive/wals/%f </dev/null'

No slave tenho hot_standby = one o seguinte recovery.conf:

standby_mode = 'on'
primary_conninfo = 'host=localhost port=5433 user=replicator password=some_pass'
trigger_file = '/tmp/pgsql.trigger'
restore_command = 'cp -f /mnt/wal_drive/wals/%f %p </dev/null'
archive_cleanup_command = '/usr/lib/postgresql/9.3/bin/pg_archivecleanup /mnt/wal_drive/wals/ %r'

Eu tenho uma porta de encaminhamento ssh-tunnel 5433 do escravo para a porta 5432 no mestre, portanto, conectar-se em localhost:5433 é na verdade o banco de dados mestre.

Eu tenho uma replicação de streaming em execução entre dois servidores.

Acabei de atualizar para postgresql 9.3 de 9.2. Anteriormente, em minha configuração 9.2, tentei que meu servidor escravo ficasse offline devido a uma queda de rede. Isso resultou em arquivos wal-log não enviados se acumulando e ocupando espaço MUITO rápido em meu servidor mestre.

Posso evitar isso de maneira fácil em 9.3?

Eu vi a nova configuração wal_sender_timeout , mas não tenho certeza do que exatamente ela faz.

Qual é a melhor maneira de evitar que arquivos wal se acumulem no remetente se o destinatário ficar offline no 9.3?

Meu comando pg_upgrade falhou. Durante a atualização, ele me disse que, se as coisas estivessem falhando após um ponto de cortina (o que aconteceu), eu deveria:

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Mas o que isso significa?

EDITAR

$> /usr/lib/postgresql/9.3/bin/pg_upgrade -b /usr/lib/postgresql/9.2/bin -B /usr/lib/postgresql/9.3/bin -d /var/lib/postgresql/9.2/main/ -D /var/lib/postgresql/9.3/main/ -j 6 -O "-c config_file=/etc/postgresql/9.3/main/postgresql.conf" -o "-c config_file=/etc/postgresql/9.2/main/postgresql.conf"
Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting files from new pg_clog                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID for new cluster                 ok
Setting oldest multixact ID on new cluster                  ok
Resetting WAL archives                                      ok
Setting frozenxid counters in new cluster                   ok
Restoring global objects in the new cluster                 ok
Adding support functions to new cluster                     ok
Restoring database schemas in the new cluster
  template1                                                 
*failure*

Consult the last few lines of "pg_upgrade_dump_27132.log" for
the probable cause of the failure.
Failure, exiting

child worker exited abnormally: Success
Failure, exiting

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

Registro:

2014-02-26 01:05:58 CET ERROR:  function pg_catalog._final_mode(anyarray) does not exist
2014-02-26 01:05:58 CET STATEMENT:  CREATE AGGREGATE "mode"("anyelement") (
        SFUNC = "array_append",
        STYPE = "anyarray",
        INITCOND = '{}',
        FINALFUNC = "pg_catalog"."_final_mode"
    );

Alguém sabe se é problemático ter uma configuração de escravo postgresql 9.3 para fazer a replicação de streaming de um mestre postgresql 9.2?

Estou usando o seguinte programa https://sourceforge.net/projects/jailer/ para extrair um subconjunto de dados do meu banco de dados com associações corretas seguindo minhas restrições de chave estrangeira. O programa gera SQL bruto inserte updateinstruções que podem ser carregadas em meu banco de dados de desenvolvimento - tudo muito bom e prático. No entanto - recentemente comecei a usar o Hstore no postgres e agora estou encontrando uma sintaxe estranha nos arquivos SQL. Se eu tiver uma tabela com uma coluna do tipo store, updateas insertinstruções serão exibidas da seguinte forma:

Update public.some_table set brand='Ford', model_name='Focus', additional_attrs={engine_size=1.6, weight=1100}, created_at=timestamp '2013-11-05 17:21:38.667085', updated_at=timestamp '2013-11-05 17:21:38.667085' Where id=246747;

Witch me dá o seguinte erro, quando tento executá-lo:

ERROR:  syntax error at or near "{"
LINE 1: ...rand='Ford', model_name='Focus', additional_attrs={engine_si...
                                                             ^


********** Error **********

ERROR: syntax error at or near "{"
SQL state: 42601
Character: 81

Tanto quanto eu sei do postgresql hstore {key=value}não é uma sintaxe válida, então minhas perguntas são:

• É de fato uma sintaxe válida?
• Por que a sintaxe de saída do programa seria assim, é provável que seja um bug no programa ou algo mais relacionado ao postgres, por exemplo, o driver jdbc postgres?
• Alguma sugestão de como posso superar esse problema?

A situação Eu tenho um banco de dados postgresql 9.2 que é bastante atualizado o tempo todo. O sistema está, portanto, vinculado à E/S, e atualmente estou pensando em fazer outra atualização, só preciso de algumas orientações sobre onde começar a melhorar.

Aqui está uma imagem de como a situação ficou nos últimos 3 meses:

Como você pode ver, as operações de atualização são responsáveis ​​pela maior parte da utilização do disco. Aqui está outra imagem de como a situação fica em uma janela de 3 horas mais detalhada:

Como você pode ver, a taxa de gravação de pico é de cerca de 20 MB/s

Software O servidor está executando o Ubuntu 12.04 e o postgresql 9.2. O tipo de atualizações são pequenas atualizações normalmente em linhas individuais identificadas por ID. Por exemplo UPDATE cars SET price=some_price, updated_at = some_time_stamp WHERE id = some_id. Eu removi e otimizei os índices tanto quanto eu acho que é possível, e a configuração dos servidores (tanto o kernel linux quanto o postgres conf) também está bastante otimizada.

Hardware O hardware é um servidor dedicado com 32 GB de RAM ECC, 4 discos SAS de 600 GB e 15.000 rpm em um array RAID 10, controlado por um controlador RAID LSI com BBU e um processador Intel Xeon E3-1245 Quadcore.

Perguntas

• O desempenho visto pelos gráficos é razoável para um sistema deste calibre (leitura/gravação)?
• Portanto, devo me concentrar em fazer uma atualização de hardware ou investigar mais profundamente o software (ajustes do kernel, confs, consultas etc.)?
• Se estiver fazendo uma atualização de hardware, o número de discos é fundamental para o desempenho?

------------------------------ATUALIZAR------------------- ----------------

Agora atualizei meu servidor de banco de dados com quatro SSDs Intel 520 em vez dos antigos discos SAS de 15k. Estou usando o mesmo controlador de raid. As coisas melhoraram bastante, como você pode ver a seguir, o desempenho de E/S de pico melhorou cerca de 6 a 10 vezes - e isso é ótimo!. No entanto, eu esperava algo mais como uma melhoria de 20 a 50 vezes de acordo com as respostas e os recursos de E/S dos novos SSDs. Então aqui vai outra pergunta.

Nova pergunta Há algo na minha configuração atual que está limitando o desempenho de E/S do meu sistema (onde está o gargalo)?

Minhas configurações:

/etc/postgresql/9.2/main/postgresql.conf

data_directory = '/var/lib/postgresql/9.2/main'
hba_file = '/etc/postgresql/9.2/main/pg_hba.conf'
ident_file = '/etc/postgresql/9.2/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/9.2-main.pid'
listen_addresses = '192.168.0.4, localhost'
port = 5432
unix_socket_directory = '/var/run/postgresql'
wal_level = hot_standby
synchronous_commit = on
checkpoint_timeout = 10min
archive_mode = on
archive_command = 'rsync -a %p [email protected]:/var/lib/postgresql/9.2/wals/%f </dev/null'
max_wal_senders = 1
wal_keep_segments = 32
hot_standby = on
log_line_prefix = '%t '
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
default_statistics_target = 100
maintenance_work_mem = 1920MB
checkpoint_completion_target = 0.7
effective_cache_size = 22GB
work_mem = 160MB
wal_buffers = 16MB
checkpoint_segments = 32
shared_buffers = 7680MB
max_connections = 400 

/etc/sysctl.conf

# sysctl config
#net.ipv4.ip_forward=1
net.ipv4.conf.all.rp_filter=1
net.ipv4.icmp_echo_ignore_broadcasts=1
# ipv6 settings (no autoconfiguration)
net.ipv6.conf.default.autoconf=0
net.ipv6.conf.default.accept_dad=0
net.ipv6.conf.default.accept_ra=0
net.ipv6.conf.default.accept_ra_defrtr=0
net.ipv6.conf.default.accept_ra_rtr_pref=0
net.ipv6.conf.default.accept_ra_pinfo=0
net.ipv6.conf.default.accept_source_route=0
net.ipv6.conf.default.accept_redirects=0
net.ipv6.conf.default.forwarding=0
net.ipv6.conf.all.autoconf=0
net.ipv6.conf.all.accept_dad=0
net.ipv6.conf.all.accept_ra=0
net.ipv6.conf.all.accept_ra_defrtr=0
net.ipv6.conf.all.accept_ra_rtr_pref=0
net.ipv6.conf.all.accept_ra_pinfo=0
net.ipv6.conf.all.accept_source_route=0
net.ipv6.conf.all.accept_redirects=0
net.ipv6.conf.all.forwarding=0
# Updated according to postgresql tuning
vm.dirty_ratio = 10
vm.dirty_background_ratio = 1
vm.swappiness = 0
vm.overcommit_memory = 2
kernel.sched_autogroup_enabled = 0
kernel.sched_migration_cost = 50000000

/etc/sysctl.d/30-postgresql-shm.conf

# Shared memory settings for PostgreSQL
# Note that if another program uses shared memory as well, you will have to
# coordinate the size settings between the two.
# Maximum size of shared memory segment in bytes
#kernel.shmmax = 33554432
# Maximum total size of shared memory in pages (normally 4096 bytes)
#kernel.shmall = 2097152
kernel.shmmax = 8589934592
kernel.shmall = 17179869184
# Updated according to postgresql tuning

Saída deMegaCli64 -LDInfo -LAll -aAll

Adapter 0 -- Virtual Drive Information:
Virtual Drive: 0 (Target Id: 0)
Name                :
RAID Level          : Primary-1, Secondary-0, RAID Level Qualifier-0
Size                : 446.125 GB
Sector Size         : 512
Is VD emulated      : No
Mirror Data         : 446.125 GB
State               : Optimal
Strip Size          : 64 KB
Number Of Drives per span:2
Span Depth          : 2
Default Cache Policy: WriteBack, ReadAhead, Direct, Write Cache OK if Bad BBU
Current Cache Policy: WriteBack, ReadAhead, Direct, Write Cache OK if Bad BBU
Default Access Policy: Read/Write
Current Access Policy: Read/Write
Disk Cache Policy   : Disk's Default
Encryption Type     : None
Is VD Cached: No

Acabei de instalar o postgres 9.1.6 em um servidor Ubuntu local. Agora estou tentando restaurar um despejo de banco de dados de um banco de dados no Heroku. O banco de dados local é configurado assim:

sudo -u postgres psql -c "create user app_user with password 'pass';"
sudo -u postgres psql -c "create database app_production owner app_user;"

Agora, quando tento restaurar o dump, uso o seguinte comando:

pg_restore --verbose --schema=public --no-acl --no-owner --jobs=8 --exit-on-error --username=app_user --dbname=app_production /tmp/app_production.dump

Agora, no psql com \lpara ver as propriedades, recebo o seguinte:

                                         List of databases
          Name    |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges  
------------------+-----------+----------+-------------+-------------+-----------------------
 app_production   | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres         | postgres  | 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
 template1        | postgres  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
                  |           |          |             |             | postgres=CTc/postgres

Como você pode ver, a propriedade do banco de dados app_production mudou de app_user para postgres. Eu esperava que o proprietário do banco de dados app_production ainda fosse app_user, então o que estou fazendo de errado?

BTW, o --schema=publicfoi adicionado, porque eu estava recebendo um erro estranho:

"Não foi possível executar a consulta: ERRO: deve ser o proprietário da extensão plpgsql"

Outra coisa é que o dono do dump é o usuário que o banco de dados estava tendo no heroku, que seria algo como'jebf473b73bv73v749b7'