Thread: ERROR: "Database 'products', OID nnn, has disappeared from pg_database"

ERROR: "Database 'products', OID nnn, has disappeared from pg_database"

From
"Barnes, Sandy (Sandra)"
Date:
> Currently, we have been having a problem with some kind of corruption of
> the pg_database file.  We are using the database on a limited number of
> writes storage medium.  (Compact Flash Card)  So that we don't always
> write to the flash, and because we don't care about some of the data, part
> of the database is kept in RAM on a ram file system.  We have two
> databases on disk.  One database called config, the other the standard
> template1.  These are kept in /var/lib/pgsql_base_slave.  We have a
> properly init'ed database in /var/lib/pgsql/.  When postgresql starts up
> via the init scripts, we create a RAM file system on /var/lib/pgsql/base/,
> and then we create symlinks to the config and template1 databases.
>
>     The next set of steps is where we sometimes run into trouble.  At
> this point in the init script, we start up the postmaster.  Next we do a
> createdb products, where products is the actual name of the database.
> This works fine.  Next, we attempt to access the database using psql to
> create a set of tables.  This is where sometimes we run into a problem.
> Normally, this works fine, but sometimes we get the error
>
> "Database 'products', OID nnn, has disappeared from pg_database"
>
> where nnn is some number, and forever afterwards we can't access a
> database with that name.  Removing and recreating the database doesn't
> help.  Neither does vacuuming it.  If we delete the whole pgsql directory,
> and recreate it, then use initdb on it, everything is fresh and working
> again, but obviously this isn't a good solution, since we have to recreate
> all the databases, tables, and data.
>
>     On the shutdown side, we do a deletedb to remove the products
> database before we shutdown the postmaster.  Once that is complete, we do
> a shutdown.
>
> If you need a short list of the startup procedure that is above, here it
> is in a psuedocode fashion.
>
> mount ram drive.
> make symlinks to config and template1 databases
> startup postmaster
> createdb products
> create some tables in products
>
> ... normal operation ...
>
> ... shutdown process
> dropdb products
> shutdown postmaster.
>
>
>     I am assuming that some kind of corruption has occured within
> pg_database file, but I don't have the expertise to find where the problem
> is occuring.  Any help you can provide would be greatly appreciated.
>
>     I am including the init script used.
>  <<postgresql.txt>>
>
> Sandy Barnes
> Honeywell
> Business & General Aviation Engineering
> One Technology Center
> 23500 West 105th Street
> Olathe, KS.     66061
> tel         913.712.2027
> fax        913.712.1347
> email    sandy.barnes@honeywell.com
>

