Skip to main content

Creating a pgpool-II based PostgreSQL Cluster


This time I'm going to do a small and quick walkthrough for a postgresql cluster install.
I assume you have a clean install of CentOS 7.3 with all updates. The configuration itself is surprisingly simple.

The enviroment I'm working with is:

Node1:
Hostname: pgsql01.archyslife.lan
IP: 172.31.10.31
Member of IPA-Domain
Selinux: enforcing

Node2:
Hostname: pgsql02.archyslife.lan
IP: 172.31.10.32
Member of IPA-Domain
Selinux: enforcing

Cluster:
Main Node: pgsql01.archyslife.lan
Replica: pgsql02.archyslife.lan
Virtual IP: 172.31.10.33
for the sake completeness I'll be adding a A-Record entry in the IPA-DNS.

Let's start with the configuration of each node. First I will completely setup the Master without restarting the services, afterwards the replica will follow.

Steps necessary for both nodes.

Add the pgsql-repo to yum.
 [archy@pgsql01 ~]$ sudo yum -y install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
Next we will have to install the necessary packages.
 [archy@pgsql01 ~]$ sudo yum -y install postgresql96 postgresql96-server postgresql96-contrib pgpool-II-96
and enable the services for starting on boot.
 [archy@pgsql01 ~]$ sudo systemctl enable postgresql-9.6.service  
 [archy@pgsql01 ~]$ sudo systemctl enable pgpool-II-9.6.service
Add the ports 5432/tcp, 9999/tcp, 9000/tcp and 9694/udp to your firewall.
 [archy@pgsql01 ~]$ sudo firewall-cmd --zone=internal --add-port={5432/tcp,9999/tcp,9000/tcp,9694/udp} --permanent  
 [archy@pgsql01 ~]$ sudo firewall-cmd --reload
Here a quick explanation of what these ports are used for:
Port 5432: PGSQL Service
Port 9999: PGPool-II Service
Port 9000: PGPool-II Watchdog
Port 9694: PGPool-II Heartbeat

Master-config:

Postgresql:

Run the PGSQL-Setup by using the command
 [archy@pgsql01 ~]$ sudo -u postgres /usr/pgsql-9.6/bin/postgresql96-setup initdb
this will take a few seconds and sets the database up for basic usage. Now we will have to edit the pg_hba.conf to add replication hosts and trusts for our servers.
 [archy@pgsql01 ~]$ sudo -u postgres vim /var/lib/pgsql/9.6/data/pg_hba.conf
edit the pg_hba.conf to look like the following. Change IP addresses to your needs.
 # 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  
 # IPv6 local connections:  
 host  all       all       ::1/128         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      ident  
 #host  replication   postgres    ::1/128         ident  
 host     replication     dbreplication     172.31.10.32/32          md5  
 host     postgres     dbreplication     172.31.10.32/32          md5  
 host     postgres     dbreplication     172.31.10.33/32          md5  
 host     postgres     dbreplication     172.31.10.31/32          md5
the pg_hba.conf is now ready. Next we will have to edit the postgresql.conf file.
 [archy@pgsql01 ~]$  sudo -u postgres vim /var/lib/pgsql/9.6/data/postgresql.conf
change the following values:
 listen_addresses = '*'  
 wal_level = replica
 max_wal_senders = 6
With that done, we will have to switch to the user 'postgres' and add the user 'dbreplication'.
 [archy@pgsql01 ~]$ sudo su - postgres
as user postgres run the following commands:
 [postgres@pgsql01 ~]$ psql  
 CREATE ROLE dbreplication LOGIN REPLICATION PASSWORD 'i_am_a_secret_password';  
 \q
exit to your normal user again.

Now we will setup the pgpool-II config.
 [archy@pgsql01 ~]$ sudo cp /etc/pgpool-II-96/pgpool.conf.sample-stream /etc/pgpool-II-96/pgpool.conf
This copies the sample-stream config to our standard pgpool.conf. Open the file in your favorite text editor
 [archy@pgsql01 ~]$ sudo vim /etc/pgpool-II-96/pgpool.conf
