pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedbinary logs: a location other than pg_xlog??
13+ messages / 7 participants
[nested] [flat]
* binary logs: a location other than pg_xlog??
@ 2010-11-22 00:32 Lou Picciano <loupicciano@comcast.net>
0 siblings, 1 reply; 13+ messages in thread
From: Lou Picciano @ 2010-11-22 00:32 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: binary logs: a location other than pg_xlog??
@ 2010-11-22 00:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Lou Picciano <loupicciano@comcast.net>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2010-11-22 00:54 UTC (permalink / raw)
To: Lou Picciano <loupicciano@comcast.net>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: binary logs: a location other than pg_xlog??
@ 2010-11-22 03:09 Lou Picciano <loupicciano@comcast.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Lou Picciano @ 2010-11-22 03:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: binary logs: a location other than pg_xlog??
@ 2010-11-22 03:21 Joshua D. Drake <jd@commandprompt.com>
parent: Lou Picciano <loupicciano@comcast.net>
0 siblings, 1 reply; 13+ messages in thread
From: Joshua D. Drake @ 2010-11-22 03:21 UTC (permalink / raw)
To: Lou Picciano <loupicciano@comcast.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: binary logs: a location other than pg_xlog??
@ 2010-11-22 03:37 Lou Picciano <loupicciano@comcast.net>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 13+ messages in thread
From: Lou Picciano @ 2010-11-22 03:37 UTC (permalink / raw)
To: jd@commandprompt.com; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: binary logs: a location other than pg_xlog??
@ 2010-11-22 05:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Lou Picciano <loupicciano@comcast.net>
0 siblings, 2 replies; 13+ messages in thread
From: Tom Lane @ 2010-11-22 05:11 UTC (permalink / raw)
To: Lou Picciano <loupicciano@comcast.net>; +Cc: pgsql-admin
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 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: binary logs: a location other than pg_xlog??
@ 2010-11-22 18:42 James Cloos <cloos@jhcloos.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 13+ messages in thread
From: James Cloos @ 2010-11-22 18:42 UTC (permalink / raw)
To: pgsql-admin; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Lou Picciano <loupicciano@comcast.net>; jd@commandprompt.com
>>>>> "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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: binary logs: a location other than pg_xlog??
@ 2010-11-22 19:02 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: James Cloos <cloos@jhcloos.com>
1 sibling, 1 reply; 13+ messages in thread
From: Kevin Grittner @ 2010-11-22 19:02 UTC (permalink / raw)
To: James Cloos <cloos@jhcloos.com>; pgsql-admin; +Cc: Lou Picciano <loupicciano@comcast.net>; jd@commandprompt.com; Tom Lane <tgl@sss.pgh.pa.us>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: binary logs: a location other than pg_xlog??
@ 2010-11-22 19:20 Craig James <craig_james@emolecules.com>
parent: James Cloos <cloos@jhcloos.com>
1 sibling, 0 replies; 13+ messages in thread
From: Craig James @ 2010-11-22 19:20 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: binary logs: a location other than pg_xlog??
@ 2010-11-22 19:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 2 replies; 13+ messages in thread
From: Tom Lane @ 2010-11-22 19:25 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: James Cloos <cloos@jhcloos.com>; pgsql-admin; Lou Picciano <loupicciano@comcast.net>; jd@commandprompt.com
"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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: binary logs: a location other than pg_xlog??
@ 2010-11-23 19:54 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: Kevin Grittner @ 2010-11-23 19:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Lou Picciano <loupicciano@comcast.net>; jd@commandprompt.com; James Cloos <cloos@jhcloos.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: binary logs: a location other than pg_xlog??
@ 2010-11-24 20:59 James Cloos <cloos@jhcloos.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: James Cloos @ 2010-11-24 20:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin; Lou Picciano <loupicciano@comcast.net>; jd@commandprompt.com
>>>>> "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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: binary logs: a location other than pg_xlog??
@ 2011-01-20 23:27 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 2011-01-20 23:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Lou Picciano <loupicciano@comcast.net>; pgsql-admin
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 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 <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2011-01-20 23:27 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-22 00:32 binary logs: a location other than pg_xlog?? Lou Picciano <loupicciano@comcast.net>
2010-11-22 00:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-11-22 03:09 ` Lou Picciano <loupicciano@comcast.net>
2010-11-22 03:21 ` Joshua D. Drake <jd@commandprompt.com>
2010-11-22 03:37 ` Lou Picciano <loupicciano@comcast.net>
2010-11-22 05:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-11-22 18:42 ` James Cloos <cloos@jhcloos.com>
2010-11-22 19:02 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-11-22 19:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-11-23 19:54 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-11-24 20:59 ` James Cloos <cloos@jhcloos.com>
2010-11-22 19:20 ` Craig James <craig_james@emolecules.com>
2011-01-20 23:27 ` Bruce Momjian <bruce@momjian.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox