Databas has no Object Identifier Types & Functions

On Tue, Sep 4, 2012 at 3:58 PM, Rebecca Clarke <r.clarke83@gmail.com> wrote:

Hi there,

I'm running postgresql 8.4 on a debian system. I have a database that has
no object identifier types and functions in the pg_catalog,
e.g. regclass, regclassout.

Are you sure you don't have them ? I thought regclass is quite old, but I
may be wrong. What does the following query returns ?

SELECT * FROM pg_type WHERE typname = 'regclass';

Thanks,
Pavan

Returns 0 rows.

On Tue, Sep 4, 2012 at 11:41 AM, Pavan Deolasee <pavan.deolasee@gmail.com>wrote:

On Tue, Sep 4, 2012 at 3:28 AM, Rebecca Clarke <r.clarke83@gmail.com> wrote:

Hi there,

I'm running postgresql 8.4 on a debian system. I have a database that has
no object identifier types and functions in the pg_catalog,
e.g. regclass, regclassout.

What's the best way to get these in to this database? I don't want to
upgrade postgresql.
There are other databases on the server that do have them.

Ouch.

First thign I would do is check and make sure they are in template1.

Then I would probably rename the db (just in case), create a new db, and
use psql and pg_dump to move the old data into the freshly created one.

There may be ways to restore just these types but I would worry about what
else is missing and this seems to me to be the safest course of action
if/when you can afford the downtime.

If they are not in template1, create the database from template0. You may
want to recreate template1 in that case also.

Best Wishes,
Chris Travers

On Tue, Sep 4, 2012 at 6:57 AM, Rebecca Clarke <r.clarke83@gmail.com> wrote:

Returns 0 rows.

how in the world did you get yourself in that situation?

merlin