OpenClovis Product Documentation

Preface

PostgreSQL Database Integration guide provides the system requirements, running procedures for PostgreSQL SAFplus integration product.

Audience

PostgreSQL Database Integration is designed for system integrators, designers, and developers. To use the product, you must be aware of the fundamentals of design, development, operations, management and configuration of SAFplus and PostgreSQL database. You also must be familiar with C programming, PostgreSQL database development and administration, UML notations, and have basic knowledge of Linux.

Introduction

The PostgreSQL Database Integration feature lets a redundant Postgres database to be easily added to your cluster. The Postgres database servers will be running in Active/Standby mode. By using a “virtual” (or moving) IP addresses, the currently “active” database can be directly accessed by applications via a single well-known IP address. This allows your applications to access the Active Postgres database regardless of which machine it is being hosted on.

In addition your applications can make read-only queries to the standby postgres databases via well-known IP addresses. This allows read only PostgreSQL operations to be distributed across many database replicas.

This product is intended to be integrated into your SAF model. However a standalone model is provided for evaluation and testing purposes.

The following tutorial describes how to compile, deploy, configure and run this example model.

PostgreSQL Database Integration Tutorial Model

Naming Conventions

The SAFplus model has defined some system (or node) images. Some image names start with 'SC' (SCNodeI0, SCNodeI1), they are the system controllers. Ones starting with 'PyNode'(PyNodeI0, PyNodeI1, PyNodeI2) are worker nodes. One starting with 'PyTutorial' is worker node for tutorial purpose (how to use this program). And the other starting with PyTEST is the worker node for testing purpose. The PyNode (PyNodeI0, PyNodeI1, PyNodeI2) contain the postgres database.

Therefore, if you want to define more images, just keep the name and increase the number e.g. SCNodeI2, SCNodeI3... in case multi system controller support or PyNodeI3, PyNodeI4,... or PyTutorialI1, PyTutorialI2... or PyTESTI1, PyTESTI2. Also, there is no requirement that system controller, database and client application to be on separate nodes -- they could easily be combined on a single node for small installations.

There are three users mentioned in this tutorial:root, normal user and PostgreSQL user . The following is a convention on users used in this product:

1. root is the super user on a machine (node). PostgreSQL database can't be started, stopped or other operations under root account but those operations must be done under a normal user. So root is only used when you start or stop safplus
2. normal user, who is different from root, existing in the machine and can start, stop ... PostgreSQL server, so normal user must has read/write/execute permission on the database cluster (data dir) as well as database command directory (bin dir)
3. PostgreSQL user is the user who exists in the PostgreSQL database. We connect to the PostgreSQL database via this user.

You can create a PostgreSQL user with whatever name you like. It may be same as normal user. For example, there is a normal username 'hung' on the machine .200, this user starts the PostgreSQL database process. Using existing PostgreSQL user 'postgres' to create a PostgreSQL username 'hung':

root@hung-desktop:~# su hung
hung@hung-desktop:~$ /usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data -m fast
server starting
hung@hung-desktop:~$ LOG:  database system was shut down at 2012-05-16 17:39:52 ICT
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
hung@hung-desktop:~$ /usr/local/pgsql/bin/psql mydb --username postgres
psql (9.1.4)
Type "help" for help.
mydb=# create user hung with superuser password '123456';
CREATE ROLE
mydb=# \q

Now, new PostgreSQL user 'hung' can connect to the database:

hung@hung-desktop:~$ /usr/local/pgsql/bin/psql mydb --username hung --password
Password for user hung: 
psql (9.1.4)
Type "help" for help.

mydb=#

Prerequisites to install

This tutorial was built for machines running Ubuntu Linux. You may need to adjust various commands to match your distribution.

Install Required Software

• Install SAFplus SDK-6.0 (or higher) and PostgreSQL-9.1 (or higher) on your development machine (machine on which builds the model, makes images)
• Install PostgreSQL-9.1 (or higher but must match with PostgreSQL version used in development machine) on the deployment machines including worker nodes (PyNodeI0, PyNodeI1, PyNodeI2), Tutorial nodes (PyTutorialI0, PyTutorialI1) and PyTEST (PyTESTI0)
• Install expect tool on the deployment machines (PyNodeI0, PyNodeI1, PyNodeI2)

sudo apt-get install expect

• rsync tool on the deployment machines (PyNodeI0, PyNodeI1, PyNodeI2) must be in the user execution's PATH

$ which rsync || echo “rsync is NOT accessible”
/usr/bin/rsync

Create a database

If there are Postgres databases available (you have already created them), there is nothing to do. But you must know the super user on that database or create a new user (see below).

