Installing

Installing PostgreSQL on Gentoo is super easy. Before installing check the USE flags, readline, ssl and zlib are very desireable. The kerberos pg-hier, pg-intdatetime, perl, python, xml2 and tcltk flags might be needed as well depending on the environment 

root@dbsvr # emerge -pv postgres
# make sure you like those packages....
root@dbsvr # emerge postgres
# Wait a bit...
# Once that is done you must create the data location and such, default set in /etc/conf.d/postgresql
~ # emerge --config =dev-db/postgresql-server-9.1.2-r2

Configuring

Edit /var/postgres/data/postgresql.conf to configure the database server. 

# Change this if you have multiple IPs
listen_addresses = '192.168.42.4'
# Default is 60, but it should happen faster than that.
authentication_timeout = 10

log_destination = 'syslog'  # like this better than PGs own log
syslog_facility = 'LOCAL2'  # Should only be postgres
syslog_ident = 'postgres'

default_with_oids = false # don't like these on my user tables

Common Errors

The first error has to do with an IDENT error, the message is similar to: 

  FATAL 1: IDENT authentication failed for user "username"...

The solution to this one is to edit pg_hba.conf (which is in your data directory), and replace "ident" auth with "md5", "trust", or the auth type of your choice. Then send the postmaster a SIGHUP, or just restart. This happens cause PostgreSQL is looking to authorise you via identd, which is most likely not running creating this error.

Backup and Restore

Archival of the PostgreSQL database is done with the command pg_dump, pg_dumpall and pg_dumplo. The backup methodology used by Edoceo is to archive each database in the system seperately at different times according to their specific needs. In a large enterprise it is likely that a single PostgreSQL server will contain multiple databases of varying sizes, some may contain LOs, or BLOBs. The procedures below describe how to create a backup of the database, including LOs and show how to restore to an empty environment.

Firstly archive the database using pg_dump. The first example dumps the database into the plain format (SQL), without the LOs. The output here is plain SQL, and can be manipulated via text editor before restoration, useful when developing. The second example dumps to the custom format and includes the LOs. This format is a binary compressed format, suitable for offsite storage and restoration from disaster recovery. Both examples expect that the database will not exist on restoration. 

pg_dump --column-inserts --create --format=p -U postgres [database] > /mnt/backup/[database].sql

pg_dump --blobs --format=c -U postgres [database] > /mnt/backup/[database].pgd

To restore from either of these archives point them to pg_restore. The first example is for the plain text format, the second is for the binary format. 

pg_restore --dbname=template1 --exit-on-error /mnt/backup/[database].sql

pg_restore --create --dbname=template1 --exit-on-error /mnt/backup/[database].pgd

Either of these commands can be limited to a single table by using the --table option. The --schema-only and --data-only options to pg_dump may also be useful, see the man pages for more information.

Upgrading

When upgrading some versions of PostgreSQL the data are not compatible between old and new versions. This was exampled by the upgraded from 9.1 to 9.2. In this case the current data must be dumped, the upgrade performed and the data imported. The script below provides the steps. 

emerge --config =dev-db/postgresql-server-9.2.4

pg_upgrade91 \
    --check \
    --verbose \
    --old-bindir=/usr/lib64/postgresql-9.0/bin/ \
    --new-bindir=/usr/lib64/postgresql-9.1/bin/ \
    --old-datadir=/var/lib/postgresql/9.0/data/ \
    --new-datadir=/var/lib/postgresql/9.1/data/

pg_upgrade92 \
    --check \
    --verbose \
    --old-bindir=/usr/lib64/postgresql-9.1/bin/ \
    --new-bindir=/usr/lib64/postgresql-9.2/bin/ \
    --old-datadir=/var/lib/postgresql/9.1/data/ \
    --new-datadir=/var/lib/postgresql/9.2/data/

The command pgbackup is a script written by Edoceo to dump the database with large objects to the custom file type, more information on the pgbackup page. This is suitable for use with pg_restore, each database must be restored seperately.

See Also

ChangeLog