POSTGRES - Cronologia

Admin il 16:37, 29 ott 2024

2024-10-29T16:37:33Z

← Versione meno recente		Versione delle 16:37, 29 ott 2024
Riga 20:		Riga 20:
	SELECT pg_promote();		SELECT pg_promote();
	SELECT pg_is_in_recovery();		SELECT pg_is_in_recovery();

			SELECT * FROM pg_stat_replication;

Admin il 16:32, 29 ott 2024

2024-10-29T16:32:15Z

← Versione meno recente		Versione delle 16:32, 29 ott 2024
Riga 4:		Riga 4:

	https://medium.com/@umairhassan27/postgresql-replication-switchover-a-step-by-step-guide-d42107d860		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();

Admin il 16:15, 29 ott 2024

2024-10-29T16:15:51Z

@@ Riga 8: / Riga 8: @@
 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):
+ host replication all 0.0.0.0/0 scram-sha-256
-sudo yum install postgresql15-server
+ primary_conninfo = 'host=primary_db_ip port=primary_db_port user=replicator password=your_password'
-Step 2: Configure the Primary Server
+ primary_slot_name = 'your_slot_name' # Optional, if using replication slots
-. Edit the PostgreSQL Configuration File (`postgresql.conf`):
+ restore_command = 'cp /path_to_archive/%f %p' # Optional, for WAL archiving
-— Set the `listen_addresses` to ‘*’ or the specific IP address to accept connections.
+ recovery_target_timeline = 'latest' # Optional, for specific recovery targets
-— Configure the `wal_level` to ‘replica’ to provide the necessary WAL (Write-Ahead Logging) data for the replicas.
+ Step 4: Start the Standby Server
-Example:
+ - Start the PostgreSQL service on the standby server:
-host replication all 0.0.0.0/0 scram-sha-256
+  sudo systemctl start postgresql-15
-Step 3: Initialize the Standby Server
+ Step 5: Verify Replication
-primary_conninfo = 'host=primary_db_ip port=primary_db_port user=replicator password=your_password'
+ Check the replication status by connecting to the primary server and querying:
-primary_slot_name = 'your_slot_name' # Optional, if using replication slots
+ SELECT * FROM pg_stat_replication;
-restore_command = 'cp /path_to_archive/%f %p' # Optional, for WAL archiving
+ - Ensure that the standby is listed and that the `state` is ‘streaming’.
-- Start the PostgreSQL service on the standby server:
+ 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.

Admin il 11:43, 29 ott 2024

2024-10-29T11:43:53Z

← Versione meno recente		Versione delle 11:43, 29 ott 2024
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

Admin il 11:36, 29 ott 2024

2024-10-29T11:36:32Z

← Versione meno recente		Versione delle 11:36, 29 ott 2024
Riga 1:		Riga 1:
			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




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

Admin: Creata pagina con "PostgreSQL 15 master-slave configuration using streaming replication step by step Step 1: Install PostgreSQL on Both Servers Sheikh Wasiu Al Hasib Sheikh Wasiu Al Hasib · Follow 2 min read · Aug 30, 2024 2 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 PostgreSQ..."

2024-10-29T11:35:44Z

Creata pagina con "PostgreSQL 15 master-slave configuration using streaming replication step by step Step 1: Install PostgreSQL on Both Servers Sheikh Wasiu Al Hasib Sheikh Wasiu Al Hasib · Follow 2 min read · Aug 30, 2024 2 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 PostgreSQ..."

Nuova pagina

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

·
Follow

2 min read
·
Aug 30, 2024
2

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.