Managing Databases

PostgreSQL stores data in a collection of operating system files. At the highest level of organization, you find a cluster. A cluster is a collection of databases (which, in turn, is a collection of schemas).

Creating a New Cluster

You create a new cluster using the initdb program. Note that initdb is an external program, not a command that you would execute in a PostgreSQL client.

When you run initdb, you are creating the data files that define a cluster. The most important command-line argument to initdb is pgdata= cluster-location[4] The pgdata argument tells initdb the name of the directory that should contain the new cluster. For example, if you execute the command

[4] There are actually three ways to specify the cluster location. The following commands are equivalent:

$ initdb pgdata=/usr/newcluster $ initdb -D /usr/newcluster $ export PGDATA=/usr/newcluster ; initdb  

$ initdb pgdata=/usr/newcluster

initdb creates the directory /usr/newcluster and a few files and subdirectories within /usr/newcluster. It's usually a good idea to let initdb create the directory that contains the cluster so that all the file ownerships and permissions are properly defined. In fact, initdb won't create a cluster in a directory that is not empty.

So, let's see the directory structure that we end up with after initdb has completed its work (see Figure 21.8).

Figure 21.8. The data directory layout.

At the top of the directory structure is the cluster directory itselfI'll refer to that as $PGDATA because that is where the $PGDATA environment variable should point.

$PGDATA contains four files and four subdirectories[5]. $PGDATA/pg_hba.conf contains the host-based authentication configuration file. This file tells PostgreSQL how to authenticate clients on a host-by-host basis. We'll look at the pg_hba.conf file in great detail in Chapter 23, "Security." The $PGDATA/pg_ident.conf file is used by the ident authentication scheme to map OS usernames into PostgreSQL user namesagain, I'll describe this file in the chapter dealing with PostgreSQL security. $PGDATA/postgresql.conf contains a list of runtime parameters that control various aspects of the PostgreSQL server. The fourth file, $PGDATA/PG_VERSION, is a simple text file that contains the version number from initdb.

[5] You are looking at a cluster created with PostgreSQL version 8.0. The exact details may differ if you are using a different version.

Now, let's look at each of the subdirectories created by initdb.

The pg_xlog directory contains the write-ahead logs. Write-ahead logs are used to improve database reliability and performance. Whenever you update a row within a table, PostgreSQL will first write the change to the write-ahead log, and at some later time will write the modifications to the actual data pages on disk. The pg_xlog directory usually contains a number of files, but initdb will create only the first one-extra files are added as needed. Each xlog file is 16MB long.

The pg_clog directory contains commit logs. A commit log reflects the state of each transaction (committed, in-progress, or aborted).

The global directory contains three tables that are shared by all databases within a cluster: pg_shadow, pg_group, and pg_database. The pg_shadow table holds user account definitions and is maintained by the CREATE USER, ALTER USER, and DROP USER commands. The pg_group table holds user group definitions and is maintained by the CREATE GROUP, ALTER GROUP, and DROP GROUP commands. pg_database contains a list of all databases within the cluster and is maintained by the CREATE DATABASE and DROP DATABASE commands. The global directory also contains a number of indexes for the pg_shadow, pg_group, and pg_database tables. global contains two other files that are shared by all databases in a cluster: pgstat.stat and pg_control. The pgstat.stat file is used by the statistics monitor (the statistics monitor accumulates performance and usage information for a database cluster). The pg_control file contains a number of cluster parameters, some of which are defined by initdb and will never change. Others are modified each time the postmaster is restarted. You can view the contents of the pg_control file using the pg_controldata utility provided in the contrib directory of a source distribution. Here's a sample of the output from pg_controldata:

