Migrate your PostgreSQL database using export and import
APPLIES TO:
Azure Database for PostgreSQL - Single Server
Azure Database for PostgreSQL - Flexible Server
You can use pg_dump to extract a PostgreSQL database into a script file and psql to import the data into the target database from that file.
Prerequisites
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 command-line utility installed
- psql command-line utility installed
Follow these steps to export and import your PostgreSQL database.
Create a script file using pg_dump that contains the data to be loaded
To export your existing PostgreSQL database on-premises or in a VM to a sql script file, run the following command in your existing environment:
pg_dump --host=<host> --username=<name> --dbname=<database name> --file=<database>.sql
For example, if you have a local server and a database called testdb in it:
pg_dump --host=localhost --username=masterlogin --dbname=testdb --file=testdb.sql
Import the data on target Azure Database for PostgreSQL
You can use the psql command line and the --dbname parameter (-d) to import the data into the Azure Database for PostgreSQL server and load data from the sql file.
psql --file=<database>.sql --host=<server name> --port=5432 --username=<user> --dbname=<target database name>
This example uses psql utility and a script file named testdb.sql from previous step to import data into the database mypgsqldb on the target server mydemoserver.postgres.database.azure.com.
For Single Server, use this command
psql --file=testdb.sql --host=mydemoserver.database.windows.net --port=5432 --username=mylogin@mydemoserver --dbname=mypgsqldb
For Flexible Server, use this command
psql --file=testdb.sql --host=mydemoserver.database.windows.net --port=5432 --username=mylogin --dbname=mypgsqldb
Next steps
- To migrate a PostgreSQL database using dump and restore, see Migrate your PostgreSQL database using dump and restore.
- For more information about migrating databases to Azure Database for PostgreSQL, see the Database Migration Guide.
Feedback
Submit and view feedback for