PostgreSQL Database Integration - Revision history

Stone: /* Deployment Machine Prerequisites */

2012-09-06T20:10:00Z

‎Deployment Machine Prerequisites

Stone: /* Deployment Machine Prerequisites */

2012-09-06T19:54:51Z

‎Deployment Machine Prerequisites

Stone: /* Building the Software */

2012-09-05T20:57:56Z

‎Building the Software

Stone: /* Naming Conventions */

2012-09-05T20:18:32Z

‎Naming Conventions

Stone: /* Introduction */

2012-09-05T19:00:01Z

‎Introduction

Stone: /* Create a database */

2012-08-30T19:41:08Z

‎Create a database

Hung: /* Create a database */

2012-08-16T08:11:46Z

‎Create a database

Hung: /* Step-by-step to run the model */

2012-08-16T08:10:07Z

‎Step-by-step to run the model

Stone: /* Step-by-step to run the model */

2012-08-14T20:09:26Z

‎Step-by-step to run the model

Stone: /* Step-by-step to run the model */

2012-08-14T20:05:30Z

‎Step-by-step to run the model

@@ Line 138: / Line 138: @@
 pg_ctl -D /home/pguser/pgsql/data stop
 </pre></code>
 === Copy the Model Images to your Cluster ===

@@ Line 80: / Line 80: @@
 consisting of SCNodeI0.tgz, SCNodeI1.tgz, PyNodeI0.tgz, PyNodeI1.tgz, PyNodeI2.tgz, and TutorialI0.tgz.  We will copy these image files to nodes in our test cluster, but first let us set up those nodes.
-=== Deployment Machine Prerequisites ===
+== Deployment Machine Prerequisites ==
 * Install PostgreSQL-9.1 (or higher but must match with PostgreSQL version used in development machine) on all machines in the cluster'''''(PyNodeI0, PyNodeI1, PyNodeI2), Tutorial nodes (PyTutorialI0, PyTutorialI1) and PyTEST (PyTESTI0)'''''
 <code><pre>tar xvfj postgresql-9.1.3.tar.bz2; cd postgresql*; ./configure; make; make install

@@ Line 60: / Line 60: @@
 <code><pre>tar xvfj postgresql-9.1.3.tar.bz2; cd postgresql*; ./configure; make; make install
 </pre></code>
-* Download and detar the OpenClovis PostgreSQL Database Integration model (this software)
+* Download and detar the OpenClovis PostgreSQL Database Integration model (this software).  It should create a directory named SAFplus-DB in your system.
-−
-=== Configure IP Addresses, etc ===
-−
@@ Line 81: / Line 78: @@
 <base dir>/SAFplus-DB/target/postgreSQL/images/
-consisting of SCNodeI0.tgz, SCNodeI1.tgz, PyNodeI0.tgz, PyNodeI1.tgz, and PyNodeI2.tgz
+consisting of SCNodeI0.tgz, SCNodeI1.tgz, PyNodeI0.tgz, PyNodeI1.tgz, PyNodeI2.tgz, and TutorialI0.tgz.  We will copy these image files to nodes in our test cluster, but first let us set up those nodes.
-Please copy these to your testing machines. Note, you can access most of the example functionality by just deploying 3 images: SCNodeI0.tgz, PyNodeI0.tgz, and PyNodeI1.tgz
+=== Deployment Machine Prerequisites ===
-Also, note that you can use your development machine as a testing machine.
+* Install PostgreSQL-9.1 (or higher but must match with PostgreSQL version used in development machine) on all machines in the cluster'''''(PyNodeI0, PyNodeI1, PyNodeI2), Tutorial nodes (PyTutorialI0, PyTutorialI1) and PyTEST (PyTESTI0)'''''
+<code><pre>tar xvfj postgresql-9.1.3.tar.bz2; cd postgresql*; ./configure; make; make install
 <code><pre>
-root@ubuntu:~# mkdir db
-root@ubuntu:~# cd db
-root@ubuntu:~/db# tar xvfz /code/git/SAFplus-DB/target/postgreSQL/images/SCNodeI0.tgz
-root@ubuntu:~/db# scp /code/git/SAFplus-DB/target/postgreSQL/images/PyNodeI0.tgz root@192.168.1.201:~
-root@192.168.1.201's password:
-PyNodeI0.tgz                                                                                                                              100%   41MB  40.7MB/s   00:01
-root@ubuntu:~/db# scp /code/git/SAFplus-DB/target/postgreSQL/images/PyNodeI1.tgz root@192.168.1.202:~
-root@192.168.1.202's password:
-PyNodeI1.tgz
 </pre></code>
