Thread: transitioning postgres oid

transitioning postgres oid

From
"Robert Abbate"
Date:
Hi. I had to re-install Postgres and I was wondering how to do I get the old OID numbers (which have all my databases) incorporated into the fresh installation? I tried just putting the old "base" directory in, but it doesn't work. Is there some other way to do this or is it even possible at all?
 
Please..any suggestions. Otherwise I lose my database data.
 
Thanks,
Robert
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.319 / Virus Database: 178 - Release Date: 1/29/02

Re: transitioning postgres oid

From
"Robert Abbate"
Date:
This doesn't work because the old databases are not accessible with the new
Postgres installation. I get the following error:


Connection to database '26955' failed.
FATAL 1:  Database "26955" does not exist in the system catalog.


Does anyone know how do you update the system catalog to include an old
Postges installation OID? I have old databases just sitting there and I
can't seem to include them in the new installation. I cannot include them in
the new installation to run pg_dump.

Thanks,
Robert





----- Original Message -----
From: "Thomas Beutin" <tyrone@laokoon.IN-Berlin.DE>
To: "Robert Abbate" <cogea@ectisp.net>
Sent: Tuesday, May 14, 2002 2:26 AM
Subject: Re: [GENERAL] transitioning postgres oid


> Hi,
>
> executing the shell command "pg_dump --help" shows the options
> of pg_dump available in Your installation. You should try to
> dump with the "-o" resp "--oids" option and inserting in the
> data into the fresh installation. Maybe this helps.
>
> -tb
>
> On Tue, May 14, 2002 at 01:26:47AM -0500, Robert Abbate wrote:
> > Hi. I had to re-install Postgres and I was wondering how to do I get the
old OID numbers (which have all my databases) incorporated into the fresh
installation? I tried just putting the old "base" directory in, but it
doesn't work. Is there some other way to do this or is it even possible at
all?
> >
> > Please..any suggestions. Otherwise I lose my database data.
> >
> > Thanks,
> > Robert
>
> --
> Thomas Beutin                             tb@laokoon.IN-Berlin.DE
> Beam me up, Scotty. There is no intelligent live down in Redmond.


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.319 / Virus Database: 178 - Release Date: 1/28/02


Re: transitioning postgres oid

From
Martijn van Oosterhout
Date:
On Tue, May 14, 2002 at 09:21:37AM -0500, Robert Abbate wrote:
> This doesn't work because the old databases are not accessible with the new
> Postgres installation. I get the following error:
>
>
> Connection to database '26955' failed.
> FATAL 1:  Database "26955" does not exist in the system catalog.
>
>
> Does anyone know how do you update the system catalog to include an old
> Postges installation OID? I have old databases just sitting there and I
> can't seem to include them in the new installation. I cannot include them in
> the new installation to run pg_dump.

The name,oid mapping is stored in pg_database which should have been kept.
If you're going to copy the datafiles directly, you have to copy the entire
tree incling the xlog and global directory. Without the xlog directory you
don't have a database.

Ofcourse, the most reliable method is pg_dump before and psql < dump after.

HTH,

> ----- Original Message -----
> From: "Thomas Beutin" <tyrone@laokoon.IN-Berlin.DE>
> To: "Robert Abbate" <cogea@ectisp.net>
> Sent: Tuesday, May 14, 2002 2:26 AM
> Subject: Re: [GENERAL] transitioning postgres oid
>
>
> > Hi,
> >
> > executing the shell command "pg_dump --help" shows the options
> > of pg_dump available in Your installation. You should try to
> > dump with the "-o" resp "--oids" option and inserting in the
> > data into the fresh installation. Maybe this helps.
> >
> > -tb
> >
> > On Tue, May 14, 2002 at 01:26:47AM -0500, Robert Abbate wrote:
> > > Hi. I had to re-install Postgres and I was wondering how to do I get the
> old OID numbers (which have all my databases) incorporated into the fresh
> installation? I tried just putting the old "base" directory in, but it
> doesn't work. Is there some other way to do this or is it even possible at
> all?
> > >
> > > Please..any suggestions. Otherwise I lose my database data.
> > >
> > > Thanks,
> > > Robert
> >
> > --
> > Thomas Beutin                             tb@laokoon.IN-Berlin.DE
> > Beam me up, Scotty. There is no intelligent live down in Redmond.
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.319 / Virus Database: 178 - Release Date: 1/28/02
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Canada, Mexico, and Australia form the Axis of Nations That
> Are Actually Quite Nice But Secretly Have Nasty Thoughts About America

Re: transitioning postgres oid

From
"Robert Abbate"
Date:
Actually, if I was able to get the old database up, I wouldn't needed to
re-install postgres again. That is my problem. I could not get the old
databases up and running at all. I had to do a fresh installation. So now
the old databases are just sitting there. Any ideas?


