Re: best practices for separating data and logs

tgl@sss.pgh.pa.us (Tom Lane) writes:
> "Peter Koczan" <pjkoczan@gmail.com> writes:
>> I'm planning a lot of changes for migrating to PostgreSQL 8.3, among
>> them being a better way of separating data and logs (transaction logs,
>> that is).
>
>> Currently, the OS and log data are on one disk system, and the data
>> (including configs) are on the other disk system. After creating the
>> database cluster, I copy the pg_xlog directory to the OS system and
>> symlink it from the database.
>
>> So, I'm wondering...
>
>> - Are there any best practices, or better practices, than symlinking?
>
> I believe 8.3's initdb has an explicit option for making pg_xlog be a
> symlink to someplace.  The results aren't different from doing it
> manually, but it saves a step (and a chance for mistake).

Yes, indeed.

  -X, --xlogdir=XLOGDIR     location for the transaction log directory

I had not been aware of this one; this seems like a nice feature to
add support for in an init script...

We've been using an init script that offers a whole bunch of options,
originally due to Drew Hammond.

These days, the actions offered are thus:
  [start|reload|stop|env|mkdir|initdb|logtail]

start/stop/reload are pretty traditional.  The other options are
pretty interesting, particularly for cases where you might want to:

 - Frequently create databases from scratch, as when testing
   CVS HEAD
 - Alternatively, to help establish common policies, for the "less
   frequent" cases.

  env:   Sets up PATH, MAN_PATH, PGPORT with the values used by
         the backend in "this" init file

  mkdir: Sets up all the directories required both for DB backend and
         for logging

  logtail:  runs "tail -f" on the last log file for the cluster

  initdb:  Runs initdb, pointing at particular directories, and with
         particular configuration policy.

         I have recently augmented this by making it smart enough to
         rewrite the postgresql.conf file (using sed) to establish
         default values for a dozen or so options that tend to get
         customized with fairly common values.

         In effect, the entire cluster configuration gets set up in
         about a 10-line section near the top of the script.

         Adding in an option to redirect pg_xlog seems like a mighty
         fine idea; I know that on various occasions, I have had the
         irritation of building clusters and having to go to some
         fragile manual effort to shift pg_xlog somewhere else.

         Automating THAT seems like it's a "no-brainer" as far as
         being an excellent idea...

I probably ought to ask around for permission to release this; it
seems like it's probably useful enough (I have been using it a lot)
that it ought to be in a CVS repository somewhere, rather than
languishing on my desktop.
--
(format nil "~S@~S" "cbbrowne" "linuxfinances.info")
http://linuxdatabases.info/info/linuxxian.html
I am not a Church numeral!
I am a free variable!