+* Install the "expect" tool on the Postgres server machines '''''(ScNodeI0, ScNodeI1, and PyNodeI0, PyNodeI1, PyNodeI2 if you are using them)'''''
-(I detarred the Py images in a "db" folder on the other two nodes as well)
-−
-−
-−
-=== Deployment Machine Prerequisites ===
-* 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)'''''
 <code><pre>sudo apt-get install expect
 </pre></code>
-* rsync tool on the deployment machines '''''(PyNodeI0, PyNodeI1, PyNodeI2)''''' must be in the user execution's PATH
+* Ensure that the rsync tool is on the Postgres server machines '''''(ScNodeI0, ScNodeI1, and PyNodeI0, PyNodeI1, PyNodeI2 if you are using them)''''' and in the user execution's PATH
 <code><pre>
 $ which rsync || echo “rsync is NOT accessible”
@@ Line 114: / Line 94: @@
 === 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 both to run the postgres software and to administer the database.
+If there are Postgres databases available (you have already created them), there is nothing to do.  But you must know the super user and password on that database or create a new user both to run the postgres software and to administer the database.
-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.
+In this tutorial, we will use a fresh database.  How to create a Postgres database is beyond the scope of this tutorial. However the following “recipe” can be used (creating the database exactly as per this recipe will mean less customization of the tutorial 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.  However, as a new user, it is recommended that you use the "postgres" username so you do not have to modify configuration files to match your username.
 Please run these steps on every database server node:
@@ Line 132: / Line 112: @@
 $ initdb /home/postgres/pgsql/data
 </pre></code>
 <code><pre>
-pg_ctl start -D /home/postgres/pgsql/data -m fast
+$ pg_ctl start -D /home/postgres/pgsql/data -m fast
 </pre></code>
-* create database: '''''<postgresBinDir>/createdb <database name>''''' For example,
+<br/>
-<code><pre>createdb pgdb</pre></code>
+<div style="background-color:#a0a0a0">
+'''Optional:'''
-* Add a user to the database.  Note, it is recommended to use the same username for both your Linux and database user.  If so, you don't need to do this step.
+{| style="background-color:#c0c0c0"
-<code><pre>psql pgdb
+| A default admin user has been added to the "pgdb" database that is the same username as your Linux username -- that is "postgres".  So you do not need to add another user.  But if you DID want separate Linux/Postgres usernames you would now create the user in the database:
-pgdb=# create user postgres with superuser password '123456';
+| <pre>
 pgdb=# \q
-</pre></code>
+</pre>
-* Stop the postgres server (SAFplus will run it automatically)
+|}
 <code><pre>
 pg_ctl -D /home/pguser/pgsql/data stop
 </pre></code>
 == How to run the product ==

@@ Line 32: / Line 32: @@
 === Naming Conventions ===
-The SAFplus model has defined some system (or node) images. Some image names start with 'SC' (SCNodeI0, SCNodeI1), they are the
+The SAFplus model has defined some images (tarballs) to be deployed on separate nodes in your cluster. These images are as follows:
-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.<br/>
+* SCNodeI0, SCNodeI1.  These are the system controllers (SC).  By default, they are also are configured to run an Active/Standby Postgres server service group.  You must run at least one SC in your cluster.
-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:
+# '''root''' is the super user on a machine (node). SAFplus is generally run as "root" (although this is not required).
-# 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
+# '''postgres''' The PostgreSQL database can't be run as root so a normal Linux user is generally created for this purpose.
-# 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)
+# '''postgres''' The PostgreSQL database ''itself'' contains users for access control to database tables.  For simplicity, we use the same username for admin access inside the database as we use for the Linux user that controls the postgres database process.
-# 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':
-<code><pre>
-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
-</pre></code>
-Now, new PostgreSQL user 'hung' can connect to the database:
-<code><pre>
-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=#
+{{note | Some simplifications have been used for the purposes of this tutorial.  These simplifications are generally not limitations in the actual implementation.  In this case, it is actually not required to have the same Linux and database usernames, or even to have the same usernames on each node.}}
-−
-</pre></code>
 == Building the Software ==

@@ Line 5: / Line 5: @@
 === 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.
+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 database 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.
+This product is intended to be integrated into your SAFplus model. However a standalone model is provided for evaluation and testing purposes.
 === PostgreSQL working model ===

@@ Line 142: / Line 142: @@
 # adduser postgres
-# mkdir -p /usr/local/pgsql/data
+# su - postgres
-# chmod -R u+rw /usr/local/pgsql/data
+$ mkdir -p /home/postgres/pgsql/data
-# chown -R postgres /usr/local/pgsql/data
+$ chmod -R 0700 /home/postgres/pgsql/data
-# su postgres
+$ initdb /home/postgres/pgsql/data
-−
-$ initdb /usr/local/pgsql/data
 </pre></code>
 <code><pre>
-pg_ctl start -D /usr/local/pgsql/data -m fast
+pg_ctl start -D /home/postgres/pgsql/data -m fast
 </pre></code>
 * create database: '''''<postgresBinDir>/createdb <database name>''''' For example,
 <code><pre>createdb pgdb</pre></code>
-* log on to database:   <postgresBinDir>/psql <database name>. For example,
-<code><pre>psql pgdb</pre></code>
+* Add a user to the database.  Note, it is recommended to use the same username for both your Linux and database user.  If so, you don't need to do this step.
-* create a super user to manipulate with this database: for example,
+<code><pre>psql pgdb
-<code><pre>create user postgres with superuser password '123456';</pre></code>
+pgdb=# create user postgres with superuser password '123456';
-* Quit the PostgreSQL CLI: type "\q" at the prompt
-<code><pre>
 pgdb=# \q
 </pre></code>

@@ Line 274: / Line 274: @@
 For example:
-  ./configure --with-username=pguser --with-database-name=movies
+  ./configure --with-username=pguser --with-database-name=pgdb
 <br/>Or you can add more parameters if you want.

@@ Line 271: / Line 271: @@
 # Change to root
 # untar & unzip the images (the payloads)
-# run <code><pre>./configure --with-username=<an existing username on this machine> --with-database-name=<postgres database name></pre></code><br/>Or you can add more parameters if you want.
+# run ./configure --with-username=<an existing username on this machine> --with-database-name=<postgres database name>
 # The script uses all default parameters mentioned above to configure the product.
 # 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.

@@ Line 207: / Line 207: @@
 </pre></code>
-Next, copy the public portion to all the nodes:
+Next, copy the public portion to all the nodes (including myself):
 <code><pre>
 $ ssh-copy-id -i id_rsa.pub pguser@192.168.1.202
 $ ssh-copy-id -i id_rsa.pub pguser@192.168.1.201
 </pre></code>