Postgres Pro
Downloads
Home   >   mailing lists

Thread: transaction log location

transaction log location

From
Marcin Gil
Date:
Have read the docs, tried to look through go(o)gles..
came up with nothing...

How can I make postgres to store transaction log on a different
drive?
Ie. my postgres files reside in /var/lib/pgsql (/dev/hda3)..
And I'd like to have transaction logs on a /dev/hdb1..

Regards
--
   Marcin Gil
   marcin.gil @ audax.com.pl, tel. 694972082
   OIS Audax Sp. z o.o., ul. Barlickiego 4, 97-200 Tomaszów Mazowiecki
   tel/fax (44) 7247530, 7244401

Re: transaction log location

From
Peter Childs
Date:
On Thu, 25 Sep 2003, Marcin Gil wrote:

> Have read the docs, tried to look through go(o)gles..
> came up with nothing...
>
> How can I make postgres to store transaction log on a different
> drive?
> Ie. my postgres files reside in /var/lib/pgsql (/dev/hda3)..
> And I'd like to have transaction logs on a /dev/hdb1..
>
> Regards
>

    Once you have your database....

1. Shut it down the database.
2. Move data/pg_xlog and data/pg_clog to another drive.
3. Symlink data/pg_xlog and data/pg_clog to this other drive.
4. Restart the database.

    Steps 1 and 4 are the most important!.

    One word of warning even though this is a log it can not be used
to see whats happerning or as a method for backup.



Peter Childs

Re: transaction log location

From
Marcin Gil
Date:
Użytkownik Peter Childs napisał:

>
> 1. Shut it down the database.
> 2. Move data/pg_xlog and data/pg_clog to another drive.
> 3. Symlink data/pg_xlog and data/pg_clog to this other drive.
> 4. Restart the database.
>
Ohmigod.. I now should eat a lemon :)
The most simple and elegant solution ;) Someone wake me up..

--
   Marcin Gil
   marcin.gil @ audax.com.pl, tel. 694972082
   OIS Audax Sp. z o.o., ul. Barlickiego 4, 97-200 Tomaszów Mazowiecki
   tel/fax (44) 7247530, 7244401

Re: transaction log location

From
Tom Lane
Date:
Peter Childs <blue.dragon@blueyonder.co.uk> writes:
> 1. Shut it down the database.
> 2. Move data/pg_xlog and data/pg_clog to another drive.
> 3. Symlink data/pg_xlog and data/pg_clog to this other drive.
> 4. Restart the database.

Actually, my recommendation would be to leave pg_clog on the data drive;
it's more of a data file than a log.  The reason for putting WAL
(pg_xlog) on a separate drive is that it'w written a lot, and written
perfectly sequentially, and so you can reduce seek overhead a bunch if
writing WAL is the only thing that particular disk head has to do.
Putting pg_clog onto the same drive introduces the seek overhead you
were trying to avoid.

            regards, tom lane