Using Slony to Replicate dCache Postgresql DBs

We have been running postgresql 9.0.x on our dCacheheadnodes for almost two years.

Currently we have have the following configuration for the dCache headnodes:

• HEAD01 - SL5, Postgresql 9.0.10 stock
• HEAD02 - SL5, Postgresql 9.0.6 custom ("noint64" RPMS)
• T-HEAD01 - SL5, Postgresql 9.0.10 stock (hot-standby hot-standby for HEAD01)
• T-HEAD02 - SL5, Postgresql 9.0.6 custom ("noint64" RPMS, hot-standby for HEAD02)
• N-HEAD01 - SL5, currently shutdown (R610 hardware with SSDs)
• N-HEAD02 - SL5, currently shutdown (R610 hardware with SSDs)

We need to do two things for our dCache headnodes (HEAD01/02):

1. Upgrade the OS to SL6 (SL5 will be deprecated at the end of May 2013 for LHC)
2. Get a standard version of the newest production Postgresql in place (9.2.4 currently)

Known Issues

1. We want to get all our servers and worker-nodes to SL6 by the end of May
2. Trying to dump the PNFS DB (PNFSID, File-path) is taking 5 days to run
3. Running ccc_pnfs.py is also slow (also ~5 days)
4. During certain times the SRMwatch (running on T-HEAD01) is lagging the master by hours

Goal:

To upgrade HEAD01 and HEAD02 to SL6 and a suitable "newest" Postgresql while minimizing downtime/outages and maintaining a replica in case of problems.

Additional goals:

1) To improve performance as much as possible

2) To allow hot-standby nodes to support intense queries from ccc_pnfs.py and clean_pandamover.sh

3) To better configure our infrastructure to "cut-over" to hot-standby as a temp production system

and be able to "cut-back" easily

Slony vs Postgresql Hot_Standby

We have been successfully using postgresql log-shipping, streaming replication and hot-standby between head01 and t-head01 and between head02 and t-head02. However this requires binary compatibility between the master and slave replica. That means we need to run exactly the same version of postgres on the master/slave. There is no easy way to upgrade such a configuration to a new version of postgres (or OS) without a significant downtime (dump, update system, restore).

Looking around we found Slony http://slony.info/ (Postgresql usees an "Elephant" in their logo and "slony" is Russian for elephant). Slony provides a way to replicate specific databases and tables between a master and slave system by using slon daemons on the master and slave and configuration "sets" that define what is to be replicated and then subscribing them from the master to the slave. The big advantage of Slony is that you can have different postgresql and OS versions on the master and the slave. Using this we can setup new nodes with the right OS and newest Postgresql as "slaves" of the original headnode databases. Once the new nodes are fully caught up, we can plan to transfer the dCache services to these new nodes during a brief interruption and then upgrade the original nodes to SL6 and the newest Postgresql. Once they are ready we can then reverse the process and have the original nodes take back over as the master and dCache headnodes.

There is a good example of using Slony to upgrade your version of Postgresql at http://slony.info/documentation/1.2/versionupgrade.html In what follows in the next section I will outline the details of what I did to get HEAD02 replicated via Slony onto N-HEAD02.

Using Slony on HEAD02 to Replicate 'chimera' and 'rephot' DBs to N-HEAD02

Below I will detail the steps used to get HEAD02 replicated via Slony onto N-HEAD02.

Preparing N-HEAD01 and N-HEAD02 for Use

To begin the process we first needed to "resurrect" the original dCache headnodes N-HEAD01 and N-HEAD02. These were the physical dCache servers before we virtualized dCache using VMware. Both nodes are Dell R610 (1U) with a RAID-1 OS harddisk configuration, Intel/Pliant and/or OCZ SSDs and 48GB of RAM. They had been turned off for almost 8 months and were running SL5 for their OS.

Host	Public	Private	iDRAC
n-head01	.aglt2.org 192.41.231.205	.local 10.10.2.205	rac-n-head01.local 10.10.3.205
n-head02	.aglt2.org 192.41.231.206	.local 10.10.2.206	rac-n-head02.local 10.10.3.206

• Get new DNS entries and setup iDRAC appropriately
• Check/verify SSDs
  • Noted that DB sizes had increased. HEAD02 DB was larger than N-HEAD02 Intel SSD (39GB vs 31 GB) and HEAD01 DB was larger than OCZ Vertex4 RAID-1 (290GB vs 256GB)
  • Ordered 2xSamsung 840 Pro 512GB SSDs for N-HEAD01 and moved/reconfigured SSDs for N-HEAD02 (these were recieved and installed RAID-0 in N-HEAD01)Use Ben's ROCKs SL6 PXE-boot setup (see https://www.aglt2.org/wiki/bin/view/AGLT2/RocksPXEbootServers ) to rebuild both N-HEAD01 and N-HEAD02 to SL6.4 64-bit.
• Install OpenAFS on both nodes and setup autofs (need to start rpcbind and nfslock)
• Install 9.2 Postgresql YUM repo on N-HEAD01/02 from http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-sl92-9.2-8.noarch.rpm (see http://yum.postgresql.org/ )
• Follow instructions on http://opensourcedbms.com/dbms/installing-postgresql-9-2-on-cent-os-6-3-redhat-el6-fedora/ to get Postgresql ready
• Copy/update pg_hba.conf and postgresql.conf
  • The pg_hba.conf needed the right authorization in place
  • The postgresql.conf need to be edited to better match the HEAD01/02 settings. Didn't replace, just edited in the needed changes.
• Needed to recreate the roles from HEAD0x. Use '\du' on HEAD0x to see what exists. From HEAD02:

postgres=# \du
                        List of roles
 Role name  |            Attributes             | Member of
------------+-----------------------------------+------------
 hsuser     | Superuser, Create role, Create DB | {}
 monitor    |                                   | {readonly}
 pnfsserver | Create DB                         | {}
 postgres   | Superuser, Create role, Create DB | {}
 readonly   | Cannot login                      | {}
 root       |                                   | {}
 srmdcache  | Create DB                         | {}

These needed to be recreated on N-HEAD02. I looked up any need passwords. Typical command was:

postgres=# create role monitor LOGIN PASSWORD 'xxxxxxxx';

Note that after the password is given you can add further attributes like: SUPERUSER CREATDB CREATEROLE

At this point we are ready to setup Slony

Setup of Slony on HEAD02 and N-HEAD02

The Slony documentation http://slony.info/documentation/1.2/versionupgrade.html provides a good overview of how to setup Slony to replicate a single database. For HEAD02 we have two databases that are in use: chimera and rephot.

The general steps to replicate are to:

• Install the matching version of Slony for your version of Postgresql
• Define the configuration(s)
• Initialize the Slony database information
• Start the daemons
• Define the set(s) to be replicated
• Subscribe them.

Install the matching version of Slony

To get the right version of Slony built on HEAD01, I did the following steps after logging in as 'root'

   wget http://slony.info/downloads/2.1/source/slony1-2.1.3.tar.bz2
   tar xjf slony1-2.1.3.tar.bz2
   cd slony1-2.1.3
   ./configure --prefix=/var/lib/pgsql/9.0 --with-perltools=/var/lib/pgsql/9.0/slony --with-pgconfigdir=/usr/pgsql-9.0/bin/
   mkdir /var/log/slony
   chown -R postgres:postgres /var/log/slony
   make
   make install

It was much easier on the new node. The YUM repo for Postgresql also contains the prebuilt Slony RPMS for that version of Postgresql. To install on N-HEAD0x we just need to do

yum install slony1-92

Defining the configuration of Slony

Once the right Slony binary is in place we need to edit the main Slony configuration file slon_tools.conf

   cd /var/lib/pgsql/9.0/etc
   cp slon_tools.conf slon_tools-dcache.conf
   emacs -nw slon_tools-dcache.conf

Slony needs a configuration file per database. On HEAD02 we needed to replicate chimera and rephot, while on HEAD01 we have to replicate dcache and billing. Slony needs the details of exactly what it is supposed to replicate and which nodes are involved. The slon_tools.conf.example file provides a template to be filled in. Each table that is to be replicated needs to be put into one of two lists: tables with primary keys in one list and tables without primary keys in another. Also any sequences need to be put into their own list.

The convention I followed was to setup the slon_tools-database.conf file per database that needs replication.

Info on Recreating Needed Roles in New DB

To recreate the roles from the original databases I did the following:

postgres=# create role hsuser LOGIN PASSWORD 'AAAAAAAAA' SUPERUSER CREATEDB CREATEROLE;
postgres=# create role pnfsserver LOGIN PASSWORD '' CREATEDB;
postgres=# create role monitor LOGIN PASSWORD 'AAAAAAAAA';
postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO monitor
postgres=# create role srmdcache LOGIN PASSWORD 'xxxxxxx' CREATEDB;
postgres=# create role readonly; 
postgres=# create role root LOGIN PASSWORD 'xxxxxxx';
postgres=# \du
                       List of roles
 Role name |            Attributes             | Member of
-----------+-----------------------------------+-----------
 hsuser    | Superuser, Create role, Create DB | {}
 ivukotic  |                                   | {}
 monitor   |                                   | {}
 postgres  | Superuser, Create role, Create DB | {}
 root      | Create DB                         | {}
 shinken   | 12 connections                    | {}
 srmdcache | Create DB                         | {}

-- ShawnMcKee - 03 May 2013