AllGoodBits.org

Installation

PostgreSQL is available in binary package form such as rpm or deb for most major linux distributions. If the version provided by your distribution is not new or sparkly enough for you, please build the latest and greatest with the mods and tweaks you desire and then make a package for it.

Basic Configuration

PostgreSQL requires its own user and cluster creation must be done by that user:

$ mkdir -p /var/pgsql/data; chown -R postgres /var/pgsql
$ su - postgres
$ initdb -D /var/pgsql/data

Starting, testing:

$ postgres -D /var/pgsql/data > /var/pgsql/logfile 2>&1 &
$ createdb test
$ psql test

And don't forget you probably want it to start upon reboot as well.

You'll probably want to change a few settings in postgresql.conf:

• shared_buffers
• work_mem
• maintenance_work_mem
• checkpoint_timeout

You can see all settings with something like::

SELECT name,setting,unit from pg_settings;

Consider also:

• putting logs/WAL on a different storage subsystem
• asynchronous commits with synchronous commit=off (not as risky as fsync=off, because you will never get corruption, although you are risking loss)

/usr/local/etc/rc.d/postgresql initdb
/usr/local/etc/rc.d/postgresql start

rpm -Uvh http://yum.postgresql.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm
yum install postgresql91{,-libs,-devel,-server}
service postgresql-9.1 initdb
service  postgresql-9.1 start

/opt/local/etc/LaunchDaemons/org.macports.postgresql84-server/postgresql84-server.wrapper start
sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql84-server.plist

brew install postgresql
initdb /usr/local/var/postgres -E utf8
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

User Administration

CREATE ROLE rolename WITH LOGIN NOSUPERUSER NOCREATEROLE ENCRYPTED PASSWORD 'clear_text_password'
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON tablename TO rolename

Managing Databases

Create a new database, the rolename should already exist (defaults to the current user):

CREATE DATABASE dbname WITH OWNER rolename

Logging

Following the basic configuration above will result in a logfile at /var/pgsql/logfile. If you take out the shell redirection of stderr when starting postgres and set some parameters, you can have postgres take care of your logging and log rotation for you:

redirect_stderr = on (must be set at server startup)
log_filename postgresql_log.%a (default postgresql-%Y-%m-%d_%H%M%S.log)
log_rotation_age = 1440
log_truncate_on_rotation = on

This will keep 7 days of logs, one log file per day named server_log.Mon, server_log.Tue, etc, and automatically overwrite last week's log with this week's log.

Vacuum

From the manual:

"PostgreSQL's VACUUM command must be run on a regular basis for several reasons:

1. To recover or reuse disk space occupied by updated or deleted rows.
2. To update data statistics used by the PostgreSQL query planner.
3. To protect against loss of very old data due to transaction ID wraparound."

and

"There are two variants of the VACUUM command. The first form, known as "lazy vacuum" or just VACUUM, marks expired data in tables and indexes for future reuse; it does not attempt to reclaim the space used by this expired data unless the space is at the end of the table and an exclusive table lock can be easily obtained. Unused space at the start or middle of the file does not result in the file being shortened and space returned to the operating system. This variant of VACUUM can be run concurrently with normal database operations.

The second form is the VACUUM FULL command. This uses a more aggressive algorithm for reclaiming the space consumed by expired row versions. Any space that is freed by VACUUM FULL is immediately returned to the operating system. Unfortunately, this variant of the VACUUM command acquires an exclusive lock on each table while VACUUM FULL is processing it. Therefore, frequently using VACUUM FULL can have an extremely negative effect on the performance of concurrent database queries. "

1. For lazy vacuuming, some edits to postgresql.conf:

   autovacuum = on
   autovacuum_naptime = 1000min
   stats_start_collector = on
   stats_row_level = on
   vacuum_cost_delay = 1000
   

2. For full vacuuming, I run the following from cron, at times when usage is low:

   vacuumdb --all --full --analyze
   

For high usage databases, defined as highly I/O constrained databases, other values for these parameters or indeed other approaches should be considered.

Reindexing

Unless you have very active databases (heavy usage or heavy churn), reindexing is probably not necessary. Consider it a performance optimisation. However, there is one major exception: after a recovery operation (any time you replay the Write Ahead Log) you will need to reindex any hash indexes

Backup and Restore

pg_dump and pg_dumpall are the tools for dumping postgresql databases. If you use --format to specify an archive format, you will be able to selectively restore from the archive, but you will need to use pg_restore to do so. Otherwise the ordinary psql is your restore tool.

pg_dump -s <dbname> [-t tablename1 [-t tablenameN]]

pg_dumpall --clean > dumpfile
psql -f dumpfile postgres

pg_dump --clean *dbname* > dumpfile
psql *dbname* < dumpfile

pg_dump --clean *dbname* | ssh hostname 'psql *dbname*'