Thread: Control File
Quick (real) story to illustrate situation: Some time ago we used to have a server with 2 disk arrays attached to it. They were /mnt/array1 and /mnt/array2. PostgreSQL (8.0) had tablespaces on both. In one cold & dark night, one SCSI controller from array2 stopped, and manufacturer was called to replace it. The "system administrator" (monkey) from the night called the DBA (sleeping...monkey? -- ups... that's me), and asked for him to stop the database so he could umount /mnt/array2. Sleeping DBA took his coffee, and stopped the database. Monkey umounted array2 and changed the SCSI controller which came alive again! Monkey calls & wake DBA (again): - Everything went fine, SCSI controller "successfully" replaced. Could you please wake up again and start the database? DBA takes another coffee and finally started the database which... just came up! Few minutes latter lot off errors being displayed. What is that??? /mnt/array2 (50% of datafiles and tablespaces were there) was still umounted and even so PostgreSQL came up. -- PostgreSQL stopped, /mnt/array2 mounted, started and... - happy end! After that night, I started to ask myself if PostgreSQL should not have a control file to check if expected datafiles are where they should be and JUST warn about missing ones? If yes, I apply for developing the patch. C ya, Teolupus
Bruno Almeida do Lago wrote: > DBA takes another coffee and finally started the database which... just came > up! Few minutes latter lot off errors being displayed. What is that??? > /mnt/array2 (50% of datafiles and tablespaces were there) was still umounted > and even so PostgreSQL came up. -- PostgreSQL stopped, /mnt/array2 mounted, > started and... - happy end! > > > After that night, I started to ask myself if PostgreSQL should not have a > control file to check if expected datafiles are where they should be and > JUST warn about missing ones? I don't think this happens frequently enough to add code for it. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Bruno Almeida do Lago wrote:
>> After that night, I started to ask myself if PostgreSQL should not have a
>> control file to check if expected datafiles are where they should be and
>> JUST warn about missing ones?
> I don't think this happens frequently enough to add code for it.
I think we saw it happen once to Joe Conway's DB. But I see no
particular reason why Postgres needs a feature for this --- you can
stick a test into your database start script if you need it.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Bruno Almeida do Lago wrote: > >> After that night, I started to ask myself if PostgreSQL should not have a > >> control file to check if expected datafiles are where they should be and > >> JUST warn about missing ones? > > > I don't think this happens frequently enough to add code for it. > > I think we saw it happen once to Joe Conway's DB. But I see no > particular reason why Postgres needs a feature for this --- you can > stick a test into your database start script if you need it. Right, that is the only other time I remember it. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Thu, Apr 13, 2006 at 04:39:59AM -0400, Bruce Momjian wrote: > Tom Lane wrote: > > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > > Bruno Almeida do Lago wrote: > > >> After that night, I started to ask myself if PostgreSQL should not have a > > >> control file to check if expected datafiles are where they should be and > > >> JUST warn about missing ones? > > > > > I don't think this happens frequently enough to add code for it. > > > > I think we saw it happen once to Joe Conway's DB. But I see no > > particular reason why Postgres needs a feature for this --- you can > > stick a test into your database start script if you need it. > > Right, that is the only other time I remember it. The difference now is that we have tablespaces, which makes this scenario more likely. Previously I suspect common practice was to either leave everything in $PGDATA, or at most to move pg_xlog somewhere else and symlink it in, and I'm guessing that the databse will complain loudly if it can't find pg_xlog. So I suspect this will become far more common. As for adding checks to startup scripts, that's a PITA because those scripts will have no idea of where tablespaces might be defined, so you'd have to hard-code that info in. -- 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 Thu, Apr 13, 2006 at 04:39:59AM -0400, Bruce Momjian wrote: > >>Tom Lane wrote: >> >>>Bruce Momjian <pgman@candle.pha.pa.us> writes: >>> >>>>Bruno Almeida do Lago wrote: >>>> >>>>>After that night, I started to ask myself if PostgreSQL should not have a >>>>>control file to check if expected datafiles are where they should be and >>>>>JUST warn about missing ones? >>> >>>>I don't think this happens frequently enough to add code for it. >>> >>>I think we saw it happen once to Joe Conway's DB. But I see no >>>particular reason why Postgres needs a feature for this --- you can >>>stick a test into your database start script if you need it. >> >>Right, that is the only other time I remember it. > > > The difference now is that we have tablespaces, which makes this > scenario more likely. Previously I suspect common practice was to either > leave everything in $PGDATA, or at most to move pg_xlog somewhere else > and symlink it in, and I'm guessing that the databse will complain > loudly if it can't find pg_xlog. So I suspect this will become far more > common. As for adding checks to startup scripts, that's a PITA because > those scripts will have no idea of where tablespaces might be defined, > so you'd have to hard-code that info in. ... And we don't have startup scripts in win32. If tablespaces are used there, they may well reside on some kind of detachable media (SAN) Regards, Andreas
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> As for adding checks to startup scripts, that's a PITA because
> those scripts will have no idea of where tablespaces might be defined,
> so you'd have to hard-code that info in.
No, just look in $PGDATA/pg_tblspc to see where the symlinks point.
I'd lean to something like
for s in $PGDATA/pg_tblspc/*do if [ x`cat $s/PG_VERSION` != x`cat $PGDATA/PG_VERSION` ] then complain ...
fidone
OTOH you could argue that it'd be just as reasonable for the postmaster
to make this check, seeing that it does go to the trouble of verifying
the contents of $PGDATA/PG_VERSION.
regards, tom lane
On Fri, Apr 14, 2006 at 11:10:34AM -0400, Tom Lane wrote: > "Jim C. Nasby" <jnasby@pervasive.com> writes: > > As for adding checks to startup scripts, that's a PITA because > > those scripts will have no idea of where tablespaces might be defined, > > so you'd have to hard-code that info in. <snip> > OTOH you could argue that it'd be just as reasonable for the postmaster > to make this check, seeing that it does go to the trouble of verifying > the contents of $PGDATA/PG_VERSION. Yup. Plus it seems rather silly to expect every port/package of PostgreSQL to add code to check for functional tablespace mounts. Perhaps an easy means would be to put a PG_VERSION file in each tablespace when it's created and then check all of them. Tablespaces arguably make it slightly easier to accidentally try and mount something from a different version... -- 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
On Sat, Apr 15, 2006 at 02:03:50PM -0500, Jim C. Nasby wrote: > Perhaps an easy means would be to put a PG_VERSION file in each > tablespace when it's created and then check all of them. Tablespaces > arguably make it slightly easier to accidentally try and mount something > from a different version... Sorry, clearly there already is a PG_VERSION there. I'll go find some caffine before sending any more email. :( -- 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" <jnasby@pervasive.com> writes:
> Perhaps an easy means would be to put a PG_VERSION file in each
> tablespace when it's created and then check all of them. Tablespaces
> arguably make it slightly easier to accidentally try and mount something
> from a different version...
I believe we already do put a PG_VERSION file in each tablespace, but
AFAIR there is no subsequent check on their contents.
regards, tom lane
OK! I'll create a shell script to verify it.
If you change your mind latter I apply for developing the patch.
Best Regards,
Bruno
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, April 13, 2006 1:57 AM
To: Bruce Momjian
Cc: Bruno Almeida do Lago; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Control File
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Bruno Almeida do Lago wrote:
>> After that night, I started to ask myself if PostgreSQL should not have a
>> control file to check if expected datafiles are where they should be and
>> JUST warn about missing ones?
> I don't think this happens frequently enough to add code for it.
I think we saw it happen once to Joe Conway's DB. But I see no
particular reason why Postgres needs a feature for this --- you can
stick a test into your database start script if you need it.
regards, tom lane
Tom,
With the new tablespace scenario in mind, how do you see this check feature
being implemented: as a script or inside the code?
As I said before, I apply for the creation of this patch. We just need to
decide the best way to implement it.
Regards,
Bruno Almeida do Lago
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, April 15, 2006 4:11 PM
To: Jim C. Nasby
Cc: Bruce Momjian; Bruno Almeida do Lago; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Control File
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> Perhaps an easy means would be to put a PG_VERSION file in each
> tablespace when it's created and then check all of them. Tablespaces
> arguably make it slightly easier to accidentally try and mount something
> from a different version...
I believe we already do put a PG_VERSION file in each tablespace, but
AFAIR there is no subsequent check on their contents.
regards, tom lane
"Bruno Almeida do Lago" <teolupus@gmail.com> writes:
> With the new tablespace scenario in mind, how do you see this check feature
> being implemented: as a script or inside the code?
I'd put it in the code, and share code somehow with the current check on
$PGDATA/PG_VERSION (maybe factor that into a subroutine that can be
pointed at different directories).
regards, tom lane