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
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;"