July 1, 2025
Summary: in this tutorial, you will learn how to dump / reload some tables, and start the logical replication based on the time of the dump.
Table of Contents
Initial setup
We’re using a source database of PostgreSQL 13 version here. For this simple demo we’ll use pgbench to initialize a simple schema and use these standard tables for the replication:
$ pgbench -i -s 10
dropping old tables...
creating tables...
generating data...
100000 of 1000000 tuples (10%) done (elapsed 0.10 s, remaining 0.93 s)
200000 of 1000000 tuples (20%) done (elapsed 0.33 s, remaining 1.31 s)
300000 of 1000000 tuples (30%) done (elapsed 0.52 s, remaining 1.22 s)
400000 of 1000000 tuples (40%) done (elapsed 0.74 s, remaining 1.10 s)
500000 of 1000000 tuples (50%) done (elapsed 0.96 s, remaining 0.96 s)
600000 of 1000000 tuples (60%) done (elapsed 1.20 s, remaining 0.80 s)
700000 of 1000000 tuples (70%) done (elapsed 1.44 s, remaining 0.62 s)
800000 of 1000000 tuples (80%) done (elapsed 1.61 s, remaining 0.40 s)
900000 of 1000000 tuples (90%) done (elapsed 1.74 s, remaining 0.19 s)
1000000 of 1000000 tuples (100%) done (elapsed 1.98 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | pgbench_accounts | table | postgres
public | pgbench_branches | table | postgres
public | pgbench_history | table | postgres
public | pgbench_tellers | table | postgres
(4 rows)
Let’s assume the problematic table is the large one, which is pgbench_accounts. The other three tables replicate fine and we can put them into the same publication:
CREATE PUBLICATION pub_test FOR TABLE pgbench_branches, pgbench_history, pgbench_tellers;
SELECT * FROM pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
----------+----------+--------------+-----------+-----------+-----------+-------------
pub_test | 10 | f | t | t | t | t
(1 row)
On the target instance, which is PostgreSQL 15 in this case, we need to prepare the same table structures as on the source. You can use pg_dump to create only the schema:
$ pg_dump --schema-only postgres > db.sql
$ psql -d postgres -f db.sql
Setup logical replication
Once we have the structures, we can create the subscription to attach to the just created publication on the source, and wait for the initial snapshot / load to complete:
CREATE SUBSCRIPTION sub_test CONNECTION 'host=localhost port=5435 user=postgres dbname=postgres' PUBLICATION pub_test;
SELECT * FROM pg_subscription;
oid | subdbid | subskiplsn | subname | subowner | subenabled | subbinary | substream | subtwophasestate | subdisableonerr | >
-------+---------+------------+----------+----------+------------+-----------+-----------+------------------+-----------------+----->
16589 | 16559 | 0/0 | sub_test | 10 | t | f | f | d | f | host>
(1 row)
SELECT count(*) FROM pgbench_branches;
count
-------
10
(1 row)
The synchronization for the three tables is up and running and changes are replicated on the fly. This can easily be verified by inserting a row on the source:
INSERT INTO pgbench_branches VALUES (-1, -1, 'aa');
and check for the same row in the target:
SELECT * FROM pgbench_branches WHERE bid = -1;
bid | bbalance | filler
-----+----------+------------------------------------------------------------------------------------------
-1 | -1 | aa
(1 row)
Start replication using pg_dump
Now we need to handle the remaining table. The first step to do is to create a publication for that table in the source:
CREATE PUBLICATION pub_test_2 FOR TABLE pgbench_accounts;
SELECT * FROM pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate
------------+----------+--------------+-----------+-----------+-----------+-------------
pub_test | 10 | f | t | t | t | t
pub_test_2 | 10 | f | t | t | t | t
(2 rows)
For being able to start the replication from a specific point in time, we need a snapshot in the source to start from. This can be done by creating a replication connection to the source database and then define a logical replication slot. The important point here is, that you need to keep this connection open until the replication is fully setup. Otherwise you’ll loose the snapshot:
$ psql "dbname=postgres replication=database"
psql (13.12)
Type "help" for help.
postgres=# CREATE_REPLICATION_SLOT my_logical_repl_slot LOGICAL pgoutput;
slot_name | consistent_point | snapshot_name | output_plugin
----------------------+------------------+---------------------+---------------
my_logical_repl_slot | 0/37404478 | 00000003-00000097-1 | pgoutput
(1 row)
This gives us the snapshot (00000003-00000097-1) we can export from with pg_dump:
$ pg_dump --snapshot=00000003-00000097-1 -a -t public.pgbench_accounts > pgbench_accounts.sql
On the target we’ll load the pgbench_accounts table:
postgres=# \i pgbench_accounts.sql
postgres=# select count(*) from public.pgbench_accounts;
count
---------
1000000
(1 row)
At this point, at least in real life, changes are still going on in the pgbench_accounts table in the source and we do not have them in the target yet. Now we need a new subscription which attaches to the logical replication slot we’ve created in the replication connection. The important point here is, that this subscription does not load the initial data automatically, which can be specified using the following options:
CREATE SUBSCRIPTION sub_test_2
CONNECTION 'host=localhost port=5435 user=postgres dbname=postgres'
PUBLICATION pub_test_2 WITH (slot_name='my_logical_repl_slot', create_slot='false' , enabled='false', copy_data='false');
Having that in place we can start the replication:
ALTER SUBSCRIPTION sub_test_2 ENABLE;
Verification
Adding data on the source should now trigger the replication of the new data to the target:
INSERT INTO pgbench_accounts
SELECT i,i,i,i::text FROM generate_series(1000001, 1000100) i;
On the target we should see 100 additional rows in the pgbench_accounts table:
SELECT count(*) FROM public.pgbench_accounts;
count
---------
1000100
(1 row)
Works as expected. This can be a nice workaround if you have tables which , for whatever reason, cannot be initially replicated in a time which is acceptable for the project.