postgres postgresql dump pg_dump pg_dumpall

Dump (pg_dump e pg_dumpall)

Um dump é uma foto lógica (objetos, schemas, dados) do estado do seu banco em um momento do passado. Eles contêm instruções de reconstrução daquele estado, incluindo possivelmente definições de objetos, dados, usuários e permissões.

ATENÇÃO! Dumps não são propriamente backups e não devemos depender deles para fins de confiabilidade ou continuidade de negócio porque além da grande quantidade de locks que eles exigem para serem extraídos e do alto tempo de restauração necessário para restaurá-los, eles também não atendem a maioria dos casos em que temos RPO (Recovery Point Objective), causando uma perda de dados maior que o aceitável.

O PostgreSQL tem duas ferramentas para geração de dumps: pg_dump e pg_dumpall. A primeira conecta a um banco de dados específico e gera o dump sobre os objetos e dados apenas daquele banco. Contudo, além de não tocar em outros bancos de dados, ela também não exporta objetos globais, como papéis e definições de tablespaces. A segunda ferramenta tem a finalidade de exportar múltiplos bancos de dados, assim como os objetos globais.

Parâmetros

Além dos parâmetros de conexão comuns (vistos no psql), o pg_dump e pg_dumpall têm uma vasta gama de parâmetros que podem ser usados para controlar o conteúdo e a forma da extração.

Parâmetros de controle da saída:

Parâmetros de controle do conteúdo extraído:

Parâmetros específicos do pg_dumpall:

Restauração

Quando usamos o formato padrão (sem informar o -F ou informando -Fp), a saída é um texto puro que pode ser alimentado diretamente ao psql para execução.

Por exemplo, extraindo um dump de um banco origem e importando em um banco destino: 

[postgres@pg-1 ~]$ pg_dump -d origem -f meubd.pgdump
[postgres@pg-1 ~]$ file meubd.pgdump
meubd.pgdump: UTF-8 Unicode text
[postgres@pg-1 ~]$ psql -d destino -f meubd.pgdump

Também é possível comprimir a saída: 

[postgres@pg-1 ~]$ pg_dump -d origem -f meubd.pgdump -Z 9
[postgres@pg-1 ~]$ file meubd.pgdump
meubd.pgdump: gzip compressed data, max compression, from Unix, original size modulo 2^32 7604
[postgres@pg-1 ~]$ gunzip meubd.pgdump | psql -d destino

Quando usamos outros formatos, a restauração precisa ser feita por um programa específico chamado pg_restore.

Por exemplo, com o formato custom: 

[postgres@pg-1 ~]$ pg_dump -d origem -f meubd.pgdump -Fc
[postgres@pg-1 ~]$ file meubd.pgdump
meubd.pgdump: PostgreSQL custom database dump - v1.14-0
[postgres@pg-1 ~]$ pg_restore -d destino meubd.pgdump

A restauração pelo pg_restore tem diversas vantagens. Uma delas é a de que o conteúdo a ser restaurado pode ser filtrado nesse momento, da mesma forma como na criação do dump, o que dá mais flexibilidade aos usos futuros. Por exemplo, um dump em formato custom pode conter todas as tabelas com dados; mas na restauração podemos importar apenas a estrutura deles (-s) ou apenas um schema (-n) ou usar outros filtros adequados à situação (mesmos filtros do pg_dump). Outra é a possibilidade de importação em paralelo (j). Ambas não são possíveis quando usamos o formato de texto simples.

O pg_restore também pode ser usado para listar o conteúdo dos dumps: 

[postgres@pg-1 ~]$ pg_restore -l meubd.pgdump
;
; Archive created at 2020-09-24 12:03:41 -03
;     dbname: origem
;     TOC Entries: 40
;     Compression: -1
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 13.0
;     Dumped by pg_dump version: 13.0
;
; Selected TOC Entries:
;
[…]
[postgres@pg-1 ~]$

Ele também pode ser usado para converter dumps para texto, para inspecionarmos ou alterarmos comandos antes de importá-los: 

[postgres@pg-1 ~]$ pg_restore -f - meubd.pgdump
--
-- PostgreSQL database dump
--

-- Dumped from database version 13.0
-- Dumped by pg_dump version 13.0

[…]

--
-- PostgreSQL database dump complete
--
[postgres@pg-1 ~]$

ATENÇÃO: Dumps gerados por um pg_dump não são necessariamente compatíveis com versões mais antigas de servidor e de pg_restore. É necessário que as versões dos PostgreSQL de origem, do pg_dump (e pg_dumpall), do pg_restore (ou psql) e do PostgreSQL de destino sigam uma ordem não decrescente:

PostgreSQL (origem) <= pg_dump <= pg_restore <= PostgreSQL (destino)

Ou seja, essa sequência de versões está correta:

10 (origem) <= 10 <= 14 <= 14 (destino)

Mas essa sequência causará problemas:

10 (origem) <= 14 <= 14 !!! 10 (destino)

Prática

  1. Crie um banco de dados novo, chamado pagila.
  2. Baixe os arquivos pagila-schema.sql e pagila-data.sql de Pagila. Eles são dois dumps, o primeiro do schema e o segundo dos dados.
  3. Importe os arquivos no banco de dados.
  4. Valide os objetos no banco de dados e o conteúdo deles com SELECTs e metacomandos.
  5. Exporte um novo dump em formato custom.
  6. Destrua e recrie o banco de dados pagila.
  7. Importe o novo dump, de formato custom, mas apenas o schema.
  8. Valide os objetos no banco de dados e o conteúdo deles com SELECTs e metacomandos.

Leitura adicional