How to Backup and Restore PostgreSQL Databases with pg_dump

PostgreSQL is a robust, open-source relational database mostly used for web applications and data storage solutions. Ensuring data recovering from unexpected losses is essential, and PostgreSQL offers powerful tools for backup and restoration. In this guide, we’ll walk through the process of backing up and restoring PostgreSQL databases using the pg_dump and pg_restore commands.

Creating a Sample Database

To walk through this backup and restore process with PostgreSQL, let’s first create a sample database called shop_inventory, populate it with some tables and data, and then demonstrate the pg_dump and pg_restore commands in real-world scenarios.

If PostgreSQL is not installed, you can do it with:

sudo apt install postgresql -y

After the installation is complete, switch to the postgres user:

sudo -i -u postgres

Then, start by connecting to PostgreSQL:

psql -U postgres

Inside the PostgreSQL prompt, create the shop_inventory database:

CREATE DATABASE shop_inventory;

Generate Tables and Insert Sample Records

Establish a connection to the shop_inventory database:

\c shop_inventory;

Then create the tables: customers, products, and orders.

CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);

CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
name VARCHAR(100),
price NUMERIC(10, 2)
);

CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
product_id INT REFERENCES products(product_id),
quantity INT,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Populate these tables with sample data:

INSERT INTO customers (name, email) VALUES
('Alice Johnson', '[email protected]'),
('Bob Smith', '[email protected]');

INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Smartphone', 800.00);

INSERT INTO orders (customer_id, product_id, quantity) VALUES
(1, 1, 2),
(2, 2, 1);

Now that we have our shop_inventory database set up, we’re ready to back up and restore it.

Backup a Single PostgreSQL Database

The pg_dump command enables you to create backups in various formats, which can be restored using pg_restore or psql. The following examples demonstrate backing up our shop_inventory database using different options.

pg_dump -U [username] -d [database_name] -f /path/to/backup_file.sql

For shop_inventory database:

mkdir backups &&
pg_dump -U postgres -d shop_inventory -F c -f backups/shop_inventory_backup.custom

• -F c specifies the format of the backup. -F stands for "format," and c denotes "custom format." The custom format is specific to PostgreSQL and creates a compressed, non-textual backup file. This format is useful for selective restoration because it allows pg_restore to restore individual tables or objects from the backup.

This command creates a PostgreSQL file containing the structure and data of the shop_inventory database.

Full Databases Backup (Full Instance Backup)

The pg_dumpall command can back up all databases, roles, and configurations in a single file.

pg_dumpall -U [username] -f /path/to/all_databases_backup.sql

Example:

pg_dumpall -U postgres -f backups/full_postgres_backup.sql

This creates a file in SQL format that includes all databases, allowing you to restore the entire PostgreSQL setup.

Backup a Remote PostgreSQL Database

To back up a database hosted on a remote server, use the -h option with pg_dump to specify the host.

pg_dump -U [username] -h [host] -d [database_name] -f /path/to/backup_file.sql

Example for shop_inventory on a remote server:

pg_dump -U postgres -h remote_host -d shop_inventory -f backups/remote_shop_inventory_backup.sql

Make sure the remote server allows connections and that the user has sufficient privileges.

Restore an Individual PostgreSQL Database

To restore a backup, it’s often necessary to drop the existing tables to avoid conflicts, especially if the table structures or data have changed. Here’s a guide on how to drop tables in shop_inventory and then restore them from a backup.

You can drop all tables in shop_inventory either manually or with a command that removes all tables at once. This example shows how to drop all tables using a single psql command.

psql -U postgres -d shop_inventory

In the psql prompt, run the following command to generate a list of DROP TABLE statements and execute them:

DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

This block drops each table in the public schema, along with any dependencies. Exit the psql prompt by typing:

\q

Now that the tables have been dropped, you can restore the shop_inventory database from your backup file.

Execute pg_restore to restore the database. Ensure that your backup was created with pg_dump using the -F c (custom) or -F t (tar) option.

pg_restore -U [username] -d [database_name] -1 /path/to/backup_file

Example:

pg_restore -U postgres -d shop_inventory -1 backups/shop_inventory_backup.custom

• -1 executes the restore in a single transaction, which is helpful for rollback in case of an error.

The pg_restore command is employed to restore non-SQL format backups (e.g., custom or .tar format).

pg_restore -U [username] -d [database_name] -1 /path/to/backup_file

Restore All PostgreSQL Databases

For a full restore, you usually work with a backup created by pg_dumpall, which includes all databases, roles, and configurations. Before performing a full restore, you might want to drop and recreate all existing databases to avoid conflicts.

For our example, let’s drop the database shop_inventory then restore it:

psql -U postgres
DROP DATABASE shop_inventory

If you backed up all databases with pg_dumpall, use psql to restore:

psql -U postgres -f backups/full_postgres_backup.sql

This command restores every database, role, and configuration as they were at the time of the backup.

Restore a PostgreSQL Database from a Remote Server

To restore a backup to a remote server, use the -h option with pg_restore or psql.

pg_restore -U [username] -h [host] -d [database_name] -1 /path/to/backup_file

For SQL files, use psql:

psql -U [username] -h [host] -d [database_name] -f /path/to/backup_file.sql

Make sure that network permissions and user access are configured correctly for the remote server.

Conclusion

By following these commands, you can back up and restore a PostgreSQL database like shop_inventory, ensuring data safety for your applications. Regular backups are vital, and testing your restore process is equally important to minimize downtime and data loss. With these tools, you can confidently manage PostgreSQL data in any scenario.

Hostman provides pre-configured and ready-to-use cloud databases, including cloud PostgreSQL.