Database Reference
In-Depth Information
There's more...
Once you've located the data directory, you can look for the files that comprise the PostgreSQL
database server. The layout is as follows:
Subdirectory Purpose
base
Main data directory. Beneath this directory each database has its own
directory within which are the files for each database table or index.
global
Database server catalog tables that are shared across all databases.
pg_clog
Transaction status files.
pg_multixact
Row-level lock status files
pg_subtrans
Subtransaction status files
pg_tblspc
Links to external tablespaces
pg_twophase
"2-phase commit", or Prepared transaction status
pg_xlog
Transaction log (or Write Ahead Log - WAL)
None of the aforementioned directories contain user-modifiable files, nor should any of the files
be manually deleted, to save space or for any reason. Don't touch it, because you'll break it,
and you may not be able to fix it. It's not even sensible to copy files in those directories without
carefully following the procedures described in the
Backup
chapter. Keep Off the Grass!
We'll talk about table spaces again elsewhere in the Cookbook. We'll also discuss a
performance enhancement, which is to put the transaction log on its own set of disk drives
(that's covered in the
Performance
chapter).
The only things you are allowed to touch are Configuration files, which are all
*.conf
files, or
Server message log files. Server message log files may or may not be in the data directory.
Locate the database server message log
The database server log is the record of all messages recorded by the database server. This is
the first place to look if you have server problems, and a good place to check regularly.
It will have messages in it that look something like the following:
2010-01-19 21:23:52 GMT LOG: database system was not properly shut
down; automatic recovery in progress
2010-01-19 21:23:52 GMT LOG: record with zero length at 0/49AF90
2010-01-19 21:23:52 GMT LOG: redo is not required
2010-01-19 21:23:52 GMT LOG: autovacuum launcher started
