CODEWITHSUNDEEP
postgresqlamazon-web-servicesamazon-rds
BigRed247
BigRed247

Reputation: 147

Problem restoring databse between 2 RDS instances using pg_dump and pg_restore

I'm having difficulty restoring a DB to an AWS RDS Postgresql instance. Context is that i am backing up from one RDS instance and restoring to another RDS insurance. They both have the same version of Postgresql 9.6.5.

I was able to take a dump using the following command:

./pg_dump.exe -U dbuser -W -h prod-pgsql-rds.3ft5coqxjdnq.eu-west-2.rds.amazonaws.com -d devdb > c:\tmp\backup.sql

From the resulting .sql file, I then attempted a restore to another RDS instance which is also using Postgresql 9.6.5 using below command:

./pg_restore.exe -U dbuser -d testdevdb -h dev-pgsql-rds.cym8coqx52lq.eu-west-2.rds.amazonaws.com "c:\tmp\backup.sql"

*I also tried the -f switch in the above restore command instead of the " " quotes before/after the file name

But when I try to restore it to a newly created database I get the following error:

pg_restore: [archiver] input file does not appear to be a valid archive

Can anyone help? FYI, I am using PGAdmin 4 via Windows PowerShell. I have to edit some of the values in the strings above due to data sensitivity.

Upvotes: 0

Views: 808

Answers (2)

BigRed247
BigRed247

Reputation: 147

So I couldn't get psql or pg_restore to work so opted to import the .SQL file into via the SQL query tool in PGAmdin. This through up some errors so had to make several changes to the .SQL file and perform below:

  1. Commented out a couple of lines that were causing errors
  2. Elevated permissions for the user and made him the owner of for the Schema and DB properties by right-clicking on these via PGAdmin
  3. The .sql file was making several references to the user from the source RDS DB so had to do a find and replace with a user account created for the destination RDS DB. Alternatively, I could have just created a new user on the destination DB with the same username and password as the source DB and then make him the owner in ref to step 2.

Upvotes: 0

AdamKG
AdamKG

Reputation: 14046

pg_restore is only used for the other, non-plain-text output formats that pg_dump can output. For .sql dumps, you just use psql. See the docs on restoring from backups.

In a Unix env, you'd do psql [yourflags] < /tmp/backup.sql, but I'm unfamiliar with powershell and don't know if it supports < for input redirection; hopefully either it's present or you know the equivalent PowerShell syntax.

Upvotes: 1

Related Questions