PostgreSQL Dump Import: How to Restore PostgreSQL Databases? | 3 Easy Methods

The concept behind the PostgreSQL Dump Import approach is to create a text file containing SQL commands that, when sent back to the server, will reconstruct the database in the same state as it was when the dump was performed. For this, PostgreSQL provides the pg_dump utility function. The PostgreSQL client application pg_dump is a standard PostgreSQL client (albeit a particularly clever one). This implies you can backup the database from any remote system that has access to the database. 

In this article, you will learn everything about ways to recover data with pg_dump Import, along with the process that you might want to carry out while using it to make the process run smoothly. You will also learn about a No-code data pipeline to carry out this process.

What is a PostgreSQL Database Dump?

pg_dump is a backup tool for PostgreSQL databases. Even if the database is in use at the same time, it creates consistent backups. Other users are not prevented from accessing the database via pg_dump (readers or writers).

pg dump only dumps one database at a time. Use pg_dumpall to backup global objects that are shared by all databases in a cluster, such as roles and tablespaces.

Dump files can be saved as scripts or archive files. Script dumps are text files that include the SQL commands needed to restore the database to the state it was in when it was saved. Feed it to psql to restore from such a script. Script files can be used to rebuild the database on various machines and architectures, as well as on other SQL database solutions with some adjustments.

To rebuild the database, various archive file formats must be utilized using pg_restore. They allow pg_restore to be picky about what it restores and even reorder items before they’re restored. The archive file formats were created with portability in mind.

When paired with pg_restore and one of the archive file formats, pg_dump provides a versatile archival and transfer method. pg_dump can backup a complete database, and then pg_restore can review the archive and/or decide which sections of the database should be restored. The “custom” format (-Fc) and the “directory” format are the most flexible output file formats (-Fd). They offer parallel restoration and are compressed by default, and they allow for the selection and reordering of all archived objects. The only format that allows for concurrent dumps is the “directory” type.While executing pg_dump, look for any warnings in the output (printed on standard error).

PostgreSQL Dump Import: Ways to Recover 

Method 1: Using Hevo Data, a No-code Data Pipeline for PostgreSQL Dump Import

Hevo supports connecting with PostgreSQL as a source database and as a Destination Database. Plus, with Hevo’s No-code approach, you can replicate data using the Log-based approach and leverage WAL Files to ensure Real-time replication, all without writing any code. It helps carry out processes like PostgreSQL Dump Import. Hevo focuses on these simple steps below to get you started.

Step 1: Add Destination

Choose one of the following options:

• Click ADD DESTINATION once you’ve configured the Source during Pipeline building.
• In the Asset Palette, click DESTINATIONS, and then + CREATE in the Destinations List View.

Step 2: Select the Destination Type

Select PostgreSQL from the Add Destination page. Alternatively, search for the Destination using the Search Destination Type search box.

Step 3: Configure PostgreSQL Connection Settings 

In the Configure your PostgreSQL Destination page, make the following changes:

Step 4: Test Connection 

To test connectivity to the Destination Postgres server, put in the details and then click TEST CONNECTION.

Step 5: Save Connection

When the test is complete, click SAVE DESTINATION to save the connection.

As can be seen, you are simply required to enter the corresponding credentials to implement this fully automated data pipeline without using any code. Hevo supports data ingestion for replication from PostgreSQL servers. Learn more about Hevo’s integration with PostgreSQL.

Method 2: PostgreSQL Dump Import Restore using PSQL Command

What is the PSQL Command? 

If your backup is a plain-text file with SQL Scripts, you can restore your database by using the following command in the PostgreSQL interactive terminal: 

psql -U db_user db_name < dump_name.sql

where db_user is the database user, db_name is the database name, and dump_name.sql is the name of your backup file.

Syntax: 

psql [option...] [dbname [username]]

When Should you use PSQL Command?

psql is a terminal-based PostgreSQL front-end. It allows you to enter Interactive Queries, send them to PostgreSQL, and view the results. Input can also come from a file or through command line arguments. psql also has a number of meta-commands and shell-like capabilities that make building scripts and automating a range of activities easier.

How do you restore PostgreSQL data using PSQL Command?

Postgres Import Dump process can be carried out using psql command. You can use the psql utility to recover the SQL script file created by pg_dump, pg_dumpall, or any other tool that creates compatible backup files. You can run the complete script in the dump file with the psql tool.

