Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

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

create database test;

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

like image 966
Micromegas Avatar asked Jun 05 '19 14:06

Micromegas


People also ask

What is Pg_restore in Postgres?

pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats. It will issue the commands necessary to reconstruct the database to the state it was in at the time it was saved.

Which AWS RDS feature will allow you to restore your database?

You can restore a DB instance to a point in time using the AWS Management Console, the AWS CLI, or the RDS API. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/ . In the navigation pane, choose Automated backups.


1 Answers

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

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

like image 60
Micromegas Avatar answered Sep 16 '22 11:09

Micromegas