Скачать или смотреть How to Enable Simultaneous Operation of Local PostgreSQL and Cloud PostgreSQL Instances

Discover how to synchronize your local and cloud PostgreSQL databases using replication for seamless operations.
---
This video is based on the question https://stackoverflow.com/q/76380186/ asked by the user 'Vlad Efanov' ( https://stackoverflow.com/u/15203179/ ) and on the answer https://stackoverflow.com/a/76409315/ provided by the user 'Vlad Efanov' ( https://stackoverflow.com/u/15203179/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Simultaneous work of local PostgreSQL and cloud PostgreSQL instances

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Synchronizing Local and Cloud PostgreSQL Instances

In today's data-driven world, businesses often rely on the flexibility of both local and cloud resources. However, maintaining consistent data across different PostgreSQL instances can be challenging. One common scenario is wanting to work with a local PostgreSQL database while simultaneously using a cloud PostgreSQL instance, such as one hosted on Azure.

If you find yourself in this situation, you're not alone. Many users seek the ability to synchronize their local database with a cloud database to ensure data integrity and availability. This article will guide you through the process of achieving this synchronization using PostgreSQL replication.

Understanding the Problem

When you have a local PostgreSQL database setup alongside another instance in the cloud, maintaining real-time data accuracy can be complicated without an effective synchronization mechanism. Replication provides a way to duplicate data from one database (the primary) to another (the replica), enabling both to operate simultaneously.

Setting Up Replication for PostgreSQL

Replication involves a series of configurations on both the primary server (your local database) and the replica server (your cloud database). Below, we break down the steps necessary to configure both servers for seamless operation.

1. Configure the Primary Server

First, you need to adjust settings on your local PostgreSQL instance.

Modify postgresql.conf

Open the postgresql.conf file on the primary server and make the following changes:

Set listen_addresses to '*' to allow connections from remote servers.

Change wal_level to replica to prepare for replication.

Adjust max_wal_senders and max_replication_slots to acceptable numbers, e.g., 100 and 10 respectively.

Create a Replication User

You need a special user for handling replication:

[[See Video to Reveal this Text or Code Snippet]]

Modify pg_hba.conf

Allow the replication user to connect by adding a line to the end of your pg_hba.conf file:

[[See Video to Reveal this Text or Code Snippet]]

Restart the Primary Server

Once the configurations are set, restart your primary PostgreSQL instance:

[[See Video to Reveal this Text or Code Snippet]]

2. Configure the Replica Server

Now, proceed to configure the PostgreSQL instance running on your cloud server.

Copy Data from the Primary Server

Use pg_basebackup to copy the data from your primary server:

[[See Video to Reveal this Text or Code Snippet]]

Modify postgresql.conf

On the replica server, make sure to adjust the following settings:

Change the port number if necessary.

Set primary_conninfo with the connection details to the primary server:

[[See Video to Reveal this Text or Code Snippet]]

Start the Replica Server

Finally, start your replica PostgreSQL instance:

[[See Video to Reveal this Text or Code Snippet]]

3. Testing the Setup

After the setup is complete, you can verify the replication process:

On the Primary Server:

Run the following command to check the status of replication:

[[See Video to Reveal this Text or Code Snippet]]

On the Replica Server:

Check the WAL receiver status with:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By following these steps, you should be able to successfully synchronize your local PostgreSQL database with a cloud instance, allowing for simultaneous operations. Replication can significantly enhance data availability and integrity across your databases, making it easier to manage and utilize your data in different environments.

If you have any further questions or encounter challenges during the configuration process, do not hesitate to ask for assistance. Happy coding!