Skip to content
Beta

Migrate PostgreSQL to STACKIT PostgreSQL Flex with pgcopydb

Last updated on

Use this example to move the databases of a self-managed PostgreSQL server, for example in an on-premises data center, into STACKIT PostgreSQL Flex. It uses pgcopydb to make one consistent copy during a write freeze. A verification step then shows whether source and target are identical before you decide on the cutover.

The repository builds the complete path as a demo, including a stand-in for the customer data center with an enterprise firewall in front of the database. Terraform only builds the infrastructure. The migration itself is a sequence of scripts that you run deliberately, because it contains a write freeze and ends with a cutover decision. Adapt the scripts to your source environment before you use them for a real migration.

Architecture of the example: a source STACKIT project simulates the on-premises data center with a VM behind a default-deny security group, running PostgreSQL 18 and a pgcopydb runner in Podman; the runner copies over TCP 5432 with verified TLS through the VM's public IP to a PostgreSQL Flex 18 instance in a target project whose ACL admits only that IP

Two gates sit on the path, one per side, as in a real migration:

  • Source side, the enterprise firewall. It denies all traffic by default. The migration stage adds the one rule it needs: egress to PostgreSQL Flex on TCP port 5432. In a real environment this is the change request to the firewall team.
  • Target side, the PostgreSQL Flex ACL. It admits only the public address of the source.

pgcopydb runs on a migration host next to the source database. It reads the source locally, streams the data with parallel COPY into PostgreSQL Flex, and builds indexes and constraints on the target afterwards. Nothing is staged on disk.

PostgreSQL Flex cannot currently open outbound connections, so native logical replication, where the target subscribes to the source, is not possible. Continuous synchronization with pgcopydb clone --follow needs privileges on replication-origin functions that PostgreSQL Flex does not currently grant. Plain pgcopydb clone works with the default roles. The cost is a write freeze for the duration of the copy, so rehearse the copy time with representative data volume.

The target major version must be at least the source major version. The runner image is built with the client tools of the target version, and the preflight step checks this.

Run the scripts on the migration host once per database:

The fingerprint compares objects, exact row counts, sequence values, constraints, triggers, functions, and privileges. It counts rows but does not compare their content. For a full content check, run 60-verify.sh <db> --compare-data, which reads every table on both sides. Plan that time inside the write freeze (costs more time and is using the default pgcopydb --compare-data)

The PostgreSQL Flex and migration stages do not depend on the demo VM. For a real data center, skip 01-source, set the source egress addresses and database list for PostgreSQL Flex, and run the runner on a Linux host next to the database with Podman or Docker. Use a read-only migration user and verified TLS to the source and request the firewall opening to the PostgreSQL Flex addresses.

If a Kubernetes cluster already runs next to the source database, pgcopydb can also run there, for example with a Kubernetes operator.

Code & registry github.com PostgreSQL to PostgreSQL Flex migration example Open the repository STACKIT documentation docs.stackit.cloud STACKIT PostgreSQL Flex documentation Open the documentation Code & registry github.com pgcopydb Open the repository
Asset historyActive 1 of the last 12 weeksTMUpdatedNo updates · 1 bar = 1 week i
Maintainer
  • ?Name not public?Name not publicThe Cloud Framework team knows who this is. The name is not shown on the site.
TMTobias M.Head of STACKIT Cloud Framework · STACKITOwnerActive 11 of the last 12 weeks · 196 updatesSTACKITwww.linkedin.com/in/tobias-müller-011304172??Name not publicThe Cloud Framework team knows who this is. The name is not shown on the site.Contributed in STACKIT
  • Tobias M.Tobias M.Head of STACKIT Cloud Framework · STACKITOwnerActive 11 of the last 12 weeks · 196 updatesSTACKITwww.linkedin.com/in/tobias-müller-011304172 · Nov 10, 2026

  • Name not public?Name not publicThe Cloud Framework team knows who this is. The name is not shown on the site. · Nov 10, 2026