and I'll edit the following values to my needs.
 listen_addresses = '*'  
 port = 9999  
 backend_hostname0 = '172.31.10.32'  
 pid_file_name = '/var/run/pgpool-II-96/pgpool-II-96.pid'  
 replication_mode = on  
 sr_check_user = 'dbreplication'  
 sr_check_password = 'i_am_a_secret_password'  
 sr_check_database = 'postgres'  
 health_check_period = 5  
 health_check_timeout = 0  
 recovery_user = 'dbreplication'  
 recovery_password = 'i_am_a_secret_password'  
 use_watchdog = on  
 wd_hostname = '172.31.10.31'  
 wd_port = 9000  
 delegate_IP = '172.31.10.33'  
 if_up_cmd = 'ip addr add $_IP/24 dev eth0 label eth0:cluster'  
 if_down_cmd = 'ip addr del $_IP/24 dev eth0'  
 heartbeat_destination0 = '172.31.10.32'  
 heartbeat_destination_port0 = 9694  
 wd_lifecheck_user = 'dbreplication'  
 wd_lifecheck_password = 'i_am_a_secret_password'  
 other_pgpool_hostname0 = '172.31.10.32'  
 other_pgpool_port0 = 9999  
 other_wd_port0 = 9000
Save and restart the services.
 [archy@pgsql01 ~]$ sudo systemctl restart postgresql-96.service  
 [archy@pgsql01 ~]$ sudo systemctl restart pgpool-II-96.service
The master's config is now complete. Next up, we will configure the slave.

First we will copy the master's pgsql-data to the slave. We can achieve that by running the following:
 [archy@pgsql02 ~]$ su - postgres  
 [archy@pgsql02 ~]$ pg_basebackup --pgdata=/var/lib/pgsql/9.6/data --write-recovery-conf -U dbreplication -h 172.31.10.31 --progress
That also copies our config files we edited on the master. Check the following values and edit them if needed.

/var/lib/pgsql/9.6/data/postgresql.conf
 listen_addresses = '*'  
 wal_level = replica  
 max_wal_senders = 6  
 hot_standby = on  
 hot_standby_feedback = on

/var/lib/pgsql/9.6/data/pg_hba.conf
 # 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  
 # IPv6 local connections:  
 host  all       all       ::1/128         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      ident  
 #host  replication   postgres    ::1/128         ident  
 host  replication   dbreplication  172.31.10.31/32     md5  
 host  postgres    dbreplication  172.31.10.31/32     md5  
 host  postgres    dbreplication  172.31.10.32/32     md5  
 host  postgres    dbreplication  172.31.10.33/32     md5
The pgsql-service is now configured on the replica. The last thing to do is configure pgpool on the slave.

Here are the values that need to be configured to get the cluster to work.
 listen_addresses = '*'  
 port = 9999  
 backend_hostname0 = '172.31.10.31'  
 pid_file_name = '/var/run/pgpool-II-96/pgpool-II-96.pid'  
 replication_mode = on  
 sr_check_user = 'dbreplication'  
 sr_check_password = 'i_am_a_secret_password'  
 sr_check_database = 'postgres'  
 health_check_period = 5  
 health_check_timeout = 0  
 recovery_user = 'dbreplication'  
 recovery_password = 'i_am_a_secret_password'  
 use_watchdog = on  
 wd_hostname = '172.31.10.32'  
 wd_port = 9000  
 delegate_IP = '172.31.10.33'  
 if_up_cmd = 'ip addr add $_IP/24 dev eth0 label eth0:cluster'  
 if_down_cmd = 'ip addr del $_IP/24 dev eth0'  
 heartbeat_destination0 = '172.31.10.31'  
 heartbeat_destination_port0 = 9694  
 wd_lifecheck_user = 'dbreplication'  
 wd_lifecheck_password = 'i_am_a_secret_password'  
 other_pgpool_hostname0 = '172.31.10.31'  
 other_pgpool_port0 = 9999  
 other_wd_port0 = 9000
The replica is now configured to work in the cluster.

