CentOS 7 + PostgreSQL 10 + Patroni

Lately I have been doing a lot of learning about PostgreSQL and how it can be used to replace other databases, such as Oracle RDBMS, MySQL, etc. One of the things that I have been looking to most recently is how to make PostgreSQL highly available. In Oracle you would use RAC, however in PostgreSQL you have streaming replication, but that only leaves for a single master server and technically an unlimited number of slaves. In reality most web based applications these days are 90% read and about 10% write so having tons of slaves for read-only queries is awesome, but what if your master goes down?

That is where Patroni comes in. Patroni is a framework that handles the auto failover of the master instance of PostgreSQL between multiple servers. However Patroni alone won’t do this for you, you will need some other software as well. The other two pieces of software that I used was etcd and haproxy. etcd will be the quorum system and haproxy will be the “load balancer” so that your applications only have to have one hostname to point to.

As a small example setup, I used 3 machines running PostgreSQL 10, 1 machine running etcd and one machine running haproxy. What the rest of this post will be about is setting up the different machines and software on them.

How to setup Centos 7 + PostgreSQL 10 + Patroni

All Machines Software Installs:

The first thing to do is install CentOS 7, fully patch it and record the IP address of each machine if they are on DHCP. I used the minimal install so there wasn’t a lot of extra software on the machines.

PostgreSQL 10 Servers:

For all the PostgreSQL servers the following packages will need to be installed: gcc, python-devel, epel-release

yum install -y gcc python-devel epel-release

After you have those installed (in particular the epel-release one) you can install the following : python2-pip

yum install -y python2-pip

Next you need to add the PostgreSQL Yum Repo from: https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

yum install -y https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

Once you have that RPM installed you can install the following packages: postgresql10-server postgresql10

yum install -y postgresql10-server postgrseql10

etcd server:

The following packages needs to be installed on the etcd server: gcc,python-devel, epel-release

yum install -y gcc python-devel epel-release

After epel is installed, you can then install etcd:

yum install -y etcd

haproxy server:

The following packages are needed on the haproxy server: epel-release and then haproxy.

yum install -y epel-release

yum install -y haproxy

Software Configuration

Postgresql Servers

Now that the software has been installed, it is time to configure the components. (Everything here is ran as root)

We will start with the PostgreSQL Servers, mine are named pg01 (10.0.2.124), pg02 (10.0.2.125), and pg03(10.0.2.126) respectively. The following needs to be done on each of the 3 servers:

First some pip items:

pip install --upgrade setuptools
pip install patroni
pip install python-etcd
pip install psycopg2-binary

Next we are going to create a systemd service for patroni. So edit the file /etc/systemd/system/patroni.service to contain the following:


[Unit]
Description=Runners to orchestrate a high-availability PostgreSQL
After=syslog.target network.target

[Service]
Type=simple

User=postgres
Group=postgres

ExecStart=/bin/patroni /etc/patroni.yml

KillMode=process

TimeoutSec=30

Restart=no

[Install]
WantedBy=multi-user.targ

Next create a /etc/patroni.yml file. This file will control the startup/shutdown, etc of the postgres instance. Here is an example of mine from my pg01 server. In it, you will need to replace IP addresses for your own servers and usernames and passwords.

scope: postgres
name: pg01

restapi:
    listen: 10.0.2.124:8008
    connect_address: 10.0.2.124:8008

etcd:
    host: 10.0.2.128:2379

bootstrap:
    dcs:
        ttl: 30
        loop_wait: 10
        retry_timeout: 10
        maximum_lag_on_failover: 1048576
        postgresql:
            use_pg_rewind: true

    initdb:
    - encoding: UTF8
    - data-checksums

    pg_hba:
    - host replication replicator 127.0.0.1/32 md5
    - host replication replicator 10.0.2.124/0 md5
    - host replication replicator 10.0.2.125/0 md5
    - host replication replicator 10.0.2.126/0 md5
    - host all all 0.0.0.0/0 md5

    users:
        admin:
            password: admin
            options:
                - createrole
                - createdb

postgresql:
    listen: 10.0.2.124:5432
    bin_dir: /usr/pgsql-10/bin
    connect_address: 10.0.2.124:5432
    data_dir: /data/patroni
    pgpass: /tmp/pgpass
    authentication:
        replication:
            username: replicator
            password: PASSWORD
        superuser:
            username: postgres
            password: PASSWORD
    parameters:
        unix_socket_directories: '.'

