binary logs: a location other than pg_xlog??
binary logs: a location other than pg_xlog??
От:
Lou Picciano <loupicciano@comcast.net>
Дата:
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.
Thanks, Lou
Re: binary logs: a location other than pg_xlog??
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Lou Picciano 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
Re: binary logs: a location other than pg_xlog??
От:
Lou Picciano <loupicciano@comcast.net>
Дата:
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
----- 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
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
Re: binary logs: a location other than pg_xlog??
От:
"Joshua D. Drake" <jd@commandprompt.com>
Дата:
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" > To: "Lou Picciano" > Cc: "pgsql-admin" > Sent: Sunday, November 21, 2010 7:54:55 PM > Subject: Re: [ADMIN] binary logs: a location other than pg_xlog?? > > Lou Picciano 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
Re: binary logs: a location other than pg_xlog??
От:
Lou Picciano <loupicciano@comcast.net>
Дата:
Sure - of course - performance, too! It just seems like such an obvious candidate for a configurable option; I'm a bit surprised it isn't in there... L
----- Original Message -----
From: "Joshua D. Drake" <jd@commandprompt.com>
To: "Lou Picciano" <loupicciano@comcast.net>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>, "pgsql-admin" <pgsql-admin@postgresql.org>
Sent: Sunday, November 21, 2010 10:21:20 PM
Subject: Re: [ADMIN] binary logs: a location other than pg_xlog??
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
----- Original Message -----
From: "Joshua D. Drake" <jd@commandprompt.com>
To: "Lou Picciano" <loupicciano@comcast.net>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>, "pgsql-admin" <pgsql-admin@postgresql.org>
Sent: Sunday, November 21, 2010 10:21:20 PM
Subject: Re: [ADMIN] binary logs: a location other than pg_xlog??
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
Re: binary logs: a location other than pg_xlog??
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Lou Picciano writes: > Sure - of course - performance, too! It just seems like such an obvious candidate for a configurable option; I'm a bit surprised 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
Re: binary logs: a location other than pg_xlog??
От:
James Cloos <cloos@jhcloos.com>
Дата:
>>>>> "TL" == Tom Lane 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 OpenPGP: 1024D/ED7DAEA6
Re: binary logs: a location other than pg_xlog??
От:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Дата:
James Cloos 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
Re: binary logs: a location other than pg_xlog??
От:
Craig James <craig_james@emolecules.com>
Дата:
On 11/22/10 10:42 AM, James Cloos wrote: >>>>>> "TL" == Tom Lane 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
Re: binary logs: a location other than pg_xlog??
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Kevin Grittner" writes: > James Cloos 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
Re: binary logs: a location other than pg_xlog??
От:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Дата:
Tom Lane wrote: > "Kevin Grittner" 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
Re: binary logs: a location other than pg_xlog??
От:
James Cloos <cloos@jhcloos.com>
Дата:
>>>>> "TL" == Tom Lane 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 OpenPGP: 1024D/ED7DAEA6
Re: binary logs: a location other than pg_xlog??
От:
Bruce Momjian <bruce@momjian.us>
Дата:
Tom Lane wrote:
> Lou Picciano writes:
> > Sure - of course - performance, too! It just seems like such an obvious candidate for a configurable option; I'm a bit surprised 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 http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +