How to Backup/Restore in PostgreSQL pgAdmin 4?

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.

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.

StackExchange Database Administrators Q#161857, answer score: 3