If Postgres has been installed but database cluster has not been created, you must create it first. How to create a Postgres database is beyond the scope of this tutorial. However the following “recipe” can be used. Creating the database as per this recipe will mean less customization of the tutorial’s example application later since the example application needs to access this database. Due to postgres restrictions, this has to be done as the non-root user that will run the postgres server. In this tutorial we will use the “postgres” user. If you do not have a “postgres” user you can either use a different user (please remember it for later usage) or create one using the “adduser” Linux command.

Please run these steps on every database server node:

• Initialize the database storage

# mkdir -p /usr/local/pgsql/data

# chmod -R u+rw /usr/local/pgsql/data

# chown -R postgres /usr/local/pgsql/data

# su postgres

$ initdb /usr/local/pgsql/data 

• Start postgres database

pg_ctl start -D /usr/local/pgsql/data -m fast

• create database: <postgresBinDir>/createdb <database name> For example,

createdb movies

• log on to database: <postgresBinDir>/psql <database name>. For example,

psql movies

• create a super user to manipulate with this database: for example,

create user postgres with superuser password '123456';

• Quit the PostgreSQL CLI: type "\q" at the prompt

movies=# \q

How to run the product

PostgreSQL working model

Introduction

This product provides high availability for postgreSQL database which has been installed on your machine. Please see README for more information. The following diagram shows the basic architecture:

In this diagram, there are 4 “well-known” virtual IP addresses defined by the administrator: 192.168.128.130, 192.168.128.114, 192.168.128.115 and 192.168.128.116 for a system that contains 3 redundancy database servers: PyNodeI0, PyNodeI1 and PyNodeI2. In this case, 192.168.128.130 will move so that it is ALWAYS the IP address of the active (read/write) Postgres database server, and the others provide access to the standby servers. The database client application can access the database via these IP addresses. Specifically, if the application wants to write to the database, it uses the active IP. If it wants to read from the database, it uses either the active or the standby IP addresses.

When SAFplus running on all three nodes, there are three IP addresses assigned: 192.168.128.130 (active), 192.168.128.115 and 192.168.128.116 (standby replicas). IP 192.168.128.114 of the first machine is not assigned because its machine is active, so the active IP 192.168.128.130 is used instead (we do not assign both addresses to ensure that lower priority "read"-only applications do not consume CPU time on the active database).

In the failure case, a standby will take over to become a new active. At this time, its old IP address 192.168.128.115 is released and the IP 192.168.128.130 is assigned to new active. Clients lose their active database connections, but are able to reconnect promptly.

This architecture allows an unsophisticated database client to simply access the database to read and write via 192.168.128.130. Whenever the database connection is dropped, clients should attempt to reconnect in a loop. But if read access optimization is needed, a slightly more sophisticated client can attempt to connect to the “standby” addresses (114,115,116) first, and only connect to the active if no standbys exist.

When the first machine recovers or is replaced, it becomes a standby and the virtual IP 192.168.128.114 is assigned.

Step-by-step to run the model

The model defined 5 working images: SCNodeI0, SCNodeI1 (the system controllers); PyNodeI0, PyNodeI1 & PyNodeI2 (payloads). You should have at least 3 nodes: 1 SCNodeIx, and 2 PyNodeIx to show most of the functionality of the product.

Users can add more system controllers ( in case of multi-system controllers support) and more database servers if needed. In this tutorial model, these images have to be deployed on separate machines but in your own model deploy these images in one machine.

First we need to prepare the machines on which the database (PyNodeX) will be deployed:

1. Choose your virtualIP, netmask and network interface devices which will be used in the application. They include 1 active virtualIP and 3 standby virtualIPs. These will need to be entered into the CSI at key ActiveServerIP (active virtualIP), ActiveServerNetmask (active netmask) and ActiveServerDev (active network interface device). The same for standbys: StandbyServerIP_PyNodeIx, StandbyServerNetmask_PyNodeIx, StandbyServerDev_PyNodeIx.
   This tutorial uses IP addresses in the 192.168.56.xxx subnet. You may be able to use this subnet for your testing if it does not conflict with an existing subnet on your network.
2. To exchange the initial database, each database server machine has to rsync from another without password for a normal user on it, so please setup trusted ssh between the database machines. In this tutorial example, we have database server machines PyNodeI0, PyNodeI1, and PyNodeI2. We need to setup trusted ssh so that the normal user running the PostgreSQL database on ANY of these machines can ssh into any of the other 2 database server machines without a password. For more information about how to accomplish this, please refer to the following article [[1]].
   
   In this tutorial we can take one shortcut. Since all the database server nodes are interchangeable we can give them all the exact same private key. If your deployed system has dedicated database server nodes, you are easily make same way. However if your nodes also have other roles, you may want them to have different keys for security purposes. In this example, we have database server nodes with IP addresses .200, .201, and .202 and the normal users on 3 machines are 'hung'. Note that if the 3 users on 3 three nodes are different, you can apply the method for each user on each node respectively.

