Re: postgreSQL : duplicate DB names - Mailing list pgsql-general

-----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.