How to use pg_restore with AWS RDS correctly to restore postgresql database

Question

I am trying to restore my Postgresql database to AWS RDS. I think I am almost there. I can get a dump, and recreate the db locally, but I am missing the last step to restore it to AWS RDS.

Here is what I am doing:

1. I get my dump

$ pg_dump -h my_public dns -U myusername -f dump.sql myawsdb

2. I create a local db in my shell called test:

create database test;

3. I put the dump into my test db

$ psql -U myusername -d test -f dump.sql

so far so good.

I get an error: psql:dump.sql:2705: ERROR: role "rdsadmin" does not exist, but I think I can ignore it, because my db is there with all the content. (I checked with \list and \connect test).

Now I want to restore this dump/test to my AWS RDS.

Following this https://gist.github.com/syafiqfaiz/5273cd41df6f08fdedeb96e12af70e3b I now should do:

pg_restore -h <host> -U <username> -c -d <database name> <filename to be restored>

But what is my filename and what is my database name?

I tried:

pg_restore -h mydns -U myusername -c -d myawsdbname test pg_restore -h mydns -U myusername -c -d myawsdbname dump.sql

and a couple of more options that I don't recall.

Most of the times it tells me something like: pg_restore: [archiver] could not open input file "test.dump": No such file or directory

Or, for the second: input file appears to be a text format dump. Please use psql.

Can somone point me into the right direction? Help is very much appreciated!

EDIT: So I created a .dump file using $ pg_dump -Fc mydb > db.dump Using this file I think it works. Now I get the error [archiver (db)] could not execute query: ERROR: role "myuser" does not exist Command was: ALTER TABLE public.users_user_user_permissions_id_seq OWNER TO micromegas;

Can I ingore that?

EDIT2: I got rid of the error adding the flags--no-owner --role=mypguser --no-privileges --no-owner

Malcolm

solved. See edits – Malcolm

if you want to formulate your own answer and mark it as correct, you can, as this might help others. Otherwise, remove the question as it's not a problem anymore. – Cloudberry

Since this helped me, I'd suggest you go with Dirk's suggestion no.1 and extract your edits to an answer, rather than just deleting your question. – Vinificator

Happy this helped. Ok, I will create an answer from the edits. – Malcolm

@Malcolm still waiting for the answer from the edits ;) – Educe

@Educe and ryanjdillon: Sorry for the delay. I posted an answer from my edits. Hope this is still useful! – Malcolm

Answer 1

Ok, since this is apparently useful to some I will post - to the best of what I remember - an answer to this. I will answer this more broadly and not too AWS-specific because a) I don't use this instance anymore and b) I also don't remember perfectly how I did this.

But I gained experience with PostreSQL and since AWS RDS was also just a postgres instance the steps should work quite similar.

Here are my recommended steps when restoring a postgreSQL DB instance:

1. Pull the backup in a .dump-format and not in .sql-format. Why? The file-size will be smaller and it is easier to restore. Do this with the following command:

pg_dump -h <your_public_dns_ending_with.rds.amazonaws.com> -U <username_for_your_db> -Fc <name_of_your_db> > name_for_your_backup.dump

2. Now you can restore the backup easily to any postgreSQL instance. In general I'd recommend to set up a fresh DB instance with a new username and new databasename. Let's say you have a DB that is called testname with superuser testuser. Then you can just do:

pg_restore --no-owner --no-privileges --role=testuser -d testname <your_backup_file.dump>

And that should restore your instance.

When restoring to AWS or to any remote postgreSQL instance you will have to specify the host with the -h-flag. So this might be something like:

pg_restore -h <your_public_dns_ending_with.rds.amazonaws.com> -p 5432 --no-owner --no-privileges --role=testuser -d testname <your_backup_file.dump>

If you have a DB-instance running on a remote linux server, the host will be be your remote IP-address (-h <ip_od_server>) and the rest will be the same.

I hope this helps. Any questions please comment and I'll try my best to help more.

Malcolm

Works excellent. Remember the database must be created before doing pg_restore – Pretend

Add --verbose to see what is going on. – Blond

Perfect! You would also need to specify a user when restoring to RDS: -U <user> Some details here docs.aws.amazon.com/dms/latest/sbs/… – Nazarius

we need to do 'export PGUSER=<db-user>' – Terat

what if <your_backup_file.dump> is stored on s3? how does your restore command work then? – Tenuis

Answer 2

This command worked for me, given your machine has access to the rds or being given access to the machine through aws security groups

psql -h your_public_dns_ending_with.us-east-2.rds.amazonaws.com -U yourusername -d db_name_to_backup_to < pg_dump_backup_file.sql

Odele