data-move.md

Data move of google_sql_database_instance.postgres to stackit_postgresflex_instance.postgres

Generated by StackToIT. StackToIT moves no data and opens no connection: this is the preparation, run by a person. The document is written from the steps and the checks of the execution plan (execution-plan.json): each section is one step, in the order of the plan, and every sentence, command and check in it is that step's own, so the document holds nothing the plan does not. 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. StackToIT runs none of them.

Source: google_sql_database_instance.postgres (google_sql_database_instance). Target: stackit_postgresflex_instance.postgres.

Steps

Preflight

Step MAN/data/google_sql_database_instance.postgres/preflight (data preflight, phase DATA); owner: Data Architect.

What. Close what the plan lists as UNKNOWN, from the move host, with read-only queries. The move host reaches the source through the connection method you already use for Cloud SQL for PostgreSQL (an authorized network of a public address, a private address inside the VPC network, or a proxy you run). Source: PGPASSWORD="$SOURCE_PGPASSWORD" PGSSLMODE=require psql --host=<source-host> --port=<source-port> --username=<source-user> --dbname=<source-db> --command='SHOW server_version;', SELECT extname, extversion FROM pg_extension ORDER BY 1;, SELECT pg_size_pretty(pg_database_size(current_database())); against the free storage of the instance (at least 25 percent of the database size free before a large export), \du and \dp for the roles and grants, and the users that sign in with an IAM identity. Target (after the draft is applied and the instance, user and database exist): PGPASSWORD="$TARGET_PGPASSWORD" PGSSLMODE=require psql --host=<target-host> --port=<target-port> --username=<target-user> --dbname=<target-db> --command='SHOW extwlist.extensions;' and SELECT version();. Check the move host is in network.acl of the instance, reaches both ends and speaks TLS 1.3. Logical replication: on the source SHOW wal_level;, SHOW max_replication_slots;, SHOW max_wal_senders; and the tables without a primary key (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);); on the empty target the probe 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;.

Why. the checks of the plan that are UNKNOWN are closed by these queries before any data moves

Needs. the checks that are UNKNOWN and what closes each: extensions (closed by: preflight: on the source SELECT extname, extversion FROM pg_extension ORDER BY 1; on the target SHOW extwlist.extensions; and trust the target); roles (closed by: preflight on the source, the roles: SELECT rolname, rolsuper, rolcanlogin, rolcreatedb, rolcreaterole, rolreplication, rolconnlimit FROM pg_roles WHERE rolname !~ '^(pg_|cloudsql)' ORDER BY 1; (psql: \du); the memberships: SELECT r.rolname AS role, m.rolname AS member, a.admin_option FROM pg_auth_members a JOIN pg_roles r ON r.oid = a.roleid JOIN pg_roles m ON m.oid = a.member ORDER BY 1, 2;; and in every database to move: \dp, \ddp, \dn+, \l and \drds; each role the application uses is created as a stackit_postgresflex_user resource and its grants are issued again); network-tls (closed by: preflight from the move host: psql to the source and to the target with PGSSLMODE=require); database-users-and-iam (closed by: preflight on the source: the users of the instance (gcloud sql users list --instance=<instance> for Cloud SQL, the pg_roles listing for both) and which of the applications sign in with a token); source-headroom (closed by: preflight on the source: SELECT pg_size_pretty(pg_database_size(current_database())); and the free storage of the instance in Cloud Monitoring, against the size the customer states under source.dataset.size); lr-enabled (closed by: preflight on the source: SHOW wal_level; and the flag cloudsql.logical_decoding of the instance (the owner of the source sets it and restarts the instance)); lr-replica-identity (closed by: preflight on the source: list the tables without a primary key (pg_class / pg_index, relkind 'r', outside pg_catalog and information_schema)); lr-subscription-privilege (closed by: preflight on the empty target: CREATE SUBSCRIPTION stacktoit_probe CONNECTION 'host=localhost dbname=x' PUBLICATION p WITH (connect = false); then ALTER SUBSCRIPTION stacktoit_probe SET (slot_name = NONE); DROP SUBSCRIPTION stacktoit_probe; if the master user cannot, the method is a design); source-reach (closed by: the rehearsal: a subscription from a scratch database of the target to a copy of the source, and psql from the move host to both ends); replication-cannot-catch-up (closed by: the customer states source.dataset.size, source.dataset.bandwidth and source.dataset.write_rate; the rehearsal measures the real throughput)

