pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Daniel Rubio <drubior@tinet.org>
To: juanmime@ono.com
To: pgsql-admin@postgresql.org
Subject: Re: Upgrade problems with OID type ...
Date: Thu, 08 Jul 2004 12:11:41 +0200
Message-ID: <40ED1DDD.2050509@tinet.org> (raw)
In-Reply-To: <40DA395400006BC8@resmta01.ono.com>
References: <40DA395400006BC8@resmta01.ono.com>


juanmime@ono.com wrote:

> Hello Daniel,
> 
> Have you tried using the -Ft option when doing the dump, and pg_restore
> instead of psql ?

I've seen the thread you started with a similar problem,  and it has 
been useful to solve the problem with the concrete database, but my 
problem is that we host 20 different databases (and growing)for hosting 
clients and we don't know who's using OID's and who's not.

I think that the Chapter 9.3 (Migration between releases) would have to 
concrete that the OIDs doesn't work with the method explained (despite 
pg_dumpall has the -o option) and how to make the migration process in 
this case.

I have not encoutered too much documentation about it, and finally I've 
followed this process (could be useful for someone):


1.-Search the databases having OIDs ( \dl command on each one), this is 
easy to automate via shell script ( but I did'nt make it).

2.-With the pg_dumplo utility (contrib directory), backup the OID 
objects for each database discovered in 1

../../pg-7.4.3/bin/pg_dumplo -s /tmp/dumpoids -d database_name -e

3.-Run the pg_dumpall

../bin/pg_dumpall -o -c -p 5432  | /apps/pg-7.4.3/bin/psql -d template1 
-p 5433 1>/tmp/sortidaimport 2>/tmp/sortidaerror

4.-Import the OIDs to the new postgres using pg_dumplo (for each 
database with OIDs)

../../pg-7.4.3/bin/pg_dumplo -s /tmp/dumpoids -d pragenda -o 5433 -i

It worked for me ...

If anyone knows a better, faster or automatic process to perform the 
upgrade I'll be pleased to know it ...

And sorry for my poor english ...

> 
> 
>>-- Mensaje original --
>>Date: Tue, 06 Jul 2004 11:06:25 +0200
>>From: Daniel Rubio <drubior@tinet.org>
>>To: pgsql-admin@postgresql.org
>>Subject: [ADMIN] Upgrade problems with OID type ...
>>
>>
>>Hi all!
>>
>>I'm trying to upgrade from 7.3.2 to 7.4.3 but I've a problem with a 
>>database (1 I know, there could be more) containing a table with an oid
> 
> 
>>type field.
>>
>>I run the migration doing:
>>
>>../bin/pg_dumpall -o -p 5432 | /apps/pgs-7.4.3/bin/psql -d template1 -p
> 
> 
>>5433 1>/tmp/out 2>/tmp/err
>>
>>The command runs fine, with no ERRORS on the output files, but when the
> 
> 
>>user accesses their application (web) the images he was storing does'nt
> 
> 
>>appear, and I can see errors like this on the logfile:
>>
>>2004-07-06 09:54:52 ERROR:  large object 417988 does not exist
>>
>>First I was doing the dump without the -o, but now I use it and the 
>>problem persists ... Ideas?
>>
>>Thanks in advance ...
>>-- 
>>********************************************************
>>Daniel Rubio Rodríguez
>>OASI (Organisme Autònom Per la Societat de la Informació)
>>c/ Assalt, 12
>>43003 - Tarragona
>>Tef.: 977.244.007 - Fax: 977.224.517
>>e-mail: drubio a oasi.org
>>********************************************************
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>>   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 
> 
> 
> 
> 


-- 
********************************************************
Daniel Rubio Rodríguez
OASI (Organisme Autònom Per la Societat de la Informació)
c/ Assalt, 12
43003 - Tarragona
Tef.: 977.244.007 - Fax: 977.224.517
e-mail: drubio a oasi.org
********************************************************



view thread (2+ messages)

Message-ID: <40ED1DDD.2050509@tinet.org>
Permalink:  ../40ED1DDD.2050509@tinet.org/
Also on:    postgresql.org/message-id/40ED1DDD.2050509@tinet.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: drubior@tinet.org, juanmime@ono.com
  Subject: Re: Upgrade problems with OID type ...
  In-Reply-To: <40ED1DDD.2050509@tinet.org>
  References: <40DA395400006BC8@resmta01.ono.com> <40ED1DDD.2050509@tinet.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox