PostgreSQL dump and restore

This is a quick note on easily dumping and restoring a specific database in Postgres 14.5. This example has PostgreSQL running on localhost and psql and pg_dump are both available. Our database administrator username is “admin”, the database to dump is called “collections”. The SQL dump file will be named “collections-dump-2022-09-19.sql”.

    pg_dump --username=admin --column-inserts \
        collections >collections-dump-2022-09-19.sql

For the restore process I follow these steps

  1. Using psql create an empty database to restore into
  2. Using psql replay (import) the dump file in the new database to restoring the data

The database we want to restore our content into is called “collections_snapshot”

    psql -U dbadmin
    \c postgres
    DROP DATABASE IF EXISTS collections_snapshot;
    CREATE DATABASE collections_snapshot;
    \c collections_snapshots
    \i ./collections-dump-2022-09-19.sql
    \q

Or if you want to stay at the OS shell level

    dropdb collections_snapshot
    createdb collections_snapshot
    psql -U dbadmin --dbname=collections_snapshot -f ./collections-dump-2022-09-19.sql

NOTE: During this restore process psql will display some output. This is normal. The two types of lines output are shown below.

    INSERT 0 1
    ALTER TABLE

If you want to stop the input on error you can use the --set option to set the error behavior to abort the reload if an error is encountered.