Leaving Cloud SQL: How We Moved 11 Postgres Databases onto One CloudNativePG Cluster

Muhammad Adnan Sarfraz — Fri, 16 Oct 2026 07:18:22 +0000

A practical playbook: one shared cluster, strict per-app isolation, a zero-disk copy pipeline, reversible cutovers, and a restore test that told us exactly how much data we could lose.
For a long time our microservice databases lived on Google Cloud SQL. It worked. But every one of our apps ran on Kubernetes in a different cloud, which meant every single query crossed the public internet, depended on cross-cloud firewall rules, and broke in surprising ways the day we changed our egress IPs.

So we brought the data home. Over about two weeks in September 2026, we moved eleven application databases (roughly 1.3 GB in total) off Cloud SQL and onto a single PostgreSQL 17 cluster run by CloudNativePG inside our production Kubernetes cluster.

This post is the playbook we ended up with: the design decisions, the exact commands, the mistakes we nearly made, and what's still on our list.

Why move at all?

Three reasons, in order of how much they hurt:
1. Keep data next to the apps. Every consumer of these databases ran in the same Kubernetes environment. A database in another cloud meant internet latency on every query, plus a fragile dependency on firewall allow-lists. When we migrated some services to new egress IPs, parts of the cluster simply lost their route to Cloud SQL.
2. One platform to run. **Storage, access, monitoring and deploys all go through Kubernetes, the same way as the applications. No separate console, no separate mental model.
**3. Cost and consolidation. One shared cluster instead of one managed instance per service.

What CloudNativePG gives you

CloudNativePG (CNPG) is a Kubernetes operator for PostgreSQL. You declare a single Cluster resource and the operator creates and looks after everything else: the Postgres pods, persistent volumes, TLS certificates (auto-renewed), and a set of Services:

<cluster>-rwalways points at the primary. This is what apps should use.
<cluster>-ro points at replicas only.
<cluster>-r points at any instance.

You can also ask CNPG to manage extra Services, such as a LoadBalancer for clients outside the cluster, so you never hand-edit them.

The design decisions that mattered
1. One shared cluster, many isolated databases

We chose a single cluster that holds every microservice database, instead of one cluster per service. Each application gets:

its own database,
its own login role, which owns that database (so it has full DDL inside it and nothing outside it),
and a single pg_hba rule that lets that role reach only that database.

Nothing is shared between apps except the server itself.

The app roles are plain LOGIN roles: no SUPERUSER, no CREATEDB, no CREATEROLE. For humans and tools like DBeaver there's a separate db_admin role with CREATEDB, CREATEROLE, pg_read_all_data, pg_write_all_data and pg_monitor. It still isn't a superuser, and it is only allowed in over TLS. The real postgres superuser is reachable only via kubectl exec into the pod (enableSuperuserAccess: false).

Isolation lives in pg_hba, and it's declarative

The access rules sit right in the Cluster spec:

spec:
  postgresql:
    pg_hba:
      - host survey_db      survey_user      all scram-sha-256
      - host payroll_db     payroll_user     all scram-sha-256
      - host finance_db     finance_user     all scram-sha-256
      - host scheduler_db   scheduler_user   all scram-sha-256  # reserved, db not created yet
      - hostssl all         db_admin         all scram-sha-256

Two things we learned:

A rule for a database or role that doesn't exist yet does nothing, so reserving lines ahead of time is harmless.
Never add a blanket host all all rule. One convenient line would quietly undo the isolation for every app.

When you change pg_hba and apply the Cluster object, CNPG reloads it without restarting Postgres.

Every database is added the same way

CNPG has a bootstrap import feature, but it only runs once, when a cluster is first created. Since we were adding databases one service at a time, we deliberately didn't use it. Every database goes through the same three steps instead:

Create the role and an empty database.
Load data with pg_dump | psql straight from Cloud SQL.
Switch the app.

Boring and repeatable beats clever.

Pin the image, and make storage outlive mistakes The image is pinned to an exact version, ghcr.io/cloudnative-pg/postgresql:17.6. Never a floating tag like :17, which can change under you on the next pod restart. We created a custom StorageClass with reclaimPolicy: Retain, so the data survives even if someone deletes a PersistentVolumeClaim. Data and WAL get separate volumes (200 Gi and 50 Gi). The StorageClass allows expansion, so volumes can grow later (they can never shrink).

