Thread: postgreSQL : duplicate DB names
Hi
Something wrong happened in our server, for some strange and unknown reason (s) the server created another DB, there is two same DB names in one server. How I can I fix this. Please help
Name | Owner | Encoding
------------+----------+----------
a9004 | postgres | UNICODE
a9004 | postgres | UNICODE
CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Khangelani Gama, 26.05.2014 09:20: > Hi > > Something wrong happened in our server, for some strange and unknown reason (s) the server created another DB, > there is two same DB names in one server. > > Name | Owner | Encoding > ---------+----------+---------- > a9004 | postgres | UNICODE > a9004 | postgres | UNICODE > Maybe there are some trailing spaces at one of the names. What do you see when you run this: select '"'||datname||'"' as name from pg_database By adding the quotes, you can see if there are any trailing (or leading) in the name. Regards Thomas
Hi,
I get:
name
--------------
"a9004"
"template1"
"template0"
"a9004"
(4 rows)
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Thomas Kellerer
Sent: Monday, May 26, 2014 9:42 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgreSQL : duplicate DB names
Khangelani Gama, 26.05.2014 09:20:
> Hi
>
> Something wrong happened in our server, for some strange and unknown
> reason (s) the server created another DB, there is two same DB names in
> one server.
>
> Name | Owner | Encoding
> ---------+----------+----------
> a9004 | postgres | UNICODE
> a9004 | postgres | UNICODE
>
Maybe there are some trailing spaces at one of the names.
What do you see when you run this:
select '"'||datname||'"' as name
from pg_database
By adding the quotes, you can see if there are any trailing (or leading) in
the name.
Regards
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by
anyone
other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer
immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no
liability
for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
On 26 May 2014, at 10:09, Khangelani Gama <kgama@argility.com> wrote:
>> Khangelani Gama, 26.05.2014 09:20:
>>> Hi
>>>
>>> Something wrong happened in our server, for some strange and unknown
>>> reason (s) the server created another DB, there is two same DB names in
>>> one server.
>>>
>>> Name | Owner | Encoding
>>> ---------+----------+----------
>>> a9004 | postgres | UNICODE
>>> a9004 | postgres | UNICODE
>>>
>>
>> Maybe there are some trailing spaces at one of the names.
>>
>> What do you see when you run this:
>>
>> select '"'||datname||'"' as name
>> from pg_database
>>
>> By adding the quotes, you can see if there are any trailing (or leading) in
>> the name.
>>
>> Regards
>> Thomas
> Hi,
>
> I get:
>
> name
> --------------
> "a9004"
> "template1"
> "template0"
> "a9004"
> (4 rows)
Looks like you might have some index corruption, do those databases have the same oid?:
select datname, oid from pg_database
What version of the database are you running?
What OS?
Did anything of notice happen on that server before this showed up?
P.S. Please don’t top-post on this list.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.
-----Original Message-----
From: Alban Hertroys [mailto:haramrae@gmail.com]
Sent: Monday, May 26, 2014 10:21 AM
To: Khangelani Gama
Cc: Thomas Kellerer; pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgreSQL : duplicate DB names
On 26 May 2014, at 10:09, Khangelani Gama <kgama@argility.com> wrote:
>> Khangelani Gama, 26.05.2014 09:20:
>>> Hi
>>>
>>> Something wrong happened in our server, for some strange and unknown
>>> reason (s) the server created another DB, there is two same DB names
>>> in one server.
>>>
>>> Name | Owner | Encoding
>>> ---------+----------+----------
>>> a9004 | postgres | UNICODE
>>> a9004 | postgres | UNICODE
>>>
>>
>> Maybe there are some trailing spaces at one of the names.
>>
>> What do you see when you run this:
>>
>> select '"'||datname||'"' as name
>> from pg_database
>>
>> By adding the quotes, you can see if there are any trailing (or
>> leading) in the name.
>>
>> Regards
>> Thomas
> Hi,
>
> I get:
>
> name
> --------------
> "a9004"
> "template1"
> "template0"
> "a9004"
> (4 rows)
Looks like you might have some index corruption, do those databases have
the same oid?:
select datname, oid from pg_database
What version of the database are you running?
What OS?
Did anything of notice happen on that server before this showed up?
P.S. Please don't top-post on this list.
Alban Hertroys
--
If you can't see the forest for the trees, cut the trees and you'll find
there is no forest.
It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike
datname | oid
------------+------------
a9004 | 4215680815
template1 | 1
template0 | 17229
a9004 | 1335509510
(4 rows)
CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by
anyone
other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer
immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no
liability
for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
On 26 May 2014, at 10:28, Khangelani Gama <kgama@argility.com> wrote: > On 26 May 2014, at 10:09, Khangelani Gama <kgama@argility.com> wrote: >>> Khangelani Gama, 26.05.2014 09:20: >>>> Hi >>>> >>>> Something wrong happened in our server, for some strange and unknown >>>> reason (s) the server created another DB, there is two same DB names >>>> in one server. >>>> >>>> Name | Owner | Encoding >>>> ---------+----------+---------- >>>> a9004 | postgres | UNICODE >>>> a9004 | postgres | UNICODE >>>> >>> >>> Maybe there are some trailing spaces at one of the names. >>> >>> What do you see when you run this: >>> >>> select '"'||datname||'"' as name >>> from pg_database >>> >>> By adding the quotes, you can see if there are any trailing (or >>> leading) in the name. >>> >>> Regards >>> Thomas > >> Hi, >> >> I get: >> >> name >> -------------- >> "a9004" >> "template1" >> "template0" >> "a9004" >> (4 rows) > > Looks like you might have some index corruption, do those databases have > the same oid?: > > select datname, oid from pg_database > > What version of the database are you running? > What OS? > Did anything of notice happen on that server before this showed up? > > P.S. Please don't top-post on this list. > > Alban Hertroys > -- > If you can't see the forest for the trees, cut the trees and you'll find > there is no forest. > > > > It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike > > datname | oid > ------------+------------ > a9004 | 4215680815 > template1 | 1 > template0 | 17229 > a9004 | 1335509510 > (4 rows) 8.0.4? That went EOL almost 4 years ago and even then you should be running a version around 8.0.26. You’re 22 bugfix releasesbehind, which likely includes fixes for several data-corruption issues. See: http://www.postgresql.org/support/versioning/ If that database contains anything of value, plan some time for an upgrade to a more recent - supported - version. Take your time to read the release notes though, as there have been some changes that probably affect you; the change thatdisabled implicit type-casting between several types of dubious compatibility, for example. That said, from the look of things your duplicate databases do have different oid’s, meaning they are different objects inyour database. Are you sure they don’t just _look_ identical due to case-folding or something? Before you start medling with it, make sure you have a recent database dump. Aside from giving you something to restore if things go sideways, the contents of that dump could be illuminating as to whatis actually going on here. Can you find both instances of the CREATE DATABASE statements for those databases? The first thing to try with that dump is whether the situation can be restored on a test-system. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.
-----Original Message----- From: Alban Hertroys [mailto:haramrae@gmail.com] Sent: Monday, May 26, 2014 11:11 AM To: Khangelani Gama Cc: Thomas Kellerer; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgreSQL : duplicate DB names On 26 May 2014, at 10:28, Khangelani Gama <kgama@argility.com> wrote: > On 26 May 2014, at 10:09, Khangelani Gama <kgama@argility.com> wrote: >>> Khangelani Gama, 26.05.2014 09:20: >>>> Hi >>>> >>>> Something wrong happened in our server, for some strange and >>>> unknown reason (s) the server created another DB, there is two same >>>> DB names in one server. >>>> >>>> Name | Owner | Encoding >>>> ---------+----------+---------- >>>> a9004 | postgres | UNICODE >>>> a9004 | postgres | UNICODE >>>> >>> >>> Maybe there are some trailing spaces at one of the names. >>> >>> What do you see when you run this: >>> >>> select '"'||datname||'"' as name >>> from pg_database >>> >>> By adding the quotes, you can see if there are any trailing (or >>> leading) in the name. >>> >>> Regards >>> Thomas > >> Hi, >> >> I get: >> >> name >> -------------- >> "a9004" >> "template1" >> "template0" >> "a9004" >> (4 rows) > > Looks like you might have some index corruption, do those databases > have the same oid?: > > select datname, oid from pg_database > > What version of the database are you running? > What OS? > Did anything of notice happen on that server before this showed up? > > P.S. Please don't top-post on this list. > > Alban Hertroys > -- > If you can't see the forest for the trees, cut the trees and you'll > find there is no forest. > > > > It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike > > datname | oid > ------------+------------ > a9004 | 4215680815 > template1 | 1 > template0 | 17229 > a9004 | 1335509510 > (4 rows) 8.0.4? That went EOL almost 4 years ago and even then you should be running a version around 8.0.26. You're 22 bugfix releases behind, which likely includes fixes for several data-corruption issues. See: http://www.postgresql.org/support/versioning/ If that database contains anything of value, plan some time for an upgrade to a more recent - supported - version. Take your time to read the release notes though, as there have been some changes that probably affect you; the change that disabled implicit type-casting between several types of dubious compatibility, for example. That said, from the look of things your duplicate databases do have different oid's, meaning they are different objects in your database. Are you sure they don't just _look_ identical due to case-folding or something? Before you start medling with it, make sure you have a recent database dump. Aside from giving you something to restore if things go sideways, the contents of that dump could be illuminating as to what is actually going on here. Can you find both instances of the CREATE DATABASE statements for those databases? The first thing to try with that dump is whether the situation can be restored on a test-system. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. I try different things, Many Thanks CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.
Alban Hertroys <haramrae@gmail.com> writes: > On 26 May 2014, at 10:28, Khangelani Gama <kgama@argility.com> wrote: >> It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike > 8.0.4? That went EOL almost 4 years ago and even then you should be running a version around 8.0.26. You�re 22 bugfix releasesbehind, which likely includes fixes for several data-corruption issues. > See: http://www.postgresql.org/support/versioning/ 8.0.x did not have any real defenses against transaction ID wraparound (the behavior of forcing a shutdown when wraparound gets too close was added in 8.1, and there was no built-in autovacuum back then either). So my money is on this being a wraparound problem, ie, some dead but never-vacuumed pg_database row has returned to visibility because its deleting transaction is now "in the future". If you're very lucky, vacuuming pg_database will fix it. But take a backup *first*, in case you're not lucky and vacuuming makes things worse. I'd shut down the postmaster and then make a physical copy (tarball) of the whole data directory tree, just to be sure you can get back to where you are. As a former Red Hatter, I cannot resist making the point that your OS version is even hoarier than your PG version, and is certainly full of since-fixed bugs. You are *years* overdue for some serious attention to software updates. regards, tom lane
-----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, May 26, 2014 5:35 PM To: Alban Hertroys Cc: Khangelani Gama; Thomas Kellerer; pgsql-general@postgresql.org Subject: Re: [GENERAL] postgreSQL : duplicate DB names Alban Hertroys <haramrae@gmail.com> writes: > On 26 May 2014, at 10:28, Khangelani Gama <kgama@argility.com> wrote: >> It's psql 8.0.4, OS is Red Hat Linux release 9 (Shrike > 8.0.4? That went EOL almost 4 years ago and even then you should be running a version around 8.0.26. You're 22 bugfix releases behind, which likely includes fixes for several data-corruption issues. > See: http://www.postgresql.org/support/versioning/ 8.0.x did not have any real defenses against transaction ID wraparound (the behavior of forcing a shutdown when wraparound gets too close was added in 8.1, and there was no built-in autovacuum back then either). So my money is on this being a wraparound problem, ie, some dead but never-vacuumed pg_database row has returned to visibility because its deleting transaction is now "in the future". If you're very lucky, vacuuming pg_database will fix it. But take a backup *first*, in case you're not lucky and vacuuming makes things worse. I'd shut down the postmaster and then make a physical copy (tarball) of the whole data directory tree, just to be sure you can get back to where you are. As a former Red Hatter, I cannot resist making the point that your OS version is even hoarier than your PG version, and is certainly full of since-fixed bugs. You are *years* overdue for some serious attention to software updates. regards, tom lane Thanks a lot Tom, And Thanks to everyone who responded. It looks like the best option we have is to restore from the latest backup. And then plan to do the upgrade in the near future. CONFIDENTIALITY NOTICE The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.