Installation of PostgreSQL on Ubuntu Hardy is easily accomplished with the 'aptitude' package manager.
This article will work through the installation process and some configuration changes suggested for a typical setup.
PostgreSQL is a mature object-relational database that has been in open source development since 1996. While it hasn't enjoyed the wild popularity of MySQL, Postgres offers many advanced features and has a reputation for stability and scalability that has gained the devotion of many database programmers.
The 'aptitude show' output for the 'postgresql' package notes the following features: "ACID transactions, foreign keys, views, sequences, subqueries, triggers, user-defined types and functions, outer joins, multiversion concurrency control. Graphical user interfaces and bindings for many programming languages are available as well."
The official documentation is well-maintained and certainly worth a look.
Let's install the base package:
sudo aptitude install postgresql
Note that this is a virtual package, and ensures we will always have the newest stable version of PostgreSQL — frequently referred to as "postgres" — during our updates. At the time of writing, aptitude will install version 8.3.6 on an Ubuntu Hardy slice.
Take note of the following as the installation wraps up:
Configuring postgresql.conf to use port 5432... * Starting PostgreSQL 8.3 database server ...done.
Cool, so the postgres process will be listening on port 5432 — by default it listens only on the slice's localhost interface. Also, the installation script configures postgres to automatically restart following a reboot.
If you're curious what packages (and their dependencies) were installed along with the postgres package, you can use the 'aptitude show' command:
aptitude show postgresql-8.3 ... Depends: libc6 (>= 2.4), libcomerr2 (>= 1.33-3), libkrb53 (>= 1.6.dfsg.2), libldap-2.4-2 (>= 2.4.7), libpam0g (>= 0.99.7.1), libpq5 (>= 8.3~beta1), libssl0.9.8 (>= 0.9.8f-1), libxml2 (>= 2.6.27), locales, postgresql-client-8.3, postgresql-common (>= 79), ssl-cert, tzdata
Note we specified the '-8.3' package to get the proper list of dependencies. You can see that the client package gets installed along with the server software — good, we'll be using that before too long.
We can install the 'dev' package too, as that may prove useful in some situations, e.g. if at some point you want to build a PostgreSQL add-on module.
sudo aptitude install postgresql-server-dev-8.3
The configuration files for postgres are located in '/etc/postgresql/8.3/main/' . We'll make two adjustments in 'postgresql.conf'.
sudo nano /etc/postgresql/8.3/main/postgresql.conf
Find and uncomment these lines:
... track_counts = on ... autovacuum = on # Enable autovacuum subprocess? 'on' ...
Those settings provide some automatic maintenance of our databases, which will help to keep them running smoothly.
We need to restart postgres to put the configuration changes into effect:
sudo /etc/init.d/postgresql-8.3 restart
Be sure to note any errors during startup, as those usually indicate typos in a config file, likely to make the database inaccessible.
Installing PostgreSQL via the 'aptitude' package manager is straightforward and ensures you can easily obtain any security updates, if and when they are released.
PLEASE NOTE: a freshly installed PostgreSQL server is not ready to accept local and remote connection from web applications or database admin tools.
The next article in this series will introduce the core concepts for PostgreSQL access control. In a later article, we'll learn how to configure the postgres server for local and remote access.