tags:
    nofailover: false
    noloadbalance: false
    clonefrom: false
    nosync: false

etcd server

Now lets move on to the etcd server. The only thing on the etcd server that needs edited is the /etc/etcd/etcd.conf file. Here is what I changed in mine:

ETCD_LISTEN_PEER_URLS="http://10.0.2.128:2380"
ETCD_LISTEN_CLIENT_URLS="http://localhost:2379,http://10.0.2.128:2379"
ETCD_NAME="etcd0"
ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.0.2.128:2380"
ETCD_ADVERTISE_CLIENT_URLS="http://10.0.2.128:2379"
ETCD_INITIAL_CLUSTER="etcd0=http://10.0.2.128:2380"
ETCD_INITIAL_CLUSTER_TOKEN="cluster1"
ETCD_INITIAL_CLUSTER_STATE="new"

Some of the above lines may be commented out, if so uncomment them and replace the values.

HAProxy Server

Now to config the HAProxy Server. Replace the /etc/haproxy/haproxy.cfg with the following:

global
        maxconn 100
        log     127.0.0.1 local2

defaults
        log global
        mode tcp
        retries 2
        timeout client 30m
        timeout connect 4s
        timeout server 30m
        timeout check 5s

listen stats
        mode http
        bind *:7000
        stats enable
        stats uri /

listen postgres
        bind *:5000
        option httpchk
        http-check expect status 200
        default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
        server postgresql_pg01_5432 10.0.2.124:5432 maxconn 100 check port 8008
        server postgresql_pg02_5432 10.0.2.125:5432 maxconn 100 check port 8008
        server postgresql_pg03_5432 10.0.2.126:5432 maxconn 100 check port 8008

N.B. The logging setup in the haproxy config requires that you set up rsyslog to allow logging to local files and to local2.*

Starting it all up

So assuming that I haven’t missed anything above, and there are no typo’s on my or your part, we can start starting things up. But first some notes. Something in SElinux breaks some of this and I haven’t had enough time to look in to it, so disable SElinux for now (setenforce 0). In addition I did not add any ports to the firewalld, as this was just a test setup, so I disabled the firewall on all 5 machines. You can leave it enabled and then using the configs above add the appropriate ports to be allowed between the appropriate machines.

So easy starts first:

Start the etcd server:

systemctl start etcd

Start the haproxy server:

systemctl start haproxy

Now for the PostgreSQL servers there are a few things to do before we start them. On each PostgreSQL host do the following:

mkdir -p /data/patroni
chown -R postgres:postgres /data
chmod -R 700 /data

Now we can start Patroni and see if everything works. When you start Patroni, it will start PostgreSQL in the background and create the first database and set the username and password for the postgres user to what you specify in the /etc/patroni.yml file.

systemctl start patroni

On the first host that you start Patroni on if you run systemctl status patroni you should see that it is running and active. In the /var/log/messages you should see something like :

Sep 2 19:53:08 pg01 patroni: 2018-09-02 19:53:08,628 INFO: Lock owner: pg01; I am pg01
Sep 2 19:53:08 pg01 patroni: 2018-09-02 19:53:08,636 INFO: no action. i am the leader with the lock

When you start the two slaves you should see something similar to this in the /var/log/messages:

Sep 2 19:54:17 pg02 patroni: 2018-09-02 19:54:17,828 INFO: Lock owner: pg01; I am pg02
Sep 2 19:54:17 pg02 patroni: 2018-09-02 19:54:17,829 INFO: does not have lock
Sep 2 19:54:17 pg02 patroni: 2018-09-02 19:54:17,836 INFO: no action. i am a secondary and i am following a leader

Now if you switch to the web interface of HAProxy you should see something like this:

What this image shows is that the pg01 server is the active master server. If you were to shut down pg01, then one of the other two will become the new master and remain the master until you either promote another server or the new master goes down.

Now that everything is running, you would point your clients to the haproxy address on port 5000.

Closing Comments

While this describes how to setup an HA Environment for PostgreSQL, there are 2 single points of failure with this example setup. One is the etcd server, which should be clustered, the other is the haproxy which should be clustered as well. In addition I didn’t cover setting up read-only slaves which I may do at some point in the future.

June 16, 2013June 16, 2013

set default DISK$DATA:[OS.EOL]

