Como fazer backup “offline” de nível de byte do banco de dados postgresql

Question

Estou tentando amar o PostGreSQL. Estou empurrando na minha empresa. Eu quero que nós o adotemos para mais e mais projetos. Mas eu pessoalmente estou perplexo com um problema de backup / restauração. Eu continuo pensando, se isso fosse MS-SQL, isso não seria um problema ...

Não podemos restaurar backups de nosso banco de dados - não com pg_dump e pg_restore. Ele falha por vários motivos. Eu procurei bastante - horas e horas - sem remédio. Grande parte do banco de dados é restaurada, mas as partes principais não.

Como muitos sabem, no MS-SQL, é possível desconectar um banco de dados, fazer uma cópia dos arquivos MDB e LDB e reconectá-lo. Eu posso então pegar esses dois arquivos copiados para qualquer outro computador e reconectá-los, e, talvez, na falta de contas de usuários, em nossa experiência, não tivemos nenhum problema.

Mas o pg_dump e o pg_dumpall (que na maioria das vezes acabam chamando iterativamente o pg_dump) fazem o dump dos comandos SQL para recriar o banco de dados. Não a cópia byte a byte do estado do banco de dados. Dado que restaurar nosso banco de dados executando os comandos pg_dump que são lançados para criá-lo não funciona, existe alguma alternativa mais próxima da solução de cópia no nível de byte do mundo MS-SQL que eu possa usar?

Qual é o objetivo? Além do óbvio (poder restaurar o banco de dados em caso de falha), estou tentando implantar um ambiente de desenvolvimento do Vagrant. Portanto, temos uma cópia principal do banco de dados que nosso desenvolvedor de banco de dados configura em seu ambiente de desenvolvimento virtual. Então eu e outros queremos ser capazes de obter instantâneos periódicos do banco de dados após grandes modificações, e carregar os instantâneos nos Vagrant em nossas máquinas.

Se pudéssemos fazer backup e restaurar o banco de dados facilmente, isso não seria um problema. Funcionaria muito bem. Eu prefiro não ter que copiar uma máquina virtual inteira para compartilhar as alterações do banco de dados.

A única coisa que eu tentei diferente do futzing com pg_dump [all] e pg_restore foi SymmetricDS, mas quebrou o banco de dados ... talvez através de configuração incorreta, ou talvez porque ele está tentando fazer o mesmo tipo de coisas que o pg_dump faz. Não tenho certeza.

Eu suspeito que vou tirar dúvidas sobre por que o pg_restore falha. Então, para esclarecer brevemente, isso tem a ver com tipos de dados personalizados, operadores e funções instaladas pelo software de terceiros, e nós, em esquemas interdependentes (ArcSDE e PostGIS) não sendo criados na ordem correta, pois o pg_dump acha que eles devem ser criados. Eu também acredito (embora não possa provar) que o search_path como definido no início de um backup pg_dump está errado, o que não ajuda o processo de restauração já prejudicado por interdependências de esquema.

    

Answer 1

Você pode fazer um backup físico / em nível de arquivo no PostgreSQL, embora não seja uma prática padrão (tentarei muito fazer o pg_dump funcionar; você perguntou na lista de discussão? )

Como outra opção, você já considerou uma configuração replicada, com pontos de entrada "ao vivo" tempo de backup / recuperação ?

De qualquer forma, para fazer um backup físico com o mínimo de tempo de inatividade, você precisa:

1. pare o PostgreSQL
2. tire um instantâneo do LVM dentro de sua máquina virtual
3. reinicie o PostgreSQL
4. monte o instantâneo do LVM em um diretório adequado dentro de sua máquina virtual
5. faça uma cópia de todo o diretório do cluster de banco de dados (por exemplo: /var/lib/pgsql )
6. desmonte e exclua a captura instantânea do LVM

Como tirar um instantâneo é muito rápido, isso minimizará o tempo de inatividade.

Para restaurar o banco de dados em outro servidor PostgreSQL, faça o seguinte:

1. pare o PostgreSQL
2. excluir / renomear o diretório do cluster do banco de dados (por exemplo: mv /var/lib/pgsql /var/lib/pgsql_original )
3. restaure sua cópia do diretório do cluster do banco de dados (por exemplo: mv mycopy /var/lib/pgsql )
4. se o SELINUX estiver ativado, renomeie o novo diretório (por exemplo: 'restorecon -RF / var / lib / pgsql)
5. reinicie o PostgreSQL

Por favor, note que este tipo de backups pode ser usado somente entre a mesma versão do PostgreSQL e o mesmo arco (ou seja: você não pode restaurar um backup de i386 / 32 bits em uma máquina x86_64 / 64 bits).