Link Search Menu Expand Document
Elastio Docs

Backup and Restore PostgreSQL Databases and Tables

Table of Contents

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 <stream-name> --tag <tag:value>

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 <tag:value>
elastio stream restore --rp <elastio-rp-id> | psql

Custom-format backup to use pg-restore features 

$ pg_dump -Fc postgres | elastio stream backup --stream-name <stream-name>

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

# Then restore it
$ elastio stream restore --rp <elastio-rp-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 <elastio-rp-id> | psql -d tempdb | pg_dump -Fc tempdb | pg_restore -t <table-name> -d <db_name>