I have a table with 16 millions rows that needs to be replicated. The table has a total of 115 columns. Within those, there are 50 varchars columns and primary key with uuid that has 36 chars. There are 15 indexes,some dependencies and some functions.
The step I took :
- make a copy of the table to the subscriber. So it doesn't have to go through the initialization phase of replication process.
- create
replica identity : default - create publication
- create subscription :
CREATE SUBSCRIPTION try1 CONNECTION 'host=10.100.9.40 port=5432 user=jhon password=gr3AT dbname=db_profile_20210714' PUBLICATION pub_try1 with (copy_data = false);
No data get sent to the subscriber.
I check the wall difference on the Publisher at 3 seconds interval using: SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) FROM pg_stat_replication .
The result of running it 3 times are : 10000, 5000 and 0. It cycles again over time, following more or less similar pattern.
Here my settings of the database :
name |setting |unit -------------------------+----------+----
archive_command |(disabled)|
archive_mode |off |
archive_timeout |0 |s
hot_standby |on |
max_replication_slots |10 |
max_wal_senders |5 |
max_wal_size |8192 |MB
min_wal_size |2048 |MB
synchronous_standby_names|* |
wal_level |logical |
wal_log_hints |off |
wal_sender_timeout |60000 |ms
I tried another replications of the same table with less data (5000 data and empty) on the Publisher, with the same number of columns and indexes etc but without functions. I create 2 subscription with option copy_data : false and copy_data : true. Then I enter some data into the table using query on the Publisher : INSERT INTO table_publisher INTO SELECT * FROM table. both working normally, data get inserted into the subscriber.
Questions :
- Does size matters in replication ? I don't think so
- The table is actually got its data from an app. Does it matter if the table got data from apps ?
- Does Functions block the replication ?
- How can I make this replication work ?
Thanks
