Thread: binary logs: a location other than pg_xlog??

Lou Picciano <loupicciano@comcast.net> writes:
> Admin Friends, Can someone tell me the conf variablee to change location of binary logs? Would like to store logs in
alocation other than pg_xlog in cluster data dir.  

There is no such setting.  But if you're so inclined you can make
pg_xlog be a symlink to some other directory.

            regards, tom lane

On Mon, 2010-11-22 at 03:09 +0000, Lou Picciano wrote:
> Really? Wouldn't it be good general practice to store the xlogs on a
> different filesystem - offering one further layer of protection, if
> you will? L

Best practice is to move the xlogs to another filesystem but not for
protection (that is garnered by using RAID). It is for performance.
Xlogs are written sequentially and can greatly benefit from not being
where the data pages are as they are written randomly.

JD
>
>
> Thanks for your response.
>
> ----- Original Message -----
> From: "Tom Lane" <tgl@sss.pgh.pa.us>
> To: "Lou Picciano" <loupicciano@comcast.net>
> Cc: "pgsql-admin" <pgsql-admin@postgresql.org>
> Sent: Sunday, November 21, 2010 7:54:55 PM
> Subject: Re: [ADMIN] binary logs: a location other than pg_xlog??
>
> Lou Picciano <loupicciano@comcast.net> writes:
> > Admin Friends, Can someone tell me the conf variablee to change
> location of binary logs? Would like to store logs in a location other
> than pg_xlog in cluster data dir.
>
> There is no such setting. But if you're so inclined you can make
> pg_xlog be a symlink to some other directory.
>
> regards, tom lane
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt

Lou Picciano <loupicciano@comcast.net> writes:
> Sure - of course - performance, too! It just seems like such an obvious candidate for a configurable option; I'm a
bitsurprised it isn't in there... L  

It *is* configurable: make pg_xlog a symlink.  The reason we don't
handle this via postgresql.conf is that you could too easily shoot
yourself in the foot by breaking the link between a data directory
and its xlogs.  There are no postgresql.conf entries that say
destroy_all_my_data_immediately_please = on
but that's more or less what an easily changeable pg_xlog pointer
would be.

In short: this is not something to change if you don't know what
you're doing.  If you do, you should be able to handle it using
the mechanism provided.

            regards, tom lane

>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> It *is* configurable: make pg_xlog a symlink.

I notice that everyone suggests using a symlink, but I never see anyone
suggest just mounting a filesystem there.

Is there a (technical) reason for that?  Or is a symlink just though to
be easier to explain?

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 1024D/ED7DAEA6

James Cloos <cloos@jhcloos.com> wrote:

> I notice that everyone suggests using a symlink, but I never see
> anyone suggest just mounting a filesystem there.

I think initdb wants an empty directory.  Once it creates the
directory structure with pg_xlog, if that's a mount point, it's a
bit awkward to mount there and then copy from behind the mount point
onto it.  A symbolic link seems both safer and easier.

-Kevin

On 11/22/10 10:42 AM, James Cloos wrote:
>>>>>> "TL" == Tom Lane<tgl@sss.pgh.pa.us>  writes:
>
> TL>  It *is* configurable: make pg_xlog a symlink.
>
> I notice that everyone suggests using a symlink, but I never see anyone
> suggest just mounting a filesystem there.
>
> Is there a (technical) reason for that?  Or is a symlink just though to
> be easier to explain?

I think it's a case of, "If you know how do do this, you don't have to ask."

Craig

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> James Cloos <cloos@jhcloos.com> wrote:
>> I notice that everyone suggests using a symlink, but I never see
>> anyone suggest just mounting a filesystem there.

> I think initdb wants an empty directory.  Once it creates the
> directory structure with pg_xlog, if that's a mount point, it's a
> bit awkward to mount there and then copy from behind the mount point
> onto it.  A symbolic link seems both safer and easier.

Well, either way you're going to have to modify things after initdb.

The notion of mounting a filesystem directly there scares me, on the
whole.  Here is the problem: what if someday that filesystem happens not
to be mounted?  Then you have a bare mountpoint directory, with no real
way for the postmaster to notice that that wasn't what you intended.
Hilarity ensues.  (You might want to go back a few years in the archives
and read Joe Conway's report of what happened to his DB when a soft NFS
mount was a bit slow to mount one day.  The symptoms for a missing
pg_xlog directory would be different but not better.)  A symlink seems
to have a bit more error detection capability built in, especially if
you don't symlink to exactly the filesystem mount point but rather a
directory level or two down, so that the target dir is not there if the
mount fails.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

>> A symbolic link seems both safer and easier.

> The notion of mounting a filesystem directly there scares me, on
> the whole.  Here is the problem: what if someday that filesystem
> happens not to be mounted?  Then you have a bare mountpoint
> directory, with no real way for the postmaster to notice that that
> wasn't what you intended.  Hilarity ensues.

Yeah, we've experienced that in our shop with backup mount points --
so it was not quite so hilarious as live database directories, but
funny enough from a space utilization perspective.  We've taken to
ensuring that the subdirectory used as a mount point is locked down
to a root:root owner with no rights granted.  Since we don't do our
backups as the root user, failure to mount (or to mount in time)
generates understandable errors in a timely fashion.  I still much
prefer a symlink for pg_xlog, but I thought that this suggestion
might save someone some pain.

> don't symlink to exactly the filesystem mount point but rather a
> directory level or two down, so that the target dir is not there
> if the mount fails.

Good point.  We have done it that way, but primarily for another
reason -- we often have more than one database cluster running on
the machine, and we generally have them share an xlog filesystem.

-Kevin

>>>>> "TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> Here is the problem: what if someday that filesystem happens not to
TL> be mounted?  Then you have a bare mountpoint directory, with no real
TL> way for the postmaster to notice that that wasn't what you intended.
TL> Hilarity ensues.

That is a convincing argument.

-JimC
--
James Cloos <cloos@jhcloos.com>         OpenPGP: 1024D/ED7DAEA6

Tom Lane wrote:
> Lou Picciano <loupicciano@comcast.net> writes:
> > Sure - of course - performance, too! It just seems like such an obvious candidate for a configurable option; I'm a
bitsurprised it isn't in there... L  
>
> It *is* configurable: make pg_xlog a symlink.  The reason we don't
> handle this via postgresql.conf is that you could too easily shoot
> yourself in the foot by breaking the link between a data directory
> and its xlogs.  There are no postgresql.conf entries that say
> destroy_all_my_data_immediately_please = on
> but that's more or less what an easily changeable pg_xlog pointer
> would be.
>
> In short: this is not something to change if you don't know what
> you're doing.  If you do, you should be able to handle it using
> the mechanism provided.

Old thread, but no one mentioned that initdb has an option to create a
symlink for you for pg_xlog:

       -X directory, --xlogdir=directory
           This option specifies the directory where the
           transaction log should be stored.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +