pg_restore into a new database

Question

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:

<snip>

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 have misunderstood that too because, again, the screen was filled with errors.

---

Edit: Based on the -C linked answer I managed to restore into a cryptically named database, but when I run the rename database command (alter database rtyghjkuyhgh rename to my_app3) it empties all the data :(

---

Any help or insight, even just a point in the right direction would be very much appreciated.

Postgres version 9.5.12

Answer 1

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"

Answer 2

1) First switch to superuser su - postgres

2) then created a new database createdb myapp3 --DROP is already created

3) now change the director to backup file path i.e cd db/backups/

4) Now just run this to restore ur dump custom file

pg_restore -j 8 -U postgres -d myapp3 myapp_2018-05-27.pg.dump

5) If still unable to restore the database kindly take a fresh bkup with

pg_dump -U postgres -Fc myapp3 > myapp_2018-05-27.dump

remove .pg and try to restore again.

Answer 3

So, the answer:

1. Create a new database.

sudo -u postgres createdb -T myapp myapp5

2. Connect to a different database and run this command.

sudo -u postgres pg_restore -d some_other_db -C -O db/backups/myapp_2018-05-27.pg.dump

3. Alter the name of the database from the one dumped to the one created above.

$ psql \c some_other_db alter database ghji76yhjkj rename to myapp5

4. Run whatever scripts needed to set up users and the like.