Thread: Odd duplicate database
Hi all,
My devel server has some wierdness happening. I tried to drop the
database (reload from a copy from the production server) and I got this
weird error:
pg_dump: query returned more than one (2) pg_database entry for database
"nexxia"
So I logged in as postgres and checked, and sure enough:
template1=# \l
List of databases
Name | Owner | Encoding
------------+----------+----------
deadswitch | digimer | UTF8
nexxia | digimer | UTF8
nexxia | digimer | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
(6 rows)
So I tried to drop the database(s?) from the shell:
template1=# DROP DATABASE nexxia ;
DROP DATABASE
template1=# \l
List of databases
Name | Owner | Encoding
------------+----------+----------
deadswitch | digimer | UTF8
nexxia | digimer | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
(5 rows)
template1=# DROP DATABASE nexxia ;
ERROR: database "nexxia" does not exist
So I still have a phantom DB there. This is still true after stopping
and restarting the daemon, too. When I try to connect to the database I
get this:
template1=# \c nexxia
FATAL: database "nexxia" does not exist
Previous connection kept
Does this mean a connection is still open somewhere? If so, how did
it survive the daemon restarting? More specifically, how do I clear it?
Thanks!
Madi
Forgot to mention, this is PostgreSQL 8.3.5 on Linux (Ubuntu 8.10, hey, it's a devel machine!). :) Madi Madison Kelly wrote: > Hi all, > > My devel server has some wierdness happening. I tried to drop the > database (reload from a copy from the production server) and I got this > weird error: > > pg_dump: query returned more than one (2) pg_database entry for database > "nexxia" > > So I logged in as postgres and checked, and sure enough: > > template1=# \l > List of databases > Name | Owner | Encoding > ------------+----------+---------- > deadswitch | digimer | UTF8 > nexxia | digimer | UTF8 > nexxia | digimer | UTF8 > postgres | postgres | UTF8 > template0 | postgres | UTF8 > template1 | postgres | UTF8 > (6 rows) > > So I tried to drop the database(s?) from the shell: > > template1=# DROP DATABASE nexxia ; > DROP DATABASE > template1=# \l > List of databases > Name | Owner | Encoding > ------------+----------+---------- > deadswitch | digimer | UTF8 > nexxia | digimer | UTF8 > postgres | postgres | UTF8 > template0 | postgres | UTF8 > template1 | postgres | UTF8 > (5 rows) > > template1=# DROP DATABASE nexxia ; > ERROR: database "nexxia" does not exist > > So I still have a phantom DB there. This is still true after stopping > and restarting the daemon, too. When I try to connect to the database I > get this: > > template1=# \c nexxia > FATAL: database "nexxia" does not exist > Previous connection kept > > Does this mean a connection is still open somewhere? If so, how did it > survive the daemon restarting? More specifically, how do I clear it? > > Thanks! > > Madi >
Madison Kelly wrote: > Hi all, > > My devel server has some wierdness happening. I tried to drop the > database (reload from a copy from the production server) and I got this > weird error: I guess this is an old release, and you haven't been vacuuming pg_database regularly, yes? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Madison Kelly wrote: > Forgot to mention, this is PostgreSQL 8.3.5 on Linux (Ubuntu 8.10, hey, > it's a devel machine!). :) Huh. Please send along select xmin, xmax, ctid, cmin, cmax, datname from pg_database; -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote: > Madison Kelly wrote: >> Forgot to mention, this is PostgreSQL 8.3.5 on Linux (Ubuntu 8.10, hey, >> it's a devel machine!). :) > > Huh. > > Please send along > select xmin, xmax, ctid, cmin, cmax, datname from pg_database; template1=# select xmin, xmax, ctid, cmin, cmax, datname from pg_database; xmin | xmax | ctid | cmin | cmax | datname ------+------+--------+------+------+------------ 383 | 0 | (0,1) | 0 | 0 | template1 384 | 0 | (0,2) | 0 | 0 | template0 386 | 0 | (0,3) | 0 | 0 | postgres 659 | 0 | (0,10) | 0 | 0 | deadswitch 3497 | 3625 | (0,35) | 0 | 0 | nexxia (5 rows) Madi
Madison Kelly <linux@alteeve.com> writes:
> Alvaro Herrera wrote:
>> Please send along
>> select xmin, xmax, ctid, cmin, cmax, datname from pg_database;
> template1=# select xmin, xmax, ctid, cmin, cmax, datname from pg_database;
> xmin | xmax | ctid | cmin | cmax | datname
> ------+------+--------+------+------+------------
> 383 | 0 | (0,1) | 0 | 0 | template1
> 384 | 0 | (0,2) | 0 | 0 | template0
> 386 | 0 | (0,3) | 0 | 0 | postgres
> 659 | 0 | (0,10) | 0 | 0 | deadswitch
> 3497 | 3625 | (0,35) | 0 | 0 | nexxia
> (5 rows)
So the "nexxia" row did get updated at some point, and either that
transaction failed to commit or we've got some glitch that made this
row look like it didn't. Have you used any "ALTER DATABASE" commands
against nexxia?
regards, tom lane
Tom Lane wrote: > Madison Kelly <linux@alteeve.com> writes: >> Alvaro Herrera wrote: >>> Please send along >>> select xmin, xmax, ctid, cmin, cmax, datname from pg_database; > >> template1=# select xmin, xmax, ctid, cmin, cmax, datname from pg_database; >> xmin | xmax | ctid | cmin | cmax | datname >> ------+------+--------+------+------+------------ >> 383 | 0 | (0,1) | 0 | 0 | template1 >> 384 | 0 | (0,2) | 0 | 0 | template0 >> 386 | 0 | (0,3) | 0 | 0 | postgres >> 659 | 0 | (0,10) | 0 | 0 | deadswitch >> 3497 | 3625 | (0,35) | 0 | 0 | nexxia >> (5 rows) > > So the "nexxia" row did get updated at some point, and either that > transaction failed to commit or we've got some glitch that made this > row look like it didn't. Have you used any "ALTER DATABASE" commands > against nexxia? > > regards, tom lane > Nope. Beyond the occasional ALTER COLUMN (few and always completed), the only thing I do directly in the shell are pretty standard queries while working out my program. Even then, the database is dropped and recreated fairly regularly with backup copies from the server. Madi PS - If I've run into a PgSQL bug, is there anything I can provide to help?
Madison Kelly <linux@alteeve.com> writes:
> PS - If I've run into a PgSQL bug, is there anything I can provide to help?
A sequence that reproduces it would be the best thing ...
regards, tom lane
Tom Lane wrote: > Madison Kelly <linux@alteeve.com> writes: >> PS - If I've run into a PgSQL bug, is there anything I can provide to help? > > A sequence that reproduces it would be the best thing ... > > regards, tom lane I guess the trick is, I have no idea what's happened or what I did to cause it to happen... Any ideas I can try? Madi
Madison Kelly wrote: > Tom Lane wrote: >> Madison Kelly <linux@alteeve.com> writes: >>> PS - If I've run into a PgSQL bug, is there anything I can provide to help? >> >> A sequence that reproduces it would be the best thing ... > > I guess the trick is, I have no idea what's happened or what I did to > cause it to happen... Any ideas I can try? No ideas here ... Can you please find out the current Xid counter? I think pg_controldata should tell you. Also, can you restore the previous state of pg_database, the one before you deleted the old tuple? If not, maybe a pg_filedump of the table file (in the hope that it hasn't been vacuumed) could show something enlightening. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support