Use the following psql import dump command to restore a full backup while ignoring any errors that may have occurred during the restoration process:

psql -U username -f backupfile.sql

If you wish to prevent a database from being restored if there are issues, use the —set ON_ERROR_STOP=on option:

psql -U username --set ON_ERROR_STOP=on -f backupfile

If you have made a backup of objects in a database, you can restore them with the following command:

psql -U username -d database_name -f objects.sql

pg_dump can dump just a single database at a time, and it does not dump data about roles or tablespaces. This is because those are not per-database, but cluster-wide. Pg_dumpall program helps for supporting the convenient dumping of the entire contents of a database cluster. pg_dumpall does the back up of each database in the cluster. It also preserves cluster-wide data like role and tablespace definitions.

Method 3: PostgreSQL Dump Import Restore using Pg_Restore Command

In this method, let me introduce you to Pg_Restore command, which is a pg_dump postgres example.

What is the Pg_Restore Command? 

If you choose custom, directory, or archive format while creating a backup file, you’ll have to restore your database with pg_restore:

pg_restore -d db_name /path/to/your/file/dump_name.tar -c -U db_user

If you use pg restore, you can choose from a number of settings, such as:

• To remove database objects before recreating them, use the –c option.
• To construct a database before restoring it, use the -C option.
• If an error occurs, leave with -e.
• -F format specifies the archive’s format.
• To get the entire list of available options, type pg_restore -?

By running man pg_dump, man psql, and man pg_restore, you may learn more about how to use the tools described.

Syntax: 

pg_restore [connection-option...] [option...] [filename]

When Should you use Pg_Restore Command?

pg_restore is a utility that allows you to restore a PostgreSQL database from a non-plain-text archive produced by pg_dump. It will execute the procedures required to restore the database at an earlier state. The archive files also allow pg_restore to be picky about what it restores and even reorder the items before restoring them. The archive files are designed to be cross-platform compatible.

pg_restore has two modes of operation. If a database name is given, pg_restore connects to it and restores the contents of the archive into the database. Otherwise, a script is written to a file or standard output providing the SQL statements required to recreate the database. This script’s output is the same as pg_dump‘s plain text output format. As a result, several of the output control options are similar to pg dump options.

Obviously, pg_restore won’t be able to restore data that isn’t in the archive file. For example, pg_restore will not be able to load the data using COPY statements if the archive was created using the “dump data as INSERT commands” option.

How to Restore PostgreSQL Data using Pg_Restore Command?

You can restore data with PostgreSQL Dump Import backed up by pg_dump or pg_dumpall tools using the pg_restore application instead of psqltool. You can restore databases using the pg restore software in a variety of ways, for example:

• Using the -j option to define the number of threads for restoration, you can conduct parallel restores with pg_restore. Each thread restores a distinct table at the same time, greatly speeding up the operation. Currently, the pg_restore supports this option for only the Custom File Format.
• You can also use the pg_restore command to restore select Database items from a backup file that contains the entire Database.
• The pg_restore command can restore a database that was backed up in an older version to a later version.

To practice with the pg_restore tool, create a new database called newdvdrental.

CREATE DATABASE newdvdrental;

Using the following command, you may restore the dvdrental database in tar file format created by the pg_dump tool in the PostgreSQL backup database tutorial:

pg_restore --dbname=newdvdrental 
--verbose c:pgbackupdvdrental.tar

You can use the following command to restore a database that is identical to the one from which the backup was taken:

pg_restore --dbname=dvdrental --create --verbose 
c:pgbackupdvdrental.tar

Since PostgreSQL 9.2, the —section option can be used to restore simply the table structure. This gives you the option of using the new database as a template for future databases.

Create a new database called dvdrental_tpl first.

CREATE DATABASE dvdrental_tpl;

Second, use the following command to restore the table structure from the dvdrental.tar backup file:

>pg_restore --dbname=dvdrental_tpl --section=pre-data  c:pgbackupdvdrental.tar

Method 4: Use phpPgAdmin

The following are the steps to use phpPgAdmin for importing PostgreSQL data into a database.