Done when. every UNKNOWN check of the plan is HOLDS or FAILS; the extension list of the source is compared with SHOW extwlist.extensions; of the target and each difference is settled

Risks.

• the move host in network.acl is a hole until it is removed at cleanup
• a public address of the source with an authorized network for the move host is a hole until the entry is removed
• check version-deprecated FAILS: PostgreSQL Flex 14, the major the draft writes, is deprecated: end of support 2026-11-12; an instance on it must be updated to a newer major

Rollback. nothing changed on either side: read-only queries and, on the target, an empty database

Rehearsal

Step MAN/data/google_sql_database_instance.postgres/rehearsal (data rehearsal, phase DATA); owner: Data Architect.

What. 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 clone or a restored backup of the Cloud SQL for PostgreSQL instance, so that production is not loaded). Note the elapsed time of the dump, the restore, the index build and the verification. Drop the scratch database and the copy afterwards.

Why. the transfer lower bound leaves out restore, index build and verification, and the throughput is not verified on STACKIT; only a rehearsal measures them

Needs. the customer's statements in inputs.yaml: source.dataset.size, source.dataset.bandwidth

Done when. the elapsed times of each part are recorded and the write-freeze window is agreed from them

Rollback. drop the scratch database and the copy; the draft's database and the source are untouched

Write freeze (dump-restore)

Step MAN/data/google_sql_database_instance.postgres/write-freeze/dump-restore (data write freeze, phase CUTOVER); owner: Migration Owner.

What. 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;) and record the connection settings of every client where they can be restored from. Then stop the writers at the application (services, jobs, migrations, cron). Do not make the source read-only and do not change it. Check: SELECT count(*) FROM pg_stat_activity WHERE datname = '<source-db>' AND state <> 'idle' AND pid <> pg_backend_pid(); is 0.

Why. the source stays read-write and untouched, so the way back needs nothing on the source

Needs. the customer's statements in inputs.yaml: source.dataset.downtime

Done when. no client writes to the source

Method. This step belongs to the method export-and-import-with-the-engine-clients of the decision DEC-GEN-CONVERT-DATA-MOVEMENT: the plan carries it out only when that decision is answered with it; until it is answered, the steps of every open method are listed.

Rollback. start the writers again against the source: nothing was changed

Final sync (dump-restore)

Step MAN/data/google_sql_database_instance.postgres/final-sync/dump-restore (data final sync, phase CUTOVER); owner: Data Architect.

