Move PostgreSQL AWS RDS to Google Cloud SQL

We have the issue, that we have to move a large postgreSQL database away from Amazon's AWS to Google's GCP.

The Problems where:

Large Database: 160GB+ We only had the Snapshots of AWS

Get a snapshot out of RDS into Storage

To do this we created a new compute engine and connected to it via ssh. We want to get the dump file direct to a new bucket in storage. So we have to enable the bucket as new volume of the machine:

gcloud init

You can either Login or use the Service Account, but keep in mind that the Service Account needs the rights to create a bucket.

gsutil mb gs://my-new-bucket/

Now we have to mount the Bucket to our machine. For this wie use Cloud Storage FUSE, to install it we need the following steps:

export GCSFUSE_REPO=gcsfuse-lsb_release -c -s
echo "deb http://packages.cloud.google.com/apt $GCSFUSE_REPO main" | sudo tee /etc/apt/sources.list.d/gcsfuse.list
curl https://packages.cloud.google.com/apt/doc/apt-key.gpg | sudo apt-key add -
sudo apt-get update
sudo apt-get install gcsfuse

And now we can finally mount it

gcsfuse db /mnt/gcs-bucket

So we have a place to store the dump, what next? We have to install the same PostgresQL Version on the Machine as the remote Server is, to get a working pg_dump

echo "deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main 9.5" | sudo tee /etc/apt/sources.list.d/postgresql.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
apt-get install postgresql-9.5
sudo apt-get install postgresql-9.5

Now we finally can do the dump:

pg_dump -h yourRDS.rds.amazonaws.com -p 5432 -F c -O -U postgres DATABASE > /mnt/gcs-bucket/db.dump

Depending on how large your Database is this will take quite a while. Whats next, create your SQL Instance on GCP. There is an import function for SQL Files out of the Bucket but sadly not for dumps, so we have to do the restore the hard way.

pg_restore -h YourNewSQLInstanceIP -n public -U postgres-user -d DATABASE -1 /mnt/gcs-bucket/db.dump

This will even take longer, be sure to whitelist the IP of the Compute Engine, that it can have access to the SQL Instance.

I did everything like you told me but i receive weird errors

Something like this?

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4198; 0 0 ACL children_of(integer) 
postgrespg_restore: [archiver (db)] could not execute query: ERROR: role "user" does not exist Command was: REVOKE ALL ON FUNCTION children_of(root_id integer) FROM PUBLIC;

Easy to answer, you have missing users on your new Database that are referenced by in the Dump.

How to avoid this?

Easy to answer, create the users. Sadly you can't export them due some regulations RDS that makes it impossible to do a pg_dumpall -g (Only User and Roles)

pg_dumpall -h yourRDS.cd8cncmdv7f0.eu-central-1.rds.amazonaws.com -g  -p 5432  -U postgres > /mnt/gcs-bucket/db_roles.dump

This do not work and you will receive the error

pg_dumpall: query failed: ERROR:  permission denied for relation pg_authid
pg_dumpall: query was: SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, rolbypassrls, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid ORDER BY 2

Because AWS RDS don't do the query as the superuser and so you cannot export it. However if you create them manually it will work fine

till next time