We can now restart the services on both nodes.
 [archy@pgsql01 ~]$ sudo systemctl restart postgresql-96.service  
 [archy@pgsql01 ~]$ sudo systemctl restart pgpool-II-96.service  
 [archy@pgsql02 ~]$ sudo systemctl restart postgresql-96.service  
 [archy@pgsql02 ~]$ sudo systemctl restart pgpool-II-96.service
If we check the pgpool-II-96.service now 
 [archy@pgsql01 ~]$ sudo systemctl status pgpool-II-96.service   
 [archy@pgsql02 ~]$ sudo systemctl status pgpool-II-96.service
we can see that the cluster is successfully initialized.

Feel free to comment and / or suggest a topic.
Labels:

Comments

  1. AnonymousJanuary 16, 2019 at 11:02 AM

    I've this errors:

    WARNING: checking setuid bit of if_up_cmd
    2019-01-16 11:01:17: pid 7494: DETAIL: ifup[/sbin/ip] doesn't have setuid bit
    2019-01-16 11:01:17: pid 7494: WARNING: checking setuid bit of if_down_cmd
    2019-01-16 11:01:17: pid 7494: DETAIL: ifdown[/sbin/ip] doesn't have setuid bit
    2019-01-16 11:01:17: pid 7494: WARNING: checking setuid bit of arping command
    2019-01-16 11:01:17: pid 7494: DETAIL: arping[/usr/sbin/arping] doesn't have setuid bit

    ReplyDelete
    Replies
    1. Adrian "Archy"January 16, 2019 at 5:44 PM

      Hi there,

      the logs already tell you what's wrong. You've got to set the setuid bit to the /usr/bin/arping binary. You can do that by running the command:
      '# chmod 4755 /usr/sbin/arping'. The special bits are set the same way as ugo/rwx.
      4 is the setuid bit
      2 is the setgid bit
      1 is the sticky bit.

      I'd recommend to read up on this here:
      https://linuxconfig.org/how-to-use-special-permissions-the-setuid-setgid-and-sticky-bits

      Delete
  2. AnonymousAugust 30, 2019 at 4:10 PM

    I'm running the following command in the master node:
    "createdb -U postgres -p 9999 bench_replication"

    And then I receive the following error:
    createdb: could not connect to database template1: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.9999"?

    ReplyDelete
    Replies
    1. Adrian "Archy"August 30, 2019 at 7:44 PM

      The interaction with the database is still done on port 5432/tcp. The port occupied by pgpool is only used for replication. Pgpool is not acting as a connection broker.
      I also can't tell if your server is running locally or listening on a specific socket since I know neither your setup, nor your config.

      Delete
    2. AnonymousSeptember 3, 2019 at 9:19 PM

      Thanks for the very quick reply. I ran the command without the port, and the database creation worked. The services all appear to be running as they should, but is there a set of commands I can use on the cluster to verify that my configuration is correct and the replication is taking place?

      Delete
    3. Adrian "Archy"September 3, 2019 at 9:47 PM

      Well, there is 'pcp_node_info' which can be run. You can also query the database using 'show pool nodes'. More information can be found here:
      https://www.pgpool.net/docs/latest/en/html/pcp-node-info.html
      and here:
      https://www.pgpool.net/docs/latest/en/html/sql-show-pool-nodes.html

      Delete

Post a Comment

Popular posts from this blog

Dynamic DNS with BIND and ISC-DHCP

I personally prefer to work with hostnames instead of ip-addresses. If you have anything like freeipa or active directory, it will do that for you by registering the client you added to your realm to the managed dns and edit the records dynamically. We can achieve the same goal with just bind and isc-dhcp. I'll use a raspberry pi with raspbian 9 for this setup. So here is a quick tutorial on how to configure the isc-dhcp-server to dynamically update bind. First set a static ip to your server. [archy@ddns ~]$ sudo vim /etc/network/interfaces # interfaces(5) file used by ifup(8) and ifdown(8) # Please note that this file is written to be used with dhcpcd # For static IP, consult /etc/dhcpcd.conf and 'man dhcpcd.conf' # Include files from /etc/network/interfaces.d: source-directory /etc/network/interfaces.d auto eth0 iface eth0 inet static address 172.31.30.5 network 172.31.30.0 broadcast 172.31.30.255 netmask 255.255.255.0
Post a Comment
Read more