What. Dump inside the freeze: 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> (Google's page names --no-owner and --no-acl for a dump of one database and the directory format for a parallel dump; dump one database at a time, since pg_dumpall fails for databases with several owners). Restore as the database owner into the empty database: 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, then read restore.log line by line; create the extensions of the source that the target lists and settle the ones it does not.

Why. the dump is a consistent snapshot of the moment it starts; inside the freeze it is the final state of the source

Needs. the customer's statements in inputs.yaml: source.dataset.downtime

Done when. pg_restore finished and every error in restore.log is settled

Risks.

• the freeze lasts as long as the dump and the restore

Method. This step belongs to the method export-and-import-with-the-engine-clients of the decision DEC-GEN-CONVERT-DATA-MOVEMENT: the plan carries it out only when that decision is answered with it; until it is answered, the steps of every open method are listed.

Rollback. the source is current to the freeze and untouched: put the writers back on it; drop and recreate the target database for another attempt

Reconcile (dump-restore)

Step MAN/data/google_sql_database_instance.postgres/reconcile/dump-restore (data reconcile, phase CUTOVER); owner: Data Architect.

What. Both sides, with the writers stopped: per-table counts (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'); run on each side), an md5 of every keyed table (SELECT count(*), md5(string_agg(t::text, ',' ORDER BY t.<key-column>)) FROM <schema>.<table> t;, a key range for a large table, DateStyle, TimeZone and extra_float_digits equal in both sessions), the sequences, and one read query the application itself runs on its most important data, <application-query>: the query and the answer are the customer's, the source does not state them. Record GO or NO-GO.

Why. the move is accepted only when the numbers equal on both sides; a mismatch is a NO-GO

Done when. counts, checksums, sequences and the application query agree: GO

Method. This step belongs to the method export-and-import-with-the-engine-clients of the decision DEC-GEN-CONVERT-DATA-MOVEMENT: the plan carries it out only when that decision is answered with it; until it is answered, the steps of every open method are listed.

Rollback. on NO-GO take the rollback without switching anything: the clients still point at the source, which is current to the freeze

Verification: the checks after the transfer

These are the checks the owner makes with the writers stopped before recording GO; the reconcile step in the execution plan states the same checks, with the same ids. A check with no command says why: the plan states no command it cannot back.

• 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; the tables of the schemas metric_helpers and user_management, which every PostgreSQL Flex instance keeps for the platform, are left out of the comparison.

~~~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; ~~~

• Content on both sides (restore-log, command): Read every error pg_restore wrote to restore.log: it does not stop at an error, so a table, an index or a constraint that failed leaves the restore finished. Expected: every line the command prints is settled and recorded; none is left unread.

~~~text grep -n 'error:' restore.log ~~~

• Content on both sides (extensions, command): List the extensions and their versions on both sides: the restore or the subscription does not create on the target what it does not offer. Expected: every extension the application uses is on the target; a difference is settled and recorded.

~~~text SELECT extname, extversion FROM pg_extension ORDER BY 1; ~~~

• Content on both sides (roles-and-grants, command): List the roles and the table grants on both sides: the schema is copied with --no-owner --no-acl, so the owners and the grants of the source are not on the target, and a user of Cloud SQL for PostgreSQL that signs in with an IAM identity has no counterpart on PostgreSQL Flex, which has password users only. Expected: every login the application uses exists on the target with the grants it needs; cloudsqlsuperuser, the roles of the platform of each side and the users that signed in with an IAM identity are settled one by one.

~~~text SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname !~ '^pg_' ORDER BY 1; SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.role_table_grants WHERE table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY 1, 2, 3, 4; ~~~

• 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 Cloud SQL for PostgreSQL database; 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 Cloud SQL for PostgreSQL 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 Cloud SQL for PostgreSQL database with a method rehearsed before the window, or accepted as lost. Once the Cloud SQL for PostgreSQL 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 Cloud SQL for PostgreSQL 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 Cloud SQL for PostgreSQL database is kept; the method that copies the writes of the target database back, or the decision that they are lost.

Clean-up (dump-restore)

Step MAN/data/google_sql_database_instance.postgres/cleanup/dump-restore (data cleanup, phase DECOMMISSION); owner: Data Architect.

What. Only after the target has run in production for the period the customer decides and the way back is no longer wanted: remove the move host from network.acl of the instance and from the authorized networks of the source if it was added; delete the dump files and restore.log; keep a final backup of the source for the retention the customer decides before anything is deleted; replace the credentials of the target user if they were ever on a ticket or in a file.

Why. the move leaves a publication, a slot, a flag, an access entry and copies of the data behind; none of them stays

When. after the owner records GO at the end of the verification, and once the way back is no longer wanted

Done when. no slot, subscription, publication or dump file is left, the move host is out of every access list, the credentials are replaced

Risks.

• the source is not deleted in the same hour as the cutover

Method. This step belongs to the method export-and-import-with-the-engine-clients of the decision DEC-GEN-CONVERT-DATA-MOVEMENT: the plan carries it out only when that decision is answered with it; until it is answered, the steps of every open method are listed.

Rollback. after the cleanup the way back is the final backup of the source; there is no other

Initial copy (logical-replication)

Step MAN/data/google_sql_database_instance.postgres/initial-copy/logical-replication (data migration, phase DATA); owner: Data Architect.

What. Source: turn logical decoding on: set the flag cloudsql.logical_decoding to on on the instance (a restart of the instance), create a user for the publication with the REPLICATION attribute that holds CREATE on the database (CREATE ROLE stacktoit_repl WITH LOGIN REPLICATION PASSWORD '<replication-password>';, typed in an interactive session). Every table needs a primary key or a replica identity. Copy the schema (no later DDL on either side): PGPASSWORD="$SOURCE_PGPASSWORD" pg_dump --schema-only --no-owner --no-acl --host=<source-host> --port=<source-port> --username=<source-user> --dbname=<source-db> | PGPASSWORD="$TARGET_PGPASSWORD" PGSSLMODE=require psql --host=<target-host> --port=<target-port> --username=<target-user> --dbname=<target-db>. Publish on the source: CREATE PUBLICATION stacktoit_move FOR ALL TABLES;. Subscribe on the target in an interactive psql session so that no password is stored in a file or the shell history: CREATE SUBSCRIPTION stacktoit_move CONNECTION 'host=<source-host> port=<source-port> dbname=<source-db> user=stacktoit_repl password=<replication-password> sslmode=require' PUBLICATION stacktoit_move;. Wait until SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel; shows r for every table.

Why. the subscription copies the existing rows and then follows the changes, so the freeze later only has to wait for the lag to reach 0

Done when. every table of the target is in state r and the replication slot of the source is active

Risks.

• a change and a restart of the source
• a slot nobody reads keeps WAL on the disk of the source
• schema changes freeze from here on

Method. This step belongs to the method replicate-with-the-engine-replication of the decision DEC-GEN-CONVERT-DATA-MOVEMENT: the plan carries it out only when that decision is answered with it; until it is answered, the steps of every open method are listed.

Rollback. drop the subscription on the target (DROP SUBSCRIPTION stacktoit_move;), the publication and the slot on the source, drop the user stacktoit_repl, and set the flag back with a second restart of the source; the source is otherwise untouched

Delta sync (logical-replication)

Step MAN/data/google_sql_database_instance.postgres/delta-sync/logical-replication (data delta sync, phase DATA); owner: Data Architect.

What. Keep the target apart: the application does not write to it and no DDL runs on either side. Watch the lag and the free storage of the 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;.

Why. replication keeps running until the freeze; counts on the changing source differ by the changes in flight, so the exact comparison is made inside the freeze

Needs. the customer's statements in inputs.yaml: source.dataset.write_rate

Done when. the lag stays bounded and the free storage of the source does not fall

Risks.

• a growing lag means the copy does not catch up at this write rate

Method. This step belongs to the method replicate-with-the-engine-replication of the decision DEC-GEN-CONVERT-DATA-MOVEMENT: the plan carries it out only when that decision is answered with it; until it is answered, the steps of every open method are listed.

Rollback. as the initial copy: drop the subscription, the publication and the slot

Write freeze (logical-replication)

Step MAN/data/google_sql_database_instance.postgres/write-freeze/logical-replication (data write freeze, phase CUTOVER); owner: Migration Owner.

What. 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;) and record the connection settings of every client where they can be restored from. Then stop the writers at the application (services, jobs, migrations, cron). Do not make the source read-only and do not change it. Check: SELECT count(*) FROM pg_stat_activity WHERE datname = '<source-db>' AND state <> 'idle' AND pid <> pg_backend_pid(); is 0.

Why. the source stays read-write and untouched, so the way back needs nothing on the source

Needs. the customer's statements in inputs.yaml: source.dataset.downtime

Done when. no client writes to the source

Method. This step belongs to the method replicate-with-the-engine-replication of the decision DEC-GEN-CONVERT-DATA-MOVEMENT: the plan carries it out only when that decision is answered with it; until it is answered, the steps of every open method are listed.

Rollback. start the writers again against the source: nothing was changed

Final sync (logical-replication)

Step MAN/data/google_sql_database_instance.postgres/final-sync/logical-replication (data final sync, phase CUTOVER); owner: Data Architect.

What. Wait until the lag is 0. Copy the sequences: on the source 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, and run the output on the target. Then disable the subscription on the target: ALTER SUBSCRIPTION stacktoit_move DISABLE; (the slot stays on the source, so the way back stays open).

Why. sequences are not replicated: without setval the first insert on the target repeats a key

Needs. the customer's statements in inputs.yaml: source.dataset.downtime

Done when. the lag was 0, the sequences on the target are not lower than the source's, the subscription is disabled

Method. This step belongs to the method replicate-with-the-engine-replication of the decision DEC-GEN-CONVERT-DATA-MOVEMENT: the plan carries it out only when that decision is answered with it; until it is answered, the steps of every open method are listed.

Rollback. the slot is still on the source and the source untouched: put the writers back on the source

Reconcile (logical-replication)

Step MAN/data/google_sql_database_instance.postgres/reconcile/logical-replication (data reconcile, phase CUTOVER); owner: Data Architect.

What. Both sides, with the writers stopped: per-table counts (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'); run on each side), an md5 of every keyed table (SELECT count(*), md5(string_agg(t::text, ',' ORDER BY t.<key-column>)) FROM <schema>.<table> t;, a key range for a large table, DateStyle, TimeZone and extra_float_digits equal in both sessions), the sequences, and one read query the application itself runs on its most important data, <application-query>: the query and the answer are the customer's, the source does not state them. Record GO or NO-GO.

Why. the move is accepted only when the numbers equal on both sides; a mismatch is a NO-GO

Done when. counts, checksums, sequences and the application query agree: GO

Method. This step belongs to the method replicate-with-the-engine-replication of the decision DEC-GEN-CONVERT-DATA-MOVEMENT: the plan carries it out only when that decision is answered with it; until it is answered, the steps of every open method are listed.

Rollback. on NO-GO take the rollback without switching anything: the clients still point at the source, which is current to the freeze

Verification: the checks after the transfer

These are the checks the owner makes with the writers stopped before recording GO; the reconcile step in the execution plan states the same checks, with the same ids. A check with no command says why: the plan states no command it cannot back.

• 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).

• 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; the tables of the schemas metric_helpers and user_management, which every PostgreSQL Flex instance keeps for the platform, are left out of the comparison.

~~~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; ~~~

• Content on both sides (subscription-state, command): On the target, list the tables of the subscription that have not reached the state ready, and read the state of the subscription: a table added after the publication, or one whose copy failed, is not followed. Expected: the first statement returns no row, and the subscription is disabled after the final sync.

~~~text SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel WHERE srsubstate <> 'r'; SELECT subname, subenabled FROM pg_subscription; ~~~

• Content on both sides (extensions, command): List the extensions and their versions on both sides: the restore or the subscription does not create on the target what it does not offer. Expected: every extension the application uses is on the target; a difference is settled and recorded.

~~~text SELECT extname, extversion FROM pg_extension ORDER BY 1; ~~~

• Content on both sides (roles-and-grants, command): List the roles and the table grants on both sides: the schema is copied with --no-owner --no-acl, so the owners and the grants of the source are not on the target, and a user of Cloud SQL for PostgreSQL that signs in with an IAM identity has no counterpart on PostgreSQL Flex, which has password users only. Expected: every login the application uses exists on the target with the grants it needs; cloudsqlsuperuser, the roles of the platform of each side and the users that signed in with an IAM identity are settled one by one.

~~~text SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname !~ '^pg_' ORDER BY 1; SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.role_table_grants WHERE table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY 1, 2, 3, 4; ~~~

• 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 Cloud SQL for PostgreSQL database; 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 Cloud SQL for PostgreSQL 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 Cloud SQL for PostgreSQL database with a method rehearsed before the window, or accepted as lost. Once the Cloud SQL for PostgreSQL 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 Cloud SQL for PostgreSQL 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 Cloud SQL for PostgreSQL database is kept; the method that copies the writes of the target database back, or the decision that they are lost.

Clean-up (logical-replication)

Step MAN/data/google_sql_database_instance.postgres/cleanup/logical-replication (data cleanup, phase DECOMMISSION); owner: Data Architect.

What. Only after the target has run in production for the period the customer decides and the way back is no longer wanted: 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;), DROP PUBLICATION stacktoit_move; and DROP ROLE stacktoit_repl; on the source, set cloudsql.logical_decoding back with a second restart if it was off before the move; remove the move host from network.acl of the instance and from the authorized networks of the source if it was added; delete the dump files and restore.log; keep a final backup of the source for the retention the customer decides before anything is deleted; replace the credentials of the target user if they were ever on a ticket or in a file.