• Log in to your cPanel and open phpPgAdmin.
• Click on the phpPgAdmin in the DB section if you are using the Jupiter theme, on the Tools page.
• Click phpPgAdmin, if you are using the Paper Lantern theme, in the DATABASES section of the cPanel home page.
• You will be able to see the phpPgAdmin administration page in a new window. 
• In the left pane of the phpPgAdmin window, expand Servers -> expand PostgreSQL.
• Click the database name to where you are going to import the data.
• Click SQL, which is on the top menu bar. You can see the SQL link between the Schemas and locate the links.
• Press Choose File.
• Click Open after locating the import file in the file dialog-> Execute. 

Solving challenges while handling larger data sets- Using standard Unix tools

When you create large pg_dump o/p fuels, a few operating systems will give trouble because of the maximum file size limits. You can use standard Unix tools to solve this problem, as pg_dump can write to the standard output. Let’s get into the different methods:

1. Use compressed dumps 

A compression program like gzip can help:

pg_dump dbname | gzip > filename.gz

Reload with:

gunzip -c filename.gz | psql dbname

or:

cat filename.gz | gunzip | psql dbname

2. Use split command

You can use split command for splitting the o/p into small files that are acceptable in size to the underlying file system. For example, to make 1 gigabyte:

pg_dump dbname | split -b 1G - filename

Reload with:

cat filename* | psql dbname

You can use GNU split and use it to gzip together:

pg_dump dbname | split -b 2G --filter='gzip > $FILE.gz'

You can use zcat to restore it.

3. Use pg_dump’s custom dump format

The custom dump format can compress your data when it writes it to the o/p file if your PostgreSQL was built on a system where you have installed zlib compression library. This produces dump file sizes like gzip. But the benefit is that you can restore the tables selectively. 

Use the command below to dump a database using the custom dump format:

pg_dump -Fc dbname > filename

A custom-format dump should be restored with pg_restore. It is not a script for psql. The following example shows that:

pg_restore -d dbname filename

When your database is huge, you can combine split with any other method mentioned. 

4. Use pg_dump’s parallel dump feature

This feature helps to fasten the dump of a large database by dumping multiple tables parallelly. And the extent of parallelism can be controlled with the -j parameter. Keep in mind that parallel dumps are only supported for the “directory” archive format.

pg_dump -j num -F d -f out.dir dbname

pg_restore -j can also be used to restore a dump parallelly. This helps for any archive of either the “custom” or the “directory” archive mode. And that is regardless of whether it has been created with pg_dump -j.

Before wrapping this up, let’s understand the benefits of the backup as well.

Why Should You Backup Your PostgreSQL Database?

Let’s understand the purpose of carrying out PostgreSQL Import Dump. The purpose of having backups or preparing for backups is to avoid data loss and maintain continuity. The main reasons why you need to do a backup are,

• Device Failure which leads to the entire data center becoming inaccessible, causing all operating instances to become unavailable, and requiring the cluster to be rebuilt from a backup.
• Human Errors and data corruption that would put you in a precarious situation.
• Program Error, which could be a problem with the operating system or with network transfers, resulting in missing bits or bytes and unexpected outcomes.
• Compliance Requirements and Retention Policies depending on your industry type.

PostgreSQL Dump Import Challenges

The major challenges while importing PostgreSQL dumps are,

• Compatibility: PostgreSQL dumps are not always compatible with different versions of PostgreSQL. This can be a problem if you are trying to import a dump from an older version of PostgreSQL into a newer version.
• Character encoding: PostgreSQL dumps can be encoded in different character sets. If the character encoding of the dump is not compatible with the character encoding of the database that you are importing into, you may encounter errors.
• Data types: PostgreSQL dumps can contain data types that are not supported by the database that you are importing into. If this is the case, you will need to convert the data types before you can import the dump.
• Foreign keys: PostgreSQL dumps can contain foreign keys that reference tables that do not yet exist in the database that you are importing into. If this is the case, you will need to create the tables before you can import the dump.
• Unique constraints: PostgreSQL dumps can contain unique constraints that violate the unique constraints in the database that you are importing into. If this is the case, you will need to drop the unique constraints in the database before you can import the dump.

Conclusion

This article teaches you about ways to recover data with PostgreSQL Dump Import. It provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently. You get to understand that PostgreSQL dump import is not easy as using SELECT statements. In case you want to transfer data into your desired Database/destination, then Hevo Data is the right choice for you! 

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the Hevo pricing details to understand which plan fulfills all your business needs.

FAQ on PostgreSQL Dump Import