Chapter 21. PostgreSQL

21.1. PostgreSQL Architecture

From a power- user standpoint, PostgreSQL is like any other database. The following terms mean essentially the same in PostgreSQL as they do in any other relational database:

Database

Table

Index

Row

Attribute

Extent

Partition

Transaction

21.1.1. Clusters

A PostgreSQL cluster is analogous to an instance in other RDBMSs, and each cluster works with one or more databases. It is not typical to have more than one cluster per database server, but it is possible and is done in some cases. For example, consider a shared hosting environment with different sets of users that should not be given administrative access to each others' data. Since user information is global within a cluster, you would need to create multiple instances to have multiple groups of administrators administering separate databases without giving them the ability to administer another group 's database. The pg_ctl command is used to start the cluster.

21.1.2. Tablespace

On one hand, a tablespace in PostgreSQL is the same as any other tablespace, as defined in Chapter 15; it is the space into which you put tables and other objects. As in other database systems, a PostgreSQL tablespace also consists of multiple pagefiles or datafiles . It's how those pagefiles are created that makes PostgreSQL different.

When you create a tablespace in PostgreSQL, you do not specify a list of one or more datafiles; you specify the name of the directory in which you want to store the tablespace. For example, the default tablespace is created within the directory where you install the PostgreSQL cluster. Additional tablespaces might reside on an LVM volume or an external RAID volume for performance reasons. When you create the tablespace, PostgreSQL starts creating the first datafile/pagefile. As you create tables in the tablespace and fill them up with data, that pagefile starts getting filled up. Once the pagefile reaches 1 GB in size , PostgreSQL starts creating another pagefile. This continues until the volume where the tablespace resides runs out of space. Additional tablespaces can be created at any time as space is needed, and tables can be moved between tablespaces with a simple alter table command.

Running out of space in a tablespace is a very bad thing: the backend of PostgreSQL crashes, and any open transactions are lost.

21.1.3. Pagefile/Datafile

As mentioned in the tablespace section, PostgreSQL pagefiles/datafiles are similar to datafiles in other databases with one exception. They are not created by the DBA and then assigned to a tablespace. They are created automatically by PostgreSQL as the tablespace needs more room for data.

21.1.4. Startup Scripts

PostgreSQL is started using scripts that are typically custom to the environment. PostgreSQL does not have a global configuration file such as Oracle's oratab to keep track of all instances on a machine. Instances on a given machine can be discovered using a process list. On a Unix/Linux machine, ps ef would show something like the following:

Need a process list from a PostgreSQL machine

21.1.5. System Tables

The system tables, which are stored in the default tablespace, can be queried for a list of all databases within the cluster, tablespaces within a database, and so on. This information can then be used for backup purposes. The following command shows a list of databases:

postgres=#

select datname from pg_database order by datname;

datname
------------------
calendar
postgres
systemvdm2
template0
template1
 (5 rows)

You can also query the system tables for a list of tablespaces with this command:

postgres=#

select spcname from pg_tablespace order by spcname;

spcname
------------
pg_default
pg_global
(2 rows)

21.1.6. Large Objects

PostgreSQL has special datatypes for storing large objects such as image files and large text data. They include ByteA, BLOB and Text. Depending on the configuration, LOB data is stored in special files in the filesystem. If you're running PostgreSQL 8.1 or higher, such data is always backed up when using the pg_dump or pg_dumpall commands. Previous versions require you to specify the b option to the pg_dump to include this data in a backup.

21.1.7. Rollback Process

PostgreSQL handles the rollback process differently from other RDBMSs. Consider a complex transaction that consists of a single begin transaction statement, followed by hundreds of transactional commands and a single commit statement. Many RDBMS products would flush the changes to disk as they happen, even if the transaction isn't complete yet. The thought is that 99.9 percent of all transactions are committed anyway, so they may as well be flushed to disk now. The challenge is what happens if someone pulls the plug on the database: we have an incomplete transaction that is partially written to disk.

This is what the rollback log is for in other RDBMSs. Before changing a block on disk, the before image of that block is stored in the rollback log. If the transaction is committed, its before images are deleted from the rollback log. That means that any blocks in the rollback log are used during crash recovery to return the database to a consistent state.

PostgreSQL handles this differently. Only committed transactions are actually written to disk; uncommitted transactions make changes only to data that is in memory. Another big difference is that changes to tuples in PostgreSQL do not overwrite the previous version of the tuple. PostgreSQL writes a new tuple and marks the old one as inactive. If a transaction completes, its changes are flushed to disk at the next checkpoint, and the inactive tuple is marked for deletion by a vacuum process that happens later. If a transaction doesn't complete, it is rolled back by making the new tuple inactive and making the previous version of the tuple active again. This is why PostgreSQL does not need a separate rollback log.

This vacuum process happens on a regular basis and deletes from the table all tuples marked for deletion. The vacuum process is extremely important and must be performed. If you ever hit over 4 billion transactions without vacuuming, your database stops working, forcing you to vacuum before you can continue.

If you don't occasionally run the vacuum process, your database eventually stops functioning, and you lose any transactions in flight when that happens. Don't let this happen to you. The good news is that automated vacuuming has been built into PostgreSQL 8.1. It is disabled by default, however.

$

archive_command = 'cp -i %p /mnt/server/archivedir/%f'

$

archive_command = 'test ! -f .../%f && cp %p .../%f'

$

archive_command = 'mycp.sh %p /mnt/server/archivedir/%f'