Spielereien
Kanu
Geopolitik
Business
TopoDB
POI
Klettersteigen
History
TransPool
Thermal Baden
Brokenstuben
Goldwaschen
Blog
Contact
/ home / computer / postgresql / postgresql for mysql admins / .
Logical Replication uses a:
FULL. Can lead to Full-Table-Scan? on the Subscriber side. Only as a fallback if no other solution is possible.pg_dump.Required settings for logical replication on Publisher:
#
# postgresql.conf or zzz-mycompany.conf
#
listen_addresses = '*'
wal_level = logical
max_replication_slots = 10 # default
max_wal_senders = 10 # default
A database instance restart is required here.
postgres=# show wal_level;
wal_level
-----------
logical
Create role:
postgres=# CREATE ROLE replication WITH REPLICATION PASSWORD 'secret';
postgres=# ALTER ROLE replication WITH LOGIN;
postgres=# SELECT rolname AS role, rolsuper AS super, rolcanlogin AS login, rolreplication AS replication
FROM pg_roles
WHERE rolsuper = true or rolreplication = true
;
role | super | login | replication
--------------+-------+-------+-------------
postgres | t | t | t
replication | f | t | t
Adapt pg_hba.conf:
#
# pg_hba.conf
#
# TYPE DATABASE USER ADDRESS METHOD
host all replication 10.223.125.0/24 scram-sha-256
postgres=# SELECT pg_reload_conf();
postgres=# SELECT user_name AS role, address, netmask, database
FROM pg_hba_file_rules
WHERE type != 'local' AND address NOT IN ('127.0.0.1', '::1')
;
role | address | netmask | database
---------------+--------------+---------------+----------
{replication} | 10.223.125.0 | 255.255.255.0 | {all}
{app} | 10.223.125.0 | 255.255.255.0 | {test}
We consider different scenarios here:
TODO: Show all privileges of a row:
postgres=# SELECT datname FROM pg_database WHERE datistemplate != true;
datname
----------
prod
qual
postgres=# \c prod
You are now connected to database "prod" as user "postgres".
prod=# SELECT nspname AS schema
FROM pg_namespace
WHERE nspname NOT IN ('pg_toast', 'pg_catalog', 'information_schema')
;
schema
---------
erp
sales
finance
prod=# GRANT SELECT ON ALL TABLES IN SCHEMA erp TO replication;
prod=# GRANT SELECT ON ALL TABLES IN SCHEMA sales TO replication;
prod=# GRANT SELECT ON ALL TABLES IN SCHEMA finance TO replication;
prod=# \c qual
You are now connected to database "qual" as user "postgres".
qual=# SELECT nspname AS schema
FROM pg_namespace
WHERE nspname NOT IN ('pg_toast', 'pg_catalog', 'information_schema')
;
schema
---------
erp
sales
finance
qual=# GRANT SELECT ON ALL TABLES IN SCHEMA erp TO replication;
qual=# GRANT SELECT ON ALL TABLES IN SCHEMA sales TO replication;
qual=# GRANT SELECT ON ALL TABLES IN SCHEMA finance TO replication;
prod=# SELECT nspname AS schema
FROM pg_namespace
WHERE nspname NOT IN ('pg_toast', 'pg_catalog', 'information_schema')
;
schema
---------
erp
sales
finance
prod=# GRANT SELECT ON ALL TABLES IN SCHEMA erp TO replication;
prod=# GRANT SELECT ON ALL TABLES IN SCHEMA sales TO replication;
prod=# GRANT SELECT ON ALL TABLES IN SCHEMA finance TO replication;
-- GRANT pg_read_all_data TO replication; -- for all schemas in database
prod=# SELECT nspname AS schema
FROM pg_namespace
WHERE nspname NOT IN ('pg_toast', 'pg_catalog', 'information_schema')
;
schema
---------
erp
sales
finance
prod=# GRANT SELECT ON ALL TABLES IN SCHEMA erp TO replication;
postgres=# \c test
test=# SELECT nspname AS schema
FROM pg_namespace
WHERE nspname NOT IN ('pg_toast', 'pg_catalog', 'information_schema')
;
schema
---------
erp
sales
finance
test
test=# SELECT table_catalog, table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema = 'public'
;
table_catalog | table_schema | table_name | table_type
---------------+--------------+------------+------------
test | public | test | BASE TABLE
test=# GRANT SELECT ON TABLE public.test TO replication;
test=# \dp test
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+----------------------------+-------------------+----------
public | test | table | postgres=arwdDxtm/postgres+| |
| | | app=arwdDxtm/postgres +| |
| | | replication=r/postgres | |
Caution: a PUBLICATION is always per database! So, if you want to publish serveral databases you have to create one publication per database.
postgres=# \connect test
test=# CREATE PUBLICATION db_test_publication FOR ALL TABLES;
CREATE PUBLICATION
test=# SELECT * FROM pg_publication;
oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot | pubgencols
-------+---------------------+----------+--------------+-----------+-----------+-----------+-------------+------------+------------
16405 | db_test_publication | 10 | t | t | t | t | t | f | n
-- CREATE PUBLICATION my_publication FOR TABLE table1, table2;
postgres=# \connect test
test=# CREATE PUBLICATION test_var FOR TABLE public.test;
CREATE PUBLICATION
test=# SELECT * FROM pg_publication;
oid | pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot | pubgencols
-------+---------------------+----------+--------------+-----------+-----------+-----------+-------------+------------+------------
16405 | db_test_publication | 10 | t | t | t | t | t | f | n
test=# SELECT * FROM pg_publication_tables;
pubname | schemaname | tablename | attnames | rowfilter
----------+------------+-----------+--------------+-----------
test_var | public | test | {id,data,ts} |
$ pg_dump --host=<publisher-host> --user=postgres --schema-only --dbname=test > /tmp/db_test_full_dump.sql
postgres=# drop database test;
DROP DATABASE
postgres=# create database test;
CREATE DATABASE
$ psql --host=<subscriber-host> --user=postgres --dbname=test --file=/tmp/db_test_full_dump.sql
The easiest way to copy a schema to a Subscriber is the following:
$ # pg_dump --user=postgres --schema-only --table=users --schema=test test > /tmp/db_test_schema_test_dump.sql
$ # transfer the file to subscriber
$ psql --command='DROP DATABASE test'
$ psql --command='CREATE DATABASE test'
$ psql --user=postgres --dbname=test --file=/tmp/db_test_schema_test_dump.sql
The easiest way to copy a some table structures to a Subscriber is the following:
$ pg_dump --user=postgres --schema-only --table=public.test test > /tmp/db_test_var_dump.sql
$ # transfer the file to subscriber
$ psql --command='DROP DATABASE test'
$ psql --command='CREATE DATABASE test'
$ psql --user=postgres --dbname=test --file=/tmp/db_test_var_dump.sql
...
psql:/tmp/test_var_dump.sql:80: ERROR: role "app" does not exist
psql:/tmp/test_var_dump.sql:81: ERROR: role "replication" does not exist
psql:/tmp/test_var_dump.sql:88: ERROR: role "app" does not exist
During creation of a SUBSCRIPTION a snapshot of the tables is created! This can take a while…
postgres=# \connect test
test=# CREATE SUBSCRIPTION db_test_var_subscription
CONNECTION 'host=10.223.125.57 port=5432 dbname=test user=replication password=secret'
PUBLICATION test_var
;
NOTICE: created replication slot "db_test_var_subscription" on publisher
CREATE SUBSCRIPTION
test=# \x
Expanded display is on.
test=# SELECT * FROM pg_subscription;
-[ RECORD 1 ]-------+--------------------------------------------------------------------------
oid | 24613
subdbid | 24597
subskiplsn | 0/0
subname | db_test_var_subscription
subowner | 10
subenabled | t
subbinary | f
substream | p
subtwophasestate | d
subdisableonerr | f
subpasswordrequired | t
subrunasowner | f
subfailover | f
subconninfo | host=10.223.125.57 port=5432 dbname=test user=replication password=secret
subslotname | db_test_var_subscription
subsynccommit | off
subpublications | {test_var}
suborigin | any
test=# ALTER PUBLICATION my_publication ADD TABLE new_table;
test=# ALTER PUBLICATION my_publication DROP TABLE table;
Reasons:
test=# ALTER SUBSCRIPTION db_test_var_subscription DISABLE;
test=# ALTER SUBSCRIPTION db_test_var_subscription ENABLE;
On the Publisher add the new table to PUBLICATION:
test=# ALTER PUBLICATION test_var ADD table erp.test;
Create the table (and eventually also the schema) on the Subscriber:
test=# CREATE SCHEMA erp;
test=# CREATE TABLE erp.test (id SERIAL);
After adding the new table to Publication on has to run REFRESH PUBLICATION on the Subscriber:
test=# ALTER SUBSCRIPTION db_test_var_subscription REFRESH PUBLICATION;
In case you get the following error on the Publisher:
replication@test LOG: logical decoding found consistent point at 0/1EA1708
replication@test DETAIL: There are no running transactions.
replication@test STATEMENT: CREATE_REPLICATION_SLOT "pg_16403_sync_16415_7672368634343456654" LOGICAL pgoutput (SNAPSHOT 'use')
replication@test ERROR: permission denied for schema erp
replication@test STATEMENT: COPY erp.test (id) TO STDOUT
you have to grant some additional privileges on the Publisher:
test=# GRANT USAGE ON SCHEMA erp TO replication;
test=# GRANT SELECT ON erp.test TO replication;
and then the error messages should magically disappear!
Check the error log on Publisher AND subscriber!
At least one per subscription:
test=# \x
Expanded display is on.
test=# SELECT * FROM pg_stat_subscription;
-[ RECORD 1 ]---------+------------------------------
subid | 16403
subname | db_test_var_subscription
worker_type | apply
pid | 4597
leader_pid |
relid |
received_lsn | 0/1C7CD90
last_msg_send_time | 2026-08-10 13:34:22.642536+00
last_msg_receipt_time | 2026-08-10 13:34:22.64266+00
latest_end_lsn | 0/1C7CD90
latest_end_time | 2026-08-10 13:34:22.642536+00
See also Monitoring of Physical Replication: here and here
DDL, Sequences, …
…
postgres=# SHOW sync_replication_slots;
postgres=# SELECT pg_sync_replication_slots();