Atualizar seu banco de dados PostgreSQL usando despejar e restaurar

Artigo
03/30/2023

APLICA-SE A: Banco de Dados do Azure para PostgreSQL – Servidor Único

Importante

O Banco de Dados do Azure para PostgreSQL – Servidor Único está prestes a ser desativado. Recomendamos fortemente que você atualize para o Banco de Dados do Azure para PostgreSQL – Servidor flexível. Para obter mais informações sobre a migração para o Banco de Dados do Azure para PostgreSQL – Servidor Flexível, veja O que está acontecendo com o Banco de Dados do Azure para PostgreSQL Servidor único?.

Observação

Os conceitos explicados nesta documentação são aplicáveis ao Banco de Dados do Azure para PostgreSQL – Servidor Único e Banco de Dados do Azure para PostgreSQL – Servidor Flexível.

Você pode atualizar o servidor PostgreSQL implantado no Banco de Dados do Azure para PostgreSQL migrando seus bancos de dados para um servidor com uma versão principal mais atualizada usando os métodos a seguir.

Método offline usando PostgreSQL pg_dump e pg_restore que incorre em tempo de inatividade para migração de dados. Este documento aborda esse método de atualização/migração.
Método online usando o DMS (Serviço de Migração de Banco de Dados). Esse método fornece uma migração com tempo de inatividade reduzido e mantém o banco de dados de destino em sincronia com a origem, além de você poder escolher quando recortar. No entanto, há poucos pré-requisitos e restrições a serem abordados para o uso do DMS. Para obter detalhes, confira a Documentação do DMS.
Método de Atualização da versão principal in-loco usando o Banco de Dados do Azure para PostgreSQL – Servidor Flexível. O recurso de atualização de versão principal in-loco executa a atualização de versão principal do servidor com apenas um clique. Isso simplifica o processo de atualização minimizando a interrupção para usuários e aplicativos que acessam o servidor. As atualizações in-loco são uma maneira mais simples de atualizar a versão principal da instância, pois mantêm o nome do servidor e outras configurações do servidor atual após a atualização e não exigem migração de dados ou alterações nas cadeias de conexão do aplicativo. As atualizações in-loco são mais rápidas e envolvem um tempo de inatividade menor do que a migração de dados.

A tabela a seguir fornece algumas recomendações com base em tamanhos e cenários de banco de dados.

Banco de dados/cenário	Despejo/restauração (offline)	DMS (online)
Você tem um banco de dados pequeno e pode arcar com o tempo de inatividade para atualizar	X
Bancos de dados pequenos (< 10 GB)	X	X
Bancos de pequenos-médios (10 GB a 100 GB)	X	X
Bancos de dados grandes (> 100 GB)		X
Pode arcar com o tempo de inatividade para atualizar (independentemente do tamanho do banco de dados)	X
Pode atender aos pré-requisitos do DMS, incluindo uma reinicialização?		X
Pode evitar tabelas DDLs e não registradas durante o processo de atualização?		X

Este guia fornece algumas metodologias e exemplos de migração offline para mostrar como você pode migrar do servidor de origem para o servidor de destino que executa uma versão mais recente do PostgreSQL.

Observação

O despejo e a restauração do PostgreSQL podem ser executados de várias maneiras. Você pode optar por migrar usando um dos métodos fornecidos neste guia ou escolher alguma forma alternativa para atender às suas necessidades. Para obter uma sintaxe detalhada de despejo e restauração com parâmetros adicionais, confira os artigos pg_dump e pg_restore.

Pré-requisitos para usar despejo e restauração com o Banco de Dados PostgreSQL do Azure

Para seguir este guia de instruções, você precisa:

Um servidor de banco de dados PostgreSQL de origem que executa uma versão inferior do mecanismo que você deseja atualizar.
Um servidor de banco de dados PostgreSQL de destino com a versão principal desejada do servidor do Banco de Dados do Azure para PostgreSQL - Servidor Único ou Banco de Dados do Azure para PostgreSQL - Servidor Flexível.
Um sistema cliente PostgreSQL para executar os comandos despejo e restauração. Recomendamos usar a maior versão do banco de dados. Por exemplo, se você estiver atualizando do PostgreSQL versão 9.6 para 11, use o cliente PostgreSQL versão 11.
- Pode ser um cliente Linux ou Windows que tenha o PostgreSQL instalado e que tenha os utilitários de linha de comando pg_dump e pg_restore instalados.
- Como alternativa, você pode usar o Azure Cloud Shell ou selecionar o Azure Cloud Shell na barra de menus na parte superior direita do portal do Azure. Você precisará fazer logon em sua conta az login antes de executar os comandos despejo e restauração.
O cliente PostgreSQL preferencialmente executado na mesma região que os servidores de origem e de destino.

Detalhes e considerações adicionais

Você pode encontrar a cadeia de conexão para os bancos de dados de origem e de destino ao selecionar “Cadeias de Conexão” no portal.
Você pode executar mais de um banco de dados em seu servidor. Você pode encontrar a lista de bancos de dados conectando-se ao seu servidor de origem e executando \l.
Crie banco de dados correspondentes no servidor de banco de dados de destino ou adicione a opção -C ao comando pg_dump que cria os bancos de dados.
Você não deve atualizar ou azure_maintenance os bancos de dados de modelo. Se você tiver feito alterações em bancos de dados de modelo, poderá optar por migrar as alterações ou fazer essas alterações no banco de dados de destino.
Confira as tabelas acima para determinar se o banco de dados é adequado para esse modo de migração.
Se você quiser usar o Azure Cloud Shell, observe que a sessão atinge o tempo limite após 20 minutos. Se o tamanho do seu banco de dados for < 10 GB, você poderá concluir a atualização sem atingir o tempo limite da sessão. Caso contrário, talvez você precise manter a sessão aberta por outros meios, como pressionar uma tecla uma vez a cada 10-15 minutos.

Banco de dados de exemplo usado neste guia

Neste guia, os servidores de origem, de destino e os nomes de banco de dados a seguir são usados para ilustrar com exemplos.

Descrição	Valor
Servidor de origem (v9.5)	pg-95.postgres.database.azure.com
Banco de dados de origem	bench5gb
Tamanho do banco de dados de origem	5 GB
Nome de usuário de origem	pg@pg-95
Servidor de destino (v11)	pg-11.postgres.database.azure.com
Banco de dados de destino	bench5gb
Nome de usuário de destino	pg@pg-11

Observação

O servidor flexível dá suporte ao PostgreSQL versão 11 em diante. Além disso, o nome de usuário do servidor flexível não exige @dbservername.

Atualizar seus bancos de dados usando métodos de migração offline

Você pode optar por usar um dos métodos descritos nesta seção para suas atualizações. Você pode usar as dicas a seguir ao executar as tarefas.

Se você estiver usando a mesma senha para o banco de dados de origem e de destino, defina a variável de ambiente PGPASSWORD=yourPassword. Assim você não precisará fornecer a senha toda vez que executar comandos como psql, pg_dump e pg_restore. Da mesma forma, você pode configurar variáveis adicionais como PGUSER, PGSSLMODE etc. Confira Variáveis de ambiente do PostgreSQL.
Se o seu servidor PostgreSQL exigir conexões TLS/SSL (ativas por padrão em servidores do Banco de Dados do Azure para PostgreSQL), defina uma variável de ambiente PGSSLMODE=require para que a ferramenta de pg_restore se conecte com TLS. Sem TLS, o erro pode indicar FATAL: SSL connection is required. Please specify SSL options and retry.
Na linha de comando do Windows, execute o comando SET PGSSLMODE=require antes de executar o comando pg_restore. No Linux ou o Bash, execute o comando export PGSSLMODE=require antes de executar o comando pg_restore.

Importante

As etapas e os métodos fornecidos neste documento se destinam a dar alguns exemplos de comandos pg_dump/pg_restore e não representam todas as maneiras possíveis de executar atualizações. Recomendamos testar e validar os comandos em um ambiente de teste antes de usá-los em produção.

Migrar as Funções

As Funções (Usuários) são objetos globais e precisam ser migradas separadamente para o novo cluster antes da restauração dos bancos de dados. Você pode usar pg_dumpall o binário com a opção -r (--roles-only) para despejá-los. Para despejar todas as funções com senhas do Servidor Único de origem:

pg_dumpall -r --host=mySourceServer --port=5432 --username=myUser@mySourceServer --database=mySourceDB > roles.sql

