Thread: pg_restore problem

I'm attempting to restore a dump from one server to another (one is a
Mac and one is a Linux base, if that makes any difference).  I keep
running into issues like this:

pg_restore: [archiver (db)] could not execute query: ERROR:  function
public.random_page_link_id_gen() does not exist

This is what I'm using to restore the files with:

pg_restore -O -x -s -N -d nuvio mac_postgres_2_2_2005_13_24

Any suggestions on how to get around this problem?  It's a huge pain so
far just to sync my two servers up.

Bradley Miller
NUVIO CORPORATION
Phone: 816-444-4422 ext. 6757
Fax: 913-498-1810
http://www.nuvio.com
bmiller@nuvio.com

Bradley Miller <bmiller@nuvio.com> writes:
> I'm attempting to restore a dump from one server to another (one is a 
> Mac and one is a Linux base, if that makes any difference).  I keep 
> running into issues like this:

> pg_restore: [archiver (db)] could not execute query: ERROR:  function 
> public.random_page_link_id_gen() does not exist

Is this a problem of items in the dump being in the wrong order (ie,
there's a forward reference to random_page_link_id_gen())?

> Any suggestions on how to get around this problem?

Use 8.0 ... or use pg_restore's -L/-l options to manually adjust the
load order.  Pre-8.0 versions of pg_dump are easily fooled if you use
ALTER to make earlier-created objects reference later-created objects.
        regards, tom lane

So in the current version I'm running (7.4.6) and I do a pg_dump I have
to then manually manipulate the order by doing a -l to get a table of
contents and then reorder (just changing the first number; or the oid
also??) just to get it to work right?   Does anyone else have these
issues?  How exactly can I use this on a mission critical app with
flaws like this?   How do other people work with this?  Do they just
not dump the files and restore?


On Feb 2, 2005, at 3:24 PM, Tom Lane wrote:

> Bradley Miller <bmiller@nuvio.com> writes:
>> I'm attempting to restore a dump from one server to another (one is a
>> Mac and one is a Linux base, if that makes any difference).  I keep
>> running into issues like this:
>
>> pg_restore: [archiver (db)] could not execute query: ERROR:  function
>> public.random_page_link_id_gen() does not exist
>
> Is this a problem of items in the dump being in the wrong order (ie,
> there's a forward reference to random_page_link_id_gen())?
>
>> Any suggestions on how to get around this problem?
>
> Use 8.0 ... or use pg_restore's -L/-l options to manually adjust the
> load order.  Pre-8.0 versions of pg_dump are easily fooled if you use
> ALTER to make earlier-created objects reference later-created objects.
>
>             regards, tom lane
>
>
Bradley Miller
NUVIO CORPORATION
Phone: 816-444-4422 ext. 6757
Fax: 913-498-1810
http://www.nuvio.com
bmiller@nuvio.com

Bradley Miller wrote:
> So in the current version I'm running (7.4.6) and I do a pg_dump I have 
> to then manually manipulate the order by doing a -l to get a table of 
> contents and then reorder (just changing the first number; or the oid 
> also??) just to get it to work right?   Does anyone else have these 
> issues?  How exactly can I use this on a mission critical app with flaws 
> like this?   How do other people work with this?  Do they just not dump 
> the files and restore?

The problem(s) are only apparent if you define/redefine objects in a 
certain order. I've tended to encounter them on databases where I've 
extensively reworked elements (particularly functions/views). In 
particular, dumping a restored database always seems OK for me.

With the -l file, you just need to cut & paste the lines into the 
correct order. In practice, I tend to just move half-a-dozen lines to 
the end of the file to get things to work. The crucial bit then is to 
make sure you keep a backup copy of the working order somewhere - you 
have no idea how often I've deleted the file as soon as I've finished 
restoring.

Of course, if you have dynamic functions in say perl/tcl and then base 
views on them there's probably no way for pg_dump to ever figure out the 
correct dependencies.

--  Richard Huxton  Archonet Ltd

Interestingly, I made a new database on my test server and then was
able to do a pg_dump from my mac box to the test server and I think it
got just about everything . . . I've got some constraint issues and
other oddities happening, but at least my functions came in fine.  I
used the pipe command to pipe it directly to the server rather than
using pg_restore.


Bradley Miller
NUVIO CORPORATION
Phone: 816-444-4422 ext. 6757
Fax: 913-498-1810
http://www.nuvio.com
bmiller@nuvio.com