Introduction
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 scenarios such as:
Database migrations
Reporting systems
Data integration
Distributed application architectures
Gradual platform upgrades
Selective data replication
Sequences introduce an important complication.
A table can replicate its rows successfully while the associated sequence state does not behave exactly as expected on the subscriber. If an application uses sequences to generate identifiers, incorrect sequence state can eventually result in duplicate-key errors or unexpected identifier behavior.
PostgreSQL 19 Beta 3 includes work related to logical-replication sequence synchronization and introduces REFRESH SEQUENCES functionality. That makes it a useful release to evaluate from a failure-recovery perspective.
The goal of this article is not to treat the beta as a production recommendation. Instead, we will design a controlled test that answers a practical question:
What happens to sequence state during logical replication, and how can sequence synchronization be validated after replication changes or failures?
Understanding PostgreSQL Sequences
A PostgreSQL sequence is an independent database object that generates numeric values.
A common example is:
CREATE SEQUENCE customer_id_seq
START WITH 1
INCREMENT BY 1;
An application can obtain the next value with:
SELECT nextval('customer_id_seq');
The result might be:
1
2
3
4
5
A table can use a sequence to generate identifiers:
CREATE TABLE customers
(
id bigint PRIMARY KEY
DEFAULT nextval('customer_id_seq'),
name text NOT NULL
);
The important detail is that the sequence is separate from the table.
That distinction matters when replication is involved.
Why Sequence State Matters in Replication
Consider a publisher and subscriber:
Publisher
|
| Logical Replication
v
Subscriber
The table rows can be replicated correctly:
Publisher customers
|
v
Subscriber customers
But the sequence state also needs to be considered:
Publisher sequence
|
?
v
Subscriber sequence
If the subscriber sequence does not reflect the values already present in the subscriber table, a future insert may attempt to reuse an identifier.
For example:
Subscriber table:
1
2
3
4
5
Subscriber sequence:
1
The next generated value could conflict with an existing row.
Logical Replication Test Environment
A controlled benchmark should use two PostgreSQL instances.
For example:
Publisher
PostgreSQL 19 Beta 3
|
v
Logical Replication
|
v
Subscriber
PostgreSQL 19 Beta 3
Keep the environment isolated.
Use test databases and synthetic data rather than production information.
A containerized environment can make repeated testing easier, but the exact container configuration is not important to the replication experiment itself.
Creating the Publisher Table
Start with a sequence and table:
CREATE SEQUENCE customer_id_seq
START WITH 1
INCREMENT BY 1;
CREATE TABLE customers
(
id bigint PRIMARY KEY
DEFAULT nextval('customer_id_seq'),
name text NOT NULL
);
Insert test data:
INSERT INTO customers (name)
VALUES
('Alice'),
('Bob'),
('Charlie');
Verify the generated identifiers:
SELECT *
FROM customers
ORDER BY id;
Expected structure:
id | name
---+--------
1 | Alice
2 | Bob
3 | Charlie
The exact values depend on the sequence's current state.
Creating the Publication
Logical replication uses a publication on the publisher.
For example:
CREATE PUBLICATION customer_publication
FOR TABLE customers;
The publication defines the data that can be replicated.
The subscriber then creates a subscription that connects to the publisher.
The exact connection configuration depends on the test environment.
Preparing the Subscriber
Create the corresponding table on the subscriber:
CREATE SEQUENCE customer_id_seq
START WITH 1
INCREMENT BY 1;
CREATE TABLE customers
(
id bigint PRIMARY KEY
DEFAULT nextval('customer_id_seq'),
name text NOT NULL
);
The structure should match the publisher.
The important part of the test is to keep the schema equivalent while observing sequence behavior separately.
Creating the Subscription
A subscription can be created using:
CREATE SUBSCRIPTION customer_subscription
CONNECTION '...'
PUBLICATION customer_publication;
The connection string should point to the isolated publisher test database.
Do not place real production credentials in test scripts.
After the subscription is active, verify the replicated rows:
SELECT *
FROM customers
ORDER BY id;
The subscriber should contain the published rows.
Checking Sequence State
Checking the table is not enough.
Inspect the sequence separately.
For example:
SELECT last_value
FROM customer_id_seq;
Then compare the sequence state with the highest identifier:
SELECT MAX(id)
FROM customers;
Conceptually:
Table maximum ID
|
v
Compare
^
|
Sequence state
A useful validation rule is that the sequence must not generate an identifier that conflicts with an existing row.
Testing a New Insert
After replication, test a local insert on the subscriber:
INSERT INTO customers (name)
VALUES ('David')
RETURNING id;
Then verify:
SELECT *
FROM customers
ORDER BY id;
If the sequence is behind the existing data, the insert can expose the problem immediately.
For example, a sequence attempting to generate an existing identifier could produce a duplicate-key error.
That is exactly the type of failure a sequence-synchronization test should detect.
Testing Sequence Divergence
To test recovery behavior, intentionally create a controlled sequence difference.
For example:
SELECT setval(
'customer_id_seq',
1,
true
);
The exact effect of setval depends on the value and is_called argument, so inspect the resulting sequence state before proceeding.
Now compare:
SELECT MAX(id)
FROM customers;
with:
SELECT last_value
FROM customer_id_seq;
The test environment now represents a sequence that is potentially behind the table data.
Why REFRESH SEQUENCES Matters
PostgreSQL 19 Beta 3 includes changes related to logical replication sequence synchronization, including REFRESH SEQUENCES.
The important operational concept is that sequence state can be explicitly synchronized instead of relying only on the initial replication setup.
The exact syntax and behavior should be validated against the PostgreSQL 19 Beta 3 build being tested because this is beta software and behavior can change before the final release.
A conceptual workflow is:
Replication
|
v
Detect Sequence Drift
|
v
Refresh Sequence State
|
v
Validate Next Value
This is particularly useful for migration and recovery testing.
Testing Sequence Recovery
A recovery test can follow these steps.
Step 1: Insert Initial Data
Insert several records on the publisher.
INSERT INTO customers (name)
VALUES
('Customer A'),
('Customer B'),
('Customer C');
Step 2: Allow Replication
Wait until the subscriber contains the rows.
Step 3: Inspect Both Sequences
Record the sequence state on both databases.
Step 4: Introduce Controlled Drift
Modify the subscriber sequence in the test environment.
Step 5: Run Sequence Synchronization
Use the PostgreSQL 19 sequence-refresh functionality being evaluated.
Step 6: Validate
Compare:
SELECT MAX(id)
FROM customers;
with the next generated identifier.
Step 7: Insert a New Row
INSERT INTO customers (name)
VALUES ('Customer D')
RETURNING id;
The new identifier should not conflict with an existing row.
Testing Sequence Growth
A stronger test uses a larger dataset.
For example:
INSERT INTO customers (name)
SELECT 'Customer ' || generate_series
FROM generate_series(1, 10000);
Then compare the table's highest identifier and sequence state.
The objective is not to claim that a particular dataset represents production performance.
It is to create enough state to expose synchronization problems that may not appear with only two or three rows.
Testing Multiple Sequences
Real applications usually have more than one sequence.
Create several tables:
CREATE TABLE customers
(
id bigint GENERATED BY DEFAULT AS IDENTITY,
name text NOT NULL
);
CREATE TABLE orders
(
id bigint GENERATED BY DEFAULT AS IDENTITY,
customer_id bigint NOT NULL
);
Now the test becomes:
Publisher
|
+--> customers sequence
|
+--> orders sequence
|
v
Subscriber
Validate every sequence independently.
One synchronized sequence does not prove that all sequences are synchronized.
Testing Gaps
Sequences can contain gaps.
For example:
1
2
4
5
The missing value does not necessarily indicate a replication problem.
Sequences are not transactionally gap-free counters.
Therefore, a benchmark should not define:
No gaps = Success
as its validation rule.
A better rule is:
New generated value
|
v
Does it conflict with existing data?
This distinction prevents false positives during testing.
Testing Rollback Scenarios
Sequence behavior can also surprise developers when transactions roll back.
For example:
BEGIN;
INSERT INTO customers (name)
VALUES ('Temporary');
ROLLBACK;
The sequence may still have advanced.
That means:
Sequence gap
does not automatically indicate:
Replication failure
The benchmark should distinguish normal sequence behavior from actual synchronization problems.
Testing Concurrent Inserts
A production-like test should eventually include concurrent activity.
For example:
Publisher
|
+--> Writer A
|
+--> Writer B
|
+--> Writer C
|
v
Logical Replication
|
v
Subscriber
Measure whether sequence state remains valid as the publisher continues generating identifiers.
The exact concurrency level should reflect the intended workload.
Do not publish arbitrary throughput numbers without documenting the test environment and methodology.
Testing Failover and Migration Scenarios
Sequence synchronization becomes particularly important during database migrations.
A simplified migration scenario is:
Old Database
|
v
Logical Replication
|
v
New Database
|
v
Application Switch
Before switching the application, validate:
Row counts
Maximum identifiers
Sequence state
Next generated identifiers
Remaining replication lag
Application insert behavior
A migration should not be considered complete simply because table data has arrived.
Common Mistakes
Checking Only Table Data
Rows can look correct while sequence state remains problematic.
Assuming Sequence Values Are Gap-Free
Rollback and other normal sequence behavior can create gaps.
Comparing Sequence Values Without Understanding setval
The meaning of the current sequence state depends on how it was advanced.
Testing Only One Table
Applications commonly have many sequences.
Testing Only Initial Synchronization
Recovery and migration scenarios can expose different problems.
Using Production Data
Replication testing should use isolated databases and synthetic data.
Troubleshooting
If a subscriber produces duplicate-key errors after replication, investigate:
The maximum identifier in the table.
The current sequence state.
The sequence's is_called state where relevant.
Whether local inserts occurred.
Whether replication was temporarily stopped.
Whether sequence synchronization was performed.
Whether multiple sequences exist.
Whether the application uses identity columns or explicit sequences.
Start with the data and sequence state rather than assuming the replication connection itself is broken.
Production Considerations
PostgreSQL 19 Beta 3 is a testing target, not a reason to move production systems to an unreleased version without appropriate validation.
A production migration should include:
Schema Validation
|
v
Data Validation
|
v
Sequence Validation
|
v
Application Testing
|
v
Cutover
Sequence validation deserves its own step because table replication and sequence correctness are separate concerns.
Best Practices
Monitor Sequences Separately
Do not rely only on row replication status.
Validate Before Cutover
Check sequence state before directing application writes to a new database.
Test Multiple Tables
Every sequence or identity-backed table should be included.
Use Synthetic Data
Create controlled datasets that reproduce realistic identifier ranges.
Test Recovery
Do not validate only the initial replication path.
Verify Generated Values
The strongest practical test is to perform an insert and verify that the generated identifier is safe.
Document Sequence Ownership
Know which application or database process is responsible for generating identifiers.
Advantages
Provides a controlled way to evaluate sequence synchronization.
Helps identify problems that table-only replication tests can miss.
Supports safer database migration testing.
Makes sequence drift easier to reproduce and diagnose.
Encourages separate validation of data and database-object state.
Disadvantages
PostgreSQL beta software can change before final release.
Logical replication testing requires multiple database environments.
Sequence behavior has several edge cases that can complicate validation.
Large replication environments require more comprehensive testing.
A successful sequence test does not automatically validate the complete migration architecture.
Conclusion
Logical replication is often evaluated by checking whether the expected rows appear on the subscriber.
That is necessary, but it is not always sufficient.
Sequences are separate database objects, and applications that depend on generated identifiers need their state validated as part of replication and migration testing.
PostgreSQL 19 Beta 3 provides an opportunity to test improved sequence synchronization behavior, including the REFRESH SEQUENCES capability. The most useful way to evaluate it is through controlled failure scenarios: replicate data, inspect sequence state, introduce controlled drift, synchronize the sequence, and then verify that new inserts generate valid identifiers.
The benchmark should also account for multiple sequences, gaps caused by normal sequence behavior, concurrent writes, and migration cutover scenarios.
The key lesson is simple: successful row replication does not automatically prove successful sequence synchronization.
For systems where identifiers are generated by sequences or identity columns, sequence state should be treated as a separate replication concern and validated explicitly before relying on the subscriber for application writes.