How to export and import database in PostgreSQL through command line?

Export a PostgreSQL Database with pg_dump Command

To export a database through command line. PostgreSQL has provided command-line utility to effectively backup your important information which is pg_dump and a pg_dumpall.

The pg_dump is a command-line tool that allows you to dump the content of a specified database into a single file. The pg_dump command provides several options that help you to specify tables or schemas to back up.

The basic syntax of the pg_dump command is shown below:

• -h Specify database server host
• -p Specify database server port
• -U Specify the user which is used to connect to the PostgreSQL database server
• -W Used to prompt for a password before connecting to the PostgreSQL server
• -d Specify the database to dump
• -F Specify one of the following output format files:
• p plain-text format
• c custom format
• d directory format
• t tar format

Examples

To export a database named sampledb into a plain-text format, run the following command:

pg_dump -h localhost -p 5432  -U username -W -F p -d sampledb > sampledb.sql

To export a database named sampledb into a tar format, run the following command:

pg_dump  -h localhost -p 5432  -U username -W -F t -d sampledb > sampledb.tar

If you want to export a large database and generate a smaller dump file, you can use the gzip tool with the pg_dump command to compress the dump file.

pg_dump -h localhost -p 5432 -U username -W -F t -d testdb | gzip > testdb.tar.gz

Export All PostgreSQL Databases with pg_dumpall Command

The pg_dumpall is a command-line utility that allows you to export all PostgreSQL databases into a single file. This file contains several SQL commands that can be used as input to psql to restore the databases. The pg_dumpall reads tables from all databases, so you will have to connect to PostgreSQL as a superuser to produce a complete dump.

The basic syntax of the pgdump_all command is shown below:

pg_dumpall [option] > dump.sql

A brief explanation of each option is shown below:

• -h Specify database server host
• -p Specify database server port
• -c Clean databases before recreating
• -g Dump only global objects, no databases
• -r Dump only roles, no databases or tablespaces -s Dump only the schema, no data -S Specify superuser user name to use in the dump -t Dump only tablespaces definition.

To export all PostgreSQL databases, run the following command:

pg_dumpall -h localhost -p 5432  > dump.sql

To back up objects in all databases, including roles, tablespaces, databases, schemas, tables, indexes, triggers, functions, constraints, views, ownerships, and privileges, you use the following command:

pg_dumpall -h localhost -p 5432 -s > dump.sql

Import a PostgreSQL Database with pg_restore Command

The pg_restore is a command-line utility used to import or restore a PostgreSQL database from an archive created by pg_dump. Generally, it is used if you choose custom, directory, or tar format when creating a dump file.

The pg_restore command provides a -j option to specify the number of threads for restoration. This will allow you to perform parallel restores and dramatically speed up the process. Currently, the pg_restore support this option for the only custom file format.

The basic syntax of the pg_restore command is shown below:

pg_restore -h localhost -p 5432 -c -U db_user -W -F t -d db_name dump_file.tar

A brief explanation of each option is shown below:

• -U Specify the user who is used for connecting to the PostgreSQL database server.
• -d Specify the database on which you want to import or restore a database.
• -W Used to prompt for a password before connecting to the PostgreSQL server.
• -F format Specify the format of the archive.
• -j Specify the number of parallel jobs to restore.
• -c Clean a database before restoring them.
• -C Create a new database before restoring it.
• -e Exit the process if an error has been encountered.

For example, to import a single database named sampledb from the dump file named dump.tar, run the following command:

pg_restore -h localhost -p 5432 -c -U username -W -F t -d sampledb dump.tar

If you want to import all databases from the alldump.tar file, run the following command:

pg_restore -h localhost -p 5432 -c -U username -W -F t alldump.tar

Import a PostgreSQL Database with psql Command

The psql is a command-line tool that allows you to import the SQL dump file generated by the pg_dump or pg_dumpall.

The basic syntax to import a single database using the psql tool is shown below:

psql -h localhost -p 5432 -U user_name -W -d db_name -f dump.sql

The basic syntax to import all databases using the psql tool is shown below:

psql -h localhost -p 5432 -U user_name -W -f dump.sql

If you want to stop importing a database in case of errors, you add the –set ON_ERROR_STOP=on option:

psql -h localhost -p 5432 -U username -W -d sampledb --set ON_ERROR_STOP=on -f dump.sql

To import all PostgreSQL databases from the dump file named alldb_dump.sql, run the following command:

psql -h localhost -p 5432 -U username -W -f alldb_dump.sql

Conclusion

In this post, I explained how to export a PostgreSQL database using the pg_dump and pg_dumpall utility. I also explained how to import a dump file using the pg_restore and psql utility. I hope this will help you to perform your day-to-day backup jobs.