Blogs

How to Import and Export PostgreSQL Database – SnapShooter

Posted on by admin

Introduction

PostgreSQL is the modern and most popular database management system today. Backing up PostgreSQL data is one of the most important tasks for any database administrator. It is an essential component of a data storage plan. Backups will help you recover your system in case of database corruption. PostgreSQL provides pg_dump and a pg_dumpall command-line utility for effective backups of your important information.

When working on a different project in different environments, it is often necessary to export a database dump from one server and import it to another server. Therefore, it is essential to implement a backup strategy to protect against data loss.

In this post, we will show you how to import and export a PostgreSQL database using pg_dump, pg_dumpall, psql, and pg_restore utility.

Export a

PostgreSQL database with

pg_dump command

The pg_dump is a command-line tool that allows you to dump the contents of a specified database into a single file. The pg_dump command provides several options that help you 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 the port of the U-database server Specify the user that is used to connect to the PostgreSQL-W database server

  • Used to prompt for a password before connecting to the PostgreSQL-d server

  • Specify

  • the basis of

  • data

  • 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 testdb to a

  • plain text

, run the following command: pg_dump -U username -W -F p -d testdb > testdb.sql To export a database named testdb to a

tar

, run the following command

:

pg_dump -U username -W -F t -d testdb > testdb.tar

To export a database named testdb to a

custom format, Run the following command: pg_dump -U username -W -F c -d testdb > testdb.dump To export a database named testdb to a

directory format, run the following command: pg_dump -U username -W -F d -d testdb -f testdb-dir 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 -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 in a single file. This file contains several SQL commands that can be used as input to psql to restore databases. The pg_dumpall reads tables from all databases, so you will need to connect to PostgreSQL as superuser to produce a full dump.

The basic syntax of the pgdump_all command

is shown below: pg_dumpall [option] > dump.sql

Below is a brief explanation of each option:

-h Specify database server host-p

    Specify database server port-c Clean databases before

  • recreating-g dump only global objects, no databases

  • R-data

    • Dump roles only, no

  • databases or tablespaces -s dump schema only, no data –S Specify the superuser user name to be used in the dump -t dump only the tablespace definition.

To export all PostgreSQL databases, run the following command: pg_dumpall > dump.sql To back up

objects to all databases, including roles, tablespaces, databases, schemas, tables, indexes, triggers, functions, constraints, views, properties, and privileges, use

the following command: pg_dumpall -s > dump.sql If you want to back up only the definition

of roles

, use the following command: pg_dumpall -r > dump.sql

If you want to back up the tablespace definition, use the following command:

pg_dumpall -t > 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 a file created by pg_dump. It is typically used if you choose the 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 restore. This will allow you to perform parallel restorations and drastically speed up the process. Currently, the pg_restore supports this option for the only custom file format.

The basic syntax of the pg_restore command

is shown below: pg_restore -c -U db_user -W -F t -d db_name dump_file.tar Below is a

brief explanation of each option:

-U Specify the

  • user that is used to connect to the PostgreSQL database server

    • . –

  • d Specify the database into 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 file.

  • -j Specify the number of parallel jobs to restore.

  • -c Clean up a database before restoring it.

  • -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 testdb from the dump file named dump.tar, run the following command: pg_restore -c -U username -W -F t -d

testdb dump

.tar

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

pg_restore -c -U user name -W -F t alldump.tar

Import a PostgreSQL database with the psql On command

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 for importing a single database using the psql tool is shown below: psql -U user_name -W -d db_name -f dump.sql

The basic syntax for importing all databases using the psql tool is shown below: psql

-U user_name -W -f dump.sql To

import a single

database from the dump

file named dump.sql to the database named testdb, run the following command

: psql -U username -W -d testdb -f dump.sql If you want to stop importing a database in case of errors, add the -set ON_ERROR_STOP=on option: psql -U username -W -d testdb –

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 -u username -W -f

alldb_dump.sql Conclusion

In this guide, we explain how to export a PostgreSQL database using the pg_dump utility and pg_dumpall. We also explain how to import a dump file using the pg_restore utility and psql. I hope this helps you perform your daily backup jobs.

admin