First, create the private key on .200:

$ cd ~/.ssh
$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/hung/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /home/hung/.ssh/id_rsa.
Your public key has been saved in /home/hung/.ssh/id_rsa.pub.
The key fingerprint is:
<>
The key's randomart image is:
+--[ RSA 2048]----+
|                 |
+-----------------+

Next, copy it to the other nodes:

$ scp id_rsa hung@192.168.1.202:/home/hung/.ssh/id_rsa
id_rsa                                                                                        100% 1675     1.6KB/s   00:00    
$ scp id_rsa hung@192.168.1.201:/home/hung/.ssh/id_rsa
id_rsa                                                                                        100% 1675     1.6KB/s   00:00    

Next, copy the public portion to all the nodes:

$ ssh-copy-id -i id_rsa.pub hung@192.168.1.202
$ ssh-copy-id -i id_rsa.pub hung@192.168.1.201

Finally test to make sure it worked (you should check all 6 possibilities):

$ ssh hung@192.168.1.201
Linux ubuntu10043vm 2.6.32-38-generic #83-Ubuntu SMP Wed Jan 4 11:12:07 UTC 2012 x86_64 GNU/Linux
Ubuntu 10.04.3 LTS

Welcome to Ubuntu!

$ ssh hung@192.168.1.202
Linux ubuntu10043vm 2.6.32-38-generic #83-Ubuntu SMP Wed Jan 4 11:12:07 UTC 2012 x86_64 GNU/Linux
Ubuntu 10.04.3 LTS

Welcome to Ubuntu!

$ ssh hung@192.168.1.200
...
# ssh hung@192.168.1.201
(and so on)

3. Run the configure script. This script is in place when you untar the tarball:
   

   ./configure --with-wal-log-dir=<full path to directory storing wal log>
   --with-recovery-template-dir=<full path to directory storing recovery.conf template file>
   --with-trigger-file-dir=<full path directory in which the trigger file will be created later on failover>
   --with-postgres-data-dir=<full path to postgres database cluster directory>
   --with-postgres-bin-dir=<full path to postgres database bin directory that contains postgres commands>
   --with-database-name=<Name of database that we're working on in the postgres server>
   --with-username=<normal user who controls the postgres database server>
   --with-safplus-dir=<full path to the safplus directory that the safplus image is deployed on this machine> --help

   • --with-wal-log-dir: specify the full path to directory to store the wal log. If the directory doesn't exist, you have 2 options: y or n; if press y, the directory will be created (recommended); otherwise, there is no directory to store the wal log, this causes an error for postgreSQL
   • --with-recovery-template-dir: specify full path to directory storing recovery.conf template file. If the directory doesn't exist, you have 2 options: y or n; if press y, the directory will be created (recommended); otherwise, there is no directory to store the recovery.conf template, this causes an error for standby postgreSQL server
   • --with-trigger-file-dir: specify full path directory in which the trigger file will be created later on failover. If the directory doesn't exist, you have 2 options: y or n; if press y, the directory will be created (recommended); otherwise, there is no directory to store the trigger file later, this causes an error for postgreSQL
   • --with-postgres-data-dir: specify full path to postgres database cluster directory you work on
   • --with-postgres-bin-dir: specify full path to postgres database bin directory that contains postgres commands
   • --with-database-name: Specify Name of database that we're working on in the postgres server
   • --with-username: specify an normal user for whom you have set up the trusted ssh on this node controlling the postgreSQL such as: start DB, stop DB, replicate DB... Note that this username must be an available normal user in this node and must be different from root
   • --with-safplus-dir: specify full path to the safplus directory where the safplus image is deployed on this machine
   • --help: for help on this command

Remarks:

1. Parameters --with-username, --with-database-name are mandatory, the others are optional and can be omitted. If they’re omitted, the script will use the default values:

   If --with-safplus-dir is specified, wal log dir, trigger file dir and recovery template dir will be created in safplus directoy

   If --with-safplus-dir is omitted, wal log dir, trigger file dir and recovery template dir will be created in the current directory

   --with-postgres-data-dir has default value /usr/local/pgsql/data

   --with-postgres-bin-dir has default value /usr/local/pgsql/bin

   --with-safplus-dir has default value . (current directory)