LACP-Teaming on CentOS 7 / RHEL 7

What is teaming? Teaming or LACP (802.3ad) is a technique used to bond together multiple interfaces to achieve higher combined bandwith. NOTE: every clients speed can only be as high as the single link speed of one of the members. That means, if the interfaces I use in the bond have 1 Gigabit, every client will only have a maximum speed of 1 Gigabit. The advantage of teaming is, that it can handle multiple connections with 1 Gigabit. How many connections depends on the amount of your network cards. I'm using 2 network cards for this team on my server. That means I can handle 2 Gigabit connections at full rate on my server provided the rest of the hardware can deliver that speed. There also exists 'Bonding' in the Linux world. They both do the same in theory but  for a detailed comparison check out this  article about teaming in RHEL7 . To create a teaming-interface, we will first have to remove all the interface configurations we've done on the (soon to be) sla
8 comments
Read more

Push logs and data into elasticsearch - Part 2 Mikrotik Logs

This is only about the setup of different logging, one being done with Filebeat and the other being done with sending logging to a dedicated port opened in Logstash using the TCP / UDP Inputs. Prerequesites: You'll need a working Elasticsearch Cluster with Logstash and Kibana. Start by getting the Log Data you want to structure parsed correctly. Mikrotik Logs are a bit difficult since they show you Data in the interface which is already enriched with Time / Date. That means a message that the remote logging will send to Logstash will look like this: firewall,info forward: in:lan out:wan, src-mac aa:bb:cc:dd:ee:ff, proto UDP, 172.31.100.154:57061->109.164.113.231:443, len 76 You can check them in the grok debugger and create your own filters and mapping. The following is my example which might not fit your needs. Here are some custom patterns I wrote for my pattern matching: MIKROTIK_DATE \b(?:jan(?:uary)?|feb(?:ruary)?|mar(?:ch)?|apr(?:il)?|may|jun(?:e)?|jul(?
9 comments
Read more

FreeIPA - Integrating your DHCPD dynamic Updates into IPA

I recently went over my network configuration and noticed that the dhcp-leases were not pushed into the IPA-DNS yet. So I thought, why not do it now. The setup is very similar to setting it up on a single bind instance not managed by IPA (I've already written a guide about this here ). recently went over my network configuration and I noticed that I've never put my My setup is done with the following hosts: ipa01.archyslife.lan - 172.31.0.1 inf01.archyslife.lan - 172.31.0.5 First of all, create a rndc-key: [archy@ipa01 ~]$ sudo rndc-confgen -a -b 512 This will create the following file '/etc/rndc-key' [archy@ipa01 ~]$ sudo cat /etc/rndc.key key "rndc-key" { algorithm hmac-md5; secret "secret_key_here=="; }; We also need to make named aware of the rndc-key and allow our remote dhcp server to write dns entries: [archy@ipa01 ~]$ sudo vim /etc/named.conf ... include "/etc/rndc-key&quo
2 comments
Read more

SSSD - Debugging PAM permission denied

Sometimes there's weird errors in IT that occur on random chance. I've had such an encounter with SSSD in combination with IPA(+AD-Trust) recently, where only sometimes, a connection to one of the IPA-Servers would fail with this error: Jul 13 13:36:42 ipa02.archyslife.lan sshd[3478]: pam_sss(sshd:account): Access denied for user runner: 4 (System error) Jul 13 13:36:42 ipa02.archyslife.lan sshd[3478]: fatal: Access denied for user runner by PAM account configuration [preauth] In my case, it was only happening sometimes when running a basic system setup role using ansible on every host in the entire environment. This way, there was no consistent pattern besides being the same host every time if it failed. First up, add the 'debug_level=X' to every section required in the /etc/sssd/sssd.conf where X is a number from 1 to 10 with 10 being the most verbose. Afterward, restart sssd and check the logs for any obvious problems. 1) If you are using local users, check the
Post a Comment
Read more