PostgreSQL is a free and open source relational database management system (RDBMS). It supports such things as transactions, schemas and foreign keys, and is often touted to more strictly adhere to the SQL standards and to be more secure, by default, than any other database, commercial or otherwise.

This article will guide you through the Gentoo specific steps to install the PostgreSQL RDBMS.

The Ebuilds covered by this article are dev-db/postgresql-docs, dev-db/postgresql-base and dev-db/postgresql-server.

This article assumes that you will be installing the latest, stable version of PostgreSQL; at the time of writing, the version was 8.4.4. Adjust the commands in this article as necessary for your specific version.

The PostgreSQL ebuilds in Portage feature slotting based on the major version. This allows you to have two major versions of PostgreSQL operating simultaneously. For instance, 8.3 and 8.4 can be installed at the same time. This is useful in such circumstances where you need to move data from an older database to a new database, or need to have a production and a testing database on the same machine. Also, this prevents a database, corresponding libraries or executables from being overwritten by an incompatible update.

Additionally, bug and security fixes, which are delivered via minor version updates, can be applied without fear of corrupting data; 8.4.2 can be updated to 8.4.4 as they are guaranteed to be compatible and require no more interaction from you than to emerge it and restart the server process: no migration, reconfiguration or initialization necessary.

Read the PostgreSQL Versioning Policy for more information.

There is quite a bit that will not be covered. The official documentation is somewhere in the neighborhood of 2,000 pages, so a lot of details will be left out in this quick start guide. Only Gentoo specific issues will be covered and some basic configuration guidelines.

If you have any of the following ebuilds installed, then you have an older, obsolete Gentoo installation of PostgreSQL: dev-db/postgresql-libs, dev-db/postgresql-client, dev-db/libpq and/or dev-db/postgresql.

This article does cover migrating from an old database to a new one, including the steps needed for migrating from the old ebuilds to the new ones.

# emerge -av dev-db/postgresql-server

[ebuild N ] dev-db/postgresql-docs-8.4.4 0 kB
[ebuild N ]dev-db/postgresql-base-8.4.4-r2 USE="doc nls pam readline ssl zlib
  -kerberos -ldap -pg_legacytimestamp -threads" LINGUAS="-af -cs -de -es -fa -fr
  -hr -hu -it -ko -nb -pl -pt_BR -ro -ru -sk -sl -sv -tr -zh_CN -zh_TW" 0 kB
[ebuild N ] dev-db/postgresql-server-8.4.4-r1 USE="doc nls perl python
  -pg_legacytimestamp (-selinux) -tcl -uuid -xml" LINGUAS="-af -cs -de -es -fa -fr
  -hr -hu -it -ko -nb -pl -pt_BR -ro -ru -sk -sl -sv -tr -zh_CN -zh_TW" 0 kB

You may receive a notice regarding that any of the above packages are blocked by any or all of the following packages: dev-db/postgresql-libs, dev-db/postgresql-client, dev-db/libpq or dev-db/postgresql. These packages are not maintained and obsoleted. Refer to the section on migration for how to handle this situation.

Once the packages have finished emerging, you may want to edit /etc/conf.d/postgresql-8.4. There are two lines that affect the defaults of the server and cannot be changed later without deleting the directory that contains the database cluster and reinitializing.

PGDATA defines where to place the database cluster and related files. PG_INITDB_OPTS may contain any extra options you would care to set. The extra options are not required as the reasonable defaults are, ahem, reasonable.

In the following example, PGDATA states that the database cluster should be installed to /var/lib/postgresql/8.4/data, which is the default. If you decide to stray from the default, bear in mind that it is a very good idea to keep the major version in the path. PG_INITDB_OPTS states that the default locale should be en_US.UTF-8. That is, U.S. English ordering and formatting, and UTF-8 character encoding.

# PostgreSQL's Database Directory
PGDATA="/var/lib/postgresql/8.4/data"

# Options to pass to PostgreSQL's initdb.
PG_INITDB_OPTS="--locale=en_US.UTF-8"

This only determines the default locale and character encoding. You can specify different locales and/or character encodings at the database or table level in the same database cluster.

There are six locale options that can be set to override --locale=. The following table lists the six options that, if used, are to be formatted as: --option=lo_LO.ENCODING.

So, if you would like the default to be English, but you want messages in, say, Swedish, then your PG_INITDB_OPTS would look like so:

PG_INITDB_OPTS="--locale=en_US.UTF-8 --lc-messages=sv_SE.UTF-8"

