PostgreSQL master-slave replication solution

PostgreSQL supports a variety of master-slave replication schemes, which can be used for data backup, fault recovery and load balancing. The following is a common master-slave replication scheme. You can choose the appropriate method according to your needs.

1. Configure the main database:

   • On the primary database server, ensure that PostgreSQL is installed and configured correctly.

   • Edit the configuration file postgresql.conf of the main database and enable the replication function. Set the following parameters:

     wal_level = replicamax_wal_senders = 10

   • Edit the pg_hba.conf file to allow the slave server to connect to the master server. Add the following lines:

     host replication <Slave server IP address> trust

   • Restart the main database server to make the configuration take effect.

2. Create slave database:

   • On the slave database server, ensure that PostgreSQL is installed and configured correctly.

   • In the configuration file postgresql.conf of the secondary database, enable the replication function. Set the following parameters:

     wal_level = replica

   • Create a data directory identical to the main database and make sure it is empty.

   • In the pg_hba.conf file of the slave database, add the following lines to allow the slave server to connect to the slave server:

     host replication <Master server IP address> trust

   • Restart the slave database server to make the configuration take effect.

3. Configure master-slave replication:

   • On the primary database server, create a user role for replication. Execute the following commands in the psql terminal:

     CREATE ROLE replicator REPLICATION LOGIN CONNECTION LIMIT 10 ENCRYPTED PASSWORD 'password';

     Note to replace 'password' with the actual password.

   • On the primary database server, create a replication slot. Execute the following commands in the psql terminal:

     SELECT * FROM pg_create_physical_replication_slot('replication_slot_name');

     Note to replace 'replication_slot_name' with the actual replication slot name.

   • On the main database server, modify the pg_hba.conf file to allow connections for replicated user roles. Add the following lines:

     host replication replicator <Slave server IP address> trust

   • Restart the main database server to make the configuration take effect.

4. Start master-slave replication:

   • On the slave database server, use the following command to connect to the master database and start replication:

     pg_basebackup -h <Master server IP address> -U replicator -p 5432 -D /path/to/data_directory -P -R -X stream -c fast

     Note that '4d74db81a7307a3d4377b2ea1f820cae' is replaced with the actual master server IP address, and '/path/to/data_directory' is replaced with the data directory path of the slave database.

   • In the configuration file postgresql.conf of the secondary database, set the following parameters:

     primary_conninfo = 'host=<Primary server IP address> port=5432 user=replicator password=password'primary_slot_name = 'replication_slot_name'

     Note to replace '4d74db81a7307a3d4377b2ea1f820cae', 'password' and 'replication_slot_name' with actual values.

   • Start the slave database server.

After completing the above steps, master-slave replication will begin

run. Data changes on the master database will be automatically replicated to the slave database, and the slave database will maintain consistency with the master database. You can use monitoring tools to monitor the status of master-slave replication to ensure it is functioning properly.

Please note that the above is only an overview of a master-slave replication solution, and actual implementation may need to be adjusted according to specific environments and needs. It is recommended to refer to the official PostgreSQL documentation and other reliable resources for more detailed guidance and instructions.

The above is the detailed content of PostgreSQL master-slave replication solution. For more information, please follow other related articles on the PHP Chinese website!