Re: [HACKERS] [Pgsqlrpms-hackers] Safer auto-initdb for RPM init script
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [Pgsqlrpms-hackers] Safer auto-initdb for RPM init script
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] 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_YET fi 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Safer auto-initdb for RPM init script
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/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Safer auto-initdb for RPM init script
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Safer auto-initdb for RPM init script
Magnus Hagander [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [Pgsqlrpms-hackers] Safer auto-initdb for RPM init script
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Safer auto-initdb for RPM init script
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/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [Pgsqlrpms-hackers] [HACKERS] Safer auto-initdb for RPM init script
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Safer auto-initdb for RPM init script
Tom Lane [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Safer auto-initdb for RPM init script
Peter Eisentraut [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Safer auto-initdb for RPM init script
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/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
