pg_restore into a new database

I'm using Heroku's Postgres option and have a backup I downloaded from it. I'd like to restore it into a fresh database so I can look at it. I've been trying different commands to little avail. My first try:

$ sudo -u postgres psql < db/backups/myapp_2018-05-27.pg.dump 
The input is a PostgreSQL custom-format dump.
Use the pg_restore command-line client to restore this dump to a database.

So I try:

$ sudo -u postgres pg_restore -d myapp3 -C db/backups/myapp_2018-05-27.pg.dump
pg_restore: [archiver (db)] connection to database "myapp3" failed: FATAL:  database "myapp3" does not exist

I don't really understand the -C option.


-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.

It would seem logical to me to restore into the new database. I digress… (and perhaps that is properly explained here)

I tried creating a fresh database from the template of another, earlier one with the same schema:

$ sudo -u postgres createdb -T myapp myapp3

$ sudo -u postgres pg_restore -d myapp3 -C db/backups/myapp_2018-05-27.pg.dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:

WARNING: errors ignored on restore: 5

I get a lot of errors about a user that doesn't exist and no data inserted.

I also tried using template0 but that brings up errors about the schema, as I'd expect.

In summary, I'd like to restore from a Heroku Pg backup into a fresh database. I don't know how I can avoid errors about indexes and missing users etc and have the schema ready.

I also tried -O because of this line in the docs:


With -O, any user name can be used for the initial connection, and this user will own all the created objects.

I seem to

Let's define a couple of variables to make the rest easier to copy/paste (using names from the original question)

old_db=myapp
new_db=myapp3
db_dump_file="backups/myapp_2018-05-27.pg.dump"


The following assumes that your backup was created with the "custom" format like this:

pg_dump -U postgres -F custom "$old_db" > "$db_dump_file"


To restore $db_dump_file to a new database name $new_db :

dropdb -U postgres --if-exists "$new_db"
createdb -U postgres -T template0 "$new_db"

pg_restore -U postgres -d "$new_db" "$db_dump_file"


StackExchange Database Administrators Q#207993, answer score: 12