This section covers the procedure to create and configure PostgreSQL. See the steps below:

Refer to the SE Suite - System Requirements document for more details on PostgreSQL considerations.

To execute the following procedure, make sure that the postgres service is stopped; otherwise, stop it:

pg_ctl stop –D <postgresql_installation_directory>/data

1.Edit the pg_hba.conf configuration file:

# vi <postgresql_installation_directory>/data/pg_hba.conf

2.Locate the IPv4 local connections configuration block and add a new line with the data of the network server where the SE Suite is being installed:

# IPv4 local connections:

host all all 127.0.0.1/32 md5

host all all 192.168.200.55 255.255.255.0 md5

3.Save and close the configuration file:

:wq!

4.Edit the postgresql.conf configuration file:

# vi <postgresql_installation_directory>/data/postgresql.conf

5.Locate the listen_addresses parameter, uncomment the line and change its value as shown below:

listen_addresses = '*' # what IP address(es) to listen on;

6.Save and close the configuration file:

:wq!

Creating folders for the Tablespaces

7.Two folders (softexpert_data and softexpert_indexes) must be created with full permission for the postgres user. Create the following directories for the tablespaces:

# mkdir <postgresql_installation_directory>/data/tbs

# mkdir <postgresql_installation_directory>/data/tbs/softexpert_data

# mkdir <postgresql_installation_directory>/data/tbs/softexpert_indexes

8.Define the permissions:

# chown postgres.postgres -R <postgresql_installation_directory>/data/tbs

User and Permissions

ATTENTION

PostgreSQL is Case Sensitive.

9.Start PSQL with the postgres user created during PostgreSQL installation:

# psql -U postgres -h localhost -d postgres

10.Create a user for SE Suite:

CREATE ROLE <user> LOGIN ENCRYPTED PASSWORD '<password>' SUPERUSER VALID UNTIL 'infinity';

Substitute <user> and <password> with the user's name and password respectively.

Tablespaces

11.Create a tablespace called SOFTEXPERT_DATA:

CREATE TABLESPACE "SOFTEXPERT_DATA" OWNER <user> LOCATION '<postgresql_installation_directory>/data/tbs/softexpert_data';

12.Create a tablespace called SOFTEXPERT_INDEXES:

CREATE TABLESPACE "SOFTEXPERT_INDEXES" OWNER <user> LOCATION '<postgresql_installation_directory>/data/tbs/softexpert_indexes';

Database

13.Create the SE Suite database:

CREATE DATABASE <database_name> WITH ENCODING='UTF8' OWNER=<user> tablespace="SOFTEXPERT_DATA";

Encoding UTF8 supports all languages.

Starting and Stopping the PostgreSQL Service

14.To start the PostgreSQL service:

# su - postgres

# pg_ctl start –D <postgresql_installation_directory>/data

# logout

15.To stop the PostgreSQL service:

# su - postgres

# pg_ctl stop –D <postgresql_installation_directory>/data

# logout

You may create a script to start and stop the PostgreSQL service. See the necessary steps to create a startup script for PostgreSQL.

Creating a startup script

See below the steps to create a startup script. If you wish to use a script, follow the example below, by changing the directories according to the data from your environment and installation.

16.Access the directory where the system services are stored:

# cd /etc/init.d

17.Create a file called postgresql and open it for editing:

# vim postgresql

18.Paste the content below in the file, editing the data according to your environment:

#!/bin/sh

# description: PostgreSQL Server

# chkconfig: 2345 80 30

# processname: postgresql

case "$1" in

"start")

echo "Starting PostgreSQL Server"

sudo -u postgres <postgresql_installation_directory>/bin/pg_ctl start -D <postgresql_installation_directory>/data

;;

"stop")

echo "Stopping PostgreSQL Server"

sudo -u postgres <postgresql_installation_directory>/bin/pg_ctl stop -D <postgresql_installation_directory>/data

;;

"restart")

/etc/init.d/postgresql stop

/etc/init.d/postgresql start

;;

echo "Usage: $0 {start|stop|restart}"

;;

esac

19.Save and close the file:

:wq!

20.After installing the service, define the execution permission for the PostgreSQL startup script:

# chmod +x /etc/init.d/postgresql

21.Configure the script to start automatically with the Operating System:

▪For distributions based on CentOS/RedHat, execute:

# chkconfig --add postgresql

# chkconfig --level 235 postgresql on

▪For distributions based on Debian, execute:

# update-rc.d postgresql defaults