Simultaneous work of local PostgreSQL and cloud PostgreSQL instances

Question

I am currently using a local PostgreSQL database instance and would like to create another PostgreSQL database instance on a virtual machine (VM) in the cloud, specifically Azure.
My goal is to synchronize data between these two instances and enable simultaneous operation of the databases.
How can I do it?
Thank you in advance

Answer 1

The solution of Simultaneous work of local PostgreSQL and cloud PostgreSQL instances is replication.
I didn't find any good guide that worked for me.
Attach a guide that I built.

Configure primary server
1. Modify postgresql.conf
    1. listen_addresses = '*'
    2. wal_level = replica
    3. max_wal_senders = 100
    4. max_replication_slots = 10
2. Create user for replication
    1. psql  -d <database name>   -U <user name with admin permissions>   -c "CREATE ROLE <user for replication> LOGIN REPLICATION ENCRYPTED PASSWORD '<password>';"
3. Modify pg_hba.conf
    1. add at the end of file
        1. host    replication     <user for replication>      <replica server ip>/32       scram-sha-256
4. Restart primary server
    1. pg_ctl -D "<data folder of primary server>" restart

Configure replica server
1. Copy the data from primary server
    1. pg_basebackup -h <primary server ip> -U <user for replication> --checkpoint=fast -D <data folder for replica server> -R --slot=<some slot name> -C --port=<port of primary server>
2. Modify postgresql.conf
    1. Change the port number
    2. primary_conninfo = 'host=<primary server ip> port=<primary server port> user=<user for replication> password=<password for the user for replication>'
3. pg_ctl -D "<data folder for replica server>" start

Testing
1. Primary server
    1. psql -d postgres -U postgres --port=<primary server port> -c "SELECT * FROM pg_stat_replication;"
2. Replica server
    1. psql -d postgres -U postgres --port=<replica server port> -c "SELECT * FROM pg_stat_wal_receiver;"