X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4F1575287E for ; Wed, 1 Jun 2005 17:31:40 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 09610-10 for ; Wed, 1 Jun 2005 20:31:39 +0000 (GMT) Received: from mail.libertyrms.com (unknown [207.219.45.62]) by svr1.postgresql.org (Postfix) with ESMTP id BC92552802 for ; Wed, 1 Jun 2005 17:31:37 -0300 (ADT) Received: from dba5.int.libertyrms.com ([10.1.3.44]) by mail.libertyrms.com with esmtp (Exim 4.22) id 1DdZsI-0005wK-V5 for pgsql-performance@postgresql.org; Wed, 01 Jun 2005 16:31:38 -0400 Message-ID: <429E1B2A.1000605@ca.afilias.info> Date: Wed, 01 Jun 2005 16:31:38 -0400 From: Brad Nicholson User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: PostgreSQL Perform Subject: Re: Moving pg_xlog References: <20050601151618.M48325@narrowpathinc.com> <14825.1117642780@sss.pgh.pa.us> <20050601200610.M12354@narrowpathinc.com> In-Reply-To: <20050601200610.M12354@narrowpathinc.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-SA-Exim-Mail-From: bnichols@ca.afilias.info X-SA-Exim-Scanned: No; SAEximRunCond expanded to false X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.034 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200506/20 X-Sequence-Number: 12658 Keith Worthington wrote: >On Wed, 01 Jun 2005 12:19:40 -0400, Tom Lane wrote > > >>"Keith Worthington" writes: >> >> >>>I have been reading about increasing PostgreSQL performance >>>by relocating the pg_xlog to a disk other than the one >>>where the database resides. I have the following pg_xlogs >>>on my system. >>> >>>/raid02/databases/pg_xlog >>>/raid02/rhdb_databases/pg_xlog >>>/raid02/databases-8.0.0/pg_xlog >>>/var/lib/pgsql/data/pg_xlog >>> >>>I have no idea why the forth entry is there. It is in the PostgreSQL >>>installation directory. >>> >>> >>It's there because the RPM sets up a database under /var/lib/pgsql/data. >> >> >> >>>1) stop the postmaster >>>2) rm -rf /var/lib/pgsql/data/pg_xlog >>>3) mv /raid02/databases/pg_xlog /var/lib/pgsql/data/pg_xlog >>>4) ln -s /var/lib/pgsql/data/pg_xlog /raid02/databases/pg_xlog >>>5) start postmaster >>> >>> >>Put the xlog anywhere BUT there!!!!!!!!! >> >> >> >>>If I can do that and place the pg_xlog in the installation >>>directory will I create any installation issues the next >>>time I upgrade PostgreSQL? >>> >>> >>Oh, the installation will be just fine ... but your database will not >>be after the upgrade wipes out your WAL. Put the xlog under some >>non-system-defined directory. >> >> regards, tom lane >> >>---------------------------(end of broadcast)--------------------------- >>TIP 7: don't forget to increase your free space map settings >> >> > >Thanks Tom. I am glad I asked before I leaped. 8-0 > >Is there a convention that most people follow. It would seem that anywhere in >the installation directory is a bad idea. From what I have read on other >threads it does not want to be in the database directory since in most cases >that would put it on the same disk as the database. > > > We tend to use somthing that associates the WAL with the appropriate cluster, like /var/lib/CLUSTER for the data /var/lib/CLUSTER_WAL for WAL files. >I am assuming due to lack of reaction that the symbolic link is not an issue. > Is there a cleaner or more appropriate way of moving the pg_xlog. > > > A symbolic link is the standard way to do it. >Finally, am I correct in assuming that as long as the postmaster is shut down >moving the log is safe? > > You are correct. Moving the WAL files with the postmaster running would be a very bad thing. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.