Logical replication is useful when PostgreSQL data needs to be replicated between databases without copying the entire physical database state. It is commonly used for migrations, reporting systems, regional databases, upgrades, and other data-distribution scenarios.
However, replicated table data and PostgreSQL sequences do not behave exactly the same way.
This distinction matters for applications that generate identifiers using GENERATED ... AS IDENTITY or traditional sequences. A database migration can appear successful because the replicated rows are present while a sequence on the target database has a different state.
PostgreSQL 19 improves logical replication with changes around sequence replication and recovery. The release documentation describes enhancements to sequence synchronization and logical replication behavior, making it important for teams to retest migration and failover procedures rather than assuming that table replication alone guarantees complete application consistency.
For .NET applications using PostgreSQL through Npgsql, sequence correctness is especially important when new records are inserted after a migration or replica promotion.
What Is Logical Replication?
PostgreSQL logical replication copies changes at the logical level.
A simplified architecture is:
Source PostgreSQL
|
| Logical Changes
v
Publication
|
v
Replication Stream
|
v
Subscription
|
v
Target PostgreSQL
The source database publishes changes, while the target database subscribes to them.
For example:
CREATE PUBLICATION app_publication
FOR TABLE customers, orders;
The target can then subscribe:
CREATE SUBSCRIPTION app_subscription
CONNECTION 'host=source-db ...'
PUBLICATION app_publication;
The exact connection string and authentication configuration depend on the environment.
Logical replication is different from physical streaming replication because it operates on logical changes to selected objects rather than maintaining a byte-level replica of the entire database.
Why Sequences Are Different
Consider a table:
CREATE TABLE orders
(
id bigint GENERATED ALWAYS AS IDENTITY,
customer_id bigint NOT NULL,
total numeric(12, 2) NOT NULL
);
When an application inserts a row without specifying the ID:
INSERT INTO orders (customer_id, total)
VALUES (101, 49.99);
PostgreSQL obtains an identifier from the underlying sequence.
The important distinction is:
Table rows
≠
Sequence state
Replicating the row:
orders.id = 1001
does not historically imply that the target's sequence has automatically advanced to the equivalent position.
That can become a problem after replication is stopped or the target begins accepting writes.
A Simple Sequence Problem
Imagine the source database has:
Last generated ID = 5000
The target contains the replicated rows:
IDs:
4998
4999
5000
But the target sequence might still be behind.
A new application insert could then attempt to generate an ID that conflicts with an existing row.
Conceptually:
Target sequence
↓
Generates 4999
↓
Existing row 4999
↓
Duplicate key error
This is why sequence state needs to be part of replication testing.
PostgreSQL 19 and Sequence Replication
PostgreSQL 19 includes improvements to logical replication of sequences.
The release introduces support for more reliable sequence synchronization in logical replication scenarios, including handling sequence state as part of replication rather than treating table rows as the only relevant data.
This does not mean that teams can stop testing sequences.
Sequence behavior still depends on:
Publication configuration
Subscription configuration
Replication direction
Sequence ownership
Write activity
Promotion or cutover strategy
Conflict handling
The safest approach is to verify the exact PostgreSQL 19 configuration used by the application.
Inspecting Sequence State
PostgreSQL provides sequence metadata through system catalogs.
For example:
SELECT
schemaname,
sequencename,
last_value
FROM pg_sequences
WHERE schemaname = 'public';
This can help compare sequence state between source and target databases.
For a specific sequence:
SELECT last_value
FROM public.orders_id_seq;
The sequence name depends on how the table and identity column were created.
Do not assume that every identity column uses the same sequence name.
Testing Source and Target Consistency
A useful migration test compares both databases.
Suppose the source contains:
Source:
orders rows = 10,000
sequence state = 10,000
and the target reports:
Target:
orders rows = 10,000
sequence state = 10,000
That is a good starting point.
But row count alone is not enough.
A better validation includes:
Row consistency
+
Sequence consistency
+
Replication status
+
Application write test
A Practical Migration Test
Consider a .NET application using Npgsql.
Before migration:
Source DB
|
+-- Orders
+-- Customers
+-- Sequences
After logical replication:
Source DB
|
v
Target DB
|
+-- Replicated rows
+-- Replicated sequence state
The application should then perform a controlled insert against the target.
For example:
await using var connection =
await dataSource.OpenConnectionAsync(
cancellationToken);
const string sql = """
INSERT INTO orders (customer_id, total)
VALUES (@customerId, @total)
RETURNING id;
""";
await using var command =
new NpgsqlCommand(sql, connection);
command.Parameters.AddWithValue(
"customerId",
101);
command.Parameters.AddWithValue(
"total",
49.99m);
var id = await command.ExecuteScalarAsync(
cancellationToken);
The critical verification is that PostgreSQL generates a valid, non-conflicting identifier.
Why Npgsql Applications Should Test This
A .NET application may not explicitly interact with the sequence.
The code simply executes:
INSERT INTO orders (...)
VALUES (...)
RETURNING id;
PostgreSQL handles identifier generation.
That makes sequence problems easy to miss during application testing.
The application may appear completely healthy until the first insert after a database migration or promotion.
A migration test should therefore include actual application writes rather than checking only replication status.
Testing Sequence Recovery
A useful test plan can simulate an interruption.
Step 1: Generate Data
Insert several records into the source:
INSERT INTO orders (customer_id, total)
VALUES
(101, 20.00),
(102, 30.00),
(103, 40.00);
Step 2: Record Sequence State
SELECT last_value
FROM public.orders_id_seq;
Step 3: Allow Replication
Wait until the target receives the changes.
Step 4: Compare Target State
Run the same sequence query on the target.
Step 5: Test a New Insert
Execute:
INSERT INTO orders (customer_id, total)
VALUES (104, 50.00)
RETURNING id;
Step 6: Verify Uniqueness
Confirm that the generated ID does not conflict with existing records.
This test should be repeated after controlled replication interruptions where sequence recovery is part of the migration scenario.
Replication Status Is Not Application Consistency
A subscription can appear operational:
Replication status = streaming
while an application still encounters a data problem.
Always separate:
Replication health
+
Data consistency
+
Application correctness
A healthy replication connection is only one part of the verification process.
Handling Concurrent Writes
Sequence testing becomes more complicated when both databases can generate IDs.
Consider:
Source → generates 1001
Target → generates 1001
Now both systems have generated the same identifier.
Logical replication does not automatically make independently generated identifiers globally unique.
For systems with multi-primary or bidirectional write requirements, identifier allocation must be designed explicitly.
Possible strategies include:
The right choice depends on the architecture.
Sequence Consistency vs Row Consistency
These are related but different checks.
| Check | What It Verifies |
|---|
| Row count | Approximate data volume |
| Row checksum | Data similarity |
| Primary-key comparison | Missing/conflicting rows |
| Sequence state | Future ID generation |
| Replication status | Replication process health |
| Application insert | End-to-end correctness |
A strong migration test should use multiple checks.
Example Consistency Query
For a simple validation, compare the highest ID:
SELECT MAX(id)
FROM orders;
Then compare it with the sequence state:
SELECT last_value
FROM orders_id_seq;
These values can differ for legitimate reasons, so they should not be treated as universally interchangeable.
For example, sequence values can be consumed without a corresponding committed row.
The purpose of this comparison is to identify suspicious divergence that requires investigation.
Testing Replication Interruptions
Production systems should test what happens when replication temporarily stops.
For example:
Source
|
X
Replication interruption
|
v
Target
After replication resumes:
Replication resumes
↓
Changes catch up
↓
Sequence state reconciles
↓
Application write test
Do not consider the recovery successful simply because the subscription returns to a healthy state.
Verify the resulting data and sequence behavior.
Common Mistakes
Checking Only Table Rows
A table can contain all expected rows while sequence state is unsuitable for future writes.
Assuming MAX(id) Equals Sequence State
They can differ legitimately.
Use both values as diagnostic signals rather than treating them as guaranteed equivalents.
Testing Only Read Queries
A migration can pass every read test and still fail on the first generated-ID insert.
Allowing Independent Writes Without an ID Strategy
Multiple writers need a deliberate identifier-generation design.
Treating Replication Status as Proof of Consistency
A healthy replication connection does not prove application-level correctness.
Forgetting Identity Columns
Modern PostgreSQL applications often use identity columns rather than explicitly created sequences. The underlying sequence still needs to be considered during migration planning.
Troubleshooting Sequence Problems
Check the Sequence
SELECT last_value
FROM public.orders_id_seq;
Check Existing IDs
SELECT MAX(id)
FROM public.orders;
Check Duplicate IDs
SELECT id, COUNT(*)
FROM orders
GROUP BY id
HAVING COUNT(*) > 1;
A properly configured primary key should prevent duplicates, so this query is primarily useful for controlled diagnostic scenarios.
Check Subscription Status
Inspect the logical replication subscription and its worker status.
Perform an Application Insert
The most meaningful test is often:
.NET application
↓
INSERT
↓
Generated ID
↓
Commit
If that succeeds with the expected identifier behavior, the application-level migration test is much stronger.
Best Practices
Include sequence state in logical replication migration testing.
Test both replicated reads and new application writes.
Compare source and target sequence behavior.
Test replication interruption and recovery.
Verify identity columns and their underlying sequences.
Avoid assuming MAX(id) represents sequence state exactly.
Design identifier generation carefully for multiple writers.
Validate replication status separately from application consistency.
Use controlled test data before production cutover.
Record sequence and replication state before and after migration.
Advantages and Disadvantages
Advantages
Helps identify sequence-related migration problems early.
Supports safer PostgreSQL logical replication testing.
Allows .NET teams to validate database behavior through real application operations.
PostgreSQL 19 improves logical sequence replication capabilities.
Encourages end-to-end consistency testing rather than relying only on replication status.
Disadvantages
Logical replication remains operationally complex.
Sequence behavior depends on the exact replication architecture.
Multiple writers require additional identifier-generation planning.
Row consistency does not automatically guarantee sequence consistency.
Migration testing requires more than a simple replication health check.
Conclusion
Logical replication is a powerful PostgreSQL capability, but successful row replication is only part of the migration story.
Sequences deserve their own validation because applications commonly depend on PostgreSQL to generate identifiers automatically.
PostgreSQL 19 improves logical replication support for sequence synchronization, but production teams should still test the complete workflow:
Source Data
↓
Logical Replication
↓
Target Data
↓
Sequence State
↓
.NET Application Insert
↓
Generated Identifier
↓
Consistency Verification
For Npgsql-based .NET applications, the strongest test is not simply confirming that the target contains the expected rows. Perform an actual insert after replication recovery or migration and verify that PostgreSQL generates a valid identifier without conflicts.
That end-to-end test connects database replication behavior with the application behavior that ultimately matters to users.