Postgresql HA

Overview

This document provides a comprehensive guide on how to install PostgreSQL on an EC2 instance, set up replication for high availability, and perform version upgrades.

Pre-requisites

• AWS Account

• Two Ubuntu EC2 Instances

Install PostgreSQL

1. Set Up EC2 Instances

   a. Log in to the AWS Management Console: - Open the AWS Management Console and search for "EC2."

   b. Create the Primary Server: - Navigate to "Instances" and click on "Launch Instance." - Configure the instance with the desired specifications and choose an appropriate name, such as "Primary Server."

   c. Create the Standby Server: - Repeat the process to launch another instance. - Name this instance "Standby Server."

2. Configure Security Groups

   a. Edit Security Group for Primary Server: - Go to the EC2 dashboard, select the Primary Server instance, and click on the "Security" tab. - Edit the inbound and outbound rules of the security group associated with the Primary Server to allow traffic from the Standby Server. This includes: - Allowing PostgreSQL traffic (default port 5432). - Adding custom TCP rules to allow connections from the IP address of the Standby Server.

b. Edit Security Group for Standby Server: - Similarly, go to the EC2 dashboard, select the Standby Server instance, and click on the "Security" tab. - Edit the security group to allow the necessary traffic from the Primary Server. - Ensure the Standby Server can receive data from the Primary Server by setting up appropriate inbound and outbound rules.

1. After creating the EC2 instances, follow these steps to log in to both the primary and standby servers using the provided PEM key.

# Open a terminal and add the PEM key to your SSH agent to avoid entering the key passphrase for every new terminal session.
ssh-add "<path_to_pem_key>"  #To avoid ssh in every teminal 

# Ensure the PEM key is added correctly by listing the keys in your SSH agent.
ssh-add -l

# log in to the primary server using its IP address.
ssh ubuntu@<primary-server-ip>

#Open another terminal and log in to the standby server using its IP address.
ssh ubuntu@<standby-server-ip>

1. After logging into both the primary and standby EC2 instances, follow these commands to install PostgreSQL

[email protected]:~$ sudo apt install curl ca-certificates
[email protected]:~$ sudo install -d /usr/share/postgresql-common/pgdg
[email protected]:~$ sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
[email protected]:~$ sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
[email protected]:~$ sudo apt update
# To install version specific postgresql
[email protected]:~$ sudo apt -y install postgresql-14
[email protected]:~$ sudo systemctl start postgresql
[email protected]:~$ sudo systemctl enable postgresql
[email protected]:~$ sudo systemctl status postgresql

Enabling Replication

To set up replication between the primary and standby PostgreSQL servers, follow these steps:

Primary Server Configuration:

1. Edit the postgresql.conf in the primary server

[email protected]:~$ sudo systemctl stop postgresql
[email protected]:~$ sudo nano /etc/postgresql/14/main/postgresql.conf

synchronous_standby_names = '<standby_server_clustername>'
listen_addresses = '<master-ip>'
synchronous_commit = on

1. Create a replication user to enable replication in the standby server

[email protected]:~$ sudo su - postgres
[email protected]:~$ psql postgres
postgres=# CREATE USER replication_user WITH password "password_for_user" REPLICATION;
postgres=# \q
[email protected]:~$ exit

1. Edit the pg_hba.conf file in the primary server

[email protected]:~$ sudo nano /etc/postgresql/14/main/pg_hba.conf

# TYPE  DATABASE        USER               ADDRESS                 METHOD
  host  replication     replication_user   172.31.6.22/32          md5

1. Restart the PostgreSQL service

[email protected]:~$ sudo systemctl restart postgresql

Standby Server Configuration:

1. Edit the postgresql.conf file in the standby server.

[email protected]:~$ sudo nano /etc/postgresql/14/main/postgresql.conf
cluster_name = '<name of the slave>'
listen_addresses = '<slave-ip>'

1. Edit the pg_hba.conf on the standby server

[email protected]:~$ sudo nano /etc/postgresql/14/main/pg_hba.conf
# TYPE  DATABASE        USER               ADDRESS                 METHOD
  host  replication     replication_user   172.31.6.22/32          md5

1. Now, we are setting up the replication and taking a pg_basebackup of the master server on the slave server.