Here's a trimmed version of the cluster definition:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres
  namespace: service-db
spec:
  instances: 1
  imageName: ghcr.io/cloudnative-pg/postgresql:17.6
  enableSuperuserAccess: false

  storage:
    size: 200Gi
    storageClass: db-retain-fast
    resizeInUseVolumes: true
  walStorage:
    size: 50Gi
    storageClass: db-retain-fast

  resources:
    requests: { cpu: "2", memory: 4Gi }
    limits:   { cpu: "2", memory: 4Gi }

  postgresql:
    parameters:
      max_connections: "200"
      shared_buffers: 2GB
      effective_cache_size: 6GB
      maintenance_work_mem: 1GB
      log_connections: "on"
      log_disconnections: "on"
      log_min_duration_statement: "1000"   # log queries slower than 1s
    pg_hba:
      - host survey_db survey_user all scram-sha-256
      # ...one line per app...

  managed:
    services:
      additional:
        - selectorType: rw
          serviceTemplate:
            metadata:
              name: postgres-external
            spec:
              type: LoadBalancer
              # restrict who can reach it!
              loadBalancerSourceRanges:
                - 203.0.113.10/32

A note on memory: our first version set shared_buffers equal to the memory request with a higher limit. That works until the node is short on memory and Postgres gets OOM-killed. Setting requests equal to limits (Guaranteed QoS) is the safer choice for a database, which is what the snippet above shows.

Step 1: Create the role, database and Secret

We wrapped database creation in a small idempotent script, create-db.sh <database> <role> <secret-name>. It:

generates a random 64-hex-character password,
stores it in a Kubernetes Secret (username, password),
creates the role and the database owned by it,
tests a real network login through the -rw Service with TLS.

Because it's idempotent, re-running it is also the fix when a password drifts (more on that in troubleshooting).

To read a password later, without ever pasting it into a ticket or chat:

kubectl -n service-db get secret <secret-name> \
  -o jsonpath='{.data.password}' | base64 -d

Step 2: Copy the data without touching disk

This is the part I'm most happy with. The dump runs inside the Postgres pod itself, which already has pg_dump 17 and can reach Cloud SQL. It pipes straight into the new database, so nothing is written to disk, and passwords go in through stdin, never on the command line (where they'd show up in ps and shell history).


K="kubectl -n service-db"
NPW=$($K get secret <secret-name> -o jsonpath='{.data.password}' | base64 -d)

printf '%s\n%s\n' '<cloudsql-password>' "$NPW" | \
$K exec -i postgres-1 -c postgres -- bash -c '
  read -r SRC; read -r DST; set -o pipefail
  PGPASSWORD="$SRC" pg_dump "host=<cloudsql-ip> user=<cloudsql-user> dbname=<source-db>" \
      --no-owner --no-acl --no-publications --no-subscriptions \
    | grep -v "^SET transaction_timeout" \
    | PGPASSWORD="$DST" psql "host=localhost user=<role> dbname=<db> sslmode=require" \
      -v ON_ERROR_STOP=1 -q --single-transaction \
  && echo RESTORE OK'

unset NPW

Every flag is there for a reason:

--no-owner --no-acl: objects are created as the new app role, and Cloud SQL's own roles and grants (hello, cloudsqlsuperuser) are dropped instead of failing the restore.
grep -v "^SET transaction_timeout": pg_dump 17 writes this setting into the dump, and servers older than 17 reject it. Filtering it keeps the pipeline portable in both directions.
--single-transaction with ON_ERROR_STOP=1: all or nothing. A failed restore leaves the database empty, so it's always safe to re-run.

Then verify. Compare count(*) for every table and the alembic_version row (or whatever your migration tool uses) between source and target. Don't trust "it finished without errors".

At cutover, re-copy with writers stopped. The first copy is a rehearsal. For the real thing, stop every writer, then drop and reload with pg_dump --clean --if-exists and SET lock_timeout = '30s' so a forgotten idle session can't hang the whole operation.

Step 3: Switch the app (and keep the way back)

Our apps read their connection string from a DATABASE_URL in a Secret in their own namespace. The switch is:

Save the current URL somewhere private (a file with mode 0600). This is your rollback.
Patch the app's Secret with the new URL, keeping the app's URL scheme (postgresql:// vs postgresql+psycopg:// matters to some drivers):

inside the cluster: postgresql://<role>:<pw>@postgres-rw.service-db.svc:5432/<db>?sslmode=require
outside the cluster: postgresql://<role>:<pw>@<load-balancer-ip>:5432/<db>?sslmode=require
Restart: kubectl -n <ns> rollout restart deploy/<app> and wait on rollout status.
Verify: logs show a clean startup with no DB errors, and pg_stat_activity shows the role connected.
Rollback if needed: put the saved URL back and restart. We scripted this for the services we switched in bulk.

Here's a handy one-liner to see where an app currently points, with the password masked:

kubectl -n <ns> get secret <app-secret> -o jsonpath='{.data.DATABASE_URL}' \
  | base64 -d | sed -E 's#://([^:]+):[^@]+@#://\1:***@#'

A real example

For our finance service, the whole run looked like this:

The pg_hba rule was already in place (reserved earlier).
create-db.sh created the role, database and Secret, and tested login and DDL over TLS.
We restored from Cloud SQL (PostgreSQL 16) into the new PostgreSQL 17 database. Every object ended up owned by the app role; row counts matched.
The service runs in two Kubernetes clusters, so before switching the second one we launched a throwaway postgres:17-alpine pod there and read the data through the LoadBalancer. That proved the network path before any real traffic depended on it.
We patched DATABASE_URL in both clusters and restarted. Migrations ran, both pods came up, "Application startup complete".
The old URLs went into a 0600 backup file, so rollback was one patch away.

One honest loose end from that run: the app had been talking to one Cloud SQL endpoint, but our dump account used a different one. If those turn out to be different instances, the data could differ. The lesson: confirm the source you dump from is the source the app actually writes to, before you decommission anything.

Backups: configure them, then prove they work

The migration isn't done until you can restore. We set up CNPG's backups to S3-compatible object storage:

a daily base backup at 02:00 UTC via a ScheduledBackup,
continuous WAL archiving (gzip), so we can restore to any point in time since the first backup,
retentionPolicy: 30d, which lets CNPG itself delete base backups and WAL that are no longer needed. (If you also add a bucket lifecycle rule, make it comfortably longer than the retention window, or the bucket will delete WAL that CNPG still needs.)

apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: postgres-daily
  namespace: service-db
spec:
  schedule: "0 0 2 * * *"   # six fields: seconds first
  cluster:
    name: postgres
  backupOwnerReference: self

Taking an ad-hoc backup before risky maintenance is one small manifest:

cat <<EOF | kubectl apply -f -
apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata: {name: postgres-manual-$(date +%Y%m%d%H%M), namespace: service-db}
spec: {cluster: {name: postgres}, method: barmanObjectStore}
EOF

And to check health, the two conditions that matter are ContinuousArchiving=True and LastBackupSucceeded=True:

kubectl -n service-db get cluster postgres -o \
  jsonpath='{range .status.conditions[*]}{.type}={.status} {.message}{"\n"}{end}'

The restore test

CNPG always restores into a new cluster; it never overwrites the running one. You create a Cluster with a recovery bootstrap pointing at the original's backup location:

spec:
  bootstrap:
    recovery:
      source: origin
      # recoveryTarget: {targetTime: "2026-10-01 09:30:00+00"}  # optional PITR
  externalClusters:
    - name: origin
      barmanObjectStore:
        serverName: postgres          # the ORIGINAL cluster's name
        destinationPath: s3://<bucket>/<prefix>/
        endpointURL: https://<s3-endpoint>
        s3Credentials:
          accessKeyId:     {name: backup-creds, key: ACCESS_KEY_ID}
          secretAccessKey: {name: backup-creds, key: ACCESS_SECRET_KEY}

On the day we enabled backups, we restored into a temporary cluster. It took about three minutes and contained all eleven databases. For one database we checked every table: all 23 had identical row counts, migration version and size. Row checksums matched on 20 tables; the other 3 differed only because of live writes after the restore point.

That gap was the most useful number we got out of the whole exercise. The newest restored change was about five minutes before the restore finished, which matches how often CNPG switches WAL segments. Our real-world recovery point is roughly five minutes of data. Now we know, instead of guessing.

We also ran pg_dump -Fc for a single database from the restored cluster. That's our path for "restore just one app's database" on a shared cluster. Then we deleted the test cluster and its volume.

One gotcha: if a restored cluster will itself back up to the same bucket, give it a different serverName or path, so it can't overwrite the original's archive.

Troubleshooting cheat sheet

The errors we actually hit, or expect to:

no pg_hba.conf entry for host ..., user X, database Y: no rule for that db/role pair, or a typo in the database name (finance_db vs financedb got us once). Check the live rules with kubectl get cluster postgres -o jsonpath='{.spec.postgresql.pg_hba}'.
password authentication failed: the app's DATABASE_URL and the source-of-truth Secret disagree. Re-running the idempotent create script re-applies the Secret's password to the role.
unrecognized configuration parameter "transaction_timeout": a pg_dump 17 dump loaded into an older server. Filter the SET line out.
role "cloudsqlsuperuser" does not exist: dump with --no-owner --no-acl.
too many clients already: usually a connection-pool leak or a scaled-out app. Find the culprit by grouping pg_stat_activity by role; fix the pool before raising max_connections (which needs a restart).
DROP/ALTER hangs forever: a lock held by another session, very often an idle DBeaver tab. Find it with pg_blocking_pids(), terminate it, and use SET lock_timeout for maintenance.
Pod stuck Pending: nodes are full, or the volume is zonal and free capacity is in a different zone.
ContinuousArchiving=False: treat this as urgent. Unarchived WAL piles up on the WAL volume, and a full WAL volume stops the database.

The queries we keep coming back to:

-- connections per database / role / client
select datname, usename, client_addr, state, count(*)
from pg_stat_activity where datname is not null
group by 1,2,3,4 order by 1;

-- database sizes
select datname, pg_size_pretty(pg_database_size(datname))
from pg_database order by pg_database_size(datname) desc;

-- who is blocking whom
select pid, usename, datname, wait_event_type, wait_event,
       pg_blocking_pids(pid), left(query, 80)
from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0;

The rules we wrote in capital letters

Never delete the data or WAL PVCs, or the Cluster object, while it holds data.
Never add host all all to pg_hba.
Never apply a manifest from git without diffing it against the live cluster first. A copy of our cluster definition on the main branch was once missing a pg_hba rule that existed live; applying it would have silently cut off a production service. kubectl get cluster postgres -o yaml first, every time.
Never apply a whole secrets file "just to update one". It can reset live passwords to older values.

What's still on the list

A migration is a snapshot, not an ending. Here's what we'd tell anyone starting this today, most of which we're working through ourselves:

1. Plan for the Barman Cloud Plugin. CNPG's built-in Barman object-store support is deprecated in favour of the Barman Cloud Plugin and is slated for removal in a future release. Migrate before you upgrade the operator, or backups can stop without anything obviously breaking. And repeat the restore test on a schedule (we're going quarterly).
2. Add a replica. One instance means a node failure or an upgrade takes every database down at once. Once backups are proven, instances: 2 or 3 is a one-line change and a big win.
3. Lock down the LoadBalancer. If clients outside the cluster need access, use loadBalancerSourceRanges so only known IPs can reach port 5432. Passwords alone shouldn't be your only wall.
4. Keep secrets out of git. Use Sealed Secrets or External Secrets, give backups their own bucket-scoped key, and rotate anything that ever lived in a repo or a chat message.
5. Monitor before users tell you. Enable the CNPG PodMonitor and Grafana dashboard, and alert on disk usage, connection count and cluster phase.
6. Decommission the old system deliberately. Check Cloud SQL for any remaining connections, take a final dump, and only then shut it down.

Wrapping up

The whole migration came down to a handful of habits: one boring, repeatable path for every database; isolation enforced in one declarative place; copies that are all-or-nothing and verified by counting rows; cutovers with a saved way back; and backups proven by an actual restore, with a measured recovery point.

CloudNativePG made the Kubernetes side of this surprisingly pleasant. The operator handles the parts that used to be "managed" for us (certificates, services, failover-ready topology, backups), and everything else is YAML we can review in a pull request.

DEV Community: Muhammad Adnan Sarfraz

Leaving Cloud SQL: How We Moved 11 Postgres Databases onto One CloudNativePG Cluster