Move PostgreSQL AWS RDS to Google Cloud SQL
Tags: googlecloud, postgres, aws, devops
Published: 2019-03-03 18:25:05

---
title: Move PostgreSQL AWS RDS to Google Cloud SQL
published: true
description: 
tags: Google Cloud, PostgreSQL, AWS, devops
cover_image: https://thepracticaldev.s3.amazonaws.com/i/tmqllq00l9u7akye7arn.jpg
---

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