Executive Summary: This playbook examines a subtle correctness problem in logical replication: ensuring that a stored generated column on the publisher yields the expected outcome at the subscriber. Simply seeing a “green” subscription with rows inserted can be misleading if the generated column’s value isn’t what the publisher intended. By defining a clear acceptance contract and using controlled tests, we systematically verify whether the generated column is correctly handled. We pin PostgreSQL versions (18.x, the first to support generated columns in replication), build isolated fixtures with various schema configurations and publication options (including column lists), and capture both expected and observed results. Each test case is compared against an independently authored manifest of expected tuples. Negative controls and failure artifacts are preserved to avoid false positives, and we log a case-by-case evidence ledger. We then reconcile identities in the rowset, distinguish what the documentation guarantees from what was observed in the test, and, if needed, apply a narrowly scoped fix (e.g. adding the missing column and re-running replication). Finally, we apply a decision matrix (ACCEPT / REPAIR / RERUN / HOLD) based on the evidence. This evidence-led approach echoes the rigorous, outcome-focused testing seen in related Refonte Learning articles. By the end, senior engineers will have a blueprint to prove whether the generated column truly replicated (or not), rather than relying on surface success indicators.
Logical replication can successfully deliver rows while a generated column’s value lags behind expectations. For example, if the publisher defines b = a+1 and the subscriber defines b = a*100, a basic check may show rows in the subscriber but with values 100,200,300 instead of 2,3,4. Such a misleading success is precisely what this playbook catches. We will demonstrate the exact conditions under which generated columns are or aren’t transmitted (as per PostgreSQL docs), and then turn that knowledge into a controlled test suite with a clear pass/fail decision.
Define the Acceptance Contract
First, we state what “correct replication” means for generated columns, before we run any tests. The acceptance contract defines the precise, independently determined outcome that must hold. In this context, that means specifying exactly which rows and column values the subscriber should have after replication. For our generated column scenario, the contract depends on configuration: if publish_generated_columns is disabled (the default), then any stored generated column on the publisher is not sent; the subscriber must supply its own value (via its own generated column logic or column default). If publish_generated_columns='stored' is enabled or the column is explicitly listed in the publication, then the publisher’s stored value should be delivered (and thus the subscriber’s column should be an ordinary column receiving that value). So our contract will account for both scenarios:
No publish of generated columns: then each subscriber row should compute column b according to the subscriber’s own formula or default. For example, if subscriber table defines b GENERATED AS (a*100), we expect subscriber values 100,200,300 etc.
Publish of generated columns (param or column-list): then subscriber should have a regular column receiving exactly the publisher’s value (2,3,4 in our example).
We write these expected outcomes in advance. This is analogous to defining a release or continuity contract before testing, as recommended in rigorous runbooks. For instance, one Refonte Learning article on data import states that “the release contract must be written before looking at the output”. In our case, the “release contract” is the expectation that each row’s business key and generated value appear correctly on the subscriber.
In practical terms, the contract might be documented as: “When publisher inserts (a=1), subscriber should end up with (b=2) if replication of b is enabled, or (b=100) (the subscriber’s own formula) if not.” We tie each test to this contract. If subscriber content diverges from the contract (e.g. subscriber’s b is neither of the two expected values), that is a violation. We will only declare the test passed (ACCEPT) when the observed subscriber tuples exactly match the contract row-for-row (see per-case identity/state ledger below). This framing avoids the trap of assuming “no errors = success.” Like the Ansible dry-run example, a zero-error summary is not sufficient evidence by itself – we require concrete evidence of each intended result.
Capture the Baseline and Pin Versions
We must fix the software baseline exactly. PostgreSQL 18 (and above) is required since it was the first major release to allow replication of stored generated columns. The official documentation warns, for example, “prior to version 18.0, logical replication does not publish GENERATED columns at all”. Therefore, we choose a specific PG 18.x build (for instance, 18.6 from September 2026) for both publisher and subscriber. We avoid any subscriber older than 18, because earlier versions simply won’t copy stored generated columns (initial table sync would ignore them). All this is documented:
“If the subscriber is from a release prior to 18, then initial table synchronization won’t copy generated columns even if publish_generated_columns is stored”.
We record the exact versions on both sides before proceeding. For example, run SELECT version(); on the publisher and subscriber to capture the full version strings. We note the dates, system architecture, and settings. A template assertion can be:
-- On publisher:
SELECT version(); -- EXPECTED / NOT EXECUTED
-- PostgreSQL 18.6 (Debian 18.6-1) on x86_64-pc-linux-gnu...
-- On subscriber:
SELECT version(); -- EXPECTED / NOT EXECUTED
-- PostgreSQL 18.6 (Debian 18.6-1) on x86_64-pc-linux-gnu...By pinning to these versions (not just “18.x” but exact minor version and build), we eliminate ambiguity. We also freeze any relevant parameters: e.g. ensure publish_generated_columns is explicitly set to NONE or STORED, and note this in the setup script. The official docs make it clear that explicitly listed columns override the parameter, but if we omit a column list then setting publish_generated_columns to stored is how to enable replication of stored generated columns.
Pin exact environment versions
We use a minimal isolated environment to avoid hidden differences. For instance, two fresh initdb clusters (or two Docker containers) could be started. We record their build metadata, such as OS kernel and build flags, and any installed extensions. For example, capture pg_config --version and system info. We insist on recording every piece of the setup (cluster IDs, config files, etc.) as per best practices. As one article summarizes:
“The runbook baseline should record the exact PostgreSQL 18 minor version in use, packaging source, logical output plugin, connector version, HA manager, and configuration revision”. This documentation-led vigilance prevents variant behavior from creeping in.
A concrete assertion, for instance, might be:
$ psql -p 5432 -tAc "SELECT version()" # EXPECTED / NOT EXECUTED
PostgreSQL 18.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 12.2.0-9ubuntu2), 64-bit
$ psql -p 5432 -c "\conninfo" # EXPECTED / NOT EXECUTED
You are connected to database "postgres" as user "test" on host "localhost" at port "5432".We do the same on the subscriber instance (e.g. port 5433). A mismatch (say if the subscriber were 17.x) would itself be a test failure requiring hold – we would not reinterpret the contract for a different version. In summary, we concretely pin versions and record them in the fixture to guarantee reproducibility.
Build the Disposable Controlled Fixture
With versions pinned, we construct the actual test scenario from the ground up. This means initializing empty databases and schemas, creating the publisher table with a generated column, populating synthetic rows, and creating a matching or differing schema on the subscriber depending on the test. The environment is disposable: it can be thrown away after the run to ensure isolation. Here is an example sequence (shown as shell/psql commands), using PostgreSQL 18 on localhost:
# Initialize publisher database (Port 5432) and subscriber (Port 5433):
initdb -D /tmp/pubdata # EXPECTED / NOT EXECUTED
initdb -D /tmp/subdata # EXPECTED / NOT EXECUTED
pg_ctl -D /tmp/pubdata -o "-p 5432" start # EXPECTED / NOT EXECUTED
pg_ctl -D /tmp/subdata -o "-p 5433" start # EXPECTED / NOT EXECUTED
# On publisher: create table with a stored generated column and insert rows
psql -p 5432 -c "CREATE TABLE items (
id INT PRIMARY KEY,
a INT,
b INT GENERATED ALWAYS AS (a + 1) STORED
);" # EXPECTED / NOT EXECUTED
psql -p 5432 -c "INSERT INTO items (id,a) VALUES (1,100),(2,200),(3,300);" # EXPECTED / NOT EXECUTED
# Create a publication without a column list (default excludes generated)
psql -p 5432 -c "CREATE PUBLICATION pub_default FOR TABLE items;" # EXPECTED / NOT EXECUTED
# Create a publication that enables generated columns (param way)
psql -p 5432 -c "CREATE PUBLICATION pub_gencol
FOR TABLE items
WITH (publish_generated_columns = 'stored');" # EXPECTED / NOT EXECUTED
# Create a publication that explicitly lists the generated column (column-list way)
psql -p 5432 -c "CREATE PUBLICATION pub_col_list FOR TABLE items (id,a,b);" # EXPECTED / NOT EXECUTEDNext, set up the subscriber database(s) to match different test cases:
# On subscriber: CASE 1 schema (with same generated definition as publisher)
psql -p 5433 -c "CREATE TABLE items_gen (
id INT PRIMARY KEY,
a INT,
b INT GENERATED ALWAYS AS (a * 100) STORED
);" # EXPECTED / NOT EXECUTED
# CASE 2 schema: generated column defined differently or not at all
psql -p 5433 -c "CREATE TABLE items_reg (
id INT PRIMARY KEY,
a INT,
b INT -- a regular column, no default
);" # EXPECTED / NOT EXECUTED
# CASE 3 schema: same as CASE 2 but with a default on b
psql -p 5433 -c "CREATE TABLE items_reg_def (
id INT PRIMARY KEY,
a INT,
b INT DEFAULT -1
);" # EXPECTED / NOT EXECUTED
# CASE 4 schema: no 'b' column at all
psql -p 5433 -c "CREATE TABLE items_missing (
id INT PRIMARY KEY,
a INT
);" # EXPECTED / NOT EXECUTEDNow for each schema, create a subscription to one of the publications. For example:
# Subscription for CASE 1: expecting subscriber formula (no published gen)
psql -p 5433 -c "CREATE SUBSCRIPTION sub1
CONNECTION 'host=localhost port=5432 dbname=postgres user=postgres'
PUBLICATION pub_default;" # EXPECTED / NOT EXECUTED
# Subscription for CASE 2: testing publish_gen = stored
psql -p 5433 -c "CREATE SUBSCRIPTION sub2
CONNECTION 'host=localhost port=5432 dbname=postgres user=postgres'
PUBLICATION pub_gencol;" # EXPECTED / NOT EXECUTED
# Subscription for CASE 3: using column list
psql -p 5433 -c "CREATE SUBSCRIPTION sub3
CONNECTION 'host=localhost port=5432 dbname=postgres user=postgres'
PUBLICATION pub_col_list;" # EXPECTED / NOT EXECUTEDAt this point, the tables should be synchronized (initial copy). We then proceed to query the subscriber tables to see the results. Throughout, we use independent inputs (the inserted rows and table definitions above) and record everything before looking at outputs, to avoid circularity (as Refonte emphasizes). No manual copying of results into “expected” – the expected values are authored before checking.
The fixture commands above are unambiguous and repeatable. In actual automation, each of those psql lines would be executed and its exit code captured. All results in this write-up are marked EXPECTED / NOT EXECUTED since we haven’t run them in a live environment. We treat this like a planning document for the test harness. (In contrast, a true execution would mark some lines OBSERVED and compare to EXPECTED.)
Author the Independent Expected-Case Manifest
Before running our comparisons, we create an oracle (expected manifest) of what should happen for each case. This manifest is a table of input conditions and the exact subscriber-row outcomes we expect in accordance with the contract, independent of the test results. For each case (C0, C1, etc.), we specify the expected id and b values on the subscriber after replication. For example:
Case | Publisher state | Subscriber table | Publication setting | Expected subscriber rows (id | b) | Decision (if met) |
C0 | items has (1,100),(2,200),(3,300); b=a+1 | items_gen (b = a*100) | pub_default (no gen) | (1, 100), (2, 200), (3, 300) | ACCEPT if rows match |
C1 | same pub rows | items_gen | pub_gencol (publish=stored) | Error/unsupported (expected FAIL) | HOLD (unsupported) |
C2 | same pub rows | items_reg | pub_default (no gen) | (1, DEFAULT), (2, DEFAULT), (3, DEFAULT) with default=NULL (or user-provided default) | REPAIR (fill default) |
C3 | same pub rows | items_reg | pub_gencol (stored) | (1, 101), (2, 201), (3, 301) (publisher values) | ACCEPT if matches |
C4 | same pub rows | items_reg_def | pub_default | (1,-1), (2,-1), (3,-1) (default -1) | REPAIR (if default not allowed) |
C5 | same pub rows | items_missing | pub_default | (1), (2), (3) (only id) | HOLD (missing col) |
C6 | same pub rows | items_missing | pub_gencol | Error (missing column) | HOLD |
C7 | (Negative control) any | add unrelated row | any | Should detect missing/extra rows | HOLD (or test fails) |
In C0, no generated columns are published, and subscriber has its own formula: so we expected subscriber values (100,200,300) exactly as per our independent oracle. In C3, we expect the publisher’s values (101,201,301) because we enabled publishing. C1 and C6 are expected to produce errors (we mark them HOLD by default because replication shouldn’t silently succeed). C2 shows that without publishing, the subscriber’s regular column b receives only its default (NULL here, since no default was set) – which violates our contract. Thus if C2 still ended up with non-NULLs, that’s a failure of the contract and should trigger REPAIR (we’d have to populate or change schema). C7 is an explicit negative control: we could, for instance, inject an extra wrong row into the subscriber table that does not correspond to any input; the test must detect that mismatch even if the row count accidentally matches.
We see that each case has an expected outcome and an associated accept/reject decision. The expected manifest is independent: it was authored before executing any test query. (We do not just copy the observed subscriber output into the “expected” set after the fact, which would be circular and invalid as noted by testing best practices.)
Specify a Falsifiable Negative Control
A negative control is a scenario designed to show that our test is sufficiently sensitive. For example, suppose we mistakenly believed that simply matching row counts is enough. We can falsify that by creating a scenario where the row count stays the same but an identity is wrong. For instance, if the subscriber somehow ended up with (1,100),(2,200),(4,300) instead of (1,100),(2,200),(3,300), the row count is 3 in both cases, but identity “3” was lost and “4” inserted. This situation should cause the test to flag a discrepancy. In practice, we might script such a negative control like this:
# Negative control example (not executed in real run):
psql -p 5433 -c "DELETE FROM items_gen WHERE id = 3; INSERT INTO items_gen (id,a) VALUES (4, 400);" # tamper subscriber
psql -p 5433 -c "SELECT * FROM items_gen;" # EXPECTED: reveals id 4 instead of 3The expected manifest for this control would list (1,100),(2,200),(4,300) as observed and note that “3” is missing and “4” is extra. The test logic should recognize this mismatch (using EXCEPT ALL or tuple-by-tuple comparison) and yield HOLD because our contract of identical identities is broken. This follows the principle from the COPY test example: “EXCEPT ALL is used instead of a count-only comparison so multiplicity and full tuple identity remain visible. A stage count of five could still conceal one missing row and one unauthorized replacement. The negative control proves that boundary by … requiring the oracle to report both the missing and extra tuple”.
Reproduce the Misleading Green Result
Now we execute the “normal” replication case and show how it can mislead. Consider C0 above: publisher has b=a+1, subscriber has b=a*100, and we publish without generated columns. The subscription will synchronize rows, and a quick inspection might show 3 rows in the subscriber – seemingly green. For example:
-- On publisher (already run):
SELECT FROM items; -- EXPECTED / NOT EXECUTED
id | a | b
----+-----+-----
1 | 100 | 101
2 | 200 | 201
3 | 300 | 301
-- On subscriber items_gen after sync:
SELECT FROM items_gen; -- EXPECTED / NOT EXECUTED
id | a | b
----+-----+-------
1 | 100 | 100
2 | 200 | 200
3 | 300 | 300Here, the subscriber’s b values (100,200,300) are exactly what we see, and no errors occurred. This might look like success, but it is misleading. The subscriber’s b column used its own formula (a*100), not the publisher’s. The published value (101,201,301) never arrived. The database still answered “3 rows copied” (green status), so only by consulting our pre-defined contract do we realize that the expected subscriber b-values, as authored in our manifest, were (100,200,300). In this case, they match exactly, so we'd accept (the subscriber’s calculated values aligned with our contract).
This result echoes the PostgreSQL documentation’s example: a subscriber defined b = a*100 and the output was (1,100),(2,200),(3,300). The key point is that this outcome is “by design” when publish_generated_columns is off – but it differs from publisher values, and we must be sure that our test contract expects it. Without the contract, one might incorrectly assume the publisher’s values should have been delivered.
Run a Valid Comparison Case
Next we exercise a case where the generated column should be replicated. For example C3: subscriber table items_reg has a regular (non-generated) column b, and we enabled publish_generated_columns='stored'. After synchronization, the subscriber should have received the publisher’s computed values (101,201,301). We run:
SELECT * FROM items_reg; -- EXPECTED / NOT EXECUTED
id | a | b
----+-----+-----
1 | 100 | 101
2 | 200 | 201
3 | 300 | 301This output matches the publisher’s original b values. The docs confirm this behavior: “Yes, GENERATED → regular → publisher table column value is replicated to the subscriber table column” when publish_generated_columns is enabled. Our expected manifest for C3 listed exactly (101,201,301), so this case compares as expected. In contrast with the previous “green” run, this verifies that replication is indeed sending the values.
Because we have both cases (C0 and C3), we see the two legitimate branches of behavior. We compare each observed result to the independent manifest. All data needed for this check (published values, subscriber output) are recorded per-case in an evidence ledger. We do not rely on any single summary metric. For instance, we keep the full list of tuples from each table and do a row-by-row comparison, much like a robust test harness would. (No cleanup of data happens until we finalize a decision.)
Preserve failure artifacts
If any step fails, we must not discard information. The fixture is intentionally set up to keep database state and logs until the owner explicitly cleans up, as one validation practice notes: “The script intentionally retains both database state and files. Deleting evidence automatically at successful exit would make later reconciliation harder… Cleanup is therefore an explicit owner action after acceptance”. Concretely, if, say, subscription creation fails or replication crashes, we keep the server log, table contents, and output snapshots. This ensures that after such a failure (e.g. C6’s missing-column error), we can inspect exactly what happened without loss of evidence. In a real run we might rename /tmp/subdata to /tmp/subdata_failed_$(date) rather than rm -rf it. The same principle applies to successful cases: we record the post-replication state even if we “ACCEPT,” so the audit trail is complete.
Exercise Negative and Boundary Cases
Beyond the “expected” paths, we exercise additional scenarios to probe boundaries. For example:
Subscriber default behavior: In C4 the subscriber column b has a default (-1). Since we did not enable generated-column publishing, PostgreSQL should not set b at all and instead the column should take its default. Indeed, the docs say “Publisher GENERATED, Subscriber regular → publisher column not replicated; use subscriber default”. We test this by checking that items_reg_def returns -1 for each row (as our manifest expects). If b were anything else, that would violate the contract.
Missing column, no publish: In C5, the subscriber table has no column b. The table should simply ignore it: “No GENERATED, subscriber --missing-- → nothing happens”. So we expect the subscriber to have only id (and a) present. For instance:
SELECT id, a FROM items_missing; -- EXPECTED / NOT EXECUTED
id | a
----+-----
1 | 100
2 | 200
3 | 300The missing b column is dropped from consideration. We must verify that no error was raised and that all rows arrived in items_missing.
Missing column with publish: In C6, we deliberately enable publish_generated_columns but the subscriber table lacks column b. The docs predict an error: “Yes, GENERATED -> --missing--: ERROR”. We assert that creating or refreshing this subscription results in a clear error (e.g. “column items_missing.b missing”). If the system did not error, that would be a discrepancy with documented behavior and cause HOLD.
Virtual vs stored: We also ensure our table uses stored columns, since the documentation clearly states replication support is currently only for stored generated columns. If we had defined b as virtual, we would expect it to never be replicated (and should not try to; such a test is out of scope per docs). In practice, we only test stored columns here.
These boundary tests ensure we cover “off-by-default,” “enabled,” and schema mismatch scenarios. If any produce unexpected outcomes (for example, a failure where docs promise success, or vice versa), that becomes critical evidence. Each result is compared against the manifest or expected error, and the case is logged accordingly. (We do not stop on the first failure; we collect as much evidence as possible.)
Capture the Per-Case Evidence Ledger
For each test case, we create a structured evidence record. This ledger records the observed data and any relevant metadata. As one methodology article puts it, “A useful evidence ledger is case-oriented rather than run-oriented. Do not store ‘the import passed’ as one Boolean. Record enough evidence to reproduce why a case was classified”. Concretely, for each case C0…C6 we capture fields such as:
Case ID (e.g. “C3”)
Publish parameters (e.g. publish_generated_columns=stored or column list used)
Subscriber schema details (columns & defaults)
Published row list (tuples (id,a,b) from publisher)
Subscriber row list (tuples after sync)
Observed differences (if any)
Command outputs (e.g. any psql notices or errors)
Decision (ACCEPT, REPAIR, etc. preliminary)
For example, we might record:
Case | Publish Gen? | Sub schema (b type) | Published b | Subscriber b | Diff | Decision |
C0 | No | b GENERATED AS a*100 | 101,201,301 | 100,200,300 | Different values (all) | ACCEPT |
C1 | Yes | b GENERATED AS a*100 | 101,201,301 | (none – error) | Replication error (unsupported) | HOLD |
C2 | No | b INT regular | 101,201,301 | NULL,NULL,NULL | Missing values (all) | REPAIR |
C3 | Yes | b INT regular | 101,201,301 | 101,201,301 | None (match) | ACCEPT |
C4 | No | b INT DEFAULT -1 | 101,201,301 | -1,-1,-1 | Different (default) | REPAIR |
C5 | No | no b | 101,201,301 | (no column) | n/a (dropped) | REPAIR/HOLD? |
C6 | Yes | no b | 101,201,301 | (error) | Missing column error | HOLD |
In this ledger, each “Observed difference” is compared to the manifest. Even if a subscription report shows “3 rows” (green), we look at the actual contents. The ledger stays explicit (no collapsing to “pass/fail” until the end). This ensures we can audit exactly what happened for each row.
Reject Incomplete Evidence
We must also ensure our evidence is complete. We do not rely on partial checks like row counts alone. A row count match with the contract is not sufficient proof that identities match, as the negative control demonstrates. As the Ansible example warns: a green check-run is not proof of coverage unless we explicitly verify each intended action. In our ledger, if any field needed for contract (like a generated column value) is missing from the evidence, we must mark that case unresolved (HOLD). For example, simply seeing 3 rows is not enough; we need the values of b. If our query against the subscriber table somehow returned blank results (maybe due to a transient error), we cannot conclude “passed” – we would mark HOLD because evidence is incomplete. In short, any discrepancy or gap in the evidence (e.g. missing rows, silent skips, lack of logs) forces us to err on the side of caution.
Reconcile Expected and Observed Identities
Once we have the evidence, we reconcile the actual subscriber data with the expected manifest row-by-row. In practice this means performing a set-based comparison (e.g. using SQL EXCEPT ALL or a join) of the expected tuples versus observed tuples for each case. The goal is to verify that every expected row appears exactly once, with no extras. We treat this as a “coverage report” problem: “a ledger of checks that maps expected checks to evidence observed”. Here, the “checks” are each individual tuple identity (the combination of key columns), and the evidence is whether the subscriber returned it.
For instance, for C3 we would compare:
WITH expected AS (
SELECT 1 AS id, 100 AS a, 101 AS b
UNION ALL SELECT 2,200,201
UNION ALL SELECT 3,300,301
),
observed AS (
SELECT FROM items_reg
)
SELECT FROM (
(SELECT id,a,b FROM expected EXCEPT ALL SELECT id,a,b FROM observed)
UNION ALL
(SELECT id,a,b FROM observed EXCEPT ALL SELECT id,a,b FROM expected)
) AS diff;This query would list any missing or extra tuple. If the result is empty, all is well. We capture the output (even if empty) in our evidence ledger. The reconciliation explicitly pairs each expected identity with an observed one, or flags the discrepancy. For example, if we saw (1,100,100) instead of (1,100,101), the diff query would show a mismatch on the b value.
We treat the reconciliation as a table of identity mappings. If any row is missing or altered, that immediately triggers a failure decision for that case. This step completes the mapping between expected identities and actual identities. Only after confirming identity-by-identity matching can we say “all expected rows reconciled” – at which point we can consider that case ACCEPT (subject to any other evidence needs). If any discrepancy appears, that case moves to REPAIR or HOLD depending on context. This precise ledger approach avoids the pitfall of assuming “close enough.” It embodies the principle that acceptance is not a boolean on the run but a confirmed match of the entire authorized population.
Separate Documented Guarantees from Observations
Throughout, we keep in mind the documented behavior versus what our tests observe. We never modify the manifest to force a passing outcome. If the tests contradict the official documentation, we note it as such. For instance, the docs state that attempting to publish a generated column to a subscriber that also has a generated column is not supported. If in our environment this produced a different outcome (say, silently ignoring the column), we would record a CONTRADICTION and likely HOLD release. We also remember that documentation applies to specific versions and conditions; for example, a particular error message or behavior might change in a future minor release. Any such difference is captured as versioned observation.
One example: as noted earlier, “generated columns are not published by default”. In code, we explicitly set or list columns to override that default. If a future PostgreSQL deviated, we would mark it. Similarly, we do not assume anything about internal implementation details. We do not, for example, rely on presuming a rollback just because a test failed (as one async test guide warns, “do not add an imaginary rollback claim”). That means we never assume, without evidence, that the database undid changes unless it actually returned a success code for the rollback.
In short, if documentation says “ERROR” in a certain scenario and our test does not see an error, that is a real discrepancy and the test must HOLD. If our test sees an error where the docs say “nothing happens,” likewise we record it. We document those as observation logs. We emphasize that correct behavior is what the documentation contract promises, and our acceptance is only as good as matching that contract. This separation ensures we do not confuse a lucky break with an intended feature.
Bound the Repair Scope
If a case fails, any repair must be tightly scoped. We only correct exactly the issue and nothing more. For example, suppose we find that subscriber items_reg missed column b. The repair is to add that column and populate it, not to alter primary keys or payload data. We explicitly note this boundary: for generated-column replication, the fix might be as simple as:
ALTER TABLE items_reg ADD COLUMN b INT;
UPDATE items_reg SET b = a + 1; -- or copy from publisher via explicit INSERTsand then REFRESH PUBLICATION or re-create the subscription. We would not, for instance, decide to recalc a or delete rows unrelated to this column issue. As one repair guide emphasizes: define the business key and only change the schema element in question. Here the change authorized is “how to replicate b,” so that’s all we touch. Keeping this boundary in mind avoids unintended side effects.
Our “repair plan” is documented alongside the evidence. We might log it as: “Cause: subscriber table missing b column. Repair: add column, run UPDATE ... to fill values, then rerun subscription.” We ensure the repair is brief, clearly enabling RERUN to complete it.
Implement a Bounded Repair
Following our evidence, we now carry out the minimal repair needed to make replication succeed. For example, if C5 (missing column without publish) was evaluated as missing the contract value, a repair could be simply to add a b column on the subscriber. Concretely:
# Example repair for missing column case C5:
psql -p 5433 -c "ALTER TABLE items_missing ADD COLUMN b INT;" # EXPECTED / NOT EXECUTED
psql -p 5433 -c "UPDATE items_missing SET b = a + 1;" # EXPECTED / NOT EXECUTED
psql -p 5433 -c "SELECT * FROM items_missing;" # EXPECTED / NOT EXECUTEDAfter this, the subscriber rows become (1,100,101),(2,200,201),(3,300,301). We would then decide whether to accept or rerun. In this scenario, the repair brought the subscriber table in-line with what it would have been if the schema were correct. Our manifest for “after repair” would be exactly those values, and the query above should show them. We record these results and see they match, so the decision would be to re-accept the case (with a note that a schema fix was applied).
The key point is that the scope of the change was exactly “create column and set values.” We do not introduce any new logic or alter the published data. We document the owner (likely the DBA) who performed the alteration and note the steps. If multiple cases needed multiple distinct fixes, we address them one at a time. For each repair, we mark in our ledger exactly what changed and why. For instance, we might log:
Repair C5: Added column b on subscriber. Populated with formula a+1 to match original intended computed values. Then reran subscription. Verified output matched published b-values.
This makes the transformation auditable and reversible.
Test Rollback and Rerun Safety
Now we check that the steps of replication can be safely rolled back and re-executed if needed. For example, suppose during setup we had an error creating subscription sub6 for C6. We should ensure that the database can revert to a clean state and allow us to re-run after a fix. A simple way is to test a BEGIN ... ROLLBACK around the failing command:
# Simulate rollback on subscriber:
psql -p 5433 <<SQL # EXPECTED / NOT EXECUTED
BEGIN;
CREATE SUBSCRIPTION sub6 CONNECTION '...'
PUBLICATION pub_gencol;
ROLLBACK;
SELECT * FROM pg_subscription WHERE subname='sub6'; -- expects no row
SQLThe result should confirm no partial subscription lingered (no sub6 entry). This test ensures that we can repair the environment (e.g. create the missing column) and then recreate the subscription fresh. If a one-line rollback had left the subscription half-created, we would have detected it (HOLD), but PostgreSQL’s transactional DDL means the failed creation should have been undone automatically upon ROLLBACK.
We also ensure that if we commit a fix and re-create the subscription, the process runs cleanly. For example, after fixing C6’s schema by adding the b column, we run CREATE SUBSCRIPTION again and expect it to work without error. If that second attempt still failed or behaved unexpectedly, we would record it. Ensuring rollback/rerun safety follows the principle that recovery requires re-running the complete controlled case, not applying piecemeal fixes.
Assign Operational Ownership
Finally, we note who is responsible for each part. Similar to the multi-role contract table in the failover example, here we assign ownership of tasks:
DBA/Platform Owner: Setup of test clusters, initial publication/subscription creation, applying schema fixes.
Test Engineer (ourselves): Authoring the manifest, running queries, and analyzing discrepancies.
Data/Application Owner: Verifying that the generated column’s business logic (e.g. b=a+1) is correctly implemented and remains aligned with expectations.
Release Manager/Incident Lead: Interpreting the final acceptance decision and coordinating any deployment actions.
By explicitly naming these roles (for instance, in a case record, marking “Owner: DBA team” for schema changes), we ensure accountability. One Refonte runbook lists roles like “CDC owner” or “data owner” for each contract dimension; we apply the same discipline to our replication test. This doesn’t change the technical outcome, but it does ensure clarity in case questions arise.
Decide ACCEPT, REPAIR, RERUN or HOLD
After all tests are run and evidence collected, we apply the final decision logic. The decision depends on how each case’s observed result matched the expected contract and what type of discrepancy occurred. We follow definitions such as those in Refonte’s lab example, adapted to our context:
ACCEPT: Observed subscriber data exactly matches the independent manifest for every case, including the generated column values or defaults as appropriate. No unresolved failures. In this state, the replication can be considered correct under the given contract.
REPAIR: A fix was required and performed, after which the subscription must be rerun. (For example, a missing column was added, or some source data was corrected.) The test shows that after repair and rerun, the contract is met. The outcome is effectively ACCEPT, but we mark it as ACCEPT after REPAIR.
RERUN: (Alternatively) indicates that evidence was incomplete or needed a second execution without any schema fix (rare in this scenario). Usually our design either accepts as-is or requires an explicit change; pure rerun might not occur unless we consider rerunning initial sync with a changed setting.
HOLD: Any other situation. If the evidence contradicts the contract in an unexplained way, or if an unexpected error occurred that we can’t immediately resolve, we hold. For example, if a generated column silently failed to copy with no error, or if a subscription creation error has an unknown cause, we would HOLD and investigate further.
We can summarize these in a decision matrix:
Observation | Decision |
Subscriber rows match expected manifest exactly | ACCEPT |
Discrepancy can be fixed by schema/data change | REPAIR (then RERUN) |
Test failure with unclear ownership or semantics | HOLD |
Subscription error due to missing column (fixable) | REPAIR |
Subscription error that is unsupported (like gen→gen) | HOLD |
For example, C0 and C3 above would lead to ACCEPT (C0’s outcome was as expected by contract, C3’s was too). C2 and C4 required a repair (adding or adjusting b), so we would label them REPAIR and then ACCEPT after recheck. C1 and C6 caused errors due to unsupported schema; we mark those HOLD (since the contract cannot be met without changing either the publication or table design beyond the bounds of this test). This decision matrix approach follows the logic in the COPY import example, where each outcome leads to one of these four states.
Connect the Practice to Refonte Learning
This evidence-driven methodology mirrors the hands-on, scenario-based training emphasized in Refonte Learning’s Database Administrator Essentials program. The program curriculum underscores “hands-on projects and real-world scenarios” to master database integrity and administration. In our playbook, we likewise build a concrete lab fixture, run fully controlled experiments, and make data-driven acceptance decisions. In fact, the program’s case-study approach is reflected in this style of write-up: every change is planned and verified. Aspiring DBAs in that program would practice exactly these skills – writing expected manifests, pinning versions, tracking evidence, and reconciling results. (If you’re looking to deepen your expertise in such systematic testing and DBA best practices, you might find their Database Administrator Essentials program valuable.)
Final Decision: Applying the above for each case yields a runbook verdict. In our controlled fixture, C0 and C3 pass (ACCEPT), C2 and C4 require a REPAIR then succeed, and C1/C6 remain HOLD due to unsupported schemas. The overall release decision depends on policy (e.g. one might require all cases to be green, or allow ACCEPT after REPAIR). But crucially, the decision is backed by the per-case evidence ledger. Each row discrepancy has been accounted for; nothing is assumed. This ensures that when the database is put into production or a standby is promoted, we have defensible confidence that the generated columns behave correctly under replication.
Checklist of Steps Not Executed: All SQL and shell commands above are marked EXPECTED / NOT EXECUTED, since they describe the intended workflow. Actual execution (running PostgreSQL nodes, creating subscriptions, capturing real outputs) is to be done in an isolated test lab. The SQL blocks provided serve as an executable harness template; in a real run these would be run and the outputs compared to the independent manifest. Any discrepancy between this plan and a live test should be documented as a conflict in evidence.
