Обсуждение: Safer auto-initdb for RPM init script
We've seen more than one report of corruption of PG databases that
seemed to be due to the willingness of the RPM init script to run
initdb if it thinks the data directory isn't there. This is pretty
darn risky on an NFS volume, for instance, which might be offline
at the instant the script looks. The failure case is
- script doesn't see data directory- script runs initdb and starts postmaster- offline volume comes online- KABOOM
The initdb creates a "local" database that's physically on the root
volume underneath the mountpoint directory for the intended volume.
After the mountable volume comes online, these files are shadowed
by the original database files. The problem is that by this point the
postmaster has a copy of pg_control in memory from the freshly-initdb'd
database, and that pg_control has a WAL end address and XID counter far
less than is correct for the real database. Havoc ensues, very probably
resulting in a hopelessly corrupt database.
I don't really want to remove the auto-initdb feature from the script,
because it's important not to drive away newbies by making Postgres
hard to start for the first time. But I think we'd better think about
ways to make it more bulletproof.
The first thought that comes to mind is to have the RPM install create
the data directory if not present and create a flag file in it showing
that it's safe to initdb. Then the script is allowed to initdb only
if it finds the directory and the flag file but not PG_VERSION.
Something like (untested off-the-cuff coding)
%post server
if [ ! -d $PGDATA ]; then mkdir $PGDATA touch $PGDATA/NO_DATABASE_YETfi
and in initscript
if [ -d $PGDATA -a -f $PGDATA/NO_DATABASE_YET -a ! -f $PGDATA/PG_VERSION ] ; then rm -f $PGDATA/NO_DATABASE_YET &&
initdb...fi
If the data directory is not mounted then the -d test would fail,
unless the directory is itself the mount point, in which case it
would be there but not contain the NO_DATABASE_YET file.
I can still imagine ways for this to fail, eg if you run an RPM
install or upgrade while your mountable data directory is offline.
But it ought to be an order of magnitude safer than things are now.
(Hm, maybe the %post script should only run during an RPM install,
not an upgrade.)
Comments? Anyone see a better way?
regards, tom lane
Am Freitag, 25. August 2006 15:19 schrieb Tom Lane: > I don't really want to remove the auto-initdb feature from the script, > because it's important not to drive away newbies by making Postgres > hard to start for the first time. But I think we'd better think about > ways to make it more bulletproof. Why not run initdb in the %post and not in the init script at all? That should be newbie-friendly as well. -- Peter Eisentraut http://developer.postgresql.org/~petere/
> I don't really want to remove the auto-initdb feature from the > script, because it's important not to drive away newbies by making > Postgres hard to start for the first time. But I think we'd better > think about ways to make it more bulletproof. Why does initdb have to happen on startup? Wouldn't it be much more logical to do it at install time? (like we do in the win32 installer, for example, I'm sure there are other examples as well) //Magnus
"Magnus Hagander" <mha@sollentuna.net> writes:
>> I don't really want to remove the auto-initdb feature from the
>> script, because it's important not to drive away newbies by making
>> Postgres hard to start for the first time. But I think we'd better
>> think about ways to make it more bulletproof.
> Why does initdb have to happen on startup? Wouldn't it be much more
> logical to do it at install time?
It eats rather a lot of disk space for a package that might just be
getting loaded as part of a system install, with no likelihood of
actually being used. In CVS tip a just-initdb'd data directory seems
to be a shade under 30MB, which I guess isn't a huge amount these days
but it compares unfavorably with the installed footprint of the code
itself (postgresql-server RPM looks to be about 4MB).
If this were a bulletproof solution then I'd consider it anyway, but
AFAICS it's got the very same vulnerabilities as the flag-file method,
ie, if you RPM install or upgrade while your mountable data directory
is offline, you can still get screwed.
regards, tom lane
Tom Lane wrote: > We've seen more than one report of corruption of PG databases that > seemed to be due to the willingness of the RPM init script to run > initdb if it thinks the data directory isn't there. This is pretty > darn risky on an NFS volume, for instance, which might be offline > at the instant the script looks. The failure case is > > - script doesn't see data directory > - script runs initdb and starts postmaster > - offline volume comes online > - KABOOM Been there, done exactly that... > I can still imagine ways for this to fail, eg if you run an RPM > install or upgrade while your mountable data directory is offline. > But it ought to be an order of magnitude safer than things are now. > (Hm, maybe the %post script should only run during an RPM install, > not an upgrade.) That's probably a good plan. > > Comments? Anyone see a better way? I can't think of any offhand that aren't too expensive. We ended up putting a root-owned empty data directory beneath the mount point, but that can't be automated. We also decided to turn off the init script execution entirely. The DBAs were more comfortable with a manual database startup for a production machine anyway (this is the way they typically handle Oracle databases also). They get paged if the server ever goes down unplanned, and in that event they like to check things out before bringing the db back up. For planned outages, database startup is simply part of the plan. Joe
Hi, > If this were a bulletproof solution then I'd consider it anyway, but > AFAICS it's got the very same vulnerabilities as the flag-file method, > ie, if you RPM install or upgrade while your mountable data directory > is offline, you can still get screwed. Isn't the most bulletproof solution to make initdb more careful about overwriting an existing data directory? - Sander
Sander Steffann wrote: > Hi, > > >> If this were a bulletproof solution then I'd consider it anyway, but >> AFAICS it's got the very same vulnerabilities as the flag-file method, >> ie, if you RPM install or upgrade while your mountable data directory >> is offline, you can still get screwed. >> > > Isn't the most bulletproof solution to make initdb more careful about > overwriting an existing data directory? > > It is extremely careful. The point is that the NFS mount will hide the existing datadir from initdb. cheers andrew
On Fri, 25 Aug 2006 at 10:20, Tom Lane wrote: > If this were a bulletproof solution then I'd consider it anyway, but > AFAICS it's got the very same vulnerabilities as the flag-file > method, ie, if you RPM install or upgrade while your mountable data > directory is offline, you can still get screwed. Another flaw of the flag-file method is, that PGDATA might have been changed by the sysadmin between installing the RPM and calling the init script for the first time. But shouldn't mountpoints always have 000 permissions to prevent writing into the directory as long as nothing is mounted to it? cuReinhard
Am Freitag, 25. August 2006 16:20 schrieb Tom Lane: > It eats rather a lot of disk space for a package that might just be > getting loaded as part of a system install, with no likelihood of > actually being used. Wouldn't the system install start the init script at the end of the installation process anyway? -- Peter Eisentraut http://developer.postgresql.org/~petere/
Am Freitag, 25. August 2006 16:31 schrieb Reinhard Max: > But shouldn't mountpoints always have 000 permissions to prevent > writing into the directory as long as nothing is mounted to it? That's an interesting point, but in practice nobody does that. And we're trying to defend exactly against the case where someone has set up a mount point manually. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Reinhard Max <max@suse.de> writes:
> Another flaw of the flag-file method is, that PGDATA might have been
> changed by the sysadmin between installing the RPM and calling the
> init script for the first time.
What problem do you see there? With either of these methods, a manual
change in PGDATA would require a manual initdb before the postmaster
would start. That seems like a good conservative thing to me.
(Actually, with the flag-file method you could get the initscript
to run initdb for you by hand-creating the flag file, but it seems
unlikely people would do that in practice.)
> But shouldn't mountpoints always have 000 permissions to prevent
> writing into the directory as long as nothing is mounted to it?
Not sure that that helps much given that the initscript runs as root.
And in any case the point here is to protect against human error,
not to assume that the installation is managed according to very
best practices.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Comments? Anyone see a better way? Well the truly bullet-proof mechanism would be to check every data file on every open. You could have a header with some kind of unique tag generated at initdb time and the backend could ensure it matches the same tag in the control flag whenever you open a data file. That might be too expensive though I don't see data files getting opened all that frequently. You could do the same thing for free by putting the tag in the file names though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Peter Eisentraut <peter_e@gmx.net> writes:
> Am Freitag, 25. August 2006 16:20 schrieb Tom Lane:
>> It eats rather a lot of disk space for a package that might just be
>> getting loaded as part of a system install, with no likelihood of
>> actually being used.
> Wouldn't the system install start the init script at the end of the
> installation process anyway?
No, it doesn't. (The Red Hat RPMs in fact did that ... for about
a week ... until I was told in no uncertain terms that we don't
start unnecessary daemons by default.)
regards, tom lane
Merhaba, On Fri, Aug 25, 2006 at 10:30:56AM -0400, Andrew Dunstan wrote: > It is extremely careful. The point is that the NFS mount will hide the > existing datadir from initdb. Am I the only one who believes that PostgreSQL project is not supposed to fix (or include workarounds for) some other systems that actually don't work very well? If NFS is causing trouble, let it be. Thanks, -- Enver
Am Freitag, 25. August 2006 17:17 schrieb Enver ALTIN: > Am I the only one who believes that PostgreSQL project is not supposed > to fix (or include workarounds for) some other systems that actually > don't work very well? Yes. > If NFS is causing trouble, let it be. NFS is not the trouble. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Am Freitag, 25. August 2006 16:54 schrieb Tom Lane: > No, it doesn't. (The Red Hat RPMs in fact did that ... for about > a week ... until I was told in no uncertain terms that we don't > start unnecessary daemons by default.) Well, there seem to be philosophical differences between the various operating systems -- "We won't install unnecessary packages." vs. "We won't start unnecessary daemons in unnecessarily installed packages." -- in which case your solution doesn't sound all that bad. -- Peter Eisentraut http://developer.postgresql.org/~petere/
> > Am Freitag, 25. August 2006 16:31 schrieb Reinhard Max: > > But shouldn't mountpoints always have 000 permissions to prevent > > writing into the directory as long as nothing is mounted to it? > > That's an interesting point, but in practice nobody does > that. And we're > trying to defend exactly against the case where someone has > set up a mount > point manually. > It had never occurred to me, but I'm definitely going to start doing it now. So it will be in practice, at least around here. Regards, Paul Bort
On Fri, Aug 25, 2006 at 07:21:50AM -0700, Joe Conway wrote: > We also decided to turn off the init script execution entirely. The DBAs > were more comfortable with a manual database startup for a production > machine anyway (this is the way they typically handle Oracle databases > also). They get paged if the server ever goes down unplanned, and in > that event they like to check things out before bringing the db back up. > For planned outages, database startup is simply part of the plan. I'd *really* like to have an official way to just disable the initdb code entirely. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > On Fri, Aug 25, 2006 at 07:21:50AM -0700, Joe Conway wrote: >> We also decided to turn off the init script execution entirely. The DBAs >> were more comfortable with a manual database startup for a production >> machine anyway (this is the way they typically handle Oracle databases >> also). They get paged if the server ever goes down unplanned, and in >> that event they like to check things out before bringing the db back up. >> For planned outages, database startup is simply part of the plan. > > I'd *really* like to have an official way to just disable the initdb > code entirely. I am not exactly sure why we initdb at all. IMHO it would be better if the start script just checked if there was a cluster. If not, it wouldn't start, it would error with: You do not have a cluster, please read man page on initdb. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
On Sat, Aug 26, 2006 at 01:32:17PM -0700, Joshua D. Drake wrote: > Jim C. Nasby wrote: > >On Fri, Aug 25, 2006 at 07:21:50AM -0700, Joe Conway wrote: > >>We also decided to turn off the init script execution entirely. The DBAs > >>were more comfortable with a manual database startup for a production > >>machine anyway (this is the way they typically handle Oracle databases > >>also). They get paged if the server ever goes down unplanned, and in > >>that event they like to check things out before bringing the db back up. > >>For planned outages, database startup is simply part of the plan. > > > >I'd *really* like to have an official way to just disable the initdb > >code entirely. > > I am not exactly sure why we initdb at all. IMHO it would be better if > the start script just checked if there was a cluster. If not, it > wouldn't start, it would error with: You do not have a cluster, please > read man page on initdb. As Tom mentioned, it's for newbie-friendliness. While I can understand that, I think it needs to be easy to shut that off. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > On Fri, Aug 25, 2006 at 07:21:50AM -0700, Joe Conway wrote: > >> We also decided to turn off the init script execution entirely. The DBAs >> were more comfortable with a manual database startup for a production >> machine anyway (this is the way they typically handle Oracle databases >> also). They get paged if the server ever goes down unplanned, and in >> that event they like to check things out before bringing the db back up. >> For planned outages, database startup is simply part of the plan. >> > > I'd *really* like to have an official way to just disable the initdb > code entirely. > Well, in the case of RPMS built with the pgfoundry pgsqlrpms project init script, it looks to me like it is already disabled: see http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgsqlrpms/patches/8.2/postgresql.init?rev=1.2&content-type=text/x-cvsweb-markup In the case of Redhat/Fedora supplied RPMs, presumably it is not disabled as a matter of policy, the idea being to make starting postgres as easy as possible. What you could do is to ask them to add some code to honor the setting of a variable called say, PG_INITDB, which you would set in the appropriate place in /etc/sysconfig. cheers andrew
Jim C. Nasby wrote: > On Sat, Aug 26, 2006 at 01:32:17PM -0700, Joshua D. Drake wrote: >> I am not exactly sure why we initdb at all. IMHO it would be better if >> the start script just checked if there was a cluster. If not, it >> wouldn't start, it would error with: You do not have a cluster, please >> read man page on initdb. > > As Tom mentioned, it's for newbie-friendliness. While I can understand > that, I think it needs to be easy to shut that off. I understand that, but it seems the whole problem of people overwriting there data dir is because we initdb from the start script. If we installed the datadir during the RPM install, it would still be newbie friendly and would removed initdb from start script solving that problem. The only downside is larger disk foot-print for something that a user may never use. Given the size of todays hard drives, and the size of a standard RedHat install, I don't think that is much of an issue.
Matthew T. O'Connor wrote: > Jim C. Nasby wrote: >> On Sat, Aug 26, 2006 at 01:32:17PM -0700, Joshua D. Drake wrote: >>> I am not exactly sure why we initdb at all. IMHO it would be better >>> if the start script just checked if there was a cluster. If not, it >>> wouldn't start, it would error with: You do not have a cluster, >>> please read man page on initdb. >> >> As Tom mentioned, it's for newbie-friendliness. While I can understand >> that, I think it needs to be easy to shut that off. > > I understand that, but it seems the whole problem of people overwriting > there data dir is because we initdb from the start script. If we > installed the datadir during the RPM install, it would still be newbie > friendly and would removed initdb from start script solving that problem. initdb will not overwrite an existing installation. Joshua D. Drake > > The only downside is larger disk foot-print for something that a user > may never use. Given the size of todays hard drives, and the size of a > standard RedHat install, I don't think that is much of an issue. > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutionssince 1997 http://www.commandprompt.com/
Joshua D. Drake wrote: > Matthew T. O'Connor wrote: >> Jim C. Nasby wrote: >>> As Tom mentioned, it's for newbie-friendliness. While I can understand >>> that, I think it needs to be easy to shut that off. >> >> I understand that, but it seems the whole problem of people >> overwriting there data dir is because we initdb from the start >> script. If we installed the datadir during the RPM install, it would >> still be newbie friendly and would removed initdb from start script >> solving that problem. > > initdb will not overwrite an existing installation. Poorly chosen words. I meant, the problem where the start script will create a new data dir when it doesn't see that one exists even though one actually does exist it's just not available at the moment. Either way, if the start scripts never created a data dir, then there is no problem.
Jim C. Nasby wrote: > On Fri, Aug 25, 2006 at 07:21:50AM -0700, Joe Conway wrote: > > We also decided to turn off the init script execution entirely. The DBAs > > were more comfortable with a manual database startup for a production > > machine anyway (this is the way they typically handle Oracle databases > > also). They get paged if the server ever goes down unplanned, and in > > that event they like to check things out before bringing the db back up. > > For planned outages, database startup is simply part of the plan. > > I'd *really* like to have an official way to just disable the initdb > code entirely. This is trivial to do --- just add a /etc/<some_dir>/postgresql file that contains a line like AUTO_INITDB=0 to turn the auto-initdb'ing feature of the init script off. If the file is not present or AUTO_INITDB is not defined to zero in that file, then the code behaves as today. I don't recall what the configuration directory is called in Redhat systems, but there is one in there (in Debian it's /etc/default). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote: > Jim C. Nasby wrote: > >> On Fri, Aug 25, 2006 at 07:21:50AM -0700, Joe Conway wrote: >> >>> We also decided to turn off the init script execution entirely. The DBAs >>> were more comfortable with a manual database startup for a production >>> machine anyway (this is the way they typically handle Oracle databases >>> also). They get paged if the server ever goes down unplanned, and in >>> that event they like to check things out before bringing the db back up. >>> For planned outages, database startup is simply part of the plan. >>> >> I'd *really* like to have an official way to just disable the initdb >> code entirely. >> > > This is trivial to do --- just add a /etc/<some_dir>/postgresql file > that contains a line like > > AUTO_INITDB=0 > > to turn the auto-initdb'ing feature of the init script off. If the file > is not present or AUTO_INITDB is not defined to zero in that file, then > the code behaves as today. I don't recall what the configuration > directory is called in Redhat systems, but there is one in there (in > Debian it's /etc/default). > > I don't see anything like this in my FC5 box's init script. Oh, and the place you put stuff like that on RH/FC systems is /etc/sysconfig cheers andrew
Andrew Dunstan wrote: > > Alvaro Herrera wrote: > >This is trivial to do --- just add a /etc/<some_dir>/postgresql file > >that contains a line like > > > >AUTO_INITDB=0 > > > >to turn the auto-initdb'ing feature of the init script off. If the file > >is not present or AUTO_INITDB is not defined to zero in that file, then > >the code behaves as today. I don't recall what the configuration > >directory is called in Redhat systems, but there is one in there (in > >Debian it's /etc/default). > > I don't see anything like this in my FC5 box's init script. Obviously I was thinking in something else when I wrote that, because what I wanted to say but failed, was that I suggested that the RPM hackers could *add* such a thing to the initscript -- not that it already existed :-) Hum, I must be still thinking in something else because I'm failing to write the above paragraph in a coherent manner (rewrote it twice already). I hope you understand it anyway :-) > Oh, and the place you put stuff like that on RH/FC systems is /etc/sysconfig Yeah, that one :-) -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Hello, On Sat, 2006-08-26 at 19:16 -0400, Andrew Dunstan wrote: > Well, in the case of RPMS built with the pgfoundry pgsqlrpms project > init script, it looks to me like it is already disabled: see > http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgsqlrpms/patches/8.2/postgresql.init?rev=1.2&content-type=text/x-cvsweb-markup This is a pre-pre-alpha; and may be reverted in stable release. I'm just trying to find a better way for initdb troubles. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
On Saturday 26 August 2006 22:08, Matthew T. O'Connor wrote: > Joshua D. Drake wrote: > > Matthew T. O'Connor wrote: > >> script. If we installed the datadir during the RPM install, it would > >> still be newbie friendly and would removed initdb from start script > >> solving that problem. > > initdb will not overwrite an existing installation. > Poorly chosen words. I meant, the problem where the start script will > create a new data dir when it doesn't see that one exists even though > one actually does exist it's just not available at the moment. Either > way, if the start scripts never created a data dir, then there is no > problem. If a prebuilt datadir tree were available, it would make it even easier for people to overwrite their existing data, as RPM does not check non-RPM-managed files prior to overwriting them. This was in fact done several years ago by Red Hat, and was speedily removed. I understand the needs from both angles; so I'll ask just a simple question: which is worse, annoying all the newbies who just want to get started quickly, or annoying the small number of people who NFS mount their datadirs? (I personally wouldn't in a million years trust NFS for ACID compliance; maybe iSCSI, but NFS?!?!). The behavior, in my opinion, should be configurable and ON by default. -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute 1 PARI Drive Rosman, NC 28772 (828)862-5554 www.pari.edu
On Sat, 9 Sep 2006 at 15:57, Lamar Owen wrote: > [...] or annoying the small number of people who NFS mount their > datadirs? This problem is not limited to NFS. It can happen with any FS just by reversing (for whatever reason) the order of mounting the FS and starting the PostgreSQL server. But the SUSE RPMs have the auto-initdb feature since the PostgreSQL 7.0 days (almost six years), and AFAIR I never got a single complaint about this sort of problem. cuReinhard