Installing and Configuring the PostgreSQL Database

Table Of Contents

Installing and Configuring PostgreSQL

Installing the PostgreSQL Database

Configuring the PostgreSQL Listening Port

Installing and Configuring PostgreSQL

---

Revised: February 28, 2012

•Installing the PostgreSQL Database (Required)

•Configuring the PostgreSQL Listening Port (Optional)

Installing the PostgreSQL Database

Before You Begin

Read the security recommendations for the PostgreSQL database in section About Security Recommendations for the External Database.

Procedure

---

Step 1 Enter these commands to sign in to the database server as a Postgres user:

>su - postgres

>psql

Step 2 Create a new database user. The example below creates a new database user called `tcuser':

#CREATE ROLE tcuser LOGIN CREATEDB;

---

Note If you deploy PostgresSQL version 8.4.x, you need to configure the database user as a superuser at this point in the procedure, for example: #ALTER ROLE tcuser WITH SUPERUSER;

---

Step 3 Create the database.

If your database will contain ASCII characters only, create the database with SQL_ASCII encoding. If your database will contain non-ASCII characters, create the database with UTF8 encoding.

The example below creates an SQL_ASCII database called "tcmadb".

#CREATE DATABASE tcmadb WITH OWNER tcuser ENCODING 'SQL_ASCII';

Step 4 Configure user access to the database. Edit the <install_dir>/data/pg_hba.conf file to allow the Postgres user and the new `tcuser' user to access the database.

For example:

# TYPE	DATABASE	USER	CIDR-ADDRESS	METHOD
host	tcmadb	tcuser	10.89.99.0/24	password
host	dbinst	mauser	10.89.99.0/24	password

Step 5 Enter these commands to define passwords for the Postgres and 'tcuser' users:

#ALTER ROLE postgres WITH PASSWORD `mypassword';

#ALTER ROLE tcuser WITH PASSWORD `mypassword';

---

Note You are required to enter a password for the database user when you configure an external database entry on Cisco Unified Presence.

---

Step 6 If you are running PostgreSQL version 8.3.7 or a later 8.3.x release, change the permission of the `tcuser' to superuser to allow this user access to the database. Enter this command:

#ALTER ROLE tcuser WITH SUPERUSER;

Step 7 Configure the number of connections to the database from remote hosts. Edit the listen_addresses parameter in the <install_dir>/data/postgresql.conf file. For example:

listen_addresses = '*'

Step 8 Stop and restart the PostgreSQL service, for example:

/etc/rc.d/init.d/postgresql-8.3 stop

/etc/rc.d/init.d/postgresql-8.3 start

---

Note The commands to stop and start the PostgreSQL service may vary between PostgreSQL releases.

---

Step 9 Enter these commands to sign in to the new database as the Postgres user and enable PL/pgSQL:

>psql tcmadb -U postgres

#CREATE FUNCTION plpgsql_call_handler () RETURNS LANGUAGE_HANDLER AS '$libdir/plpgsql' 
LANGUAGE C;

#CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler;

---

Troubleshooting Tips

Do not turn on the following configuration items in the <install_dir>/data/postgresql.conf file (by default these items are commented out):

client_min_messages = log 

log_duration = on

Related Topics

•About Security Recommendations for the External Database

•PostgreSQL documentation:

http://www.postgresql.org/docs/manuals/

Configuring the PostgreSQL Listening Port

---

Note This section is optional configuration.

---

By default, the Postgresql database listens on port 5432. If you want to change this port, you must edit the PGPORT environment variable in /etc/rc.d/init.d/postgresql with the new port number.

---

Note The PGPORT environment variable overrides the `Port' parameter value in the /var/lib/pgsql/data/postgresql.conf file, so you must edit the PGPORT environment variable if you want the Postgresql database to listen on a new port number.

---

Procedure

---

Step 1 Edit the PGPORT environment variable in /etc/rc.d/init.d/postgresql with the new port, for example:

IE: PGPORT=5555

Step 2 Enter these commands to stop and start the PostgreSQL service:

# /etc/rc.d/init.d/postgresql start

# /etc/rc.d/init.d/postgresql stop

Step 3 Confirm that the Postgresql database is listening on the new port using this command:

`lsof -i -n -P  | grep postg'  

postmaste  5754    postgres    4u  IPv4 1692351       TCP *:5555 (LISTEN)

Step 4 To connect to the database after you have changed the port, you must specify the new port number in the command using the -p argument. If you do not include the -p argument in the command, the Postgresql database will attempt to use the default port of 5432, and the connection to the database will fail.

For example:

psql tcmadb -p 5555 -U tcuser

---