Odd duplicate database

Started by Madison Kellyabout 17 years ago10 messagesgeneral

linux@alteeve.com

about 17 years ago

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:

So I tried to drop the database(s?) from the shell:

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

linux@alteeve.com

about 17 years ago

In reply to: Madison Kelly (#1)

Re: Odd duplicate database

Forgot to mention, this is PostgreSQL 8.3.5 on Linux (Ubuntu 8.10, hey,
it's a devel machine!). :)

Madi

Madison Kelly wrote:

Show quoted text

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

Alvaro Herrera

alvherre@2ndquadrant.com

about 17 years ago

In reply to: Madison Kelly (#1)

Re: Odd duplicate database

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.

Alvaro Herrera

alvherre@2ndquadrant.com

about 17 years ago

In reply to: Madison Kelly (#2)

Re: Odd duplicate database

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

Madison Kelly

linux@alteeve.com

about 17 years ago

In reply to: Alvaro Herrera (#4)

Re: Odd duplicate database

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

Tom Lane

tgl@sss.pgh.pa.us

about 17 years ago

In reply to: Madison Kelly (#5)

Re: Odd duplicate database

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

Madison Kelly

linux@alteeve.com

about 17 years ago

In reply to: Tom Lane (#6)

Re: Odd duplicate database

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?

Tom Lane

tgl@sss.pgh.pa.us

about 17 years ago

In reply to: Madison Kelly (#7)

Re: Odd duplicate database

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

Madison Kelly

linux@alteeve.com

about 17 years ago

In reply to: Tom Lane (#8)

Re: Odd duplicate database

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

#10

Alvaro Herrera

alvherre@2ndquadrant.com

about 17 years ago

In reply to: Madison Kelly (#9)

Re: Odd duplicate database

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