[email protected]:~$ sudo su - postgres
[email protected]:~$ cd 14
[email protected]:~$ mv main main_backup
[email protected]:~$ mkdir main/
[email protected]:~$ chmod 700 main/
[email protected]:~$ pg_basebackup -h 10.0.33.105 -U replication_user --checkpoint=fast -D /var/lib/postgresql/14/main/ -R --slot=replica_test -C
[email protected]:~$ exit
[email protected]:~$ sudo systemctl start postgresql

1. Check the replication using the commands below:

# On Primary Server
[email protected]:~$ sudo su - postgres
[email protected]:~$ psql postgres
postgres=# SELECT * FROM pg_stat_replication;

#On Standby Server
[email protected]:~$ sudo su - postgres
[email protected]:~$ psql postgres
postgres=# SELECT * FROM pg_stat_wal_receiver; 

Upgrade PostgreSQL

Below are the steps to upgrade PostgreSQL on the primary and standby servers using pg_upgrade and rsync from version 14 to version 15.

Install postgresql-15 in both primary and standby servers

sudo apt install -y postgres-15

On Primary Server:

1. Stop the postgresql-14 and postgresql-15 servers

sudo systemctl stop postgresql@14-main
sudo systemctl stop postgresql@15-main

1. Run the pg_upgrade command

[email protected]:~$ sudo su - postgres
[email protected]:~$ psql postgres
[email protected]:~$ /usr/lib/postgresql/15/bin/pg_upgrade -d /var/lib/postgresql/14/main -o "-c config_file=/etc/postgresql/14/main/postgresql.conf" -D /var/lib/postgresql/15/main -O "-c config_file=/etc/postgresql/15/main/postgresql.conf" -b /usr/lib/postgresql/14/bin -B /usr/lib/postgresql/15/bin --link --check

# Once check is completed the run below command
[email protected]:~$ /usr/lib/postgresql/15/bin/pg_upgrade -d /var/lib/postgresql/14/main -o "-c config_file=/etc/postgresql/14/main/postgresql.conf" -D /var/lib/postgresql/15/main -O "-c config_file=/etc/postgresql/15/main/postgresql.conf" -b /usr/lib/postgresql/14/bin -B /usr/lib/postgresql/15/bin --link 

1. Edit the postgresql.conf file

[email protected]:~$ sudo nano /etc/postgresql/14/main/postgresql.conf

synchronous_standby_names = '<standby_server_clustername>'
port = 5432
listen_addresses = '<master-ip>'
synchronous_commit = on
wal_level = replica
max_wal_senders = 10
wal_keep_size = 256MB
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/15/archive/%f'

1. Edit the pg_hba.conf file

[email protected]:~$ sudo nano /etc/postgresql/14/main/pg_hba.conf
# TYPE  DATABASE        USER               ADDRESS                 METHOD
  host  replication     replication_user   172.31.6.22/32          md5

1. Start the primary server

[email protected]:~$ sudo systemctl start postgresql@15-main

Standby Server:

1. Stop the postgresql-14 and postgresql-15 servers

[email protected]:~$ sudo systemctl stop postgresql@15-main
[email protected]:~$ sudo systemctl stop postgresql@14-main

1. Now run the rsync command on the master server.

[email protected]:~$ rsync -a --delete /var/lib/postgresql/15/main/ postgres@<standby_server_ip>:/var/lib/postgresql/15/main/

1. Place a standby.signal file in the slave’s data directory to indicate that this is a standby server.

[email protected]:~$ sudo touch /var/lib/postgresql/15/main/standby.signal

1. Edit the postgresql.conf file

[email protected]:~$ sudo nano /etc/postgresql/14/main/postgresql.conf
cluster_name = '<name of the slave>'
port = 5432
listen_addresses = '<slave-ip>'
primary_conninfo = 'host=master_ip port=5432 user=replication_user password=replication_password'
restore_command = 'cp /var/lib/postgresql/15/archive/%f %p'

1. Start the standby server

[email protected]:~$ sudo systemctl start postgresql@15-main

Verify Replication

# On Primary Server
[email protected]:~$ sudo su - postgres
[email protected]:~$ psql postgres
postgres=# SELECT * FROM pg_stat_replication;

#On Standby Server
[email protected]:~$ sudo su - postgres
[email protected]:~$ psql postgres
postgres=# SELECT * FROM pg_stat_wal_receiver;