Why. the move leaves a publication, a slot, a flag, an access entry and copies of the data behind; none of them stays

When. after the owner records GO at the end of the verification, and once the way back is no longer wanted

Done when. no slot, subscription, publication or dump file is left, the move host is out of every access list, the credentials are replaced

Risks.

• the source is not deleted in the same hour as the cutover

Method. This step belongs to the method replicate-with-the-engine-replication of the decision DEC-GEN-CONVERT-DATA-MOVEMENT: the plan carries it out only when that decision is answered with it; until it is answered, the steps of every open method are listed.

Rollback. after the cleanup the way back is the final backup of the source; there is no other

Write freeze (own-tool)

Step MAN/data/google_sql_database_instance.postgres/write-freeze/own-tool (data write freeze, phase CUTOVER); owner: Migration Owner.

What. 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;) and record the connection settings of every client where they can be restored from. Then stop the writers at the application (services, jobs, migrations, cron). Do not make the source read-only and do not change it. Check: SELECT count(*) FROM pg_stat_activity WHERE datname = '<source-db>' AND state <> 'idle' AND pid <> pg_backend_pid(); is 0.

Why. the source stays read-write and untouched, so the way back needs nothing on the source

Needs. the customer's statements in inputs.yaml: source.dataset.downtime

Done when. no client writes to the source

