Re: [ADMIN] binary logs: a location other than pg_xlog??
Tom Lane wrote: Lou Picciano loupicci...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] binary logs: a location other than pg_xlog??
TL == Tom Lane t...@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 cl...@jhcloos.com OpenPGP: 1024D/ED7DAEA6 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] binary logs: a location other than pg_xlog??
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] binary logs: a location other than pg_xlog??
TL == Tom Lane t...@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 cl...@jhcloos.com OpenPGP: 1024D/ED7DAEA6 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] binary logs: a location other than pg_xlog??
James Cloos cl...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] binary logs: a location other than pg_xlog??
On 11/22/10 10:42 AM, James Cloos wrote: TL == Tom Lanet...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] binary logs: a location other than pg_xlog??
Kevin Grittner kevin.gritt...@wicourts.gov writes: James Cloos cl...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] binary logs: a location other than pg_xlog??
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: [ADMIN] binary logs: a location other than pg_xlog??
Lou Picciano loupicci...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] binary logs: a location other than pg_xlog??
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 t...@sss.pgh.pa.us To: Lou Picciano loupicci...@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 loupicci...@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: [ADMIN] binary logs: a location other than pg_xlog??
On Mon, 2010-11-22 at 03:09 +, 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 t...@sss.pgh.pa.us To: Lou Picciano loupicci...@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 loupicci...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] binary logs: a location other than pg_xlog??
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 j...@commandprompt.com To: Lou Picciano loupicci...@comcast.net Cc: Tom Lane t...@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 +, 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 t...@sss.pgh.pa.us To: Lou Picciano loupicci...@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 loupicci...@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: [ADMIN] binary logs: a location other than pg_xlog??
Lou Picciano loupicci...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
