Introduction to PostgreSQL

PostgreSQL is an advanced object-relational database management system (ORDBMS), derived from the Berkeley Postgres database management system.

Package information

Installation of PostgreSQL

In order for configure to properly discover Docbook SGML DTD (v3.1), you may need to remove OpenSP catalog definitions from the system SGML catalogs. Use the following command before building the package to accomplish this:

sed -i.orig \
    -e "/CATALOG \/etc\/sgml\/" \
    /etc/sgml/catalog \

Install PostgreSQL with the following commands:

patch -Np1 -i ../postgresql-7.4.6-dsssl_fix-1.patch &&
./configure --prefix=/usr --enable-thread-safety &&
make &&
make install &&
chown -R root:root /usr/share/doc/postgresql/html

The standard installation provides only the header files needed for client application development. Server-side applications require the entire PostgreSQL include tree which can be installed using the following command:

make install-all-headers


If you are upgrading an existing system and are going to install the new files over the old ones, then you should back up your data, shut down the old server and follow the instructions in the official PostgreSQL documentation.

Initialize a database cluster with the following commands:

mkdir -p /srv/pgsql/data &&
useradd -d /srv/pgsql/data postgres &&
chown postgres /srv/pgsql/data &&
su - postgres -c '/usr/bin/initdb -D /srv/pgsql/data'

Start the database server with the following command:

su - postgres -c '/usr/bin/postmaster -D /srv/pgsql/data > \
    /srv/pgsql/data/logfile 2>&1 &'

Create a database and verify the installation:

su - postgres -c '/usr/bin/createdb test' &&
echo "create table t1 ( name varchar(20), state_province varchar(20) );" \
    | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Billy', 'NewYork');" \
    | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Evanidus', 'Quebec');" \
    | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Jesse', 'Ontario');" \
    | (su - postgres -c '/usr/bin/psql test ') &&
echo "select * from t1;" | (su - postgres -c '/usr/bin/psql test')

Command explanations

--enable-thread-safety: This switch makes the client libraries thread-safe by allowing concurrent threads in libpq and ECPG programs to safely control their private connection handles.

chown -R root:root /usr/share/doc/postgresql/html: This command corrects the improper ownership of some documentation files.

useradd -d /srv/pgsql/data postgres: Add an unprivileged user to run the database server.

createdb test, create table t1 , insert into t1 values..., select * from t1: Create a database, add a table to it, insert some rows into the table and select them to verify that the installation is working properly.

Configuring PostgreSQL

Config files

$PGDATA/pg_ident.con, $PGDATA/pg_hba.conf and $PGDATA/postgresql.conf

The PGDATA environment variable is used to distinguish database clusters from one another by setting it to the value of the directory which contains the cluster desired. The three configuration files exist in every PGDATA/ directory. Details on the format of the files and the options that can be set in each can be found in file:///usr/share/doc/postgresql/html/index.html.

Install the /etc/rc.d/init.d/postgresql init script included in the blfs-bootscripts-6.0 package.

make install-postgresql


The PostgreSQL package contains clusterdb, createdb, createlang, createuser, dropdb, droplang, dropuser, ecpg, initdb, initlocation, ipcclean, pg_config, pg_controldata, pg_ctl, pg_dump, pg_dumpall, pg_encoding, pg_id, pg_resetxlog, pg_restore, pgtclsh, pgtksh, pltcl_delmod, pltcl_listmod, pltcl_loadmod, postgres, postmaster, psql, vacuumdb, libecpg, libpgtcl, libpgtypes, libpq and various charset modules.



clusterdb is a utility for reclustering tables in a PostgreSQL database.


createdb creates a new PostgreSQL database.


createlang defines a new PostgreSQL procedural language.


createuser defines a new PostgreSQL user account.


dropdb removes a PostgreSQL database.


droplang removes a PostgreSQL procedural language.


dropuser removes a PostgreSQL user account.


ecpg is the embedded SQL preprocessor.


initdb create a new database cluster.


initlocation creates a secondary database storage area.


ipcclean removes shared memory and semaphores left over by an aborted database server.


pg_config retrieves PostgreSQL version information.


pg_controldata returns information initialized during initdb, such as the catalog version and server locale.


pg_ctl controls stopping and starting the database server.


pg_dump dumps database data and metadata into scripts which are used to recreate the database.


pg_dumpall recursively calls pg_dump for each database in a cluster.


pg_resetxlog clears the write-ahead log and optionally resets some fields in the pg_control file.


pg_restore creates databases from dump files created by pg_dump.


pgtclsh is a Tcl shell interface extended with PostgreSQL database access functions.


pgtksh is a Tcl/Tk shell interface extended with PostgreSQL database access functions.


postgres is a single user database server, generally used for debugging.


postmaster is the multi-user database daemon.


psql is a console based database shell.


vacuumdb compacts databases and generates statistics for the query analyzer.

Last updated on 2005-02-12 09:12:38 -0700