Re: postgreSQL : duplicate DB names

On 26 May 2014, at 10:28, Khangelani Gama <kgama(at)argility(dot)com> wrote:

> On 26 May 2014, at 10:09, Khangelani Gama <kgama(at)argility(dot)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.