Posgtres database migration from Heroku to Google Cloud

migrate.md

Overview

Goal is to migrate a database from Heroku to Google Cloud

Issues

• recreate extensions
• skip copying postgis spatial ref table

Environment Variables

• $DB_URI: your full connection string for the heroku database
• $PWD: is the new database password
• $PG: is the new database instance name

Create new DB

• In google cloud console:
  • Create a new postgres database instance
  • name it
    • store as $PG
  • give it a password
    • store as $PWD

Dump database to .sql file

pg_dump -Fc \
  --format=plain \
  --no-owner --no-acl \
  --quote-all-identifiers \
  -T table --exclude-table=spatial_ref_sys $DB_URI \
  | sed -E 's/(DROP|CREATE|COMMENT ON) EXTENSION/-- \1 EXTENSION/g' \
  > dump.sql

extract the gcloud email address

TEMPGCEMAIL=`gcloud sql instances describe $PG | grep Email | awk '{print $2}'`

create and setup a bucket to restore from

gsutil mb gs://restore
gsutil acl ch -u $TEMPGCEMAIL:W gs://restore
gsutil cp dump.sql gs://restore
gsutil acl ch -u $TEMPGCEMAIL:R gs://restore/dump.sql

configure ssl

• in the gcloud console for the new instance
  • Click 'allow only ssl connections'
  • Create a client certificate with a unique id of your choice
  • download the .pem files
  • move .pem files to your working dir
  • reduce permission: chmod 0400 client-key.pem

get instance ip

TEMPGCIP=`gcloud sql instances describe $PG | grep -w ipAddress | awk '{print $3}'`

authorize ip

gcloud sql instances patch $PG --authorized-networks=$TEMPGCIP

Wait a minute or 2...

Connect to DB instance

psql "sslmode=verify-ca sslrootcert=server-ca.pem sslcert=client-cert.pem sslkey=client-key.pem hostaddr=$TEMPGCIP port=5432 user=postgres dbname=postgres password=$PWD"

Recreate any extensions you may have had

CREATE EXTENSION IF NOT EXISTS "citext" WITH SCHEMA "public";
CREATE EXTENSION IF NOT EXISTS "hstore" WITH SCHEMA "public";
CREATE EXTENSION IF NOT EXISTS "pg_stat_statements" WITH SCHEMA "public";
CREATE EXTENSION IF NOT EXISTS "postgis" WITH SCHEMA "public";

Exit and do the import (answer Y)

gcloud sql import sql $PG gs://restore/dump.sql --database=postgres

remove restore bucket

gsutil rm -r gs://restore