2. The script helps you to update safplus xml configuration files, it uses some additional default values not existed in the paramters list. They are:
   • postgresPort: use default port 5432
   • postgresUsername: use “postgres” as the default username of postgreSQL. Note that “postgres” username must exist in your postgreSQL server on the machines

Typical use of the script: By following steps below, please skip the section Work with model.

1. Go to postgreSQL model directory
2. Build the model
3. Make images
4. Create SafplusPostgres directory to deploy the images on separate machines
5. Copy images to SafplusPostgres directory at step 4
6. Change to root
7. untar & unzip the images (the payloads)
8. run

   ./configure --with-username=<an existing username on this machine> --with-database-name=<postgres database name>

   
   Or you can add more parameters if you want.
9. The script uses all default parameters mentioned above to configure the product.
10. Open etc/clAmfDefinitions.xml to enter some values which the script hasn’t done for you, they are ActiveServerIP, ActiveServerNetmask, ActiveServerDev, StandbyServerIP_PyNodeI0, StandbyServerIP_PyNodeI1, StandbyServerIP_PyNodeI2, StandbyServerNetmask_PyNodeI0, StandbyServerNetmask_PyNodeI1, StandbyServerNetmask_PyNodeI2, StandbyServerDev_PyNodeI0, StandbyServerDev_PyNodeI1, StandbyServerDev_PyNodeI2. If the values of PostgresStandbyServerPort, StandbyServerPostgresDataDir, StandbyServerPostgresBinDir, PostgresUsername don’t match with your machines, please correct them.
11. Open etc/asp.conf to change images configurations: node_addr, link_name... if necessary

The safplus can be started now.

Work with model

1. Import the model into the OpenClovis IDE
2. Open the postgresql component model
3. Open PostgresqlCSI definition. Correct the values that has been setup at II.1.Preparation steps for each key. You have to input those values below (you need to do for PyNodeI0, PyNodeI1 & PyNodeI2):
   • ActiveServerIP: the active virtual IP. All write (or read also) SQLs must be through it
   • ActiveServerNetmask: subnet mask for the active server
   • ActiveServerDev: the ethernet device interface for the active server
   • StandbyServerIP_PyNodeI0: the virtual IP for standby server on which the first image (PyNodeI0) is deployed. All read only SQLs can be through it. Change PyNodeI0,PyNodeI1,PyNodeI2
   • StandbyServerServerDev_PyNodeI0: the ethernet device interface for the first standby server
   • StandbyServerServerNetmask_PyNodeI0: the subnet mask for the first standby server
   • StandbyServerUsername_PyNodeI0: the user control the postgreSQL operations on first standby server. Notice that the username here must be the user specified at running configure script (see II.2.3) with option --with-username. For example, if --with-username=postgres, then the value for this key is postgres
   • PostgresStandbyServerPort_PyNodeI0: the port number the postgreSQL installed on the first standby server listens (default: 5432)
   • StandbyServerUsername_PyNodeI0: the username of the postgreSQL installed on the first standby server. This user should be a super user, it has at least read, write, replicate privilege on that database
   • StandbyServerPostgresDataDir_PyNodeI0: the full path to the postgreSQLdatabase cluster installed on the first standby server
   • StandbyServerPostgresBinDir_PyNodeI0: the full path to the postgreSQLexecutable files (commands) installed on the first standby server
   • StandbyServerPostgresTriggerFile_PyNodeI0: specify trigger file name (include full path and user-defined filename) on the first standby server. Notice that the path here must be the path specified at running configure script (see II.2.3) with option --with-trigger-file-dir. For example, if --with-trigger-file-dir=/home/postgres/trigger and user-defined trigger file name is pgsql.trigger.5432, then the value for this key is /home/postgres/trigger/pgsql.trigger.5432. This trigger file will be removed automatically after failover
   • PostgresRecoveryTemplateConfFile_PyNodeI0: specify recovery file name (include full path and recovery.conf) on the first standby server. Notice that the path here must be the path specified at running configure script (see II.2.3) with option --with-recovery-template-dir. For example, if --with-recovery-template-dir=/home/postgres/recoverytemplate, then the value for this key is /home/postgres/recoverytemplate/recovery.conf.
   • PostgresDatabaseName: the name of the postgreSQL database. Both active postgreSQL and the standbys use one
4. After correcting all values, regenerate source by Generate Source command from menu Project:
   • Do backup source code
   • Do merge source code
     When generating source code compleles, copy all files from backup/app to src/app
