pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: best practices for separating data and logs
Date: Wed, 02 Jan 2008 14:48:37 -0500
Message-ID: <601w90c8kq.fsf@dba2.int.libertyrms.com> (raw)
References: <4544e0330801020946i314c0c8n5830dcc4512849ee@mail.gmail.com>
	<17603.1199297401@sss.pgh.pa.us>

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!



view thread (4+ messages)  latest in thread

Message-ID: <601w90c8kq.fsf@dba2.int.libertyrms.com>
Permalink:  ../601w90c8kq.fsf@dba2.int.libertyrms.com/
Also on:    postgresql.org/message-id/601w90c8kq.fsf@dba2.int.libertyrms.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: best practices for separating data and logs
  In-Reply-To: <601w90c8kq.fsf@dba2.int.libertyrms.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox