transitioning postgres oid

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/

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

Dump your old database with a -o switch:

pg_dump -o dbname >database.sql

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

Reinstall the old version of postgres to get a dump out of it.

On Tue, 14 May 2002, Robert Abbate wrote:

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

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