POSTGRES: differenze tra le versioni

Da GazziNet.
Vai alla navigazione Vai alla ricerca
Nessun oggetto della modifica
Nessun oggetto della modifica
 
(3 versioni intermedie di uno stesso utente non sono mostrate)
Riga 3: Riga 3:
   https://medium.com/@wasiualhasib/understanding-wal-archiving-and-wal-streaming-in-postgresql-dea98c593cc8
   https://medium.com/@wasiualhasib/understanding-wal-archiving-and-wal-streaming-in-postgresql-dea98c593cc8


  https://medium.com/@umairhassan27/postgresql-replication-switchover-a-step-by-step-guide-d42107d860




sudo -u postgres pg_ctl promote -D /var/lib/pgsql/16/data


PostgreSQL 15 master-slave configuration using streaming replication step by step
postgresql.conf
Step 1: Install PostgreSQL on Both Servers
trigger_file = '/tmp/postgresql.trigger'
Sheikh Wasiu Al Hasib
 
Sheikh Wasiu Al Hasib
sudo touch /tmp/postgresql.trigger
 
SELECT pg_is_in_recovery();
 
 
sudo -u postgres pg_ctl promote -D /var/lib/pgsql/16/data
 
SELECT pg_promote();
SELECT pg_is_in_recovery();
 
SELECT * FROM pg_stat_replication;


·
Follow


2 min read
·
Aug 30, 2024
2


PostgreSQL 15 master-slave configuration using streaming replication step by step
Step 1: Install PostgreSQL on Both Servers








Ensure PostgreSQL 15 is installed on both the primary and standby servers. You can use the package manager for your system (like `yum` for Red Hat-based systems):


Ensure PostgreSQL 15 is installed on both the primary and standby servers. You can use the package manager for your system (like `yum` for Red Hat-based systems):
sudo yum install postgresql15-server
Step 2: Configure the Primary Server
1. Edit the PostgreSQL Configuration File (`postgresql.conf`):
— Set the `listen_addresses` to ‘*’ or the specific IP address to accept connections.
— Configure the `wal_level` to ‘replica’ to provide the necessary WAL (Write-Ahead Logging) data for the replicas.
— Set `max_wal_senders` to the number of standby servers; for one standby, you might set it to 2 or 3 to allow some headroom.
— Set `max_replication_slots` if you plan to use replication slots (recommended).
— Adjust `archive_mode` to ‘on’ and set `archive_command` to a method that archives your WAL files to a secure location.


sudo yum install postgresql15-server
Example:
Step 2: Configure the Primary Server
listen_addresses = '*'
1. Edit the PostgreSQL Configuration File (`postgresql.conf`):
wal_level = replica
— Set the `listen_addresses` to ‘*’ or the specific IP address to accept connections.
max_wal_senders = 3
— Configure the `wal_level` to ‘replica’ to provide the necessary WAL (Write-Ahead Logging) data for the replicas.
max_replication_slots = 2
— Set `max_wal_senders` to the number of standby servers; for one standby, you might set it to 2 or 3 to allow some headroom.
archive_mode = on
— Set `max_replication_slots` if you plan to use replication slots (recommended).
archive_command = 'cp %p /path_to_archive/%f'
— Adjust `archive_mode` to ‘on’ and set `archive_command` to a method that archives your WAL files to a secure location.


Example:
2. Modify `pg_hba.conf`:
listen_addresses = '*'
— Add rules to allow the standby server to connect:
wal_level = replica
max_wal_senders = 3
max_replication_slots = 2
archive_mode = on
archive_command = 'cp %p /path_to_archive/%f'
2. Modify `pg_hba.conf`:
— Add rules to allow the standby server to connect:


host replication all 0.0.0.0/0 scram-sha-256
host replication all 0.0.0.0/0 scram-sha-256
Step 3: Initialize the Standby Server
Step 3: Initialize the Standby Server
Create a Base Backup:
Create a Base Backup:
— Stop the standby server if it’s running.
— Stop the standby server if it’s running.
— Remove any existing data in the standby’s data directory.
— Remove any existing data in the standby’s data directory.
— Use the `pg_basebackup` utility to take a base backup from the primary. This utility automatically creates a valid configuration for replication in the `postgresql.auto.conf`:
— Use the `pg_basebackup` utility to take a base backup from the primary. This utility automatically creates a valid configuration for replication in the  
pg_basebackup -h primary_db_ip -U replicator -p primary_db_port -D /data/pgsql/14/test -v -P --wal-method=stream  --write-recovery-conf
`postgresql.auto.conf`:
2. Create and Modify the Required Signal and Configuration Files:
pg_basebackup -h primary_db_ip -U replicator -p primary_db_port -D /data/pgsql/14/test -v -P --wal-method=stream  --write-recovery-conf
— In the standby’s data directory, create an empty file named `standby.signal` to indicate that this server should start in standby mode.
2. Create and Modify the Required Signal and Configuration Files:
— Modify `postgresql.auto.conf` or use the `ALTER SYSTEM` command in SQL to set replication-related parameters:
— In the standby’s data directory, create an empty file named `standby.signal` to indicate that this server should start in standby mode.
— Modify `postgresql.auto.conf` or use the `ALTER SYSTEM` command in SQL to set replication-related parameters:


primary_conninfo = 'host=primary_db_ip port=primary_db_port user=replicator password=your_password'
primary_conninfo = 'host=primary_db_ip port=primary_db_port user=replicator password=your_password'  
primary_slot_name = 'your_slot_name' # Optional, if using replication slots
primary_slot_name = 'your_slot_name' # Optional, if using replication slots
restore_command = 'cp /path_to_archive/%f %p' # Optional, for WAL archiving
restore_command = 'cp /path_to_archive/%f %p' # Optional, for WAL archiving
recovery_target_timeline = 'latest' # Optional, for specific recovery targets
recovery_target_timeline = 'latest' # Optional, for specific recovery targets
Step 4: Start the Standby Server
Step 4: Start the Standby Server


- Start the PostgreSQL service on the standby server:
- Start the PostgreSQL service on the standby server:


sudo systemctl start postgresql-15
sudo systemctl start postgresql-15
Step 5: Verify Replication
Step 5: Verify Replication


Check the replication status by connecting to the primary server and querying:
Check the replication status by connecting to the primary server and querying:
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_stat_replication;
- Ensure that the standby is listed and that the `state` is ‘streaming’.
- Ensure that the standby is listed and that the `state` is ‘streaming’.


This basic setup should get you started with streaming replication in PostgreSQL 15. Remember to secure your database connections using appropriate authentication methods and consider implementing SSL for data transmission security. In the above configuraiton you need to replace primary_db_ip and primary_db_port with your real ip and port.
This basic setup should get you started with streaming replication in PostgreSQL 15. Remember to secure your database connections using appropriate authentication methods and  
consider implementing SSL for data transmission security. In the above configuraiton you need to replace primary_db_ip and primary_db_port with your real ip and port.

Versione attuale delle 16:37, 29 ott 2024

 https://medium.com/@wasiualhasib/postgresql-15-master-slave-configuration-using-streaming-replication-step-by-step-a4ccb2ba7083

 https://medium.com/@wasiualhasib/understanding-wal-archiving-and-wal-streaming-in-postgresql-dea98c593cc8
 https://medium.com/@umairhassan27/postgresql-replication-switchover-a-step-by-step-guide-d42107d860


sudo -u postgres pg_ctl promote -D /var/lib/pgsql/16/data
postgresql.conf
trigger_file = '/tmp/postgresql.trigger'
sudo touch /tmp/postgresql.trigger
SELECT pg_is_in_recovery();


sudo -u postgres pg_ctl promote -D /var/lib/pgsql/16/data
SELECT pg_promote();
SELECT pg_is_in_recovery();
SELECT * FROM pg_stat_replication;


PostgreSQL 15 master-slave configuration using streaming replication step by step Step 1: Install PostgreSQL on Both Servers



Ensure PostgreSQL 15 is installed on both the primary and standby servers. You can use the package manager for your system (like `yum` for Red Hat-based systems): 
sudo yum install postgresql15-server 
Step 2: Configure the Primary Server
1. Edit the PostgreSQL Configuration File (`postgresql.conf`):
— Set the `listen_addresses` to ‘*’ or the specific IP address to accept connections.
— Configure the `wal_level` to ‘replica’ to provide the necessary WAL (Write-Ahead Logging) data for the replicas.
— Set `max_wal_senders` to the number of standby servers; for one standby, you might set it to 2 or 3 to allow some headroom.
— Set `max_replication_slots` if you plan to use replication slots (recommended).
— Adjust `archive_mode` to ‘on’ and set `archive_command` to a method that archives your WAL files to a secure location.
Example:
listen_addresses = '*'
wal_level = replica
max_wal_senders = 3
max_replication_slots = 2
archive_mode = on
archive_command = 'cp %p /path_to_archive/%f'
2. Modify `pg_hba.conf`:
— Add rules to allow the standby server to connect:
host replication all 0.0.0.0/0 scram-sha-256
Step 3: Initialize the Standby Server
Create a Base Backup:
— Stop the standby server if it’s running.
— Remove any existing data in the standby’s data directory.
— Use the `pg_basebackup` utility to take a base backup from the primary. This utility automatically creates a valid configuration for replication in the 
`postgresql.auto.conf`:
pg_basebackup -h primary_db_ip -U replicator -p primary_db_port -D /data/pgsql/14/test -v -P --wal-method=stream  --write-recovery-conf
2. Create and Modify the Required Signal and Configuration Files:
— In the standby’s data directory, create an empty file named `standby.signal` to indicate that this server should start in standby mode.
— Modify `postgresql.auto.conf` or use the `ALTER SYSTEM` command in SQL to set replication-related parameters:  
primary_conninfo = 'host=primary_db_ip port=primary_db_port user=replicator password=your_password' 
primary_slot_name = 'your_slot_name' # Optional, if using replication slots
restore_command = 'cp /path_to_archive/%f %p' # Optional, for WAL archiving
recovery_target_timeline = 'latest' # Optional, for specific recovery targets
Step 4: Start the Standby Server
- Start the PostgreSQL service on the standby server:
sudo systemctl start postgresql-15
Step 5: Verify Replication
Check the replication status by connecting to the primary server and querying:
SELECT * FROM pg_stat_replication;
- Ensure that the standby is listed and that the `state` is ‘streaming’.
This basic setup should get you started with streaming replication in PostgreSQL 15. Remember to secure your database connections using appropriate authentication methods and 
consider implementing SSL for data transmission security. In the above configuraiton you need to replace primary_db_ip and primary_db_port with your real ip and port.