Compartilhar via

Práticas recomendadas para pg_dump e pg_restore para o Banco de Dados do Azure para PostgreSQL

Este artigo analisa as opções e melhores práticas para acelerar pg_dump e pg_restore. Também explica as melhores configurações de servidor para realizar o pg_restore.

Melhores práticas pg_dump

Você pode usar o utilitário pg_dump para extrair um banco de dados de servidor flexível do Banco de Dados do Azure para PostgreSQL em um arquivo de script ou arquivo morto. Algumas das opções de linha de comando que podem ser usadas para reduzir o tempo de despejo geral usando pg_dump estão listadas nas seções a seguir.

Formato de diretório (-Fd)

Esta opção gera um arquivo em formato de diretório que pode ser inserido em pg_restore. Por padrão, a saída é compactada.

Trabalhos paralelos (-j)

Com pg_dump, é possível executar trabalhos de despejo simultaneamente usando a opção de trabalhos paralelos. Essa opção reduz o tempo total de dump, mas aumenta a carga no servidor de banco de dados. Recomenda-se chegar a um valor de trabalho paralelo após um monitoramento detalhado das métricas do servidor de origem, como uso de CPU, memória e IOPS (operações de entrada/saída por segundo).

Quando você define um valor para a opção de trabalhos paralelos, o pg_dump requer que o seguinte ocorra:

  • O número de conexões deve ser igual ao número de trabalhos paralelos +1, portanto, defina o valor max_connections adequadamente.
  • O número de trabalhos paralelos deve ser menor ou igual ao número de vCPUs alocados para o servidor de banco de dados.

Compressão (-Z0)

Esta opção especifica o nível de compactação a ser usado. Zero significa sem compressão. A compactação zero durante o processo pg_dump pode ajudar nos ganhos de desempenho.

Inchaço de tabelas e limpeza

Antes de iniciar o processo pg_dump, considere se a limpeza da tabela é necessária. O inchaço nas tabelas aumenta significativamente os tempos de pg_dump. Execute a seguinte consulta para identificar sobrecargas de tabela:

select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;

A coluna dead_pct na consulta é a porcentagem de tuplas mortas quando comparadas às vivas. Um valor dead_pct alto para uma tabela pode indicar que ela não está sendo limpa corretamente. Para obter mais informações, veja ajuste de limpeza automática no servidor flexível do Banco de Dados do Azure para PostgreSQL.

Para cada tabela identificada, é possível realizar uma análise manual de limpeza executando o seguinte:

vacuum(analyze, verbose) <table_name>

Usar um servidor PITR

É possível executar um pg_dump em um servidor online ou ativo. Faz backups consistentes mesmo se o banco de dados estiver sendo usado. Ele não impede que outros usuários usem o banco de dados. Considere o tamanho do banco de dados e outras necessidades de negócios ou dos clientes antes de iniciar o processo de pg_dump. Bancos de dados pequenos podem ser bons candidatos para a execução de um pg_dump no servidor de produção.

Para os bancos de dados grandes, é possível criar um servidor de PITR (recuperação pontual) com base no servidor de produção e executar o processo de pg_dump nele. A execução do pg_dump em um PITR seria um processo de execução a frio. A compensação dessa abordagem é que você não precisa se preocupar com a utilização extra de CPU, memória e E/S resultante da execução de um processo de pg_dump em um servidor de produção real. É possível executar o pg_dump em um servidor PITR e, após a conclusão do processo, remover esse servidor.

Quando observar um pg_dump lento enquanto a largura de banda da rede estiver alta, considere usar uma SKU com mais vCores. SKUs com mais vCores geralmente fornecem CPU e taxa de transferência de rede adicionais, o que pode reduzir o tempo total de despejo. Monitore as métricas de CPU, rede e IOPS para confirmar se a largura de banda ou a computação é o gargalo antes de dimensionar.

Ajuste de parâmetro

Ajuste os parâmetros de servidor a seguir para ajudar a acelerar a criação do índice durante as operações de restauração. arquivos pg_dump geralmente incluem comandos de criação de índice (por exemplo, CREATE INDEX ou ALTER TABLE... ADICIONAR CONSTRAINTE); melhorar o desempenho de construção de índices pode reduzir o tempo total de migração:

  • maintenance_work_mem = 2097151 (2 GB) – aumente esse valor para alocar mais memória para criação de índice e outras tarefas de manutenção. Para índices grandes, considere elevar essa configuração (por exemplo, centenas de megabytes para vários gigabytes) e validar o uso de memória em uma instância de não produção antes de aplicá-la em produção.
  • max_parallel_maintenance_workers = 4 — Aumente esse valor para permitir a criação de índice paralelo em servidores multi-vCore. Defina isso em relação ao número de vCores e teste para determinar o nível ideal para sua carga de trabalho.

Teste qualquer alteração de parâmetro ou SKU em um servidor não de produção ou PITR. Valide o desempenho e a estabilidade e aplique as alterações à produção. Após a conclusão da migração ou restaurações grandes, reverta os parâmetros para valores que correspondam aos requisitos normais de carga de trabalho.

Sintaxe de pg_dump

Use a seguinte sintaxe para pg_dump:

pg_dump -h <hostname> -U <username> -d <databasename> -Fd -j <Num of parallel jobs> -Z0 -f sampledb_dir_format

Você pode usar o utilitário pg_restore para restaurar um banco de dados de servidor flexível do Banco de Dados do Azure para PostgreSQL de um arquivo criado por pg_dump. Algumas opções de linha de comando para reduzir o tempo geral de restauração estão listadas nas seções a seguir.

Restauração paralela

Ao usar diversos trabalhos simultâneos, é possível reduzir o tempo necessário para restaurar um grande banco de dados em um servidor de destino multi-vCore. O número de trabalhos pode ser igual ou menor que o número de vCPUs alocados para o servidor de destino.

Parâmetros do servidor

Se você estiver restaurando dados para um novo servidor ou servidor de não produção, poderá otimizar os seguintes parâmetros de servidor antes de executar pg_restore:

work_mem = 32 MB
max_wal_size = 65536 (64 GB)
checkpoint_timeout = 3600 equivalente a 60 min
maintenance_work_mem = 2097151 (2 GB)
autovacuum = desativado
wal_compression = ativado

Após a conclusão da restauração, certifique-se de que todos esses parâmetros sejam atualizados adequadamente de acordo com os requisitos de carga de trabalho.

Observação

Siga as recomendações anteriores somente se houver memória e espaço em disco suficientes. Se você tiver um servidor pequeno com dois, quatro ou oito vCores, defina os parâmetros de acordo.

Outras considerações

  • Desabilite a ALTA DISPONIBILIDADE (HA) antes de executar pg_restore.
  • Analise todas as tabelas migradas após a restauração.

Sintaxe para pg_restore

Use a seguinte sintaxe para pg_restore:

pg_restore -h <hostname> -U <username> -d <db name> -Fd -j <NUM> -C <dump directory>

  • -Fd: o formato do diretório.
  • -j: o número de trabalhos.
  • -C: inicie a saída com um comando para criar o próprio banco de dados e, em seguida, reconecte-se a ele.

Aqui está um exemplo de como essa sintaxe pode aparecer:

pg_restore -h <hostname> -U <username> -j <Num of parallel jobs> -Fd -C -d <databasename> sampledb_dir_format

Considerações sobre a máquina virtual

Crie uma máquina virtual na mesma região e zona de disponibilidade, de preferência onde estão os servidores de destino e origem. Também é possível, no mínimo, criar a máquina virtual mais próxima do servidor de origem ou de destino. Recomenda-se usar as Máquinas Virtuais do Azure com um SSD local de alto desempenho.

Para saber mais sobre SKUs, confira:

Exemplos

Aqui estão alguns exemplos de como usar pg_dump e pg_restore com as práticas recomendadas discutidas acima.

Usar pg_dump com formato de diretório e trabalhos paralelos

pg_dump -h <server>.postgres.database.azure.com -U <username> -d <database> \
  -Fd -j 4 -f /backups/mydb_dump_dir

Explicação:

  • -Fd: despejos no formato de diretório, que é necessário para restauração paralela.
  • -j 4: usa 4 trabalhos paralelos para acelerar o despejo.
  • -f: especifica o diretório de saída.

Usando pg_dump sem compactação para desempenho

pg_dump -h <server>.postgres.database.azure.com -U <username> -d <database> \
  -F c -Z 0 -f /backups/mydb_nocompress.dump

Explicação:

  • -F c: formato personalizado.
  • -Z 0: desabilita a compactação para melhorar o desempenho.

Usando pg_restore com obs paralelos

pg_restore -h <server>.postgres.database.azure.com -U <username> -d <target_database> \
  -Fd -j 4 /backups/mydb_dump_dir

Explicação:

  • -Fd: corresponde ao formato de diretório usado no despejo.
  • -j 4: usa 4 trabalhos paralelos para acelerar a restauração.

Conteúdo relacionado

  • Last updated on