Method. This step belongs to the method migrate-with-a-tool-of-my-own of the decision DEC-GEN-CONVERT-DATA-MOVEMENT: the plan carries it out only when that decision is answered with it; until it is answered, the steps of every open method are listed.

Rollback. start the writers again against the source: nothing was changed

Final sync (own-tool)

Step MAN/data/google_sql_database_instance.postgres/final-sync/own-tool (data final sync, phase CUTOVER); owner: Data Architect.

What. Copy the data of google_sql_database_instance.postgres to stackit_postgresflex_instance.postgres with the migration tool or the replication you chose: write its steps, the checks before the move, the freeze window it needs and the way back

Why. the draft creates the instance empty and the plan knows nothing of a tool of your own, so it holds the step and the person who chose the tool writes the steps

Needs. the tool, its steps, the checks before the move, the freeze window and the way back, from the person who runs it

Done when. the data of google_sql_database_instance.postgres is in stackit_postgresflex_instance.postgres and the checks of the tool hold

Method. This step belongs to the method migrate-with-a-tool-of-my-own of the decision DEC-GEN-CONVERT-DATA-MOVEMENT: the plan carries it out only when that decision is answered with it; until it is answered, the steps of every open method are listed.

Rollback. the writers are stopped, not removed, and nothing the plan does changes the source database, so the source database is the way back until the reconcile records GO: put the writers back on it; before another attempt drop what the tool wrote into the databases of stackit_postgresflex_instance.postgres (the draft created them empty); check that the tool you chose does not change the source database, since the plan knows nothing of it

