If you're used to your database vendor supplying a full tool chain with the database itself, from server management to application development, PostgreSQL may be a shock to you. Like many successful open-source projects, PostgreSQL tries to stay focused on the features it's uniquely good at. This is what the development community refers to as the PostgreSQL core: the main database server, and associated utilities that can only be developed as a part of the database itself. When new features are proposed, if it's possible for them to be built and distributed "out of core", this is the preferred way to do things. This approach keeps the database core as streamlined as possible, as well as allowing those external projects to release their own updates without needing to synchronize them against the main database's release schedule.
Successful PostgreSQL deployments should recognize that a number of additional tools, each with their own specialized purpose, will need to be integrated with the database core server to build a complete system.
One part of the PostgreSQL core that you may not necessarily have installed is what's called the contrib modules (it is named after the contrib directory they are stored in). These are optional utilities shipped with the standard package, but that aren't necessarily installed by default on your system. The contrib code is maintained and distributed as part of the PostgreSQL core, but not required for the server to operate.
From a code quality perspective, the contrib modules aren't held to quite as high of a standard primarily by how they're tested. The main server includes heavy regression tests for every feature, run across a large build farm of systems that look for errors. The optional contrib modules don't get that same level of testing coverage. However, the code itself is maintained by the same development team, and some of the modules are extremely popular and well tested by users.
A list of all the contrib modules available is at http://www.postgresql.org/docs/current/static/contrib.html.
One good way to check if you have contrib modules installed is to see if the pgbench program is available. That's one of the few contrib components that installs a full program, rather than just the scripts you can use. Here's a UNIX example of checking for pgbench:
$ pgbench -V pgbench (PostgreSQL) 9.0
If you're using an RPM or DEB packaged version of PostgreSQL, as the case would be on many Linux systems, the optional postgresql-contrib package contains all of the contrib modules and their associated installer scripts. You may have to add that package using yum, apt-get, or a similar mechanism if it wasn't installed already. On Solaris, the package is named SUNWpostgr-contrib.
If you're not sure where your system PostgreSQL contrib modules are installed, you can use a filesystem utility to search. locate works well for this purpose on many UNIX-like systems, as does the find command. The file search utilities, available on the Windows Start menu, will work. A sample file you could look for is pg_buffercache.sql, which will be used in the upcoming chapter on memory allocation. Here's where that might be on some of the platforms that PostgreSQL supports:
- RHEL and CentOS Linux systems will put the main file you need into
/usr/share/pgsql/contrib/pg_buffercache.sql - Debian or Ubuntu Linux systems will install the file at
/usr/share/postgresql/version/contrib/pg_buffercache.sql - Solaris installs it into
/usr/share/pgsql/contrib/pg_buffercache.sql - The standard Windows one-click installer with the default options will always include the contrib modules, and this one will be in
C:\Program Files\PostgreSQL/version/share/contrib/pg_buffercache.sql
Building your own PostgreSQL from source code can be a straightforward exercise on some platforms, if you have the appropriate requirements already installed on the server. Details are documented at http://www.postgresql.org/docs/current/static/install-procedure.html.
After building the main server code, you'll also need to compile contrib modules like pg_buffercache by yourself too. Here's an example of how that would work, presuming that your PostgreSQL destination is /usr/local/postgresql and that there's a directory there named source you put the source code into (this is not intended to be a typical or recommended structure you should use):
$ cd /usr/local/postgresql/source $ cd contrib/pg_buffercache/ $ make $ make install /bin/mkdir -p '/usr/local/postgresql/lib/postgresql' /bin/mkdir -p '/usr/local/postgresql/share/postgresql/contrib' /bin/sh ../../config/install-sh -c -m 755 pg_buffercache.so '/usr/local/postgresql/lib/postgresql/pg_buffercache.so' /bin/sh ../../config/install-sh -c -m 644 ./uninstall_pg_buffercache.sql '/usr/local/postgresql/share/postgresql/contrib' /bin/sh ../../config/install-sh -c -m 644 pg_buffercache.sql '/usr/local/postgresql/share/postgresql/contrib'
It's also possible to build and install all the contrib modules at once by running make/make install from the contrib directory. Note that some of these have more extensive source code build requirements. The uuid-ossp module is an example of a more challenging one to compile yourself.
While some contrib programs like pgbench are directly executable, most are utilities that you install into a database in order to add extra features to it.
As an example, to install the pg_buffercache module into a database named abc, the following command line would work (assuming the RedHat location of the file):
$ psql -d abc -f /usr/share/postgresql/contrib/pg_buffercache.sql
You could instead use the pgAdmin III GUI management utility, which is bundled with the Windows installer for PostgreSQL, instead of the command line:
- Navigate to the database you want to install the module into.
- Click on the SQL icon in the toolbar to bring up the command editor.
- Choose File/Open. Navigate to
C:\Program Files\PostgreSQL/version/share/contrib/pg_buffercache.sqland open that file. - Execute using either the green arrow or Query/Execute.
You can do a quick test of the module installed on any type of system by running the following quick query:
SELECT * FROM pg_buffercache;
If any results come back, the module was installed. Note that pg_buffercache will only be installable and usable by database superusers.
The official home of many PostgreSQL-related projects is pgFoundry: http://pgfoundry.org/.
pgFoundry only hosts software for PostgreSQL, and it provides resources like mailing lists and bug tracking in addition to file distribution. Many of the most popular PostgreSQL add-on programs are hosted there:
- Windows software allowing access to PostgreSQL through .Net and OLE
- Connection poolers like pgpool and pgBouncer
- Database management utilities like pgFouine, SkyTools, and pgtune
While sometimes maintained by the same people who work on the PostgreSQL core, pgFoundry code varies significantly in quality. One way to help spot the healthier projects is to note how regularly and recently new versions have been released.
Beyond what comes with the PostgreSQL core, the contrib modules, and software available on pgFoundry, there are plenty of other programs that will make PostgreSQL easier and more powerful. These are available at sources all over the Internet. There are actually so many available that choosing the right package for a requirement can itself be overwhelming.
Some of the best programs will be highlighted throughout the book, to help provide a short list of the ones you should consider early. This approach, where you get a basic system running and then add additional components as needed, is the standard way large open-source projects are built.
It can be difficult for some corporate cultures to adapt to that style such as the ones where any software installation requires everything from approval to a QA cycle. In order to improve the odds of your PostgreSQL installation being successful in such environments, it's important to start early on introducing this concept. Additional programs to add components building on the intentionally slim database core will be needed later, and not all of what's needed will be obvious at the beginning.