Thread: transitioning postgres oid
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
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
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
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
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
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 >
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
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 >
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