How to Migrate Heroku Postgres to Google Cloud SQL

Heroku Postgres hands every app a DATABASE_URL, and most code on Heroku is written around that one variable: a connection pool opened from it at startup, sslmode=require appended, and a backup schedule Heroku runs for you. Cloud SQL for PostgreSQL takes over all of that once the app moves to Google Cloud, but the instance lives on a private network, the connection goes through a connector or a Unix socket, and the dump has to arrive with its extensions intact.

This guide moves the database on its own: the checks to run against Heroku first, the restore with either Encore or the Cloud SQL Auth Proxy, what replaces DATABASE_URL in the code, and the cutover. The rest of a Heroku app, dynos and Scheduler included, can follow on Cloud Run afterwards.

Ways to get Heroku Postgres onto Cloud SQL at a glance

Checks to run against Heroku first

Version. heroku pg:info -a your-app shows the PostgreSQL version and data size. The Cloud SQL instance needs the same major version or a newer one, and pg_restore on the machine doing the restore must be at least as new as the server that wrote the dump. For more than a few gigabytes, run the restore from a Compute Engine VM in the target region instead of a laptop.

Extensions. List them with heroku pg:psql -a your-app -c "SELECT extname, extversion, extnamespace::regnamespace FROM pg_extension" and compare the names with the Cloud SQL extension list for your version. Cloud SQL has no true superuser, so COMMENT ON EXTENSION errors during the restore are expected and harmless.

The heroku_ext schema. The third column of that query matters here. Between August 2022 and mid 2023 Heroku required extensions to live in a schema called heroku_ext, and databases created in that window still have them there, as Heroku's note on extension schemas explains. A dump from such a database refers to heroku_ext, so the target needs CREATE SCHEMA IF NOT EXISTS heroku_ext; before the restore.

Ownership. Objects on Heroku belong to a generated user, so restore with --no-owner --no-acl, as Google's pg_restore import guide also requires for dumps from outside Cloud SQL.

How do you migrate Heroku Postgres to Cloud SQL with Encore?

Encore brings infrastructure from code to a Go or TypeScript app and provisions it in your own GCP account: the database the Heroku app used to read from DATABASE_URL becomes a declaration in the service, and Encore creates a Cloud SQL for PostgreSQL instance for it in a private network with daily backups and point-in-time recovery, as listed in the GCP infrastructure docs. Existing database/sql code keeps working through db.Stdlib().

Set up the app

The schema comes from Heroku as a schema-only dump, which becomes the first migration:

Clean up the file before committing it: drop COMMENT ON EXTENSION lines and any grants to the Heroku user, and make the heroku_ext schema creation idempotent if the dump has one. Trimmed down, it looks like this:

-- pg_dump --schema-only output from Heroku, cleaned up
CREATE SCHEMA IF NOT EXISTS heroku_ext;
CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA heroku_ext;

CREATE TABLE public.orders (
    id bigserial PRIMARY KEY,
    customer_email text NOT NULL,
    status text NOT NULL DEFAULT 'pending',
    created_at timestamptz NOT NULL DEFAULT now()
);

The repository code from the Heroku app stays as it is, since it only needs a *sql.DB:

// store/repo.go (unchanged from the Heroku app)
package store

import (
	"context"
	"database/sql"
)

type Order struct {
	ID     int64  `json:"id"`
	Email  string `json:"email"`
	Status string `json:"status"`
}

type Repo struct{ db *sql.DB }

func NewRepo(db *sql.DB) *Repo { return &Repo{db: db} }

func (r *Repo) Order(ctx context.Context, id int64) (*Order, error) {
	var o Order
	err := r.db.QueryRowContext(ctx,
		`SELECT id, customer_email, status FROM orders WHERE id = $1`, id,
	).Scan(&o.ID, &o.Email, &o.Status)
	if err != nil {
		return nil, err
	}
	return &o, nil
}

The change is in where that *sql.DB comes from: the service declares the database and hands the existing code a standard library handle to it:

// store/store.go
package store

import (
	"context"
	"database/sql"
	"errors"

	"encore.dev/beta/errs"
	"encore.dev/storage/sqldb"
)

