| title | description | services | author | ms.author | manager | editor | ms.service | ms.topic | ms.date |
|---|---|---|---|---|---|---|---|---|---|
How To Dump and Restore in Azure Database for PostgreSQL | Microsoft Docs |
Describes how to extract a PostgreSQL database into a dump file and restore the PostgreSQL database from an archive file created by pg_dump in Azure Database for PostgreSQL. |
postgresql |
SaloniSonpal |
salonis |
jhubbard |
jasonwhowell |
postgresql |
article |
11/03/2017 |
You can use pg_dump to extract a PostgreSQL database into a dump file and pg_restore to restore the PostgreSQL database from an archive file created by pg_dump.
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:
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> > <database>.dumpFor example, if you have a local server and a database called testdb in it
pg_dump -Fc -v --host=localhost --username=masterlogin --dbname=testdb > testdb.dumpOnce you have 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 –-host=<server name> --port=<port> --username=<user@servername> --dbname=<target database name> <database>.dumpIn this example, restore the data from the dump file testdb.dump into the database mypgsqldb on target server mypgserver-20170401.postgres.database.azure.com.
pg_restore -v --host=mypgserver-20170401.postgres.database.azure.com --port=5432 --username=mylogin@mypgserver-20170401 --dbname=mypgsqldb testdb.dump- To migrate a PostgreSQL database using export and import, see Migrate your PostgreSQL database using export and import