A complete list of language and character encodings supported by the server can be found in the documentation, but your system must also support the respective languages and character encodings. Compare the output of locale -a to the encodings in the documentation.

You can change your locale and encoding selections at database creation time (CREATE DATABASE). Also, you can change the locale used within a database after it has been created with the exception of LC_COLLATE and LC_CTYPE. To change the locale for these two, you must drop the database and start over again. The same applies for the encoding; you must drop the database to change the encoding.

To finish the installation:

# emerge --config =dev-db/postgresql-server-8.4.4-r1

This will create the database cluster and store all the related server files into PGDATA.

This time the focus is upon the files in the PGDATA directory /var/lib/postgresql/8.4/data/ instead with primary focus on the postgresql.conf and pg_hba.conf files.

This is the main configuration file. The line that you may find of immediate interest is listen_addresses. This variable defines to which addresses PostgreSQL will bind. By default, only loopback devices and Unix sockets are bound; localhost and /var/run/postgresql/.s.PGSQL.5432. Changing listen_addresses is not enough, though, to enable remote connections. There is another file that actually controls the connections, which is covered in the next subsection. The official documentation is fairly easy to understand and is exhaustive on all the settings available. We recommend you to read the online official documentation on this.

Of secondary interest is the logging destination. By default, everything is logged to postmaster.log in the PGDATA directory. There is an entire subsection of postgresql.conf that covers a slew of options for how and where to log. The section is marked: ERROR REPORTING AND LOGGING.

Other than listen_addresses and the logging options, the rest of the defaults in postgresql.conf are reasonable enough to get you going.

The pg_hba.conf file states who is allowed to and in which way they may connect to the database. Again, the documentation is quite exhaustive on the settings and what they all mean, but a few things are covered here for clarification.

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust

As has been mentioned before, by default the server is secure. Kind of. There is only one database role that is available for log in by default: postgres. And, the only way to initiate a connection to the database is through the /var/run/postgresql/.s.PGSQL.5432 Unix socket, which is owned by the postgres system user and system group, or via localhost. Now for the "kind of" bit: Any user on the system can make a connection to the database through the localhost. Even as the postgres database superuser.

To make a connection through the Unix socket, however, the users - including the users for other services such as apache - must be in the postgres system group. Use gpasswd -auser postgres to add user to the postgres group. Users not in the postgres group will be rejected with "Permission denied".

The trust method is what allows any user to log on as any user without a password. It specifies just what it implies: Trust all connections for the given type to the given database from the given database user (but not the system user) from the given location without a password. This is what allows any user on the system to log on as any user through the localhost connection from the get go. This is not as dangerous as it seems, but does pose a serious security risk in most circumstances.

The two methods you will most likely use are: password and md5. The password method only specifies that a password is required to start the connection and the password is sent "in-the-clear". This method is fine when such information will never leave the machine, such as connecting via the Unix socket or localhost. The md5 method is like password, but protects the password by using an md5 hash. This is what you want to use whenever the password is going to traverse a network.

At this point, we would like to bring your attention to the last two lines (or four lines including comments) of the configuration file. PostgreSQL has native support for IPv6 regardless of your desires for such support. Additionally, IPv4 addresses are automatically mapped to IPv6 addresses, so 127.0.0.1 will be mapped to ::FFFF:127.0.0.1 and as "pure" IPv6 ::FFFF:7F00:0001.

There seems to be some misunderstanding, though, as to how host names are mapped to IP addresses. Let us take a look at the /etc/hosts file.

# IPv4 and IPv6 localhost aliases
127.0.0.1       localhost
::1             localhost

From the example above you can see that both an IPv4 and an IPv6 IP address are mapped to localhost. When psql refers to this file, it will grab the first match and use that as the address; in this case 127.0.0.1. When PostgreSQL parses this, it will match the IPv6 formatted address as well, e.g. ::ffff:127.0.0.1. If, however, the IPv6 address appears first, then psql will map to ::1 alone; ::1 is not the same as ::ffff:127.0.0.1. As such, if you do not have ::1 as a permitted means of access, psql will not be able to establish a connection. Furthermore, your kernel needs to support the IPv6 protocol.

So, it is better to specify IP addresses alone to psql and in pg_hba.conf rather than to rely on /etc/hosts to be ordered properly, and it removes any doubt as to which IP addresses are allowed or to which server you will connect.

Now start PostgreSQL and set the password for the database superuser postgres. The commands are to be performed as 'root' in the following code listing:

(Change 'trust' to 'password' for the localhost connections.)
# nano -w /var/lib/postgresql/8.4/data/pg_hba.conf
# /etc/init.d/postgresql-8.4 start
postgresql-8.4  | * Starting PostgreSQL ...                               [ ok ]

