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