Backup and Restore PostgreSQL Databases and Tables

Table of Contents

• Perform a full backup of a PostgreSQL database
• Restore the full backup from a stream
• Custom-format backup to use pg-restore features
  • Restore a single table from a full database dump

In its most basic form, the pg_dump command creates a database dump as a SQL script to be executed using the psql command. This functionality is perfect for the restoration of an entire database. Nonetheless, you can also use it to extract specific tables from a full database backup.

Perform a full backup of a PostgreSQL database

Run this command to create a full database backup that we will restore in further examples.

pg_dump postgres | elastio stream backup --stream-name my_pg_dump_20210807s --tag postgres:20210807

Restore the full backup from a stream

Use the restore point ID for this backup.

Note: The --tag value specified when performing the full backup is the restore point ID.

elastio rp list --tag 20210807
elastio stream restore --rp <restore_point_id> | psql

Custom-format backup to use pg-restore features

$ pg_dump -Fc postgres | elastio stream backup --stream-name my_pg_dump_20210807

# Later restore a single table
# Create a temp database
$ psql -c "CREATE DATABASE `temp`;" postgres

# Then restore it
$ elastio stream restore --rp <restore_point_id> | pg_restore -t users -d temp

Restore a single table from a full database dump

This feature restores a standard SQL dump, converts it to a custom format dump, then restores one table to a file.

elastio stream restore --rp <restore_point_id> | psql -d tempdb | pg_dump -Fc tempdb | pg_restore -t <my_table> -d <db_name>