Migrate your PostgreSQL database using dump and restore
To step through this how-to guide, you need:
- An Azure Database for PostgreSQL server with firewall rules to allow access and database under it.
- pg_dump and pg_restore command-line utilities installed
Follow these steps to dump and restore your PostgreSQL database:
Create a dump file using pg_dump that contains the data to be loaded
To back up an existing PostgreSQL database on-premises or in a VM, run the following command:
pg_dump -Fc -v --host=<host> --username=<name> --dbname=<database name> -f <database>.dump
For example, if you have a local server and a database called testdb in it
pg_dump -Fc -v --host=localhost --username=masterlogin --dbname=testdb -f testdb.dump
Restore the data into the target Azure Database for PostrgeSQL using pg_restore
After you've created the target database, you can use the pg_restore command and the -d, --dbname parameter to restore the data into the target database from the dump file.
pg_restore -v --no-owner --host=<server name> --port=<port> --username=<user@servername> --dbname=<target database name> <database>.dump
Including the --no-owner parameter causes all objects created during the restore to be owned by the user specified with --username. For more information, see the official PostgreSQL documentation on pg_restore.
If your PostgreSQL server requires SSL connections (on by default in Azure Database for PostgreSQL servers), set an environment variable
PGSSLMODE=require so that the pg_restore tool connects with SSL. Without SSL, the error may read
FATAL: SSL connection is required. Please specify SSL options and retry.
In the Windows command line, run the command
SET PGSSLMODE=require before running the pg_restore command. In Linux or Bash run the command
export PGSSLMODE=require before running the pg_restore command.
In this example, restore the data from the dump file testdb.dump into the database mypgsqldb on target server mydemoserver.postgres.database.azure.com.
pg_restore -v --no-owner --host=mydemoserver.postgres.database.azure.com --port=5432 --username=mylogin@mydemoserver --dbname=mypgsqldb testdb.dump
Optimizing the migration process
One way to migrate your existing PostgreSQL database to Azure Database for PostgreSQL service is to back up the database on the source and restore it in Azure. To minimize the time required to complete the migration, consider using the following parameters with the backup and restore commands.
For the backup
Take the backup with the -Fc switch so that you can perform the restore in parallel to speed it up. For example:
pg_dump -h MySourceServerName -U MySourceUserName -Fc -d MySourceDatabaseName -f Z:\Data\Backups\MyDatabaseBackup.dump
For the restore
We suggest that you move the backup file to an Azure VM in the same region as the Azure Database for PostgreSQL server you are migrating to, and do the pg_restore from that VM to reduce network latency. We also recommend that the VM is created with accelerated networking enabled.
It should be already done by default, but open the dump file to verify that the create index statements are after the insert of the data. If it isn't the case, move the create index statements after the data is inserted.
Restore with the switches -Fc and -j # to parallelize the restore. # is the number of cores on the target server. You can also try with # set to twice the number of cores of the target server to see the impact. For example:
pg_restore -h MyTargetServer.postgres.database.azure.com -U MyAzurePostgreSQLUserName -Fc -j 4 -d MyTargetDatabase Z:\Data\Backups\MyDatabaseBackup.dump
You can also edit the dump file by adding the command set synchronous_commit = off; at the beginning and the command set synchronous_commit = on; at the end. Not turning it on at the end, before the apps change the data, may result in subsequent loss of data.
On the target Azure Database for PostgreSQL server, consider doing the following before the restore:
Turn off query performance tracking, since these statistics are not needed during the migration. You can do this by setting pg_stat_statements.track, pg_qs.query_capture_mode, and pgms_wait_sampling.query_capture_mode to NONE.
Use a high compute and high memory sku, like 32 vCore Memory Optimized, to speed up the migration. You can easily scale back down to your preferred sku after the restore is complete. The higher the sku, the more parallelism you can achieve by increasing the corresponding
-jparameter in the pg_restore command.
More IOPS on the target server could improve the restore performance. You can provision more IOPS by increasing the server's storage size. This setting is not reversible, but consider whether a higher IOPS would benefit your actual workload in the future.
Remember to test and validate these commands in a test environment before you use them in production.
- To migrate a PostgreSQL database using export and import, see Migrate your PostgreSQL database using export and import.
- For more information about migrating databases to Azure Database for PostgreSQL, see the Database Migration Guide.