(Open a connection to the server and set the password.)
# psql -U postgres
psql (8.4.4)
Type "help" for help.

postgres=# \password
Enter new password:
Enter it again:
postgres=# \q

(Change 'trust' to 'password' for the local connection.)
# nano -w /var/lib/postgresql/8.4/data/pg_hba.conf
# /etc/init.d/postgresql-8.4 reload
postgresql-8.4 | * Reloading PostgreSQL configuration ...                 [ ok ]
# rc-update add postgresql-8.4 default
 * service postgresql-8.4 added to runlevel default

You only need to perform a migration when moving from one major version to another, for instance from PostgreSQL 8.3.11 to 8.4.4, but not from 8.4.2 to 8.4.4.

Because the new ebuilds feature a more advanced slotting method than the previous ones, the downtime is quite minimal, most likely minutes rather than hours.

In the following examples, it is assumed that you are using the default locations and port settings, and that you are migrating from 8.3 to 8.4. Adjust accordingly if you have deviated from the default.

If you have not already done so, follow the installation instructions before starting the migration. Such a compile may hamper performance on the database server but it can keep going.

A couple of files need to be tweaked before beginning the migration. Edit PGPORT in the /etc/conf.d/postgresql-8.4 configuration file to 6543 (any port number other than what your old installation is bound to will do.)

Next, edit /var/lib/postgresql/8.3/pg_hba.conf so that only the database superuser postgres can access the database cluster via the Unix socket.

# cd /var/lib/postgresql/
# cp -p 8.3/data/pg_hba.conf 8.4/data/

(The following should be safe. Read the documentation to be sure.)
#  cp -p 8.3/data/postgresql.conf 8.4/data/

(Don't forget to copy over any other configuration files that you may need.)

# /etc/init.d/postgresql-8.3 reload
# /etc/init.d/postgresql-8.4 start

(Begin piping the data from the old cluster to the new cluster.)
# pg_dumpall -U postgres -p 5432 | psql -U postgres -d postgres -p 6543
# /etc/init.d/postgresql-8.3 stop
# /etc/init.d/postgresql-8.4 stop

(Edit PGPORT back to 5432.)
# nano -w /etc/conf.d/postgresql-8.4

(Allow users access once more.)
# nano -w /var/lib/postgresql/8.4/data/pg_hba.conf
# /etc/init.d/postgresql-8.4 start
# rc-update del postgresql-8.3 && rc-update add postgresql-8.4 default

Hopefully everything went according to plan and you have a successfully updated server that contains precisely the same data, bit for bit, as the old server.

You will need to schedule some downtime for your server. The old ebuilds cannot be installed at the same time as the new Ebuilds. As such, assume that the server will have to be down for a few hours. Maybe for the weekend, even.

Before starting, you will need to deny access to the server, so that no changes are made. You may also want to backup your postgresql.conf and pg_hba.conf and any other configuration file that you deem important.

# pg_dumpall -U postgres > backup_file
# /etc/init.d/postgresql stop
# emerge -C dev-db/postgresql dev-db/libpq dev-db/postgresql-client \
  dev-db/postgresql-client

(Follow the steps detailed in this article for installing and configuring the
server.)

# /etc/init.d/postgresql-8.4 start
# psql -f backup_file postgres

You may break some packages that were built against those ancient packages, but once you have installed dev-db/postgresql-base and/or dev-db/postgresql-server you can run revdep-rebuild to reemerge any packages that may have been broken.

pgAdmin III is a graphical utility for managing PostgreSQL.

This problem is easy to solve. What is difficult about it is finding the answer. What is required is an import from a file that already exists on the storage drive: adminpack.sql. To resolve this issue, run this command:

# psql -U postgres --file /usr/share/postgresql-8.4/contrib/adminpack.sql

If you get an error upon emerging dev-db/postgresql-base that reads as follows:

# emerge dev-db/postgresql-base
. . .
configure: error: thread test program failed
This platform is not thread-safe.  Check the file 'config.log' for the
exact reason.

You can use the configure option --enable-thread-safety-force to force
threads to be enabled.  But you must then run the program in
src/test/thread and add locking function calls to your applications to
guarantee thread safety.

The solution, in most cases, is to update sandbox to a version greater than or equal to 2.0.

# echo '>=sys-apps/sandbox-2.0' >> /etc/portage/package.keywords
# emerge -av '>=sys-apps/sandbox-2.0'
# emerge -av dev-db/postgresql-base