Edit

Migrate your PostgreSQL database using export and import

  • Article
  • 2 minutes to read

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:

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