Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 83F512E32B6 for ; Wed, 2 Jan 2008 16:20:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 87541-08 for ; Wed, 2 Jan 2008 16:20:50 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id 9B9372E324B for ; Wed, 2 Jan 2008 16:20:52 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.1/8.14.1) with ESMTP id m02KKojv088905 for ; Wed, 2 Jan 2008 16:20:50 -0400 (AST) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.1/8.14.1/Submit) id m02K42MG084731 for pgsql-admin@postgresql.org; Wed, 2 Jan 2008 16:04:02 -0400 (AST) (envelope-from news) From: Chris Browne X-Newsgroups: pgsql.admin Subject: Re: best practices for separating data and logs Date: Wed, 02 Jan 2008 14:48:37 -0500 Organization: cbbrowne Computing Inc Lines: 76 Message-ID: <601w90c8kq.fsf@dba2.int.libertyrms.com> References: <4544e0330801020946i314c0c8n5830dcc4512849ee@mail.gmail.com> <17603.1199297401@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1008 (Gnus v5.10.8) XEmacs/21.4.21 (linux) Cancel-Lock: sha1:dVx+VIIvHCGeje3lyy+6RKCEsrI= To: pgsql-admin@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/29 X-Sequence-Number: 27803 tgl@sss.pgh.pa.us (Tom Lane) writes: > "Peter Koczan" 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!