Para despejar todos os nomes de funções, sem senhas do Servidor Flexível de origem:

pg_dumpall -r --no-role-passwords --host=mySourceServer --port=5432 --username=myUser --database=mySourceDB > roles.sql

Importante

No Banco de Dados do Azure para PostgreSQL – Servidor Flexível, os usuários não têm permissão para acessar a tabela pg_authid que contém informações sobre os identificadores de autorização de banco de dados com as senhas do usuário. Portanto, não é possível recuperar senhas para os usuários. Considere o uso do Azure Key Vault para armazenar seus segredos com segurança.

Edite roles.sql e remova as referências de NOSUPERUSER e NOBYPASSRLS antes de restaurar o conteúdo usando psql no servidor de destino:

psql -f roles.sql --host=myTargetServer --port=5432 --username=myUser --dbname=postgres

Não espere que o script de despejo seja executado por completo sem erros. Em particular, como o script emitirá CREATE ROLE para cada função existente no cluster de origem, é certo que você receberá o erro “A função já existe” para o superusuário de inicialização como azure_pg_admin ou azure_superuser. Esse erro é inofensivo e pode ser ignorado. O uso da opção --clean provavelmente produzirá mensagens de erro não válidas adicionais sobre objetos inexistentes, embora você possa minimizá-los adicionando --if-exists.

Método 1: usando pg_dump e psql

Esse método envolve duas etapas. A primeira é despejar um arquivo SQL do servidor de origem usando pg_dump. A segunda etapa é importar o arquivo para o servidor de destino usando psql. Para obter mais detalhes, confira a documentação Migrar usando exportação e importação.

Método 2: usando pg_dump e pg_restore

Nesse método de atualização, primeiro crie um despejo do servidor de origem usando pg_dump. Em seguida, restaure esse arquivo de despejo para o servidor de destino usando pg_restore. Para obter mais detalhes, confira a documentação Migrar usando despejo e restauração.

Método 3: usando o streaming dos dados de despejo para o banco de dados de destino

Se você não tiver um cliente PostgreSQL ou quiser usar o Azure Cloud Shell, use esse método. O despejo do banco de dados é transmitido diretamente para o servidor de banco de dados de destino e não armazena o despejo no cliente. Portanto, isso pode ser usado com um cliente com armazenamento limitado e até mesmo pode ser executado a partir do Azure Cloud Shell.

Verifique se o banco de dados existe no servidor de destino usando o comando \l. Se o banco de dados não existir, crie-o.

 psql "host=myTargetServer port=5432 dbname=postgres user=myUser password=###### sslmode=mySSLmode"

postgres> \l   
postgres> create database myTargetDB;

Execute o despejo e a restauração como uma linha de comando única usando um pipe.

pg_dump -Fc --host=mySourceServer --port=5432 --username=myUser --dbname=mySourceDB | pg_restore  --no-owner --host=myTargetServer --port=5432 --username=myUser --dbname=myTargetDB

Por exemplo,

pg_dump -Fc --host=pg-95.postgres.database.azure.com --port=5432 --username=pg@pg-95 --dbname=bench5gb | pg_restore --no-owner --host=pg-11.postgres.database.azure.com --port=5432 --username=pg@pg-11 --dbname=bench5gb

Depois que o processo de atualização (migração) for concluído, você poderá testar seu aplicativo com o servidor de destino.
Repita esse processo para todos os bancos de dados no servidor.

Por exemplo, a tabela a seguir ilustra o tempo necessário para migrar usando o método de streaming dump. Os dados de exemplo são populados usando pgbench. Como o seu banco de dados pode ter um número diferente de objetos com tamanhos variados do que as tabelas e os índices gerados pelo pgbench, é altamente recomendável testar o despejo e a restauração do banco de dados para entender o tempo real necessário para atualizar o banco de dados.

Tamanho do Banco de Dados	Tempo gasto aproximadamente
1 GB	1 – 2 minutos
5 GB	8 – 10 minutos
10 GB	15-20 minutos
50 GB	1 – 1,5 horas
100 GB	2,5 – 3 horas

Método 4: usando o despejo e a restauração paralelos

Você pode considerar esse método se tiver poucas tabelas grandes no banco de dados e quiser paralelizar o processo de despejo e restauração para esse banco de dados. Você também precisa de armazenamento suficiente no seu sistema cliente para acomodar os despejos de backup. Esse processo paralelo de despejo e restauração reduz o tempo de consumo para concluir a migração inteira. Por exemplo, o banco de dados de 50 GB pgbench que levou de 1 a 1,5 h para migrar foi concluído usando o método 1 e 2, levou menos de 30 minutos usando esse método.

Para cada banco de dados no servidor de origem, crie um banco de dados correspondente no servidor de destino.

psql "host=myTargetServer port=5432 dbname=postgres user=myuser password=###### sslmode=mySSLmode"

postgres> create database myDB;

Por exemplo,

psql "host=pg-11.postgres.database.azure.com port=5432 dbname=postgres user=pg@pg-11 password=###### sslmode=require"
psql (12.3 (Ubuntu 12.3-1.pgdg18.04+1), server 13.3)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres> create database bench5gb;
postgres> \q

Execute o comando pg_dump em um formato de diretório com número de trabalhos = 4 (número de tabelas no banco de dados). Com uma camada de computação maior e com mais tabelas, você pode aumentá-la para um número maior. Esse pg_dump criará um diretório para armazenar arquivos compactados para cada trabalho.
```
pg_dump -Fd -v --host=sourceServer --port=5432 --username=myUser --dbname=mySourceDB -j 4 -f myDumpDirectory
```
Por exemplo,
```
pg_dump -Fd -v --host=pg-95.postgres.database.azure.com --port=5432 --username=pg@pg-95 --dbname=bench5gb -j 4 -f dump.dir
```

Em seguida, restaure o backup no servidor de destino.

$ pg_restore -v --no-owner --host=myTargetServer --port=5432 --username=myUser --dbname=myTargetDB -j 4 myDumpDir

Por exemplo,

$ pg_restore -v --no-owner --host=pg-11.postgres.database.azure.com --port=5432 --username=pg@pg-11 --dbname=bench5gb -j 4 dump.dir

Dica

O processo mencionado neste documento também pode ser usado para atualizar seu Banco de Dados do Azure para PostgreSQL – Servidor flexível. A principal diferença é que a cadeia de conexão para o servidor flexível de destino não tem o @dbName. Por exemplo, se o nome de usuário for pg, o nome de usuário do servidor único na cadeia de conexão será pg@pg-95, enquanto que com o servidor flexível, você poderá simplesmente usar pg.

Pós-atualização/migração

Depois que a atualização da versão principal for concluída, recomendamos executar o comando ANALYZE em cada banco de dados para atualizar a tabela pg_statistic. Caso contrário, você poderá ter problemas de desempenho.

postgres=> analyze;
ANALYZE

Próximas etapas

Quando ficar satisfeito com a função de banco de dados de destino, você poderá remover o servidor de banco de dados antigo.
Somente para o Banco de Dados do Azure para PostgreSQL - Servidor Único. Se quiser usar o mesmo ponto de extremidade de banco de dados que o servidor de origem depois de excluir o servidor de banco de dados de origem antigo, você poderá criar uma réplica de leitura com o nome do servidor de banco de dados antigo. Quando o estado de replicação estável for estabelecido, você poderá interromper a réplica, o que promoverá o servidor de réplica para ser um servidor independente. Consulte Replicação para obter mais detalhes.

Importante

É altamente recomendável testar a nova versão atualizada do PostgreSQL antes de usá-la diretamente para produção. Isso inclui comparar os parâmetros de servidor entre a origem de versão mais antiga e o destino de versão mais recente. Verifique se eles são iguais e se novos parâmetros foram adicionados na nova versão. As diferenças entre as versões podem ser encontradas aqui.

Atualizar seu banco de dados PostgreSQL usando despejar e restaurar

Pré-requisitos para usar despejo e restauração com o Banco de Dados PostgreSQL do Azure

Detalhes e considerações adicionais

Banco de dados de exemplo usado neste guia

Atualizar seus bancos de dados usando métodos de migração offline

Migrar as Funções

Método 1: usando pg_dump e psql

Método 2: usando pg_dump e pg_restore

Método 3: usando o streaming dos dados de despejo para o banco de dados de destino

Método 4: usando o despejo e a restauração paralelos

Pós-atualização/migração

Próximas etapas

Recursos adicionais