5. Add or remove images as your decision
6. Build the model
7. Make images. You can change the network interface for each image as your system
8. Deploy system controllers (SCNodeI0 & SCNodeI1) and PyNodeIx on separate machines
9. Start safplus under root for system controllers first then start PyNodeIx. The application must be run under root because only root can open port and set/clear the virtualIP address

PostgreSQL tutorial model

Introduction

This is the model which shows the user how to use the postgreSQL high availability. Refer to picture at the first page of this document for a good understand how the system works.

Step by step to run the tutorial model

1. To make the tutorial work, the PostgreSQL working model (section A) have to be configured and work first
2. Open the TutorialCSI. Enter some paramters as in the previous section
3. Generate source code
4. Edit the source code. All source code is put in clCompAppMain.c of Tutorial package. The current source code interacts with a sample database. Because we don’t know what table in your database is, so you have to edit the source code to be suitable for your database. What you have to do is in 2 functions:
   • cleanDb(): this function demonstrates a deletion of all records in a table. This deletion is only permitted on master (active) postgres server. You must change the table name in SQL DELETE statement:
     res = PQexec(conn,"DELETE FROM weather");
   • writeDb(): this function demonstrates an insertion of some records into a table. This is only permitted on master (active) postgres server. You must change the table name in SQL INSERT statement:
     res = PQexecParams(conn,"INSERT INTO weather values($1,$2,$3)",
     as well as parameters: names, types... (refer to the sample code to see how to do that)
5. Build and make images
6. Deploy PyTutorialI0 and PyTutorialI1 on separate machines and start safplus on them. You’ll see the machine which contains the active CSI deletes records from a table and insert some new records to it. And the machine which contains the standby CSI continues reading records from the table on the standbys in turn

Appendix

Changing the model’s configuration permanently

Open src/config/clAmfDefinitions.xml and correct the values:

    <csiType name="PostgresqlCSI">
        <rank>0</rank>
        <nameValueLists>
          <nameValue name="ActiveServerIP" value="192.168.56.120"/>
          <nameValue name="ActiveServerNetmask" value="255.255.255.0"/>
          <nameValue name="ActiveServerDev" value="eth0:1"/>
          <nameValue name="StandbyServerIP_PyNodeI0" value="192.168.56.121"/>
          <nameValue name="StandbyServerNetmask_PyNodeI0" value="255.255.255.0"/>
          <nameValue name="StandbyServerDev_PyNodeI0" value="eth0:1"/>
          <nameValue name="PostgresStandbyServerPort_PyNodeI0" value="5430"/>
          <nameValue name="StandbyServerIP_PyNodeI1" value="192.168.56.122"/>
          <nameValue name="StandbyServerNetmask_PyNodeI1" value="255.255.255.0"/>
          <nameValue name="StandbyServerDev_PyNodeI1" value="eth0:1"/>
          <nameValue name="PostgresStandbyServerPort_PyNodeI1" value="5432"/>
          <nameValue name="StandbyServerIP_PyNodeI2" value="192.168.56.123"/>
          <nameValue name="StandbyServerNetmask_PyNodeI2" value="255.255.255.0"/>
          <nameValue name="StandbyServerDev_PyNodeI2" value="eth0:1"/>
          <nameValue name="PostgresStandbyServerPort_PyNodeI2" value="5432"/>
          <nameValue name="PostgresDatabaseName" value="mydb"/>
          <nameValue name="StandbyServerUsername_PyNodeI0" value="pgres"/>
          <nameValue name="StandbyServerUsername_PyNodeI1" value="tae"/>
          <nameValue name="StandbyServerUsername_PyNodeI2" value="tae"/>
          <nameValue name="StandbyServerPostgresDataDir_PyNodeI0" value="/usr/local/pgsql/data"/>

Edit src/target.conf if you want to change the image configuration such as slot number, LINK (network interface)...

      TRAP_IP=127.0.0.1
      INSTALL_PREREQUISITES=YES
      INSTANTIATE_IMAGES=YES
      CREATE_TARBALLS=YES
      TIPC_NETID=1340
      SLOT_SCNodeI0=1
      SLOT_PyTutorialI0=6
      SLOT_PyTutorialI1=7
      SLOT_SCNodeI1=2
      SLOT_PyTESTI0=8
      SLOT_PyNodeI0=3
      SLOT_PyNodeI1=4
      SLOT_PyNodeI2=5
      LINK_SCNodeI0=eth1
      LINK_PyTutorialI0=eth1
      LINK_PyTutorialI1=eth1
      LINK_SCNodeI1=eth1
      LINK_PyTESTI0=eth1
      LINK_PyNodeI0=eth1
      LINK_PyNodeI1=eth1
      LINK_PyNodeI2=eth1
      ARCH=x86_64/linux-2.6.32