begin 600 postgresql
M(R$@+V)I;B]S:`HC('!O<W1G<F5S<6P)5&AI<R!I<R!T:&4@:6YI="!S8W)I
M<'0@9F]R('-T87)T:6YG('5P('1H92!0;W-T9W)E4U%,"B,)"7-E<G9E<@H*
M(R!697)S:6]N(#8N-2XS+3(@3&%M87(@3W=E;@HC($%D9&5D(&-O9&4@=&\@
M9&5T97)M:6YE(&EF(%!'1$%402!E>&ES=',L('=H971H97(@:70@:7,@8W5R
M<F5N="!V97)S:6]N"B,@("`@(&]R(&YO="P@86YD(&EN:71D8B!I9B!N;R!0
M1T1!5$$@*&EN:71D8B!W:6QL(&YO="!O=F5R=W)I=&4@82!D871A8F%S92DN
M"@HC(%9E<G-I;VX@-RXP($QA;7(@3W=E;@HC($%D9&5D(&QO9V=I;F<@8V]D
M90HC($-H86YG960@4$=$051!+@H*(R!C:&MC;VYF:6<Z(#,T-2`X-2`Q-0HC
M(&1E<V-R:7!T:6]N.B!3=&%R=',@86YD('-T;W!S('1H92!0;W-T9W)E4U%,
M(&)A8VME;F0@9&%E;6]N('1H870@:&%N9&QE<R!<"B,)("`@("`@(&%L;"!D
M871A8F%S92!R97%U97-T<RX*(R!P<F]C97-S;F%M93H@<&]S=&UA<W1E<@HC
M('!I9&9I;&4Z("]V87(O<G5N+W!O<W1M87-T97(N<&ED"B,*"B,@4V]U<F-E
M(&9U;F-T:6]N(&QI8G)A<GDN"BX@+V5T8R]R8RYD+VEN:70N9"]F=6YC=&EO
M;G,*"B,@1V5T(&-O;F9I9RX*+B`O971C+W-Y<V-O;F9I9R]N971W;W)K"@HC
M($-H96-K('1H870@;F5T=V]R:VEN9R!I<R!U<"X*(R!0<F5T='D@;75C:"!N
M965D(&ET(&9O<B!P;W-T;6%S=&5R+@I;("1[3D545T]22TE.1WT@/2`B;F\B
M(%T@)B8@97AI="`P"@I;("UF("]U<W(O;&]C86PO<&=S<6PO8FEN+W!O<W1M
M87-T97(@72!\?"!E>&ET(#`*"B,@5&AI<R!S8W)I<'0@:7,@<VQI9VAT;'D@
M=6YU<W5A;"!I;B!T:&%T('1H92!N86UE(&]F('1H92!D865M;VX@*'!O<W1M
M87-T97(I"B,@:7,@;F]T('1H92!S86UE(&%S('1H92!N86UE(&]F('1H92!S
M=6)S>7-T96T@*'!O<W1G<F5S<6PI"@H*"@HC(%-E92!H;W<@=V4@=V5R92!C
M86QL960N"F-A<V4@(B0Q(B!I;@H@('-T87)T*0H*"@EE8VAO("UN(")#:&5C
M:VEN9R!P;W-T9W)E<W%L(&EN<W1A;&QA=&EO;CH@(B`*"@DC"@DC"@DC("!-
M;W5N="!O=7(@8F%S92!204T@1')I=F4*"2,*"2,*"2,*"@ED9"!I9CTO9&5V
M+WIE<F\@;V8]+V1E=B]R86TQ(&)S/3%K(&-O=6YT/30P.38*"6UK93)F<R`M
M=FTP("]D978O<F%M,2`T,#DV"@EM;W5N="`O9&5V+W)A;3$@+W9A<B]L:6(O
M<&=S<6PO8F%S92\*"6-H;W=N('!O<W1G<F5S+G!O<W1G<F5S("]V87(O;&EB
M+W!G<W%L+V)A<V4*"2,*"2,*"2,@0W)E871E('-Y;6)O;&EC(&QI;FMS('1O
M('1H92!A<'!R;W!R:6%T92!D871A8F%S97,*"2,@=&AA="!A<F4@(G-U<'!O
M<V5D(B!T;R!B92!U;F1E<B!T:&4@8F%S92!D:7)E8W1O<GDN"@DC"@DC"@EL
M;B`M<R`O=F%R+VQI8B]P9W-Q;%]B87-E7W-L879E+V-O;F9I9R`O=F%R+VQI
M8B]P9W-Q;"]B87-E+V-O;F9I9PH);&X@+7,@+W9A<B]L:6(O<&=S<6Q?8F%S
M95]S;&%V92]T96UP;&%T93$@+W9A<B]L:6(O<&=S<6PO8F%S92]T96UP;&%T
M93$*"6QN("US("]V87(O;&EB+W!G<W%L7V)A<V5?<VQA=F4O<&]S=&=R97,@
M+W9A<B]L:6(O<&=S<6PO8F%S92]P;W-T9W)E<PH)8VAO=VX@<&]S=&=R97,N
M<&]S=&=R97,@+W9A<B]L:6(O<&=S<6PO8F%S92\J"@H)(R!#:&5C:R!F;W(@
M;VQD97(@4$=$051!(&QO8V%T:6]N+@H):68@6R`M9B`O=F%R+VQI8B]P9W-Q
M;"]01U]615)324].(%T@)B8@6R`M9"`O=F%R+VQI8B]P9W-Q;"]B87-E+W1E
M;7!L871E,2!="@ET:&5N"@D)97AP;W)T(%!'1$%403TO=F%R+VQI8B]P9W-Q
M;`H)96QS90H)"65X<&]R="!01T1!5$$]+W9A<B]L:6(O<&=S<6PO9&%T80H)
M9FD*"@DC($-H96-K(&9O<B!T:&4@4$=$051!('-T<G5C='5R90H):68@6R`M
M9B`D4$=$051!+U!'7U9%4E-)3TX@72`F)B!;("UD("101T1!5$$O8F%S92]T
M96UP;&%T93$@70H)=&AE;@H)(R!#:&5C:R!V97)S:6]N(&]F(&5X:7-T:6YG
M(%!'1$%400H*"0EI9B!;(&!C870@)%!'1$%402]01U]615)324].8"`A/2`G
M-RXP)R!="@D)=&AE;@H)"0EE8VAO(")O;&0@=F5R<VEO;BX@3F5E9"!T;R!5
M<&=R861E+B(*"0D)96-H;R`B4V5E("]U<W(O9&]C+W!O<W1G<F5S<6PM-RXP
M+U)%041-12YR<&T@9F]R(&UO<F4@:6YF;W)M871I;VXN(@H)"0EE>&ET(#$*
M"0EE;'-E"@D)"65C:&\@(FQO;VMS(&=O;V0A(@H)"69I"@H)(R!.;R!E>&ES
M=&EN9R!01T1!5$$A($EN:71D8B!I="X*"@EE;'-E"@D)96-H;R`B;F\@9&%T
M86)A<V4@9FEL97,@9F]U;F0N(@H@("`@("`@("`@("`@("`@:68@6R`A("UD
M("101T1!5$$@70H)"71H96X*"0D);6MD:7(@+7`@)%!'1$%400H)"0EC:&]W
M;B!P;W-T9W)E<RYP;W-T9W)E<R`D4$=$051!"@D)9FD*"0ES=2`M;"!P;W-T
M9W)E<R`M8R`G+W5S<B]L;V-A;"]P9W-Q;"]B:6XO:6YI=&1B("TM<&=L:6(]
M+W5S<B]L;V-A;"]P9W-Q;"]L:6(O("TM<&=D871A/2]V87(O;&EB+W!G<W%L
M+V1A=&$G"@EF:0H*"2,@0VAE8VL@9F]R('!O<W1M87-T97(@86QR96%D>2!R
M=6YN:6YG+BXN"@EP:60]8'!I9&]F('!O<W1M87-T97)@"@EI9B!;("1P:60@
M70H)=&AE;@H)"65C:&\@(E!O<W1M87-T97(@86QR96%D>2!R=6YN:6YG+B(*
M"65L<V4*"0DC86QL('-Y<W1E;7,@9V\@+2T@<F5M;W9E(&%N>2!S=&%L92!L
M;V-K(&9I;&5S"@D)<FT@+68@+W1M<"\N<RY01U-13"XJ(#X@+V1E=B]N=6QL
M"@D)96-H;R`M;B`B4W1A<G1I;F<@<&]S=&=R97-Q;"!S97)V:6-E.B`B"@D)
M<W4@+6P@<&]S=&=R97,@+6,@(B]U<W(O;&]C86PO<&=S<6PO8FEN+W!G7V-T
M;"`@+40@)%!'1$%402`M<"`O=7-R+VQO8V%L+W!G<W%L+V)I;B]P;W-T;6%S
M=&5R('-T87)T(#XO9&5V+VYU;&P@,CXF,2(*"0ES;&5E<"`Q"@D)<&ED/6!P
M:61O9B!P;W-T;6%S=&5R8`H)"6EF(%L@)'!I9"!="@D)=&AE;@H)"0EE8VAO
M("UN(")P;W-T;6%S=&5R(%LD<&ED72(*"0D)=&]U8V@@+W9A<B]L;V-K+W-U
M8G-Y<R]P;W-T9W)E<W%L"@D)"65C:&\@)'!I9"`^("]V87(O<G5N+W!O<W1M
M87-T97(N<&ED"@D)"65C:&\*"0EE;'-E"@D)"65C:&\@(F9A:6QE9"XB"@D)
M9FD*"69I"@H)(PH)(PH)(R!.;W<@=V4@8G5I;&0@=&AE('!R;V1U8W1S('1A
M8FQE<RX*"2,*"2,*"2,*"2,*"2,*"7-U("UL('!O<W1G<F5S("UC("<O=7-R
M+VQO8V%L+W!G<W%L+V)I;B]D<F]P9&(@<')O9'5C=',G"@ES=2`M;"!P;W-T
M9W)E<R`M8R`G+W5S<B]L;V-A;"]P9W-Q;"]B:6XO8W)E871E9&(@<')O9'5C
M=',G"@ES;&5E<"`S"@ES=2`M;"!P;W-T9W)E<R`M8R`G+W5S<B]L;V-A;"]P
M9W-Q;"]B:6XO<'-Q;"!P<F]D=6-T<R`M8R`B0U)%051%(%1!0DQ%('!R;V1U
M8W0@*'!R;V1U8W1?:60@:6YT-"!04DE-05)9($M%62P@9&5S=%]A9&1R97-S
M(&EN=#0L(&5X<&ER871I;VX@:6YT-"P@<V5N9%]I;G1E<G9A;"!I;G0T+"!P
M<FEO<FET>2!I;G0T*3LB)PH*"0H)<W4@+6P@<&]S=&=R97,@+6,@)R]U<W(O
M;&]C86PO<&=S<6PO8FEN+W!S<6P@<')O9'5C=',@+6,@(D-214%412!404),
M12!A<&1U("AA<&1U7VED(&EN=#0@4%))34%262!+15DL('!R;V1U8W1?:60@
M:6YT-"P@87!D=5]L96YG=&@@:6YT-"P@;&%S=%]T>%]T:6UE(&EN=#0L($-/
M3E-44D%)3E0@8V]N,2!&3U)%24=.($M%62`H<')O9'5C=%]I9"D@4D5&15)%
M3D-%4R!P<F]D=6-T*3LB)PH*"3L["B`@<W1O<"D*"65C:&\@+6X@(E-T;W!P
M:6YG('!O<W1G<F5S<6P@<V5R=FEC93H@(@H*"@DC"@DC(%=E(&YE960@=&\@
M=VAA8VL@=&AE('!R;V1U8W0@=&%B;&4@8F5F;W)E(&MI;&P@=&AI<R!P<F]C
M97-S(&]F9BX*"2,*"@ES=2`M;"!P;W-T9W)E<R`M8R`G+W5S<B]L;V-A;"]P
M9W-Q;"]B:6XO9')O<&1B('!R;V1U8W1S)PH*"6MI;&QP<F]C('!O<W1M87-T
M97(*"@DC"@DC(%1I;64@=&\@=6YM;W5N="!T:&4@8F%S92!D:7)E8W1O<GDN
M"@DC"@DC"@H)<VQE97`@,PH)=6UO=6YT("]V87(O;&EB+W!G<W%L+V)A<V4*
M"@H)<VQE97`@,@H)<FT@+68@+W9A<B]R=6XO<&]S=&UA<W1E<BYP:60*"7)M
M("UF("]V87(O;&]C:R]S=6)S>7,O<&]S=&=R97-Q;`H)96-H;PH*"@D[.PH@
M('-T871U<RD*"7-T871U<R!P;W-T;6%S=&5R"@D[.PH@(')E<W1A<G0I"@DD
M,"!S=&]P"@DD,"!S=&%R=`H).SL*("`J*0H)96-H;R`B57-A9V4Z('!O<W1G
M<F5S<6P@>W-T87)T?'-T;W!\<W1A='5S?')E<W1A<G1](@H)97AI="`Q"F5S
+86,*"F5X:70@,`H=
`
end

Re: ERROR: "Database 'products', OID nnn, has disappeared from pg_database"

From
Tom Lane
Date:
"Barnes, Sandy (Sandra)" <Sandy.Barnes@Honeywell.com> writes:
>> Normally, this works fine, but sometimes we get the error
>>
>> "Database 'products', OID nnn, has disappeared from pg_database"
>>
>> where nnn is some number, and forever afterwards we can't access a
>> database with that name.  Removing and recreating the database doesn't
>> help.  Neither does vacuuming it.

Try vacuuming pg_database (after connecting to template1 or the other
always-there db).  7.1 will be a little smarter about this, but the
setup you describe is still pretty risky IMHO.  You have a nonvolatile
reference in pg_database to a volatile database in the RAM filesystem,
so a system crash will leave you with a dangling reference...

            regards, tom lane