Using PostgreSQL Database Software with N1 Grid Engine (Sun N1 Grid Engine 6.1 Installation Guide)

Sun N1 Grid Engine 6.1 Installation Guide

Using PostgreSQL Database Software with N1 Grid Engine

Detailed information on the PostgreSQL database software can be found in the Postgres documentation.

How to Start the Database Server

Once the PostgreSQL software is installed, you can start the database server.

Before You Begin

Download, compile, and install the PostgreSQL database software. Create a user account to own the database processes. Usually, this user is postgres. Add the PostgreSQL bin directory and necessary LD_LIBRARY_PATH settings to your environment.

Create a home directory for the postgres user.

In this example, the home directory is /space/postgres/data.

% mkdir -p /space/postgres/data
% useradd -d /space/postgres  postgres 
% chown postgres /space/postgres/data
% su - postgres

Continue as described in the PostgreSQL documentation to set up a database.

> initdb -D /space/postgres/data

creating directory /space/postgres/data... ok
creating directory /space/postgres/data/base... ok
creating directory /space/postgres/data/global... ok
creating directory /space/postgres/data/pg_xlog... ok
creating directory /space/postgres/data/pg_clog... ok
creating template1 database in /space/postgres/data/base/1... ok
creating configuration files... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
initializing pg_depend... ok
creating system views... ok
loading pg_description... ok
creating conversions... ok
setting privileges on built-in objects... ok
vacuuming database template1... ok
copying template1 to template0... ok

Success. You can now start the database server using:
   postmaster -D /space/postgres/data
   or
   pg_ctl -D /space/postgres/data -l logfile start

Make the following changes to the pg_hba.conf file.

This change permits unrestricted and password free access to the database superuser postgres but requires md5 encrypted passwords for all other database users. Replace nnn.nnn.nnn with your subnet address without the trailing 0. You also can add access rules on a per-host basis by adding similar lines with host IP addresses.
```
# TYPE  DATABASE  USER  IP-ADDRESS     IP-MASK         METHOD
local   all       postgres                             trust
local   all       all                                  md5
# IPv4-style local connections:
#host    all       all   nnn.nnn.nnn.0  255.255.255.0  md5
```

Make the following changes to the postgresql.conf file, to enable TCP/IP access from other hosts.

Note –
Ensure that the value of shared_buffers is at least twice the value of max_connections.
```
tcpip_socket = true
max_connections = 40   (increase if necessary)
```

Start the database.

In this example, -i enables TCP/IP communication, and -S is for silent mode.
> postmaster -S -i

Verify the installation.

As the postgres user, try the following commands:

% su - postgres

> createuser -P test_user
Enter password for new user: 
Enter it again: 
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

> createdb -O test_user -E UNICODE test
CREATE DATABASE

Execute commands as the database super user.

 > psql test
Welcome to psql 7.3, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# create table test (x int, y text);
CREATE TABLE
test=# insert into test values (1, 'one');
INSERT 16982 1
test=# insert into test values (2, 'two');
INSERT 16983 1
test=# select * from test;
x |  y
---+------
1 | one
2 | two
(2 rows)

test=# \q

 > psql -U test_user test
Password: 
Welcome to psql 7.4.1, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
test=>

After you have successfully tested your database, you should proceed to the next task, How to Set Up a PostgreSQL Database.

How to Set Up a PostgreSQL Database

Create the owner of the database.

> createuser -P arco_write
Enter password for new user: 
Enter it again: 
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER

Create the database for accounting and reporting.
> createdb -O arco_write arco CREATE DATABASE

Create a database user for reading the database.

If you do not use the arco_read user, you need to modify the privileges script that is used in the next step.

> createuser -P arco_read
Enter password for new user: 
Enter it again: 
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER