We have the issue, that we have to move a large postgreSQL database away from Amazon's AWS to Google's GCP.
Large Database: 160GB+ We only had the Snapshots of AWS
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:
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.
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.
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