$ pg_controldata pg_control version number: 74 Catalog version number: 200411041 Database system identifier: 4773932360515448816 Database cluster state: in production pg_control last modified: Tue 22 Jun 2005 02:16:00 PM EST Current log file ID: 0 Next log file segment: 16 Latest checkpoint location: 0/F1AC608 Prior checkpoint location: 0/52EA2B8 Latest checkpoint's REDO location: 0/F1AC608 Latest checkpoint's UNDO location: 0/0 Latest checkpoint's TimeLineID: 1 Latest checkpoint's NextXID: 558 Latest checkpoint's NextOID: 798284 Time of latest checkpoint: Tue 22 Jun 2005 02:16:00 PM EST Database block size: 8192 Blocks per segment of large relation: 131072 Bytes per WAL segment: 16777216 Maximum length of identifiers: 64 Maximum number of function arguments: 32 Date/time type storage: floating-point numbers Maximum length of locale name: 128 LC_COLLATE: en_US.UTF-8 LC_CTYPE: en_US.UTF-8

The initdb utility also creates two template databases in the new cluster: template0 and template1. The template0 database represents a "stock" databaseit contains the definitions for all system tables, as well as definitions for the standard views, functions, and data types. You should never modify template0in fact, you can't even connect to the template0 database without performing some evil magic. When you run initdb, the template0 database is copied to template1. You can modify the template1 database. Just as the template0 database is cloned to create template1, template1 is cloned whenever you create a new database using CREATE DATABASE (or createdb). It's useful to modify the template1 database when you want a particular feature (like a custom data type, function, or table) to exist in every database that you create in the future. For example, if you happen to run an accounting business, you might want to define a set of accounting tables (customers, vendors, accounts, and so on) in the template1 database. Then, when you sign up a new customer and create a new database for that customer, the new database will automatically contain the empty accounting tables.

You may also find it useful to create other template databases. To extend the previous example a bit, let's say that you have a core set of financial applications (general ledger, accounts payable, accounts receivable) that are useful regardless of the type of business your customer happens to run. You may develop a set of extensions that are well suited to customers who own restaurants, and another set of extensions that you use for plumbers. If you create two new template databases, restaurant_template and plumber_template, you'll be ready to sign up new restaurants and new plumbers with minimal work. When you want to create a database for a new restaurateur, simply clone the restaurant_template database.

After you have created a cluster (and the two default template databases), you can create the actual databases where you will do your work.

Creating a New Database

There are two ways to create a new database. You can use the CREATE DATABASE command from within a PostgreSQL client application (such as psql), or you can use the createdb shell script. The syntax for the CREATE DATABASE command is

CREATE DATABASE database-name [WITH [TEMPLATE = template-database-name ] [ENCODING = character-encoding ] [OWNER = database-owner ] [LOCATION = pathname ]]

A database-name must conform to the usual rules for PostgreSQL identifiers: it should start with an underscore or a letter and should be at most 31 characters long. If you need to include a space (or start the database name with a digit), enclose the database-name in double quotes.

When you execute the CREATE DATABASE command, PostgreSQL will copy an existing template database. If you don't include a TEMPLATE= template-_database-name clause, CREATE DATABASE will clone the template1 database. A few restrictions control whether or not you can clone a given database. First, a cluster superuser can clone any database. The owner of a database can clone that database. Finally, any user with CREATEDB privileges can clone a database whose datistemplate attribute is set to true in the pg_database system table. Looking at this in the other direction, ordinary users cannot clone a database that is not specifically marked as a template (according to the datistemplate attribute).

You can choose an encoding for the new database using the ENCODING= character-encoding clause. An encoding tells PostgreSQL which character set to use within your database. If you don't specify an encoding, the new database will use the same encoding that the template database uses. Encodings are discussed in detail in Chapter 22, "Internationalization and Localization."

If you don't include the OWNER= username clause or if you specify OWNER=DEFAULT, you become the owner of the database. If you are a PostgreSQL superuser, you can create a database that will be owned by another user using the OWNER= username clause. If you are not a PostgreSQL superuser, you can still create a database (assuming that you hold the CREATEDB privilege), but you cannot assign ownership to another user.

