While working on different projects and in different environments, we often need to export a dump from one database and then import it into another. A while ago Slobodan wrote how to export and import a mySQL dump, and here is a guide how do it for PostgreSQL.
To export PostgreSQL database we will need to use the pg_dump tool, which will dump all the contents of a selected database into a single file.
We need to run
pg_dump in the command line on the computer where the database is stored. So, if the database is stored on a remote server, you will need to SSH to that server in order to run the following command:
pg_dump -U db_user -W -F t db_name > /path/to/your/file/dump_name.tar
Here we used the following options:
-Uto specify which user will connect to the PostgreSQL database server.
--passwordwill force pg_dump to prompt for a password before connecting to the server.
-Fis used to specify the format of the output file, which can be one of the following:
p- plain-text SQL script
c- custom-format archive
d- directory-format archive
t- tar-format archive
custom, directory and tar formats are suitable for input into pg_restore.
To see a list of all the available options use
With given options
pg_dump will first prompt for a password for the database user
db_user and then connect as that user to the database named
db_name. After it successfully connects,
> will write the output produced by pg_dump to a file with a given name, in this case
File created in the described process contains all the SQL queries that are required in order to replicate your database.
There are two ways to restore a PostgreSQL database:
psqlfor restoring from a plain SQL script file created with
pg_restorefor restoring from a .tar file, directory, or custom format created with
If your backup is a plain-text file containing SQL script, then you can restore your database by using PostgreSQL interactive terminal, and running the following command:
psql -U db_user db_name < dump_name.sql
db_user is the database user,
db_name is the database name, and
dump_name.sql is the name of your backup file.
If you choose custom, directory, or archive format when creating a backup file, then you will need to use pg_restore in order to restore your database:
pg_restore -d db_name /path/to/your/file/dump_name.tar -c -U db_user
If you use pg_restore you have various options available, for example:
-cto drop database objects before recreating them,
-Cto create a database before restoring into it,
-eexit if an error has encountered,
-F formatto specify the format of the archive.
pg_restore -? to get the full list of available options.
You can find more info on using mentioned tools by running
man psql and