Critical PostgreSQL Server Files

Note the following types of files that are relevant when working with PostgreSQL Server with Universal CMDB. It is strongly recommended for these file types to be located on different disks from each other and from the main database files ( the installation files):

• Data (PGDATA)

  All the data needed for a database is stored within the data directory, commonly referred to as PGDATA (after the name of the environment variable that can be used to define it). A common location for PGDATA is /var/lib/pgsql/data.

  The PGDATA directory contains several subdirectories and control files. In addition to these required items, the cluster configuration files PostgreSQL.conf, pg_hba.conf, and pg_ident.conf are traditionally stored in PGDATA (although in PostgreSQL 8.0 and later, it is possible to keep them elsewhere).

  During the PostgreSQL installation, select the option to store the PGDATA files in a designated path.

• Write-Ahead Logging (WAL)

  Write-Ahead Logging (WAL) is a standard method for ensuring data integrity. WAL's central concept is that changes to data files (where tables and indexes reside) must be written only after those changes have been logged, that is, after log records describing the changes have been flushed to permanent storage. This procedure eliminates the need to flush data pages to disk on every transaction commit, because in the event of a problem, any changes that have not been applied to the data pages can be redone from the log records. (This is roll-forward recovery, also known as REDO.)

  You can ensure that these files are located on different disks by moving the pg_xlog directory to another location. To do this, follow this procedure:

  1. Shut down the server.

  2. Create a symbolic link from the original location in the main data directory to the new location, using the following command:

     mklink /j "C:\Program Files\PostgreSQL\9.2\data\pg_xlog" "E:\pg_xlog".

     For details, see http://www.postgresql.org/docs/9.2/static/wal-internals.html.