Reconcile (own-tool)

Step MAN/data/google_sql_database_instance.postgres/reconcile/own-tool (data reconcile, phase CUTOVER); owner: Data Architect.

What. Both sides, with the writers stopped: per-table counts (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'); run on each side), an md5 of every keyed table (SELECT count(*), md5(string_agg(t::text, ',' ORDER BY t.<key-column>)) FROM <schema>.<table> t;, a key range for a large table, DateStyle, TimeZone and extra_float_digits equal in both sessions), the sequences, and one read query the application itself runs on its most important data, <application-query>: the query and the answer are the customer's, the source does not state them. Record GO or NO-GO.

Why. the move is accepted only when the numbers equal on both sides; a mismatch is a NO-GO

Done when. counts, checksums, sequences and the application query agree: GO

Method. This step belongs to the method migrate-with-a-tool-of-my-own of the decision DEC-GEN-CONVERT-DATA-MOVEMENT: the plan carries it out only when that decision is answered with it; until it is answered, the steps of every open method are listed.

Rollback. on NO-GO take the rollback without switching anything: the clients still point at the source, which is current to the freeze

Verification: the checks after the transfer

These are the checks the owner makes with the writers stopped before recording GO; the reconcile step in the execution plan states the same checks, with the same ids. A check with no command says why: the plan states no command it cannot back.

• 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; the tables of the schemas metric_helpers and user_management, which every PostgreSQL Flex instance keeps for the platform, are left out of the comparison.

~~~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; ~~~

• Content on both sides (extensions, command): List the extensions and their versions on both sides: the restore or the subscription does not create on the target what it does not offer. Expected: every extension the application uses is on the target; a difference is settled and recorded.

~~~text SELECT extname, extversion FROM pg_extension ORDER BY 1; ~~~

• Content on both sides (roles-and-grants, command): List the roles and the table grants on both sides: the schema is copied with --no-owner --no-acl, so the owners and the grants of the source are not on the target, and a user of Cloud SQL for PostgreSQL that signs in with an IAM identity has no counterpart on PostgreSQL Flex, which has password users only. Expected: every login the application uses exists on the target with the grants it needs; cloudsqlsuperuser, the roles of the platform of each side and the users that signed in with an IAM identity are settled one by one.

~~~text SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname !~ '^pg_' ORDER BY 1; SELECT grantee, table_schema, table_name, privilege_type FROM information_schema.role_table_grants WHERE table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY 1, 2, 3, 4; ~~~

• 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 Cloud SQL for PostgreSQL database; 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 Cloud SQL for PostgreSQL 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 Cloud SQL for PostgreSQL database with a method rehearsed before the window, or accepted as lost. Once the Cloud SQL for PostgreSQL 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 Cloud SQL for PostgreSQL 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 Cloud SQL for PostgreSQL database is kept; the method that copies the writes of the target database back, or the decision that they are lost.