problem while synchronization replication with pg_easy_replicate

[Jamic28]

I am doing synchronous replication from standalone Postgres version 14 to a database cluster version 16. On the source database the data volume is 270 GB, and on the target cluster it is already 500 GB and the data copy is still going (no errors)...

root@db-1:/home/administrator# du -sh /var/lib/postgresql/16/main/base/
500G    /var/lib/postgresql/16/main/base/

"replication_stats_count_by_state": {
    "replicating": 128,
    "data_is_being_copied": 148
  },
  "message_lsn_receipts": [
    {
      "received_lsn": "394/3B0D3A58",
      "last_msg_send_time": "2024-11-30 14:38:54 UTC",
      "last_msg_receipt_time": "2024-11-30 14:38:49 UTC",
      "latest_end_lsn": "394/3B0D3A58",
      "latest_end_time": "2024-11-30 14:38:54 UTC"
    },
    {
      "received_lsn": null,
      "last_msg_send_time": "2024-11-30 14:38:04 UTC",
      "last_msg_receipt_time": "2024-11-30 14:38:04 UTC",
      "latest_end_lsn": null,
      "latest_end_time": "2024-11-30 14:38:04 UTC"
    },
    {
      "received_lsn": null,
      "last_msg_send_time": "2024-11-30 14:37:48 UTC",
      "last_msg_receipt_time": "2024-11-30 14:37:48 UTC",
      "latest_end_lsn": null,
      "latest_end_time": "2024-11-30 14:37:48 UTC"
    }
  ],
  "sync_started_at": "2024-11-30 08:22:38 UTC",
  "sync_failed_at": null,
  "switchover_completed_at": null

When setting up the target I specified a VIP address with port 5000
why volume of data on the target DB much larger than on the source?
Thanks!

[vitabaks]

And have you checked what exactly is taking up so much space? It might not be the database itself, but rather WAL files accumulating, for example, due to an issue with archive_command.

To check the size of your databases, you can use the \l+ command in psql. It provides a detailed list of all databases along with their sizes.

[Jamic28]

Everything is working! No errors! archive_command is working and archiving

tail -f /var/log/postgresql/postgresql-Sat.log 
INFO: 2024/11/30 23:59:58.819880 Files will be uploaded to storage: default
INFO: 2024/11/30 23:59:59.106275 FILE PATH: 000000010000022600000012.zst
INFO: 2024/11/30 23:59:59.164363 FILE PATH: 000000010000022600000013.zst
INFO: 2024/11/30 23:59:59.276384 FILE PATH: 000000010000022600000014.zst
INFO: 2024/11/30 23:59:59.351705 Files will be uploaded to storage: default
INFO: 2024/11/30 23:59:59.404831 Files will be uploaded to storage: default
INFO: 2024/11/30 23:59:59.455798 Files will be uploaded to storage: default
INFO: 2024/11/30 23:59:59.786261 FILE PATH: 000000010000022600000016.zst
INFO: 2024/11/30 23:59:59.845213 FILE PATH: 000000010000022600000015.zst
INFO: 2024/12/01 00:00:00.105827 FILE PATH: 000000010000022600000017.zst

patroni cluster is working!

patronictl list
+ Cluster: postgres-cluster (7442984343249332720) +-----------+------------------+
| Member | Host        | Role    | State     | TL | Lag in MB | Tags             |
+--------+-------------+---------+-----------+----+-----------+------------------+
| db-1   | 172.16.9.30 | Leader  | running   |  1 |           | datacenter: db-1 |
| db-2   | 172.16.9.31 | Replica | streaming |  1 |         0 | datacenter: db-2 |
| db-3   | 172.16.9.32 | Replica | streaming |  1 |         0 | datacenter: db-3 |
+--------+-------------+---------+-----------+----+-----------+------------------+

\l+ command shows the amount of space of /var/lib/postgresql/16/main/base/ directory.

du -sh /var/lib/postgresql/16/main/base/
780G    /var/lib/postgresql/16/main/base/

pg_wal is not rising:

du -sh /var/lib/postgresql/16/main/pg_wal/
8.1G    /var/lib/postgresql/16/main/pg_wal/

journalctl -eu patroni -f
Nov 30 22:10:07 db-1 patroni[920023]: 2024-11-30 22:10:07,420 INFO: no action. I am (db-1), the leader with the lock
...

just rising /base/ directory...

[vitabaks]

Hmm, determine which objects in the database are growing.

Try asking the same question in the repository of this utility that you use.

[Jamic28]

Thanks!
The inspection table of mydb on source is 237GB

public | inspection | table | mydb | permanent | heap | 237 GB |
And on target is growing while start synch

public | inspection | table | mydb | permanent | heap | 500 GB |
could this be related to the fact that a record is being written to the table on the source base during synchronization?

[vitabaks]

compare the size of the largest tables on the source and target:

select
  quote_ident(schemaname)||'.'||quote_ident(relname) as table_full_name,
  pg_size_pretty(pg_relation_size(relid)) as table_size,
  pg_size_pretty(pg_total_relation_size(relid)) as total_relation_size
from
  pg_stat_user_tables ut
-- leaving out fully locked tables as pg_relation_size also wants a lock and would wait
where not exists (select 1 from pg_locks where relation = relid and mode = 'AccessExclusiveLock' and granted)
order by pg_total_relation_size(relid) desc
limit 10;

[Jamic28]

It was inspection table
on source:
public | inspection | table | mydb | permanent | heap | 250 GB |
on target
public | inspection | table | mydb | permanent | heap | 1 TB |

I solved synchronous logical repilication by using another utility - https://github.com/2ndQuadrant/pglogical
And also I'd ask you to add in:
./roles/pgbouncer/templates/pgbouncer.ini.j2: -> reserve_pool_size = {{ pgbouncer_reserve_pool_size }}
./vars/main.yml: -> pgbouncer_reserve_pool_size:
Thanks!

[vitabaks]

And also I'd ask you to add in

Feel free to suggest a PR