Nov 17, 20265 min read

Migrate Railway Postgres to Cloud SQL

Move a Railway Postgres database into Cloud SQL in your own GCP account with pg_dump and pg_restore through Railway's public TCP proxy, with a rehearsal, a short write freeze and checks before cutover.

Illustration for Migrate Railway Postgres to Cloud SQL

Railway makes Postgres a one-click service next to the app, with the app reading it through a ${{Postgres.DATABASE_URL}} variable and the data living on a volume attached to that service. The reasons to move it tend to arrive together: a second backend needs to reach the database from GCP, the team wants backups and replicas it controls, or the rest of the stack already runs in a Google Cloud project.

This guide moves a Railway Postgres database into Cloud SQL in your own GCP account with pg_dump and pg_restore. The Cloud SQL side is set up with Encore, which provisions it from the database declared in your Go code, with the hand-built setup covered as an alternative.

Ways to move Railway Postgres to Cloud SQL at a glance

StepEncore targetHand-built Cloud SQL
1. Open a path to the dataEnable the TCP proxy, read DATABASE_PUBLIC_URLSame
2. Provision the targetsqldb.NewDatabase and a deploy to your GCP accountCloud SQL instance, users, databases and a proxy
3. Rehearse the restorepg_restore through encore db conn-uripg_restore through Cloud SQL Auth Proxy
4. Freeze writes and cut overFinal dump, restore, point traffic at the Encore environmentFinal dump, restore, change DATABASE_URL and restart

Reaching Railway Postgres from outside

The DATABASE_URL that the app uses points at Railway's private network and does not resolve from your laptop or from GCP. Enabling public networking on the Postgres service adds a TCP proxy and a DATABASE_PUBLIC_URL variable with a proxy host and port, and that URL is what every command below reads. Railway bills network egress for traffic through that TCP proxy, so dump once for the rehearsal and once for the cutover rather than in a loop.

Connect with psql "$DATABASE_PUBLIC_URL" and record what the target has to support:

~
$ psql "$DATABASE_PUBLIC_URL" -c 'SHOW server_version;'$ psql "$DATABASE_PUBLIC_URL" -c '\dx'$ psql "$DATABASE_PUBLIC_URL" -c 'SELECT pg_size_pretty(pg_database_size(current_database()));'$ psql "$DATABASE_PUBLIC_URL" -c "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 20"

The Cloud SQL major version must be the same or newer, every extension must be on the Cloud SQL for PostgreSQL extension list, and the size gives a first estimate of the freeze. A pg_dump older than the server refuses to run, so install a client that matches or exceeds version().

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

Encore

Encore brings infrastructure from code: you declare the database with sqldb.NewDatabase and a migrations folder, connect your GCP account as described in Connect your cloud account, and a deploy provisions Cloud SQL in your project. Encore provisions an IAM role for the service, or a service account on GCP. After the first deploy, encore db proxy --env=production --write opens a tunnel to the instance, and encore db conn-uri projects --env=production --write prints a connection string that can insert rows, as the database docs describe.

A coding agent can add the database declaration and the first migration in the same change. The Railway password stays in DATABASE_PUBLIC_URL on your machine or in an Encore secret, not in the repository.

Set up the app

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

~
$ encore app create projects-api --example=hello-world$ cd projects-api && mkdir -p projects/migrations$ pg_dump "$DATABASE_PUBLIC_URL" --schema-only --no-owner --no-acl > projects/migrations/1_bootstrap.up.sql

Review the generated SQL before you commit it: drop Railway role names, confirm extensions Cloud SQL supports, and keep the schema in one migration so encore run and the first deploy apply it cleanly. Deploy to a GCP environment, then restore data through the Encore proxy:

~
$ pg_dump "$DATABASE_PUBLIC_URL" --data-only --format=custom --no-owner --no-acl -f railway.dump$ encore db proxy --env=production --write$ encore db conn-uri projects --env=production --write$ pg_restore --dbname "$CLOUDSQL_URL" --data-only --no-owner --no-acl --jobs=4 railway.dump

$CLOUDSQL_URL is the output of encore db conn-uri while the proxy is running. --format=custom lets pg_restore load tables in parallel with --jobs, and --no-owner --no-acl leaves out Railway's role names, which do not exist on Cloud SQL. If pg_dump warns about circular foreign keys in a data-only dump, drop and re-create those constraints around the restore, as the pg_dump documentation explains. Empty the target with TRUNCATE ... RESTART IDENTITY CASCADE before the final run.

pg_dump includes sequence values in a data-only dump, so a sequence behind max(id) usually means the dump started before the last write, which is the reason for the freeze. Compare row counts for the large tables and pg_sequences after the restore.

With Encore, the API in the GCP environment already reads the Cloud SQL database, so the cutover is pointing DNS or the frontend's API URL at the Encore environment and leaving the Railway services at zero. Disable the TCP proxy on Railway once the copy is done, and keep the Railway database around read-only for a few days until Cloud SQL has taken its first automated backups.

Best fit: teams moving a Railway Postgres database into their own GCP account and restoring it through encore db proxy and encore db conn-uri.

Provision Cloud SQL by hand

Create a Cloud SQL for PostgreSQL instance in a private IP setup, add a database and a user, and connect with the Cloud SQL Auth Proxy from your laptop and from Cloud Run or GKE. The dump and restore commands are the same, with the proxy listen address in place of encore db conn-uri. Grant the application's service account roles/cloudsql.client and keep the instance off the public internet.

Best fit: teams that already manage Cloud SQL in Terraform or gcloud and want the restore to target an instance they already own.

How to choose

Pick Encore when you want Cloud SQL provisioned from the database declaration and a restore path through encore db proxy and encore db conn-uri. Pick a hand-built instance when Cloud SQL already exists in the project and the app will keep its own connection string. In both cases, rehearse the dump and restore once, freeze writers for the final pass, and compare row counts and sequences before cutover.

Frequently asked questions

How do I export a Railway Postgres database?

Enable public networking on the Postgres service so Railway exposes a TCP proxy, copy the DATABASE_PUBLIC_URL variable, and run pg_dump --format=custom --no-owner --no-acl against it with a pg_dump client at least as new as the server. The internal DATABASE_URL only resolves inside Railway's private network.

How do I migrate Railway Postgres to Cloud SQL with Encore?

Declare the database with sqldb.NewDatabase and a migrations directory, connect your GCP account and deploy so Encore provisions Cloud SQL in your project, then restore a data-only dump through encore db proxy and encore db conn-uri with write access, or through the Cloud SQL Auth Proxy. After cutover, point the app at the Encore environment and disable the Railway TCP proxy.

What should I check before the dump?

Record version(), installed extensions, database size and the largest tables. The Cloud SQL major version must be the same or newer, and every extension must be available on Cloud SQL. Install a pg_dump client that matches or exceeds the Railway server version.

How long does the write freeze need to be?

Long enough to take a final dump after writers are stopped, restore it, and compare row counts and sequences. Rehearse once against a throwaway instance so the final window is the restore time, not the first time you learn about a missing extension.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders