Introduction to PostgreSQL

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

This package is known to build and work properly using an LFS-7.7 systemd platform.

Package Information

PostgreSQL Dependencies


Python-2.7.10, Tcl-8.6.4, OpenSSL-1.0.2d, libxml2-2.9.2, libxslt-1.1.28, OpenLDAP-2.4.42, Linux-PAM-1.2.1, MIT Kerberos V5-1.13.2 and Bonjour

Optional (To Regenerate Documentation)

docbook-4.5, docbook-dsssl-1.79, OpenJade-1.3.2, and SGMLSpm-1.1

User Notes:

Installation of PostgreSQL

Install PostgreSQL with the following commands:

sed -i '/DEFAULT_PGSOCKET_DIR/s@/tmp@/run/postgresql@' src/include/pg_config_manual.h &&
./configure --prefix=/usr          \
            --enable-thread-safety \
            --docdir=/usr/share/doc/postgresql-9.4.4 &&

There are a number of programs in the contrib/ directory. If you are going to run this installation as a server and wish to build some of them, enter make -C contrib or make -C contrib/<SUBDIR-NAME> for each subdirectory.

Now, as the root user:

make install      &&
make install-docs

If you made any of the contrib/ programs, as the root user:

make -C contrib/<SUBDIR-NAME> install


This package contains a known security hole that allows other users on the same machine to gain access to an operating system account while it is doing "make check": CVE-2014-0067. Tests must be run after install, with postgresql server down, as unprivileged user.

To test the results, issue: make check.

If you only intend to use PostgreSQL as a client to connect to a server on another machine, your installation is complete and you should not run the remaining commands.

Initialize a database cluster with the following commands issued by the root user:

install -v -dm700 /srv/pgsql/data &&
install -v -dm755 /run/postgresql &&
groupadd -g 41 postgres &&
useradd -c "PostgreSQL Server" -g postgres -d /srv/pgsql/data \
        -u 41 postgres &&
chown -Rv postgres:postgres /srv/pgsql /run/postgresql &&
su - postgres -c '/usr/bin/initdb -D /srv/pgsql/data'

As the root user, start the database server with the following command:

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

Still as user root, 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

sed -i ...: This sed changes server socket location from /tmp to /run/postgresql.

--docdir=/usr/share/doc/postgresql-9.4.4: This switch puts the documentation in a versioned directory.

--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.

--with-openssl: build with support for OpenSSL encrypted connections.

--with-perl: build the PL/Perl server-side language.

--with-python: build the PL/Python server-side language.

--with-tcl: build the PL/Tcl server-side language.

groupadd ...; useradd ...: These commands add an unprivileged user and group 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-9.4.4/html/index.html.

Systemd Units

To start the postgresql daemon at boot, install the systemd unit from the blfs-systemd-units-20150210 package by running the following command as the root user:

make install-postgresql


Installed Programs: clusterdb, createdb, createlang, createuser, dropdb, droplang, dropuser, ecpg, initdb, pg_basebackup, pg_config, pg_controldata, pg_ctl, pg_dump, pg_dumpall, pg_isready, pg_receivexlog, pg_recvlogical, pg_resetxlog, pg_restore, pltcl_delmod, pltcl_listmod, pltcl_loadmod, postgres, postmaster (deprecated), psql, reindexdb, vacuumdb, and optionally (in contrib/) oid2name, pg_archivecleanup, pgbench, pg_standby, pg_test_fsync, pg_test_timing, pg_upgrade, pg_xlogdump, vacuumlo, and many others
Installed Libraries: libecpg.{so,a}, libecpg_compat.{so,a}, libpgcommon.a, libpgport.a, libpgtypes.{so,a}, and libpq.{so,a}
Installed Directories: /usr/include/{libpq,postgresql}, /usr/lib/postgresql, /usr/share/doc/postgresql-9.4.4, /usr/share/postgresql, and /srv/pgsql

Short Descriptions


is a utility for reclustering tables in a PostgreSQL database.


creates a new PostgreSQL database.


defines a new PostgreSQL procedural language.


defines a new PostgreSQL user account.


removes a PostgreSQL database.


removes a PostgreSQL procedural language.


removes a PostgreSQL user account.


is the embedded SQL preprocessor.


creates a new database cluster.


resolves OIDs (Object IDs) and file nodes in a PostgreSQL data directory.


clean up PostgreSQL WAL (write-ahead log) archive files.


takes base backups of a running PostgreSQL cluster.


retrieves PostgreSQL version information.


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


controls stopping and starting the database server.


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


recursively calls pg_dump for each database in a cluster.


check the connection status of a PostgreSQL server.


is used to stream transaction log from a running PostgreSQL cluster.


controls PostgreSQL logical decoding streams.


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


creates databases from dump files created by pg_dump.


supports the creation of a PostgreSQL warm standby server.


determine fastest wal_sync method for PostgreSQL.


measure timing overhead.


upgrade a PostgreSQL server instance.


display a human-readable rendering of the write-ahead log of a PostgreSQL database cluster.


run a benchmark test on PostgreSQL.


is a support script used to delete a module from a PL/Tcl table. The command requires the Pgtcl package to be installed also.


is a support script used to list the modules in a PL/Tcl table. The command requires the Pgtcl package to be installed also.


is a support script used to load a module into a PL/Tcl table. The command requires the Pgtcl package to be installed also.


is the PostgreSQL database server.


(deprecated, a symlink to postgres) is a multi-user database daemon.


is a console based database shell.


is a utility for rebuilding indexes in a database.


compacts databases and generates statistics for the query analyzer.


remove orphaned large objects from a PostgreSQL database.


contains functions to support embedded SQL in C programs.


is the ecpg compatibility library.


is the port-specific subsystem of the Postgres backend.


contains functions for dealing with Postgres data types.


is the C programmer's API to Postgres.

Last updated on 2015-08-25 15:46:34 -0500