How to Backup/Restore in PostgreSQL pgAdmin 4?

Question

So I feel like this is something that should be very basic and easy to do, maybe I'm just not doing things correctly.

I want to do the equivalent of SQL Server backups. I want to create the equivalent of a full .bak file and be able to restore a database to its original state.

So far I've tried many different ways in PgAdmin4 to create a backup file from a freshly created database. I migrate my schema which creates a bunch of tables, then perform a restore on the previously created file. What I expect after the restore is for all those tables I created to be gone, I expect the database to be in the exact state that it was when I took the backup.

I've tried many different options like including the Create/Drop Database commands preserving/not saving data on backup/restore but have had no luck.

Anyone able to help me out here?

I am using commands pg_dump mydb -Fc > db and pg_restore db. Maybe there's some magic parameters I'm missing for it to do what I'm asking above?

Tried pg_restore --clean db, same results.

The problem is I create my database, fresh with nothing in it, take a backup of it, create some tables, restore the backup, and the tables I created are still there. I want a restore method that puts the db in the same state as it was when it was backed up.

Answer 1

_{Community wiki answer:}

As a workaround, forget pgAdmin, just run pg_dump from the command line.

See 25.1. SQL Dump in the Backup and Restore documentation.

Also from the manual entry for pg_restore:

-c
--clean

Clean (drop) database objects before recreating them. (Unless --if-exists is used, this might generate some harmless error messages, if any objects were not present in the destination database.)

-C
--create
Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.

When this option is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands. All data is restored into the database name that appears in the archive.

...or simply manually drop the database before you restore it.

From Why is pg_restore returning successfully but not actually restoring my database? on Stack Overflow, you also need to specify a --dbname when connecting, or it won't restore anything at all.

Answer 2

You may find the docs useful

It sounds to me like pg_dump is "The Backup Dialog" and pg_dumpall is "The Backup Server Dialog".

• The Backup Dialog

  Using the pg_dump utility, pgAdmin provides an easy way to create a backup in a plain-text or archived format. You can then use a client application (like psql or the Query Tool) to restore a plain-text backup file, or use the Postgres pg_restore utility to restore an archived backup. The pg_dump utility must have read access to all database objects that you want to back up.

• The Backup Server Dialog

  Use the Backup Server dialog to create a plain-text script that will recreate the selected server. You can use the pgAdmin Query Tool to play back a plain-text script, and recreate the server.

Answer 3

I believe the --clean parameter that you found helpful in pg_restore translates to the "Clean before restore" option in the "Restore options" tab of the pgAdmin 4 restore dialog.

This option is necessary if you want the restore process to drop any existing tables/data before restoring your backup.

• Tigerware: Sadly the Clean before restore does nothing.