Postgresql Administration

Table of Content

---

---

Installation and Initialization of Postgresql

On a Linux operating system, a Postgresql database can be simply installed as package:

apt-get install postgresql postgresql-8.3

where 8-3 is the Postgresql version.

Depending of the Linux distribution, the Postgresql package will initialize a first database cluster of same name, like template1. Below we will rename the cluster to main and write the data into the directory /data/db/postgresql.

A Postgresql cluster is one internal postgres database to handle and administrate a set of database.

We need a Postgres configuration directory /etc/postgresql/8-3/main where main is the name of your database cluster. In this configuration directory we should have the clustor configuration files:

• pg_hba.conf
• pg_ident.conf
• postgresql.conf

The file pg_hba.conf defines how users can access the databases. Connections can be made locally over a unix socket, or over the a network throw a TCP socket, running by default on the port number 5432.

The file pg_ident.conf is only used when the login user does not match the database user, and we want to connect to a database with method ident * in pg_hba.conf. Normally a user tells, which database user account is used for the connection. This is generally the *md5 connection method.

The file postgresql.conf is the main configuration file, which holds all relevant information needed to starts the postgres cluster instance.

General configuration postgresql.conf

We can take must default configuration values for

Important are the three entries:

data_directory = '/var/lib/postgres/data' 
hba_file = '/etc/postgresql/8.3/main/pg_hba.conf
ident_file = '/etc/postgresql/8.3/main/pg_ident.con

data_directory is the directory of the given database cluster. hba_file is the file which handles the database connection methods to the cluster. pg_ident.conf maps login user to database user accounts.

Next we can intake which IP addresses the postgresql clustor instance will listen to. If the service should listen to all TCP network we put:

listen_addresses = '*'

The postgres service in runging by default on TCP port:

port = 5432 

Authentication Strategies pg_hba.conf

Starting and Stopping the Postgresql Clustor Instance

Multiples Postgresql Clustors

We consider the case of multiple Postgresql clustors instances, running on the same machine. This can be of interest, if we have several database sets, which must be administrate differentelly. We can also run a backup database in a other cluster, for the case, when the main database cluster crashes. With several database service instances, we can adjust the nice performance for each cluster.

For a performance purpose, it is better to run each database instance on it on machine. For the case of several instances on the same machine, it is good to have the cluster data on different disk device or different raid 5 block devices. Each database instances requires a fixes amount of memory space, which requires RAM.

First we must make a directory for data of new posgreSQL cluster:

install -d m 2775 -o postgres -g postgres DIR

where DIR stands for the cluster data directory, belonging to the user postgre and group =postgres. I Next we must define the new configuration directory for the cluster in /etc/postgres/8.3. In the configuration file postgresql.conf we will change:

Next we change the TCP port number for the new cluster to:

port = 5433  

The user must then connect to database in the new cluster over this TCP port 5433.

If we want to keep the default TCP port number 5432 for the database cluster, we must set up a machine with multiple network IP addresss. We then have to set the IP address to listen to:

listen_addresses = 'IP ADDRESS' 

in both configuration files postgresql.conf of the main cluster an of the new cluster.

It is also good the define a new location for the unix socket:

unix_socket_directory = '/var/run/psql-geo'

Before we can start the new database cluster, we must generate the database structure of the cluster, with the command:

We then start the database cluster. Depending on the boot script the argument restart only restart the main running cluster. Therefore is better to stop the postgreSQL service with argment stop and to start with argument start.

We then check if all work fine. With the command:

ps ax | grep postgresql

we should see the two cluster instances up.

On a Debian Linux, we only see the entries:

.. -c unix_socket_directory=/var/run/postgresql ...
   -c external_pid_file=/var/run/postgresql/8.3-main.pid

for the main (default) database cluster. Still we can see, if the unix socket exist, by checking the socket directory, where we should finde the hidden unix socket file .s.PGSQL.5433.

Finaly we check if we can connected to both database cluster. For new database cluster we must first connect locally to the right unix socket with the command:

psql -h / -p -l

We must indicate the port number 5433 of the new database cluster, so that the correct unix socket file is taken, which as the port number as suffix.

We then can alter the user postgres with password in the new cluster, and the tray connect the new database cluster over TCP with:

psql -h localhost -p 5433 -U postgres -l

Administration of a Database Cluster

Backup and Recovery

-- TWikiAdminUser - 2010-05-19