Migrate a self-managed PostgreSQL database to klickops

01Pick your path: can the cluster reach your database?

There is one fork that decides everything, and it is not about size. It is whether the klickops cluster can open a connection to your source database over the network.

A private address like 10.x.x.x or 192.168.x.x means “only from my machine”. That is fine, and arguably safer: your database never has to be exposed to move it.

02Look at what you are moving

From a machine that can reach the source, connect and list the databases so you know exactly what you are copying. Let psql prompt for the password so it stays out of your shell history.

You will get back the real databases with their sizes, for example an app database called myapp at a few hundred megabytes. Note the one you want; that is your <source-db> below.

# connects to the always-present maintenance DB just to look around
psql -h db.internal -U admin -d postgres -c \
  "SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
   FROM pg_database WHERE NOT datistemplate ORDER BY 2 DESC;"

03Move the data

Create a managed PostgreSQL database in your klickops project first (the wizard's defaults are production-shaped, you only pick a size). Then find the target with the CLI, and run the migration itself as a single pipe, dump from the source, stream straight into klickops, which runs the restore inside the cluster:

You will see pg_restore progress line by line, then “Import complete”. The --skip-ownership flag is on by default, which matters (see the next section).

Publicly reachable instead? Skip the local dump entirely. On the database's Data & backups tab, choose Import → From another server and paste the source connection string; klickops copies it server-side and you can close the browser while it runs. The same route is best for very large databases, where a local upload would be the slow part.

klops databases list --project my-project

pg_dump -W --format=custom -h db.internal -U admin -d myapp \
  | klops db import myapp-db --project my-project --file -

04What happens to roles and ownership

This trips people up, so it is worth being explicit: your database roles do not migrate, and they should not. A single-database pg_dump does not include roles at all (they are cluster-global objects), and --skip-ownership strips the ownership and grant references on top of that.

The result is clean: every table lands owned by klickops's managed app role, which is exactly the role your application connects as on klickops. The admin user from your old VM is irrelevant to the new database. After the move, point your app at klickops's connection string (host, port, database and user app, with the password revealed on the database's Connect tab), and that is the entire “user” change.

Need more than one login role (a read-only reporting user, say)? Recreate it in klickops as a managed role rather than importing it; the platform manages the password and keeps it in sync. For a single app talking to a single database, you do not need to.

05If the target is not empty

If you already deployed your app to klickops before migrating, it likely ran its own migrations and created the schema. Importing on top of that collides on the first object that already exists. Two things fix it: stop the app first (scale it to zero so nothing writes while the tables drop and reload, then scale it back), and add --clean to the import, which drops each object before recreating it in dependency order so the reload starts from a clean slate.

pg_dump -W --format=custom -h db.internal -U admin -d myapp \
  | klops db import myapp-db --project my-project --file - --clean

06Verify and cut over

Compare a couple of row counts against the source to be sure everything landed. If the counts match, cut over: update your app's connection settings to the klickops app credentials from the previous section and start it back up. It reconnects to the populated database and you are done.

One thing to know about a very large table: a browser upload is capped, and a single enormous table can be slow to stream. For multi-gigabyte databases, prefer the server-side “From another server” copy (if reachable) or a port-forward with a local pg_restore; both move the data as a continuous stream with no upload ceiling.

klops db query myapp-db --project my-project -- \
  "SELECT count(*) FROM users;"