Re: best practices for separating data and logs

tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:
> "Peter Koczan" <pjkoczan(at)gmail(dot)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(at)~S" "cbbrowne" "linuxfinances.info")
http://linuxdatabases.info/info/linuxxian.html
I am not a Church numeral!
I am a free variable!