The final option to the CREATE DATABASE command is LOCATION= pathname. This clause is used to control where PostgreSQL places the files that make up the new database. If you don't specify a location, CREATE DATABASE will create a subdirectory in the cluster ($PGDATA) to hold the new database. There are some restrictions to where you can place a new database; see the "Creating New Databases" section of Chapter 3, "PostgreSQL SQL Syntax and Use," for more information.

As I mentioned earlier, there are two ways to create a new database: CREATE DATABASE and createdb. The createdb utility is simply a shell script that invokes the psql client to execute a CREATE DATABASE, command. createdb does not offer any more functionality than CREATE DATABASE so use whichever you find most convenient. For more information on the createdb utility, invoke createdb with the help flag:

$ createdb help createdb creates a PostgreSQL database. Usage: createdb [options] dbname [description] Options: -D, location=PATH Alternative place to store the database -T, template=TEMPLATE Template database to copy -E, encoding=ENCODING Multibyte encoding for the database -h, host=HOSTNAME Database server host -p, port=PORT Database server port -U, username=USERNAME Username to connect as -W, password Prompt for password -e, echo Show the query being sent to the backend -q, quiet Don't write any messages By default, a database with the same name as the current user is created. Report bugs to .

Routine Maintenance

Compared to most relational database management systems, PostgreSQL does not require much in the way of routine maintenance, but there are a few things you should do on a regular basis.

Managing Tables (CLUSTER and VACUUM)

When you delete (or update) rows in a PostgreSQL table, the old data is not immediately removed from the database. In fact, unlike other database systems, the free space is not even marked as being available for reuse. If you delete or modify a lot of data, your database may become very large very fast. You may also find that performance suffers because PostgreSQL will have to load obsolete data from disk even though it won't use that data.

To permanently free obsolete data from a table, you use the VACUUM command. The VACUUM command comes in four flavors:

VACUUM [table-name] VACUUM FULL [table-name] VACUUM ANALYZE [table-name] VACUUM FULL ANALYZE [table-name]

The first and third forms are the ones most commonly used.

In the first form, VACUUM makes all space previously used to hold obsolete data available for reuse. This form does not require exclusive access to the table and usually runs quickly. If you don't specify a table-name, VACUUM will process all tables in the database.

In the second form, VACUUM removes obsolete data from the table (or entire database). Without the FULL option, VACUUM only marks space consumed by obsolete data as being available for reuse. With the FULL option, VACUUM TRies to shrink the data file instead of simply making space available for reuse. A VACUUM FULL requires exclusive access to each table and is generally much slower than a simple VACUUM.

The VACUUM ANALYZE command will first VACUUM a table (or database) and will then compute statistics for the PostgreSQL optimizer. I discussed optimization and statistics in Chapter 4, "Performance." If you will VACUUM a table (or database), you may as well update the per-table statistics as well.

The final form combines a VACUUM FULL with a VACUUM ANALYZE. As you might expect, this shrinks the database by removing obsolete data and then computes new performance-related statistics. Like VACUUM FULL, VACUUM FULL ANALYZE locks each table for exclusive use while it is being processed.

Another command that you may want to execute on a routine basis is the CLUSTER command. CLUSTER rearranges the rows in a given table so that they are physically stored in index order. This is a cheap way to get enormous performance gainsrun this command occasionally and you'll look like a hero. See Chapter 4 for more information.