> On Tue, May 14, 2002 at 09:21:37AM -0500, Robert Abbate wrote:
> > This doesn't work because the old databases are not accessible with the
new
> > Postgres installation. I get the following error:
> >
> >
> > Connection to database '26955' failed.
> > FATAL 1:  Database "26955" does not exist in the system catalog.
> >
> >
> > Does anyone know how do you update the system catalog to include an old
> > Postges installation OID? I have old databases just sitting there and I
> > can't seem to include them in the new installation. I cannot include
them in
> > the new installation to run pg_dump.
>
> You do pg_dump with the old backend up, then restore to the new one.
> You can automate this by starting (say) the new backend on a different
> port:
>
> pg_dump -c database_name | psql -p new_port database_name
>
>
> A
> --
> ----
> Andrew Sullivan                               87 Mowat Avenue
> Liberty RMS                           Toronto, Ontario Canada
> <andrew@libertyrms.info>                              M6K 3E3
>                                          +1 416 646 3304 x110
>


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.319 / Virus Database: 178 - Release Date: 1/28/02


Re: transitioning postgres oid

From
Martijn van Oosterhout
Date:
On Tue, May 14, 2002 at 10:15:45AM -0500, Robert Abbate wrote:
> Actually, if I was able to get the old database up, I wouldn't needed to
> re-install postgres again. That is my problem. I could not get the old
> databases up and running at all. I had to do a fresh installation. So now
> the old databases are just sitting there. Any ideas?

If you are only upgrading minor versions (say 7.2 to 7.2.1) then you can
just replace the binaries and you're set. If you're doing a major upgrade
then a pg_dump is the only way.

Just confirm you still have a pg_database file, a pg_xlog directory and a
global directory.

I beleive postgres -D /path/to/db will work.

HTH,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Canada, Mexico, and Australia form the Axis of Nations That
> Are Actually Quite Nice But Secretly Have Nasty Thoughts About America

Re: transitioning postgres oid

From
Scott Marlowe
Date:
Dump your old database with a -o switch:

pg_dump -o dbname >database.sql

On Tue, 14 May 2002, Robert Abbate wrote:

> Hi. I had to re-install Postgres and I was wondering how to do I get the old OID numbers (which have all my
databases)incorporated into the fresh installation? I tried just putting the old "base" directory in, but it doesn't
work.Is there some other way to do this or is it even possible at all?  
>
> Please..any suggestions. Otherwise I lose my database data.
>
> Thanks,
> Robert
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.319 / Virus Database: 178 - Release Date: 1/29/02
>


Re: transitioning postgres oid

From
Andrew Sullivan
Date:
On Tue, May 14, 2002 at 10:15:45AM -0500, Robert Abbate wrote:
> Actually, if I was able to get the old database up, I wouldn't needed to
> re-install postgres again. That is my problem. I could not get the old
> databases up and running at all. I had to do a fresh installation. So now
> the old databases are just sitting there. Any ideas?

Ah, sorry.  I should have read farther down.  I guess someone else
offered a suggestion.  There was also an (apparently unreliable)
pg_upgrade tool that Bruce Momjian wrote -- it's in contrib/, but
I've never looked at it.

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110


Re: transitioning postgres oid

From
Scott Marlowe
Date:
Reinstall the old version of postgres to get a dump out of it.

On Tue, 14 May 2002, Robert Abbate wrote:

> Actually, if I was able to get the old database up, I wouldn't needed to
> re-install postgres again. That is my problem. I could not get the old
> databases up and running at all. I had to do a fresh installation. So now
> the old databases are just sitting there. Any ideas?
>
>
> > On Tue, May 14, 2002 at 09:21:37AM -0500, Robert Abbate wrote:
> > > This doesn't work because the old databases are not accessible with the
> new
> > > Postgres installation. I get the following error:
> > >
> > >
> > > Connection to database '26955' failed.
> > > FATAL 1:  Database "26955" does not exist in the system catalog.
> > >
> > >
> > > Does anyone know how do you update the system catalog to include an old
> > > Postges installation OID? I have old databases just sitting there and I
> > > can't seem to include them in the new installation. I cannot include
> them in
> > > the new installation to run pg_dump.
> >
> > You do pg_dump with the old backend up, then restore to the new one.
> > You can automate this by starting (say) the new backend on a different
> > port:
> >
> > pg_dump -c database_name | psql -p new_port database_name
> >
> >
> > A
> > --
> > ----
> > Andrew Sullivan                               87 Mowat Avenue
> > Liberty RMS                           Toronto, Ontario Canada
> > <andrew@libertyrms.info>                              M6K 3E3
> >                                          +1 416 646 3304 x110
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.319 / Virus Database: 178 - Release Date: 1/28/02
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


Re: transitioning postgres oid

From
Tom Lane
Date:
Andrew Sullivan <andrew@libertyrms.info> writes:
> On Tue, May 14, 2002 at 10:15:45AM -0500, Robert Abbate wrote:
>> Actually, if I was able to get the old database up, I wouldn't needed to
>> re-install postgres again. That is my problem. I could not get the old
>> databases up and running at all. I had to do a fresh installation. So now
>> the old databases are just sitting there. Any ideas?

> Ah, sorry.  I should have read farther down.  I guess someone else
> offered a suggestion.  There was also an (apparently unreliable)
> pg_upgrade tool that Bruce Momjian wrote -- it's in contrib/, but
> I've never looked at it.

pg_upgrade is unlikely to work if the input database is corrupt.

In any case, IMHO Robert ought to reinstall his old version and work out
the failure-to-start problem in that context.  Adding a version
discrepancy to the underlying problem isn't going to make his life
better.  After he's resurrected the old database, or at least gotten the
best pg_dump he can out of it, a version update would be a sensible
thing to do.

            regards, tom lane