Postgresql - AWS RDS data migration to GCP Cloudsql

Question

I am trying to migrate bunch of databases from AWS RDS Postresql server to GCP Cloud SQL. Since both are postgresql engine, I thought it will be a simple solution to take pgdump from aws and do import in gcp. However I am surprised when import cloud sql failed with error complaining some roles are missing. Below are the steps which are tried

Dump of database in AWS RDS

pg_dump -h <connection_endpoint> -U root -f db_dump.sql <db_name>

Then I tried to import it in GCP Cloud sql with below command

instance-1:~$ PGPASSWORD=<passwprd> psql   --host=<host_name>  --port=5432   --username=postgres   --dbname=<db_name> < db_dump.sql
SET
SET
SET
SET
SET
 set_config 
------------

(1 row)

SET
SET
SET
CREATE SCHEMA
ERROR:  must be member of role "rdsadmin"
CREATE SCHEMA
ERROR:  must be member of role "root"
CREATE SCHEMA
ERROR:  must be member of role "root"
CREATE SCHEMA
ERROR:  must be member of role "root"
CREATE SCHEMA
ERROR:  must be member of role "root"
CREATE EXTENSION
ERROR:  must be owner of extension plpgsql
CREATE EXTENSION
COMMENT
SET
SET
CREATE TABLE...

As you can see rdsadmin and root roles are missing. How to make sure that these missing roles are present in GCP Cloud sql with correct settings because even after creating roles with same name in cloud sql it doesn't succeed? Any solution please?

Neeraj Neeraj · Accepted Answer · 2019-01-12T14:49:35

Answering my own question as I found a solution for it.

Since rdsAdmin user is created by AWS for administrative tasks on RDS cluster. Taking pg_dump without owners and restoring it without owners does the trick and I am able to perform restore in cloud sql.

pg_dump -Fc -O -h  <rds-host> -U <user> -d <db> > db.dump
pg_restore -U postgres -d <db> -h <cloudsql-host> -v --no-owner db.dump

pg_restore needs formatted compressed output to restore. To fulfil that -Fc is used in pg_dump command

Postgresql - AWS RDS data migration to GCP Cloudsql

1 Answers