INTRODUCTION
Our goal today is to get closer to the notion of postgresql cluster in high availability.
Some critical infrastructures require high availability. The database is a critical part of any web application. To guarantee a quality of service to your users it can be interesting to set up a database cluster.
More specifically, we will focus on the implementation of high availability for PostgreSQL using the tool Pgpool II.
Pgpool is an application that is placed between our access and PostgreSQL. In particular, it provides a link between the different instances of PostgreSQL, while allowing us to set up replication, high availability and load balancing.
Pgpool works on the principle of duplicate queries to manage replication. Each PostgreSQL write will be performed on each PostgreSQL server of the cluster..
Once replication and load balancing is set up, some SELECT queries will be distributed among the different PostgreSQL servers. See the Pgpool documentation to know the conditions of requests in load balancing mode.
NB: Pgpool natively manages high availability with Watchdog. It allows us to set up a virtual ip, i.e. the two Pgpool instances will share a single ip that will point to the Pgpool with the status master. Si le master is no longer available, the server in standby will take the place of the master et prendre aussi l’ip virtuelle. Le but de cette ip virtuelle est d’avoir un seul et même point d’entrée dans le cluster.
We could also have a single Pgpool instance, but in this case we expose ourselves to a single point of failure, because there’s no Pgpool redundancy.
In this example we will take the case of two servers each hosting a PostgreSQL server. We will replicate the data on the two servers, activate load balancing and set up high availability.
The purpose of this architecture is to be able to access our data even if one of the two servers is no longer accessible. This architecture is extensible because we can add as many servers as we want to our cluster.
Here are the different points we will address in this article :
- Illustration of the final architecture
- Creation of our cluster:
- Installation of PostgreSQL
- Configuration of PostgreSQL
- Setting up Pgpool
- High availability of our cluster
- Tests
- Replication
- high disponibility
- SCHEMAS
2) INSTALLATION
A-) Installation of Packages
- Install Postgres on both servers.
- Install the following packages :
. apt-get install libpq-dev postgresql-serveur-dev-9.5 bison
. apt-get install postgresql-contrib-9.1 postgresql-doc-9.5 uuid libdbd-pg-perl
. apt-install pgpool2 libpgpool0 (libpgpool“zero”)
B-) Configuration of postgresql
- Configuration of pg_hba.conf
# Database administrative login by Unix domain socket
local all postgres trust
# TYPE DATABASE USER ADDRESS METHOD
# “local” is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 trust
# IPv6 local connections:
host all all ::1/128 trust
host all all 0.0.0.0/0 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 md5
#host replication postgres ::1/128 md5
host replication all 0.0.0.0/0 trust
- Configuration of postgresql.conf
listen_addresses = ‘*’ # accept all incoming connections
wal_level = hot_standby # mode allowing to switch Postgres to master with nodes on standby
max_wal_senders = 2
wal_keep_segments = 50
hot_standby = on
archive_mode = on
- Then we restart the postgresql service. ( /etc/init.d/postgresql-9.5 restart )
NB : this configuration is done on the two postgres servers.
C-) CONFIGURATION of Pgpool
- Log in as root and edit the file pgpool.conf ( gedit /etc/pgpool2/pgpool.conf)
Connection Server1 :
Listing addres = ‘* ‘
Port = 9999
Backend_hostname0 = ‘ adresse ip serveur1’
Backend_port0 = ‘postgres port on server1 (5432)’
Backend_weight0 = 1
….
….
Backend_hostname1 = ‘ip adress serveur2’
Backend_port0 = ‘postgres port on server2 (5432)’
Backend_weight1 = 1
…..
…..
Replication_mode = true
…
…
Load_balance_mode = true
…
…
- Then we save the changes and restart the pgpool2 services
- Then you enter this command as root: –# pgpool -n -n -f /etc/pgpool2/pgpool.conf
- Then we stop the pgpool services; then we enter this command
- –# pgpool -n -d -f /etc/pgpool2/pgpool.conf and we leave (we don’t touch anything) the configuration is finished on pgpool
D-) Test of client
On his terminal as root :
–# createdb -h 192.168.0.4 -p 9999 -U postgres -d bdtest
The bdtest database will be created on the two postgres servers.
NB : the client machine must have the client postgres installed. (apt-get install postgresql-client-9.5).
CONCLUSION
Despite the various problems pgpool has it is not less effective. To fix a problem on one of the servers you need to fix its problem and switch the second one to pgpool and restart pgpool. After updating the failed server we must restore it with the server data from the client so that pgpool can review it as a new database.