// Replaces sql.Open("pgx", os.Getenv("DATABASE_URL")). Cloud SQL for
// PostgreSQL in your GCP project, a local Postgres with `encore run`.
var db = sqldb.NewDatabase("app", sqldb.DatabaseConfig{
	Migrations: "./migrations",
})

//encore:service
type Service struct {
	repo *Repo
}

func initService() (*Service, error) {
	// The existing repository code keeps working on a *sql.DB.
	return &Service{repo: NewRepo(db.Stdlib())}, nil
}

//encore:api public method=GET path=/orders/:id
func (s *Service) GetOrder(ctx context.Context, id int64) (*Order, error) {
	o, err := s.repo.Order(ctx, id)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, &errs.Error{Code: errs.NotFound, Message: "order not found"}
	}
	return o, err
}

There is no DATABASE_URL left to set, rotate or paste into a secret, since Encore supplies the connection in every environment, and encore run starts Postgres locally and applies the bootstrap migration, which also proves the cleaned dump is valid SQL before it reaches GCP. A coding agent that adds a refunds table later writes 2_add_refunds.up.sql next to the bootstrap file and tests it against that local database, and the next deploy applies it to Cloud SQL.

Deploy and restore the data

Connect your GCP account in the Encore dashboard as described in Connect your cloud account, create an environment on GCP and push. Encore creates the Cloud SQL instance and applies the bootstrap migration, after which only the rows are missing. encore db conn-uri prints a temporary connection string for a cloud environment, as covered in the database docs, and the data-only restore goes there inside the cutover window:

Maintenance mode and scaling workers to zero stop new writes, but requests and transactions that already started can still commit. heroku pg:ps shows those connections; wait until they have finished before capturing the final backup, so the dump cannot miss an order written during the cutover. --single-transaction means a failed restore leaves the tables empty instead of half full, so it can run again. If you would rather size the Cloud SQL instance yourself and restore the whole dump into it first, create the environment with an imported instance instead, as described in Import an existing Cloud SQL instance, and mark the bootstrap migration as applied there.

Best fit: teams moving a Heroku app to their own GCP account who want Cloud SQL created and connected from the code, with the Heroku schema as the starting migration for every new environment.

Cloud SQL by hand with the Auth Proxy

Create the instance and database, then connect from the restore machine through the Cloud SQL Auth Proxy, which authenticates with your gcloud credentials so the instance needs no authorized networks:

Heroku backups are custom-format archives, which is the format pg_restore --jobs can parallelize, as the Heroku import and export guide notes. The app side is then a new DATABASE_URL with host=/cloudsql/PROJECT:REGION:INSTANCE, stored in Secret Manager and passed to a Cloud Run service started with --add-cloudsql-instances, plus roles/cloudsql.client for the runtime service account, following the Cloud Run connection guide. Staging needs its own instance, user, secret and binding, and the restored roles and grants are yours to recreate.

Best fit: teams that already manage Cloud SQL in Terraform, or databases that need instance flags, sizing or replicas set before the first byte arrives.

The cutover

1. Rehearse the restore against a copy and time it, since the final run happens with writes stopped.
2. Run heroku maintenance:on, which blocks HTTP traffic to web dynos. The maintenance mode docs point out that worker dynos keep running, so scale them to zero as well.
3. Capture and download a fresh backup, restore it, and run ANALYZE; on Cloud SQL so the planner has statistics.
4. Compare contents as well as row counts: for a key table, SELECT count(*), max(id), md5(string_agg(id::text || status, ',' ORDER BY id)) FROM orders; on both sides catches truncated and reordered data, and SELECT last_value FROM orders_id_seq; confirms the sequence continued.
5. Send traffic to the new deployment and keep the Heroku database untouched for a few days, so rolling back is a matter of pointing the old app at it again.

How to choose

Pick Encore when the app is moving to your own GCP account and you want Cloud SQL, its private network and the connection created from the service code, with the Heroku schema carried forward as a migration. Pick Cloud SQL by hand when an infrastructure team already owns the instance layout, or when the database must be sized and configured before the restore.

Related guides

• How to migrate Heroku Postgres to Amazon RDS
• How to connect Cloud Run to Cloud SQL
• What is Cloud SQL?
• RDS vs Cloud SQL
• Heroku alternatives