PostgreSQL includes a client application named pg_autovacuum (you'll find it in the contrib source code directory) that you can use to automatically VACUUM tables as required. pg_autovacuum watches for modifications (INSERT, UPDATE, DELETE commands) and VACUUM s the modified tables as soon as the number of changes reaches a certain threshold (you can control the threshold). pg_autovacuum will also update optimizer statistics on your behalf. If you're running an active database server, be sure to consider pg_autovacuum; it can increase performance and decrease the amount of work you put in to managing your system.

Managing Indexes

For the most part, indexes are self-maintaining. Occasionally, you may find that an index has become corrupted and must be rebuilt (actually, you are more likely to suspect a corrupted index than to find one). You can also improve performance slightly (and reduce disk space consumption) by rebuilding indexes on an occasional basis.

The easiest way to rebuild an index is with the REINDEX command. REINDEX comes in the following forms:

REINDEX INDEX index-name [FORCE] REINDEX TABLE table-name [FORCE] REINDEX DATABASE database-name [FORCE]

In all three forms, you can force REINDEX to rebuild indexes on system tables (they are normally ignored by REINDEX) by including the keyword FORCE at the end of the command. If you find you need to REINDEX system tables, you should consult the PostgreSQL Reference Manual for the gory details. (Warningthis is not for the faint-of-heart.)

Managing Tablespaces

PostgreSQL version 8.0 introduces a new feature that you can use to organize the data files that make up a cluster. A tablespace is a name that you give to a directory somewhere in your computer's filesystem. A tablespace can store both tables and indexes. When you CREATE (or ALTER) a table or index, you can tell PostgreSQL to store that object inside of a specific tablespace. A tablespace is defined within a single clusterall databases within a cluster can refer to the same tablespace.

To create a new tablespace, use the CREATE TABLESPACE command:

CREATE TABLESPACE tablespacename [ OWNER username ] LOCATION 'directory'

PostgreSQL reserves all tablespace names that begin with pg_. If you omit the OWNER username clause, the new tablespace is owned by the user executing the CREATE TABLESPACE command. By default, you can't create an object in a tablespace unless you are the owner of that tablespace (or you are a cluster superuser). You can grant CREATE privileges to other users with the GRANT command (see Chapter 23 for more information on the GRANT command).

The LOCATION clause identifies the directory where PostgreSQL will store any objects that you place in the new tablespace. There are a few rules that you must follow before you can create a tablespace:

• You must be a cluster superuser
• PostgreSQL must be running on a system that supports symbolic links (that means you can't create tablespaces on a Windows host)
• The directory must already exist (PostgreSQL won't create the directory for you)
• The directory must be empty
• The directory name must be shorter than 991 characters
• The directory must be owned by the owner of the postmaster process (typically a user named postgres)

If all of those conditions are satisfied, PostgreSQL creates the new tablespace.

When you create a tablespace, PostgreSQL changes the permissions on the directory to 700 (read, write, and execute permissions for the directory owner, all other permissions denied). PostgreSQL creates a single file named PG_VERSION in the given directory (the PG_VERSION file stores the version number of the PostgreSQL server that created the tablespaceif the PostgreSQL developers change the structure of a tablespace in a future version, PG_VERSION will help any conversion tools understand the structure of an existing tablespace). Next, PostgreSQL adds a new row to the pg_tablespace table (a cluster-wide table) and assigns a new OID (object-id) to that row. Finally, the server uses the OID to create a symbolic link between your cluster and the given directory.

To help you understand the details, consider the following scenario:

movies# CREATE TABLESPACE mytablespace LOCATION '/fastDrive/pg'; CREATE TABLESPACE movies# SELECT oid, spcname, spclocation movies-# FROM movies-# pg_tablespace movies-# WHERE movies-# spcname = 'mytablespace'; oid | spcname | spclocation -------+--------------+-------------- 34281 | mytablespace | /fastDrive/pg

In this case, PostgreSQL assigned the new tablespace (mytablespace) an OID of 34281. PostgreSQL creates a symbolic link that points from $PGDATA/pg_tblspc/34281 to /fastDrive/pg. When you create an object (a table or index) inside of this tablespace, the object is not created directly inside of the /fastDrive/pg directory. Instead, PostgreSQL creates a subdirectory in the tablespace and then creates the object within that subdirectory. The name of the subdirectory corresponds to the OID of the database (that is, the object-id of the database's entry in the pg_database table) that holds the new object. If you create a new table within the mytablespace tablespace, like this

movies# CREATE TABLE foo ( data VARCHAR ) TABLESPACE mytablespace; CREATE TABLE

then find the OID of the new table and the OID of the database (movies):

movies# SELECT oid FROM pg_class WHERE relname = 'foo'; oid ------- 34282 (1 row) movies# SELECT oid FROM pg_database WHERE datname = 'movies'; oid ------- 17228 (1 row)

You can see the relationships between the tablespace, the database subdirectory, and the new table:

$ ls -l $PGDATA/pg_tblspc total 0 lrwxrwxrwx 1 postgres postgres 12 Nov 9 19:31 34281 -> /fastDrive/pg $ ls -l /fastDrive/pg total 8 drwx----- 2 postgres postgres 4096 Nov 9 19:50 17228 -rw------ 1 postgres postgres 4 Nov 9 19:31 PG_VERSION $ ls -l /fastDrive/pg/17228 total 0 -rw------ 1 postgres postgres 0 Nov 9 19:50 34282

Notice that $PGDATA/pg_tblspc/34281 is a symbolic link that points to /fastDrive/pg (34281 is the OID of mytablespace's entry in the pg_tablespace table), PostgreSQL has created a subdirectory (17228) for the movies database, and the table named foo was created in that subdirectory (in a file whose name, 34282, corresponds to the table's OID). By creating a subdirectory for each database, PostgreSQL ensures that you can safely store objects from multiple databases within the same tablespace without worrying about OID collisions.

When you create a cluster (which is done for you automatically when you install PostgreSQL), PostgreSQL silently creates two tablespaces for you: pg_default and pg_global. PostgreSQL creates objects in the pg_default tablespace when it can't find a more appropriate tablespace. The pg_default tablespace is always located in the $PGDATA/base directory. The pg_global tablespace stores cluster-wide tables like pg_database, pg_group, and pg_tablespaceyou can't create objects in the pg global tablespace.

The name of the pg_default tablespace can be a bit misleading. You may think that PostgreSQL always creates an object in pg_default if you omit the TABLESPACE tablespacename clause, but that's not the case. Instead, PostgreSQL follows an inheritance hierarchy to find the appropriate tablespace. If you specify a TABLESPACE tablespacename clause when you execute a CREATE TABLE or CREATE INDEX command, the server creates the object in the given tablespacename. If you don't specify a tablespace and you're creating an index, the index is created in the tablespace of the parent table (that is, the table that you are indexing). If you don't specify a tablespace and you're creating a table, the table is created in the tablespace of the parent schema. If you are creating a schema and you don't specify a tablespace, the schema is created in the tablespace of the parent database. If you are creating a database and you don't specify a tablespace, the database is created in the tablespace of the template database (typically, template1). To summarize: An index inherits its tablespace from the parent table, a table inherits its tablespace from the parent schema, a schema inherits its tablespace from the parent database, and a database inherits its database from the template database.

To view the databases defined in a cluster, use the db (or db+) command in psql:

movies=# db+ List of tablespaces Name | Owner | Location | Access privileges --------------+----------+------------------+------------------ mytablespace | postgres | /fastDrive/pg | pg_default | postgres | | pg_global | postgres | | {pg=C/pg} (4 rows)

To see a list of objects defined with a given tablespace, use the following query:

SELECT relname FROM pg_class WHERE reltablespace = ( SELECT oid FROM pg_tablespace WHERE spcname = 'tablespacename' );

Logfile Rotation

When you start a PostgreSQL server process (either the postmaster or a backend server process), that process typically writes a whole sequence of messages to the server log. If you don't take any special action, PostgreSQL writes the server log to the stderr (standard error) stream of the postmaster process. If you use pg_ctl to control the postmaster, you probably include the -l logfile command-line option to redirect the log to a disk file. Starting with PostgreSQL version 8.0, you have a better method at your disposal: rotating log files.

Server logs can grow very quickly, particularly when you're recording a lot of details about how your PostgreSQL cluster is working. At the very least, the server log will tell you when something goes wrong. If you're in a development environment (or if you are trying to solve some sort of problem), the server log can show quite a bit more. You can record the text of every query that causes an error, every query that takes more than, say, two minutes to execute, or the text of every query that your server executes. You can record execution plans, parser and planner statistics, or buffer manager operations.

It's easy to talk PostgreSQL into setting up a rotating log file mechanism for you. First, forget about the -l logfile option. Instead, change the REDIRECT_STDERR configuration parameter from its default value (False) to true and restart the postmaster. That's it. As soon as you restart the postmaster, PostgreSQL will create a new log file in the $PGDATA/pg_log directory. The log filename will look something like: postgresql-2005-06-20_102644.log. PostgreSQL will automatically close that file and start a new file when 24 hours have elapsed, or when the log file grows to 10MB in size, whichever occurs first. PostgreSQL will continue to cycle through new log files every 24 hours (or 10MB) as long as the postmaster is running. You can adjust the interval between new log files by changing the configuration parameter (LOG_ROTATION_AGE is measured in minutes). You can also adjust the maximum log file size by changing LOG ROTATION SIZE (measured in kilobytes). If you want to move the log files to a different filesystem (or just move them to a different directory), modify the LOG_DIRECTORY parameter.

At this point, PostgreSQL is creating new log files for you every so often, but it's not actually rotating the files. To rotate (or reuse) log files, you have to change the file naming scheme. Take another look at the name of the log file that PostgreSQL created: postgresql-2005-06-20_102644.log. The middle part of the name (the 2005-06-20_102644) looks suspiciously similar to a date/time stamp, and in fact, that's what it is. Unless you tell it to do otherwise, PostgreSQL generates log filenames by splicing together three strings: 'postgresql-', the current date and time, and '.log'. That pattern is defined by the LOG_FILENAME configuration parameter. The default value for LOG_FILENAME is postgresql-%Y-%m-%d_%H%M%S.log. When PostgreSQL creates a new filename, it expands the macros (the % x thingies) in LOG_FILENAME to come up with the actual name. For example, PostgreSQL expands %Y to the current century and year (2005); %m expands to the current two-digit month number (06), and so on. Table 21.5 shows the complete list of macros that you can include in LOG_FILENAME. Obviously, PostgreSQL will never come up with the same filename twice if you include a complete time stamp in LOG_FILENAME (assuming that the granularity of the time stamp is greater than the interval between creating two log files). If it does come up with a duplicate filename, we'll all be too old (and too dusty) to care.

To set up a rotating log scheme, you want PostgreSQL to generate duplicate file names on a periodic basis. For example, if you set LOG_FILENAME to a pattern such as postgresql-%a.log, PostgreSQL will generate log filenames like postgresql-Mon.log, postgresql-Tue.log, postgresql-Wed.log, and so on (the %a macro expands to the abbreviated name of the weekday, in the server's locale). If you set LOG_ROTATION_AGE to 10080 (one week), you can see that PostgreSQL will begin generating duplicate log filenames as soon as one week has gone by. What happens when PostgreSQL runs into a file with the same name as the log file it wants to record? That depends on the value of the LOG_TRUNCATE_ON_ROTATION configuration parameter. If LOG_TRUNCATE_ON_ROTATION is False (which is the default value), PostgreSQL will simply append new messages to the end of the existing file. In this example, the postgresql-Mon.log file would contain all messages generated on any Monday. If LOG_TRUNCATE_ON_ROTATION is true, you get a true rotation scheme; PostgreSQL deletes the existing file and replaces it with a new log file that contains only the message generated on the most recent Monday. Of course, when PostgreSQL replaces the Monday file, the Tuesday file still contains messages recorded on the previous Tuesday. That means that you have one week's worth of history. If you want to keep a year's worth of history, set LOG_FILENAME to postgresql-%b.log (%b expands to the abbreviated name of the month, in the server's locale). To keep a month's worth of history, include a %d in LOG_FILENAME (%d expands to the two-digit day of the month). You can use the LOG_FILENAME macros to set up just about any rotation scheme you want. In general, you want the date mask in LOG_FILENAME pattern to match the LOG_ROTATION_AGE interval.