Data move of module.dependencies.module.orders_rds.aws_rds_cluster.this to stackit_postgresflex_instance.dependencies_orders_rds_this
Generated by StackToIT. StackToIT moves no data and opens no connection: this is the preparation, run by a person. Every step below is DEFINED; it is APPLIED once it has been carried out and VERIFIED once its result on STACKIT was checked. Nothing here is APPLIED or VERIFIED, and the draft's database is empty until a step below has filled it.
Source: module.dependencies.module.orders_rds.aws_rds_cluster.this, an Aurora PostgreSQL cluster; connect to the cluster endpoint (the writer). Source PostgreSQL version: major 15 (the draft writes this major as the version of the target; check that it is the one you want). Target: stackit_postgresflex_instance.dependencies_orders_rds_this (the user and the database the draft writes next to it).
Placeholders: <source-host>, <source-port>, <source-db>, <source-user> are the source's; <target-host>, <target-port>, <target-db>, <target-user> are the target's (tofu state show stackit_postgresflex_instance.dependencies_orders_rds_this shows connection_info.write.host and .port; what the draft writes beside the instance is stated above). Passwords are read from the environment of the shell (SOURCE_PGPASSWORD, TARGET_PGPASSWORD); the password of the target user is computed by STACKIT and sensitive (it is in the Terraform state: read it there, do not paste it into a file or a ticket). <table>, <schema>, <key-column> and <application-query> are yours.
Where each value comes from: <source-host> is the attribute module.dependencies.module.orders_rds.aws_rds_cluster.this.endpoint of the source; <source-port> is the attribute module.dependencies.module.orders_rds.aws_rds_cluster.this.port of the source; <target-host> is the attribute stackit_postgresflex_instance.dependencies_orders_rds_this.connection_info.write.host of the draft; <target-port> is the attribute stackit_postgresflex_instance.dependencies_orders_rds_this.connection_info.write.port of the draft; <target-db> is the attribute stackit_postgresflex_database.dependencies_orders_rds_this_orders.name of the draft (the draft writes the name orders).
Decide and check
| # | Step | State |
|---|---|---|
| 1 | Method decision. Both methods are defined below and none is chosen, because what decides it is not in the source: the size of the data and the write-freeze window, which is the open field write-freeze window of this step in the plan, UNKNOWN until the customer states it (allocated_storage is provisioned capacity, not data size). Rule: time a rehearsal of dump and restore (step 7, with steps 8 and 9) into a scratch database of the target; if dump, restore and the verification of step 16 fit the write-freeze window once the customer states it, use dump/restore (a consistent snapshot, nothing changes on the source); if they do not, use logical replication (steps 10 to 15), which needs a change of the source that takes a reboot and a privilege on the target that STACKIT does not document (step 5). Record the method, and the value of that field once the customer states it, next to this table before step 6. <!-- kind: DECISION --> | DEFINED |
| 2 | Version. Source: SHOW server_version;. The target offers the majors 14, 15, 16, 17, 18 (the eu01 catalogue of 2026-10-18). pg_dump must be of a major at least as new as the source: it can dump older servers and refuses a newer one, and a dump is not guaranteed to load into an older major (PostgreSQL pg_dump documentation). <!-- kind: SOURCE --> | DEFINED |
| 3 | Extensions. Source: SELECT extname, extversion FROM pg_extension ORDER BY 1;. Compare with the extensions STACKIT lists (btree_gist, btree_gin, hypopg, pg_trgm, fuzzystrmatch, unaccent, citext, vector, uuid-ossp, hstore, ltree, intarray, cube, hll, timescaledb, postgis and its companions, earthdistance, pg_partman, pg_repack, postgres_fdw, amcheck, pgstattuple, lo, pgcrypto, tablefunc, extra_window_functions, first_last_agg, tcn). pg_stat_statements and pg_cron are not on that list, and an extension that exists only on RDS has no documented counterpart: the application changes, or the move waits. STACKIT warns that pg_available_extensions also lists extensions it does not offer: on the target run SHOW extwlist.extensions; and trust that. The database owner (the draft's user) may create the listed extensions. <!-- kind: SOURCE --> | DEFINED |
| 4 | Roles, owners and superuser. pg_dump of one database carries no role. The draft creates one user (the master user) and one database; every other role the application uses is created beside them (one stackit_postgresflex_user each, with roles from the instance's role list: GET .../instances/{instanceId}/roles, which depends on the version; the API names login, createdb, createrole, replication among them) and its grants are issued again by hand (source: \du and \dp). --no-owner --no-acl below makes the restoring user the owner of every object and drops the grants of the source (RDS roles such as rds_superuser do not exist on STACKIT). STACKIT documents no superuser for a customer and reserves the users admin and postgres (409 ALREADY_EXISTS, observed 2026-10-15): a statement that needs a superuser fails on the target. The master user's other privileges are not documented. <!-- kind: TARGET --> | DEFINED |
| 5 | Network and TLS. The host that runs the commands must reach both ends: it is in network.acl of the instance (the draft's ACL holds the ranges of the source's clients; add this host's address for the move and remove it afterwards), and it reaches the source (a private RDS endpoint needs a host in or peered with that VPC). The port of the target cannot be changed (STACKIT FAQ): use connection_info.write.port, which need not be 5432. Connections are always TLS 1.3 (FAQ): PGSSLMODE=require. Logical replication only: the target instance must also reach the source (security group of the source, an address it can be reached from); the address a PostgreSQL Flex instance connects from is not documented. Logical replication only: on the empty target run CREATE SUBSCRIPTION stacktoit_probe CONNECTION 'host=localhost dbname=x' PUBLICATION p WITH (connect = false); then ALTER SUBSCRIPTION stacktoit_probe SET (slot_name = NONE); and DROP SUBSCRIPTION stacktoit_probe;: if the first is refused, the master user cannot subscribe and dump/restore is the only defined method. <!-- kind: TARGET --> | DEFINED |
Prepare the target
| # | Step | State |
|---|---|---|
| 6 | Apply the draft (tofu apply) so that the instance, the user and the database exist, then check the connection: PGPASSWORD="$TARGET_PGPASSWORD" PGSSLMODE=require psql --host=<target-host> --port=<target-port> --username=<target-user> --dbname=<target-db> --command='SELECT version();'. Create the listed extensions the source uses as the database owner: CREATE EXTENSION IF NOT EXISTS <extension>;. <!-- kind: TARGET --> | DEFINED |
| 7 | Rehearse the chosen method into a scratch database of the target (a second stackit_postgresflex_database or a clone of the instance) from a copy of the source (a restore of a snapshot into a scratch RDS instance, so that production is not loaded). Note the elapsed time of the dump, the restore and the verification of step 16; they decide the rule of step 1. Drop the scratch database afterwards. <!-- kind: EXTERNAL --> | DEFINED |
Method A: dump/restore
| # | Step | State |
|---|---|---|
| 8 | Dump (in the real run: inside the freeze of step 19, after the writers stopped; the dump is a consistent snapshot of the moment it starts, and what is written later is not in it): PGPASSWORD="$SOURCE_PGPASSWORD" PGSSLMODE=require pg_dump --host=<source-host> --port=<source-port> --username=<source-user> --dbname=<source-db> --format=directory --jobs=4 --no-owner --no-acl --file=<dump-dir>. The dump holds your data: keep it where only the person who moves it can read it, and delete it after step 24. Platform-owned schemas. Every STACKIT PostgreSQL Flex instance already carries the schemas metric_helpers and user_management, owned by the platform (observed on a STACKIT PostgreSQL Flex instance; the STACKIT documentation read for this document does not name them): a dump that holds a schema of either name makes pg_restore report schema ... already exists and permission denied for schema ... for every object in it. An RDS or Aurora source does not ship them; check what the source holds with \dn before the dump. If the source is itself a STACKIT PostgreSQL Flex instance, add --exclude-schema=metric_helpers --exclude-schema=user_management to the dump. If a schema of your own has one of these names, it cannot be restored under that name: rename it in the source copy before the dump (the effect on the application is yours to settle), and do not exclude it. <!-- kind: SOURCE; branch: A --> | DEFINED |
| 9 | Restore: PGPASSWORD="$TARGET_PGPASSWORD" PGSSLMODE=require pg_restore --host=<target-host> --port=<target-port> --username=<target-user> --dbname=<target-db> --jobs=4 --no-owner --no-acl <dump-dir> 2> restore.log, as the database owner (the draft's user). The command does not stop at an error: read every line of restore.log and settle each one (an error on a statement that needs a superuser, such as a comment on an extension, is expected to appear and is not assumed harmless). A restore into a database that already holds the objects fails on them: restore into the empty database the draft creates. <!-- kind: TARGET; branch: A --> | DEFINED |
Method B: logical replication
| # | Step | State | ||||
|---|---|---|---|---|---|---|
| 10 | Source: prepare. Every table needs a primary key or a replica identity for UPDATE and DELETE to replicate: SELECT n.nspname, c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema') AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = c.oid AND i.indisprimary); lists the tables without one. Turn logical replication on at the source: set rds.logical_replication to 1 in the DB cluster parameter group and reboot the writer (the Aurora page read for this document does not name the parameter: check it in the Aurora documentation before relying on it); the user that creates the publication has the rds_superuser role and the replication user has rds_replication (AWS RDS User Guide). This is a change of the source and a reboot of it; StackToIT makes neither. Parameters that raise WAL generation are set with it: set it only for the move. A slot that nobody reads fills the storage of the source: watch the free storage from step 13 on. <!-- kind: SOURCE; branch: B --> | DEFINED | ||||
| 11 | Copy the schema (logical replication does not replicate the schema or later DDL; freeze schema changes on the source from here on): pg_dump --schema-only --no-owner --no-acl --host=<source-host> --port=<source-port> --username=<source-user> --dbname=<source-db> --file=schema.sql (password from SOURCE_PGPASSWORD), then load it into the empty target database with psql --file=schema.sql as the database owner, with the extensions of step 6 already created. Apply the check about the platform-owned schemas of the target (step 8) to this dump too. <!-- kind: TARGET; branch: B --> | DEFINED | ||||
| 12 | Publish and subscribe. On the source: CREATE PUBLICATION stacktoit_move FOR ALL TABLES; (or FOR TABLE <schema>.<table>, ...). On the target, in an interactive psql session so that the password is not stored in a file or the shell history: CREATE SUBSCRIPTION stacktoit_move CONNECTION 'host=<source-host> port=<source-port> dbname=<source-db> user=<replication-user> password=<replication-password> sslmode=require' PUBLICATION stacktoit_move;. The subscription copies the existing rows first and then follows the changes. Sequences and large objects are not replicated (step 15 handles the sequences; a large object has no replication path). <!-- kind: TARGET; branch: B --> | DEFINED | ||||
| 13 | Watch the initial copy and the lag. Target: SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel; until every table is r. Source: SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS lag FROM pg_replication_slots; and the free storage of the source. <!-- kind: TARGET; branch: B --> | DEFINED | ||||
| 14 | Keep the target apart until the cutover: the application does not write to it, and no DDL runs on either side. Replication keeps running through the verification of step 16 on the changing source, so counts differ by the changes in flight: the exact comparison is made inside the freeze. <!-- kind: TARGET; branch: B --> | DEFINED | ||||
| 15 | Sequences (inside the freeze, after the lag is 0): on the source run `SELECT format('SELECT setval(%L, %s);', schemaname \ | \ | '.' \ | \ | sequencename, last_value) FROM pg_sequences WHERE last_value IS NOT NULL; with psql --tuples-only --no-align` into a file, and run that file on the target. Without it the first insert on the target repeats a key. <!-- kind: TARGET; branch: B --> | DEFINED |
Verify with the application
| # | Step | State | ||||
|---|---|---|---|---|---|---|
| 16 | Row counts and a checksum, both sides, with the writers stopped. Counts of every table: `SELECT format('SELECT %L AS tbl, count(*) FROM %I.%I;', schemaname \ | \ | '.' \ | \ | tablename, schemaname, tablename) FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') \gexec. The estimates in pg_stat_user_tables are not counts. Checksum of a named table: SELECT count(*), md5(string_agg(t::text, ',' ORDER BY t.<key-column>)) FROM <schema>.<table> t; (use a key range for a large table; DateStyle, TimeZone and extra_float_digits equal in both sessions). The numbers equal on both sides, or the move is not accepted. Sequences: SELECT sequencename, last_value FROM pg_sequences ORDER BY 1;` equal, or the target's is higher. <!-- kind: TARGET --> | DEFINED |
| 17 | Application query. Run one read query that the application itself runs on its most important data, <application-query>, on both sides and compare the answers; then start one writer against the target in a test environment and check that an insert succeeds and a read returns it. The query and the answer are yours: the source does not state them. <!-- kind: TARGET --> | DEFINED |
Checks after the transfer
These are the checks the owner makes with the writers stopped before recording GO; the reconcile step of each method in the execution plan states the same checks, with the same ids. A command is a template: a placeholder in angle brackets stands for a name, an endpoint or a credential the plan does not hold, and a password is read from the environment, never pasted. A check with no command says why: the plan states no command it cannot back. StackToIT runs none of them.
Every method (dump/restore, logical replication, a tool of your own)
- Count on both sides (
row-counts, command): Count the rows of every table on both sides, with the writers stopped (an exact count of each table; the estimates of pg_stat_user_tables are not counts). Expected: the same tables with the same number of rows on both sides.
~~~text SELECT format('SELECT %L AS tbl, count(*) FROM %I.%I;', schemaname || '.' || tablename, schemaname, tablename) FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') \gexec ~~~
- Content on both sides (
table-checksums, command): Take the md5 of the rows of every keyed table that matters on both sides, ordered by its key (a key range for a large table; DateStyle, TimeZone and extra_float_digits equal in both sessions). Expected: the row count and the md5 are equal on both sides for every table checked.
~~~text SELECT count(*), md5(string_agg(t::text, ',' ORDER BY t.<key-column>)) FROM <schema>.<table> t; ~~~
- Not provided, so left as a placeholder or for the owner to fill in: the key column of each table (<key-column>); the tables that matter.
- Content on both sides (
sequences, command): Compare the position of every sequence on both sides, so that the first insert on the target does not repeat a key. Expected: the positions are equal, or the target's is higher.
~~~text SELECT sequencename, last_value FROM pg_sequences ORDER BY 1; ~~~
- Application check (
application-check, manual): Run one read the application itself makes on its most important data against the target database and compare its answer with the answer the application gets from the source; then, from a test copy of the application, make one write against the target database and read it back. Expected: the two answers are equal, the write succeeds and the read returns it. - No command: the read and the write are the application's, and the source states neither the query nor the answer. - Not provided, so left as a placeholder or for the owner to fill in: the read the application makes on its most important data and its expected answer; the write the test copy of the application makes. - Rollback limit (
rollback-limit, manual): Record the point after which a rollback loses data. Until the first of the writes that the target database accepts after the switch, a rollback is only the connection settings put back: the source database was not changed after the freeze and is current to it. From that write on, what the target database holds exists only there: it is copied back to the source database with a method rehearsed before the window, or accepted as lost. Once the source database is retired (its retirement step, after the GO and the retention the owner decides) there is nothing to go back to. Expected: before the window the owner records the date until which the source database is kept, the method that copies the writes of the target database back or the decision that they are lost, and who calls the rollback. - No command: how long the source is kept and whether the writes made on the target are copied back or lost are decisions of the owner of the application: the source states neither, so the plan names the limit and no date. - Not provided, so left as a placeholder or for the owner to fill in: the date until which the source database is kept; the method that copies the writes of the target database back, or the decision that they are lost.
Logical replication only
- Replication lag (
replication-lag, command): On the source, inside the freeze and before the final sync, read how far the subscription is behind the source: the lag of the replication slot in bytes. Expected: the lag is 0 bytes with the writers stopped; the final sync (the sequences, then disabling the subscription) waits for it.
~~~text SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn) AS lag_bytes FROM pg_replication_slots; ~~~
- Time of the last sync (
last-sync-time, command): On the target, read the time of the last change the subscription applied, and record it with the time the writers stopped. Expected: the time of the last sync is not earlier than the time the writers stopped, and no change arrives after it.
~~~text SELECT subname, latest_end_lsn, latest_end_time FROM pg_stat_subscription; ~~~
- Not provided, so left as a placeholder or for the owner to fill in: the time the writers stopped (recorded by the freeze step).
Cutover window
| # | Step | State |
|---|---|---|
| 18 | Before the window. List who writes: SELECT usename, application_name, client_addr, count(*) FROM pg_stat_activity WHERE datname = '<source-db>' GROUP BY 1, 2, 3 ORDER BY 4 DESC;. Record the current connection settings of every client (host, port, user, database, sslmode) where you can restore them from: the rollback needs them. Agree the window from the rehearsal times of step 7, and lower the TTL of any DNS name your clients use for the source. <!-- kind: EXTERNAL --> | DEFINED |
| 19 | Freeze writes. Stop the writers at the application (services, jobs, migrations, cron). Do not make the source read-only and do not change it: it stays read-write and untouched, so the rollback needs nothing on the source. Check that nothing writes: SELECT count(*) FROM pg_stat_activity WHERE datname = '<source-db>' AND state <> 'idle' AND pid <> pg_backend_pid(); is 0. <!-- kind: EXTERNAL --> | DEFINED |
| 20 | Final sync. Dump/restore: run steps 8 and 9 now, inside the freeze. Logical replication: wait until the lag of step 13 is 0, copy the sequences (step 15), then disable the subscription: ALTER SUBSCRIPTION stacktoit_move DISABLE; (the slot stays on the source, so the way back stays open; step 24 removes it). <!-- kind: TARGET --> | DEFINED |
| 21 | Verify inside the freeze (steps 16 and 17). If a number or the application query differs, stop here and take the rollback (step 23) without switching anything. <!-- kind: TARGET --> | DEFINED |
| 22 | Switch the connection strings. Every client changes host to <target-host>, port to <target-port>, user and password to the target user's, database to <target-db>, and sslmode=require (a client that does not speak TLS 1.3 fails: not verified here). Keep the old values. Start the writers against the target, run the application query, make one test write. <!-- kind: TARGET --> | DEFINED |
Rollback
| # | Step | State |
|---|---|---|
| 23 | Rollback. If the verification fails or the application misbehaves after the switch: stop the writers, put the connection strings of the source back (the values recorded before the window), start the writers on the source. The source was neither stopped nor made read-only nor changed after the freeze, so it is current up to the freeze. What was written to the target after the switch is not on the source: copy it back (the same method in the other direction, a separate rehearsal) or accept it as lost; take that decision before the window (step 18), not in the incident. Keep the target until the cause is known; do not delete the source in the same hour. <!-- kind: SOURCE --> | DEFINED |
After acceptance
| # | Step | State |
|---|---|---|
| 24 | Clean up only after the target has run in production for the period you decide and the rollback is no longer wanted: logical replication: DROP SUBSCRIPTION stacktoit_move; on the target (it drops the slot on the source if the source is reachable; check SELECT * FROM pg_replication_slots; on the source), DROP PUBLICATION stacktoit_move; on the source, set rds.logical_replication back and reboot (a second outage of the source, only when the source is kept); remove the move host from network.acl; delete the dump files and restore.log; keep a final snapshot of the source for the retention you decide before you delete it. The credentials of the target user are rotated if they were ever on a ticket or in a file. <!-- kind: TARGET --> | DEFINED |
Not verified on STACKIT (to be settled when the step is first carried out against the target, then recorded): that the master user of an instance can create a subscription (not documented), that a PostgreSQL Flex instance can reach a source for logical replication and from which address, which errors pg_restore reports on a PostgreSQL Flex target, the throughput of either method, and that a client that does not speak TLS 1.3 is refused (the FAQ says every connection is TLS 1.3). Not verified in this build: the name of the Aurora parameter that turns logical replication on, and the privileges the roles of the target's role list carry.