PostgreSQL Database Integration

Revision as of 16:28, 5 June 2012 by Stone (Talk | contribs)



Contents

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, operation, management and configuration of SAFplus and PostgreSQL database. You must also be familiar with C programming, PostgreSQL database development and administration, UML notations, and have the basic knowledge of Linux.

Introduction

The PostgreSQL Database Integration 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.

Additionally, your applications can make read-only queries to the standby postgres databases via well-known IP addresses. This allows read accesses 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), these are the system controllers. Ones starting with 'PyNode'(PyNodeI0, PyNodeI1, PyNodeI2) are the worker nodes. Ones 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.
WHICH ONES CONTAIN THE 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 be on separate nodes -- they could easily be combined on a single node for small installations.

Prerequisites to install

Install Required Software

  • Install SAFplus SDK-6.0 or higher on your development machine (machine on which builds the model, makes images)
  • Install PostgreSQL-9.1 or higher on the deployment machines what nodes exactly? SC, PY, Tutorial?
  • Install expect tool (on ubuntu: sudo apt-get install expect)
  • rsync tool 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 if 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.

  • <postgresBinDir>/initdb <path to location where the database cluster is in> For example,

initdb /usr/local/pgsql/data

  • start postgres database: <postgresBinDir>/pg_ctl start -D <postgresDataDir> -m fast For example,

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';

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 images PyNodeI0, PyNodeI1 and PyNodeI2. In this case, 192.168.128.130 is 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 virtual 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 for the 3 images is up, there are 3 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. In the failure case, a standby will take over by becoming a new active. At this time, its old IP address 192.168.128.115 is released and the IP for active machine 192.168.128.130 is taken.

This architecture allows an unsophisticated database client to simply access the database via 192.168.128.130. 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.

If the first machine recovers, 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 SC, 2 Py to show most functionality of the product.

User can add more system controllers ( in case of multi-system controllers support) and more payloads if needed. These images have to be deployed on separate machines respectively. We need to prepare some stuffs on the machine on which the payload deployed prior to getting the product worked:

  1. Define by yourself what are the virtualIP, netmask and network interface devices which will be used in the application. They include 1 active virtualIP and 3 standby virtualIPs. Those 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 an user on it, so please setup trusted ssh between the database machines. For example, we have 3 machines A, B and C . We need to setup trust ssh in order that an user from A can ssh to B and C without password; an user from B can ssh to A and C without password; an user from C can ssh A and B without password. Suppose that machine A has an user account a; machine B has an user account b; machine C has an user account c. We need to setup trust ssh in order that:
    from A, issue command ssh b@<IP of B> and c@<IP of C>
    from B, issue command ssh a@<IP of A> and c@<IP of C>
    from C, issue command ssh a@<IP of A> and b@<IP of B>
    without password required.
    If PyNodeI0 is deployed on A, PyNodeI1 on B and PyNodeI2 on C, then the CSI value of key StandbyServerUsername_PyNodeI0 must be a, StandbyServerUsername_PyNodeI1 must be b and StandbyServerUsername_PyNodeI2 must be c. Those users will be the ones who control the postgreSQL operations ( please refer to configure command below to see how to configure the application on each machine)
  3. Copy configure script to each machine and run it:
    ./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=<username 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 user controlling the postgreSQL such as: start DB, stop DB, replicate DB... Note that this username must be an available normal user in the machine 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, the section Work with model can be passed over.

  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 iv
  6. Change to root
  7. untar & unzip the images (the payloads)
  8. Copy this script to SafplusPostgres
  9. 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.
  10. The script uses all default parameters mentioned above to configure the product.
  11. 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.
  12. 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 I don’t know what table in your database is, so you have to edit the source code to be suitable with 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
Retrieved from "https://help.openclovis.com/index.php?title=PostgreSQL_Database_Integration&oldid=23885"