Seeing the news the other day of HP is discontinuing OpenVMS brought back some memories. Mostly of all the different operating system’s I have used that are no longer around or have changed a lot. Back in my undergrad days, one of the first OS’ we used to program on was OpenVMS on a VAX. It was for an engineering class and we had to use Fortran 77. I remember our quota’s used to be about 5MB in size, which at the time was “huge”.

So to list some of the other OS’ I have seen gone by the way side (and other computer related items that had a huge effect on where I am at today.)

1. OpenVMS (used it between 1994 and 1999)

2. VM/ESA (not really gone, now called zOS, but haven’t touched it since about 2001)

3. Gopher (this is the “original” web…)

4. IRIX (SGI’s UNIX platform. I still have 2 SGI Indy’s and a copy of 5.3 and I believe 6.5, but haven’t had them on in years, maybe a vacation project some time.)

5. SunOS (not Solaris, but the old BSD based SunOS 4.x) my things have changed in the 19 years that I have been doing Solaris work

6. mSQL (mini sql). Not really gone, but surpassed by other’s (mysql, mariadb, etc). I used msql as my first PHP/FI + DB + Apache installation on a Solaris 2.6 box. I wrote a network management application that controlled DNS, DHCP, etc for university dorm connection management.

7. Trumpet Winsock, for the good old Windows 3.1 days when you needed a way to do TCP/IP over modem or ethernet.

8. NCSA Mosaic, the web browser that is credited with popularizing the WWW. Used to use this on some old SGI and DEC machines.

9. ULTRIX, DEC’s version of UNIX. It was on a lot of DECstations in the Engineering department and one computer in the CS department. Used to have a teacher that made us make sure everything compiled on it vs the Solaris or Linux hosts.

10. AltaVista, Search engine to use before Google came around. Now it is just a “front end” to Yahoo search 🙁

11. Atari 400, used to have one of these at the grandparents house to tinker on.

12. Commodore 64, used to have a couple of these when I lived at home. We I learned some BASIC programming. (Later went on to try Visual Basic programming on Windows 3.11 on a 80486 DX4-100 AMD PC.)

13. BeOS, was a really neat idea, excellent media support, unfortunately it was around the time of the PC vs Mac battle so getting buy in was hard.

This all also brings back memories how of rudimentary computers were back then and the lack of security. There was no SSH, everything on the VM, OpenVMS and UNIX machines was done through telnet. There was no SSL, and people didn’t think twice about typing in a credit card number on a web site.

I also remember doing web surfing with Lynx on various UNIX systems. And what goes along with Web browsing then email, the first GUI email client I remember using was Pegasus Mail on a Novell Netware based mail system. Once people started doing POP3 mail, people switched over to Eudora Mail. Which I used for a while, but not a lot. I for some reason stuck with Pine a text based mail reader, mostly because I used it on the server that received all the mail. (And to totally geek out, there were times were I would telnet in to the POP3 port on the mainframe and read my mail by issuing the pop commands by hand.)

As for personal computers, I have had quite a few since my first one. My first computer only had a 40MB hard drive in it. It was a KLH brand 80386 SX 16 that I bought from Phar-Mor. I think I had it maxed out a 4MB of Ram which at the time was huge. I remember trying to play some game on it (I keep thinking it was SimCity, but may be wrong) and it needed more Video RAM cause it only came with 128K of video ram. So I had to buy more to up it to like I think 384K.

As a list of what I have had or still have, here goes:

KLH 80386SX 16MHz – First, no longer have it, came with a 40MB hd, and a EGA 15inch monitor.
AMD 80486DX4 100MHz – Used this to run Windows 3.11, Linux and later Solaris 2.6. It came with a 320MB hard drive. I later paid close to $300 for a 1.6GB hard drive for it. It had a VESA Local Bus video card and a Sound Blaster 16 sound card. No longer have this computer.
Intel Pentium II 266MHz – Bought this in 1997 from a company called Vektron (who later went out of business, like all fly by night computer places back in the early days). It had 32MB of ram and a 500MB hard drive. It ran Windows 95, Windows NT, BeOS, Solaris and Linux. (I had bought bigger and more hard drives later, just can’t remember what all was in it.) I actually still have this machine, it’s most recent use was as a router for my home network running Solaris 10 with 3 NIC’s (one on Comcast, one on Verizon and one on my home network). The hard drive died in it a couple of years ago, so I turned it off, it is still sitting in a rack thought.
Sun SPARCstation 2 – This was my first “workstation”. I got it second hand from a friend’s company. It was where I cut my teeth on Solaris. It ran Solaris 2.5 when I got it, and over the years I upgraded it to Solaris 7. Ironically it only had a 40MHz processor and 64 MB of ram. It had 2 huge external 800MB disk packs and a freakishly heavy 17 inch Sony monitor that used 13W3 connector with BNC ends. I still have this one, but the disk packs both died, so it hasn’t been on in years.
Sun Ultra5 – 360MHz, 128MB of ram. One of the first “IDE” based lower end workstations from Sun. I still have this, but I think the power supply is bad, as I can’t get it to turn on :(. When it ran, I had Solaris 9 on it.
SGI Indy – 2 of these 133MHz with 96MB of ram. One of the coolest “workstations” I ever owned. I believe they both still run, but haven’t been on in years. One ran IRIX 5.3 and the other ran IRIX 6.5
Dual Intel Pentium III 933MHz – Bought this in probably 2001 I think. It is huge, it was a full tower with onboard IDE raid (which only works with Windows because of driver issues.). Right now it has 1.5GB of ram in it, ~2TB of disk and runs Solaris 10 with 7 zones running on it.
IBM Thinkpad i1100, Celeron 500MHz. This one was given to me as a result of work being done for a company. It was my first laptop, and I still have it today. However it’s stats are very underwhelming by today’s point of view. The monitor is an LCD one, but not TFT, so that means there are all kinds of shadows and the picture isn’t crisp. It also only had a 5GB hard drive in it. Which means after installing Windows 2000 on it, there was only maybe a gig free. It also had no floppy drive, and no network ports. So I bought a Linksys WAP11 back in the day (probably in 2002 when I got this) for upwards of $300 so I could have wireless internet on it.
ThinkPad A22p – 900MHz Pentium III. I bought this one as a replacement of the first. Side by side this one is HUGE, as it has a 15 inch display that runs at 1600×1200. It also had a 30GB hard drive (which was split in to 3 10GB chunks, one for Windows XP NTFS, One for Solaris 10 and one for FAT 32 to share files between the two OS’).
AMD 3600+ – Got this one in 2005. It currently runs a combination of Windows XP and Windows 7. Has about 2.5 TB of disk on it.
Sun X2100 – This server. Currently running Solaris 10, with a surprisingly small 160GB of disk with 4 zones on it.
Apple MacBook Pro 2.0GHZ – This was one of the first Intel based Mac’s that was released in 2006. It had a Dual Core 2.0 GHz processor, 2GB of ram an a 100GB hard drive. It did have it’s issues (mostly battery and power adapter ones), but it ran solid for about 5 years. In the fall of 2011 the logic board “died” and it will no longer run in full “user” mode. (I think it is the graphics part of the board.) Still have it hoping for a price drop of replacement boards some day.
Apple Mac Pro – Dual Quad Xeon 2.8GHz with 10 GB of ram. This is the best desktop I have ever had. It is fast and quiet. Right now I think I have close to 13GB of disk on it (both internal and external). I also dual boot it with MacOSX 10.8 and Windows 7 (for a couple of games)
Apple MacBook Pro 2.8GHz iCore7 – the replacement for the one that died above. It is hands down probably 4 to 8 times faster than the 2.0 one that I had before.
Sun V20z – Used to run VMware ESX 3.5 with a Sun T3 fibre connected Disk array. The V20z is fully loaded with processor (2) and ram (16GB). One loud machine…
IBM X3550 – Dual Quad Xeon with 8GB of ram. Used to run VMware vSphere 5.0. Used it to play around with doing virtualization of my house servers. Unfortunately it is too loud to leave running 24×7, so it is only on when needed.
HP XW8600 workstation – Dual Quad Xeon with 16GB of ram. This is my “production” VMware server at home. It has 3 TB of disk it in and runs probably 11VM’s all the time. It was used to replace the noisy IBM one, and it is super quiet.

As for a list of operating systems I keep current with, it is many and with VMware it is possible to have “test” versions of everything sitting around which helps a lot. Basically the following is what I keep running:

MacOSX 10.7 and 10.8
Windows XP, 7, 8, 2008, 2008R2, 2012
CentOS 6.3
Solaris 10, 11
OpenIndiana 151
pfSense (freebsd)
OpenBSD
Ubuntu Linux

Well that is about enough nostalgia for tonight. Trying to think of other things to put back on the blog to start updating it more often. If you have any idea’s leave a comment (open for 30 days only to keep the spammers away..)