PostgreSQL Database Integration

Revision as of 07:08, 4 June 2012 by Hung (Talk | contribs)



Contents

Contents

  1. Preface
    1. Audience
    2. Introduction
    3. Naming Conventions
    4. Documentation Feedback
  2. Prerequisites to install
    1. Install required softwares
    2. Create a database
  3. How to run the product
    1. PostgreSQL working model
      1. Introduction
      2. Step-by-step to run the model
    2. PostgreSQL tutorial model
      1. Introduction
      2. Step-by-step to run the model

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.

Prerequisites to install

Install required softwares

  • 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
  • Install expect tool (on ubuntu: sudo apt-get install expect)
  • rsync tool must be in the user execution's PATH

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)

  1. 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 2. 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
Retrieved from "https://help.openclovis.com/index.php?title=PostgreSQL_Database_Integration&oldid=23858"