PostgreSQL

PostgreSQL is a free and open source relational database. It supports such things as transactions, schemata and foreign keys, and is often touted to more strictly adhere to the SQL standards than any other database, commercial or otherwise.

Gentoo has an official article on:

PostgreSQL

[edit] Preface

The new Ebuilds — the old Ebuilds being dev-db/postgresql-libs, dev-db/postgresql-client, dev-db/libpq and dev-db/postgresql — in Portage feature slotting matching the major version. This allows you to have two major versions of PostgreSQL operating simultaneously; 8.3 and 8.4 can serve 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.

The greatest benefit reaped from this slotting method being that bug and security fixes, which are delivered via minor version updates, can be applied without fear of corrupting data; 8.4.1 can be updated to 8.4.2 as they're guaranteed to be compatible, in the same slot and require no more interaction from you than to emerge it — no configuration necessary.

Read PostgreSQL's Versioning Policy for more information.

[edit] Installation

[edit] USE Flags

• doc: Include the documentation. This documentation is the same as you'd read online.
• -kerberos: Utilize Kerberos authentication.
• -ldap: Support for LDAP authentication and connection parameter lookup.
• nls: Enable the ability to display messages in a language other than English. Used in conjunction with the Portage variable LINGUAS.
• pam: Pluggable Authentication Module support.
• perl: Enable support for using Perl to write functions and trigger procedures.
• pg-intdatetime: Use the newer method for formatting time stamps. Unless you had a previous installation that utilized the deprecated method, leave this enabled.
• -pg_legacytimestamp: Use the older method for formatting time stamps. Unless you had a previous installation that utilized the deprecated method, leave this disabled.
• python: Enable support for using Python to write functions and trigger procedures.
• readline: You really want this enabled. Disabling removes command line editing and history in psql.
• -selinux: Install respective SELinux policy. This can only be enabled by using the SELinux profile.
• ssl: Enable support for SSL connections.
• tcl: Enable support for using Tcl to write functions and trigger procedures.
• threads: Make the client libraries thread-safe. The rest of your system must be thread-safe as well.
• uuid: Include support to generate a 128 bit random unique identifier. This is useful for merging databases together as the chances of collision are extremely low.
• xml: Enable SQL/XML support.
• zlib: Support for compressed archives in pg_dump and pg_restore.

[edit] Start Emerging

[ebuild  N    ] dev-db/postgresql-docs-8.4.3  0 kB
[ebuild  N    ] dev-db/postgresql-base-8.4.3
    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.3
    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 are ancient. If you're currently using a database via one of those packages, dump the database then unmerge them.

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

[edit] Before Finalizing the Installation

Once the packages have finished emerging, you must edit /etc/conf.d/postgresql-*.*, where *.* is the major version. There are two lines that effect the base installation of the server and cannot be changed later without deleting the directory that contains the database and reinstalling.

PGDATA defines where to place the database and related files. PG_INITDB_OPTS must contain at the very least --locale=locale. PG_INITDB_OPTS may contain any extra options you'd 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 directory should be located to /var/lib/postgresql/8.4/data. 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.
# At the very least this should contain a valid locale.
# Run 'locale -a' to see which locales are available on your system.
# More information at:
#     http://www.postgresql.org/docs/8.4/static/locale.html
#     http://www.postgresql.org/docs/8.4/static/multibyte.html
# Uncomment the following line to set locale.
PG_INITDB_OPTS="--locale=en_US.UTF-8"

You can specify different locales to be used in the same database cluster. If you decide to exclude --locale=, then all six of the other options must be set.

• --lc-collate=locale: String sort order
• --lc-ctype=locale: Character classification (What is a letter? Its upper-case equivalent?)
• --lc-messages=locale - Language of messages
• --lc-monetary=locale - Formatting of currency amounts
• --lc-numeric=locale - Formatting of numbers
• --lc-time=locale - Formatting of dates and times

So, if you'd like the default to be English, but you want messages in, let's say, Swedish, then you're 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 encoding 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 may change your encoding selections after you've installed PostgreSQL with the exception of two: LC_COLLATE and LC_CTYPE. To change the encoding used on those, you must start the installation over again.

[edit] Optional: Mask Packages

In order to keep your installation on the lighter side, you may want to ensure that only minor updates are allowed. An upgrade from 8.4.1 to 8.4.2 is guaranteed to be 100% compatible with no data corruption. However, an upgrade from 8.4.1 to 8.5 might not be. Minor updates fix bugs and security issues, so you'll definitely want them as soon as they're available.

An example:

...
>=dev-db/postgresql-docs-8.5
>=dev-db/postgresql-base-8.5
>=dev-db/postgresql-server-8.5

As has been stated before, slotting will prevent the incompatible updates from happening in the first place. This is only a method to keep one major version installed on your machine.

[edit] Finalizing the Installation

To finish the installation:

[edit] Configuration

By default, PostgreSQL will only read configuration files within its own data directory, which in turn is within it's own slot. The data directory is located where specified in PGDATA.

So, from the examples above, you'll find all the configuration files in /var/lib/postgresql/8.4/.

[edit] postgresql.conf

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, covered in the next subsection. The official documentation is fairly easy to understand and is exhaustive on all the settings available. It would behoove you to read that rather than it be covered here as some things may change, and this author wouldn't be able to clarify it any further.

[edit] pg_hba.conf

This file states who is or is not allowed to connect to the database and in which way a user may log in to the database.

Again, the documentation is quite exhaustive on the settings and what they all mean. There seems to be some misunderstanding, though, as to how host names are mapped to IP addresses.

# 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 is mapped to localhost. When PostgreSQL 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 IPv6 formatted address as well, e.g. ::ffff:127.0.0.1. If, however, the IPv6 address appears first, then PostgreSQL will map to ::1 alone, and ignore all IPv4 formatted requests; ::1 is not the same as ::ffff:127.0.0.1. So, it is better to specify IP addresses alone in this file rather than to rely on /etc/hosts to be ordered properly and it removes any doubt as to which IP addresses are allowed.

[edit] The postgres Group

In order for your users to make local connections via the Unix socket to the server, your users must be in the postgres group. Use usermod -a -G postgres user to add user to the postgres group. Users not in the postgres group will be rejected with: Permission denied.

[edit] Wrapping Up

The only thing left to do is to start PostgreSQL, where *.* is the major version:

You can verify if PostgreSQL has started by tail'ing the log file, like so:

And you should see output like this:

LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

And, optionally, add it to start at boot:

[edit] Utilities

[edit] pgAdmin III

pgAdmin III is a graphical utility for managing PostgreSQL.

[edit] Troubleshooting

[edit] Server Lacks Intrumentation Functions

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:

Obviously, change posgresql-8.3 accordingly to the version you actually have installed.

Note that with 9.1, this is no longer supported. Read this for further information.