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.We consider different scenarios here:
Required settings for logical replication on Publisher:
#
# postgresql.conf
#
wal_level = logical
max_replication_slots = 10 # default
max_wal_senders = 10 # default
postgres=# show wal_level;
wal_level
-----------
logical
postgres=# CREATE ROLE replication WITH REPLICATION PASSWORD 'secret';
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
#
# 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}
TODO: Show all privileges of a row: https://dba.stackexchange.com/questions/315091/how-can-i-list-all-privileges-that-a-postgresql-role-has https://stackoverflow.com/questions/40759177/postgresql-show-all-the-privileges-for-a-concrete-user
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;
TODO: Create tables per schema:
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 TABLE table1, table2 TO replication;
Caution: a PUBLICATION is always 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;
…
$ pg_dump --host=<publisher-host> --user=postgres --schema-only --dbname=test > full_schema_test_dump.sql
postgres=# drop database test;
DROP DATABASE
postgres=# create database test;
CREATE DATABASE
$ psql --host=<subscriber-host> --user=postgres --dbname=test --file=full_schema_test_dump.sql
The easiest way to copy a database/schema to a Subscriber ist the following:
$ # pg_dump --host=127.0.0. --user=postgres --schema-only --table=users --table=orders test > schema_test_dump.sql
$ # psql -h subscriber_host -U postgres -d mydb -f schema.sql
…
postgres=# \connect
test=# CREATE SUBSCRIPTION db_test_subscription
CONNECTION 'host=10.223.125.65 port=5432 dbname=test user=replication password=secret'
PUBLICATION db_test_publication;
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_subscription
subowner | 10
subenabled | t
subbinary | f
substream | p
subtwophasestate | d
subdisableonerr | f
subpasswordrequired | t
subrunasowner | f
subfailover | f
subconninfo | host=10.223.125.65 port=5432 dbname=test user=replication password=secret
subslotname | db_test_subscription
subsynccommit | off
subpublications | {db_test_publication}
suborigin | any
During creation of a SUBSCRIPTION a snapshot of the tables is created! This can take a while…
postgres=# SHOW sync_replication_slots;
postgres=# SELECT pg_sync_replication_slots();
postgres=# ALTER PUBLICATION my_publication ADD TABLE new_table;
postgres=# ALTER PUBLICATION my_publication DROP TABLE table1;
postgres=# ALTER SUBSCRIPTION my_subscription DISABLE;
postgres=# ALTER SUBSCRIPTION my_subscription ENABLE;
On Subscriber:
test=# \x
Expanded display is on.
test=# SELECT * FROM pg_stat_subscription;
-[ RECORD 1 ]---------+------------------------------
subid | 24613
subname | db_test_subscription
worker_type | apply
pid | 6615
leader_pid |
relid |
received_lsn | 0/E00F8B0
last_msg_send_time | 2026-08-03 20:17:41.945136+00
last_msg_receipt_time | 2026-08-03 20:17:41.94527+00
latest_end_lsn | 0/E00F8B0
latest_end_time | 2026-08-03 20:17:41.945136+00
DDL, Sequences, …
…