Migrate postgres dump to RDS

Question

I have a Django postgres db (v9.3.10) running on digital ocean and am trying to migrate it over to Amazon RDS (postgres v 9.4.5). The RDS is a db.m3.xlarge instance with 300GB. I've dumped the Digital Ocean db with:

sudo -u postgres pg_dump -Fc -o -f /home/<user>/db.sql <dbname>

And now I'm trying to migrate it over with:

 pg_restore -h <RDS endpoint> --clean -Fc -v -d <dbname> -U <RDS master user> /home/<user>/db.sql

The only error I see is:

  pg_restore: [archiver (db)] Error from TOC entry 2516; 0 0 COMMENT EXTENSION plpgsql
  pg_restore: [archiver (db)] could not execute query: ERROR:  must be owner of extension plpgsql
  Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

Apart from that everything seems to be going fine and then it just grinds to a halt. The dumped file is ~550MB and there are a few tables with multiple indices, otherwise pretty standard.

The Read and Write IOPS on the AWS interface are near 0, as is the CPU, memory, and storage. I'm very new to AWS and know that the parameter groups might need tweaking to do this better. Can anyone advise on this or a better way to migrate a Django db over to RDS?

Edit:

Looking at the db users the DO db looks like:

Role Name   Attr                                           Member Of
<user>      Superuser                                      {}
postgres    Superuser, Create role, Create DB, Replication {}

And the RDS one looks like:

Role Name      Attr                     Member Of
<user>        Create role, Create DB    {rds_superuser}
rds_superuser Cannot login              {}
rdsadmin      ...                        ...

So it doesn't look like it's a permissions issue to me as <user> has superuser permissions in each case.

Solution for anyone looking:

I finally got this working using:

cat <db.sql> | sed -e '/^COMMENT ON EXTENSION plpgsql IS/d' > edited.dump
psql -h <RDS endpoint> -U <user> -e <dname> < edited.dump

It's not ideal for a reliable backup/restore mechanism but given it is only a comment I guess I can do without. My only other observation is that running psql/pg_restore to a remote host is slow. Hopefully the new database migration service will add something.

I've now changed RDS to use the same postgres version but still no luck! I've tried directly trying to pipe in a non custom format dump with psql which also fails to work. Please help! — Silian Rails, Mar 31 '16 at 12:16

score 0 · Answer 1 · answered Mar 31 '16 at 13:19

0

Considering your dumped DB file is of ~550MB, I think using the Amazon guide for doing this is the way out. I hope it helps.

Importing Data into PostgreSQL on Amazon RDS

answered Mar 31 '16 at 13:19

Moses Koledoye

77,341
8
133
139

That's the guide I'm following. However it still hangs and eventually just seems to timeout. I don't know if I've set the suggested parameters correctly. – Silian Rails Mar 31 '16 at 13:21
You are having privilege issues on your local machine. See http://stackoverflow.com/questions/13410631/how-to-solve-privileges-issues-when-restore-postgresql-database – Moses Koledoye Mar 31 '16 at 13:22
I looked at the permissions and have edited the question to show you what \du looks like from psql. It doesn't look like the same issue mentioned in that post as is already superuser on each db (or at least an rds_superuser which is the best I can do on RDS). – Silian Rails Mar 31 '16 at 14:21
Try running the `pg_restore` command as root. If that also fails, you can try connecting to your AWS RDS instance via pgAdmin, and from there run a restore. To do this your local machine should be able to access the remote DB via the DB's security group on AWS. Let's see how these go. . . – Moses Koledoye Mar 31 '16 at 15:31

score 0 · Answer 2 · answered Apr 05 '16 at 08:04

I think it did not halt. It was just recreating indexes, foreign keys etc. Use pg_restore -v to see what's going on during the restore. Check the logs or redirect output to a file to check for any errors after import, as this is verbose.

Also I'd recommend using directory format (pg_dump -v -Fd) as it allows for parallel restore (pg_restore -v -j4).

You can ignore this ERROR: must be owner of extension plpgsql. This is only setting a comment on extension, which is installed by default anyway. This is caused by a peculiarity in RDS flavor of PostgreSQL, which does not allow to restore a database while connecting as postgres user.

Migrate postgres dump to RDS

2 Answers2

Linked