Thread: pg_restore problem with 7.3.1

pg_restore problem with 7.3.1

From
Ian Burrell
Date:
I am upgrading my PostgreSQL install from 7.2.3 to 7.3.1.  I ran into a
problem with pg_restore creating the database.  I am not sure if this is
a documentation problem or a real bug.  I have one database with BLOBs
and used tar format backup: "pg_dump -Ft -b mpmx > backup.tar".

I saw the -C option for pg_restore to create the database.  The man page
also says that with the -C option, the -d option specifies the database
create the database from

pg_restore -C -d template1 backup.tar

Instead of creating the mpmx database, the command loaded everything
into the template1 database.

Is the man page correct about the operation of the -C option?  Should I
have run things differently?  I noticed that without the -d option,
pg_restore spits out a script to restore the database.  Should I create
the database manually and restore it by piping pg_restore into psql?
How can I return template1 to its initial state?

  - Ian


Re: pg_restore problem with 7.3.1

From
Jeff Ross
Date:
On Fri, 31 Jan 2003, Ian Burrell wrote:

> How can I return template1 to its initial state?
>
>   - Ian
>
I know how to return template1 to its initial state.

http://techdocs.postgresql.org/techdocs/pgsqladventuresep1.php

Sorry, I can't help with your other questions.

Good luck!
--
Jeff Ross
Open Vistas Networking, Inc.
http://www.openvistas.net


Re: pg_restore problem with 7.3.1

From
Tom Lane
Date:
Ian Burrell <ib@onsitetech.com> writes:
> ...and used tar format backup: "pg_dump -Ft -b mpmx > backup.tar".
> pg_restore -C -d template1 backup.tar
> Instead of creating the mpmx database, the command loaded everything
> into the template1 database.

I tried to replicate this, and could not: the restore went into the
expected database.  (But I did notice that pg_restore needed to be
explicitly told -Ft, which seems less than bright of it.)  Are you
sure those are the exact commands you issued?  Were you using the 7.3
versions of pg_dump and pg_restore?

> How can I return template1 to its initial state?

Drop and recreate it --- see recipe for this procedure on techdocs.
Or you could just re-initdb.

            regards, tom lane

Re: pg_restore problem with 7.3.1

From
Ian Burrell
Date:
Tom Lane wrote:
>
> I tried to replicate this, and could not: the restore went into the
> expected database.  (But I did notice that pg_restore needed to be
> explicitly told -Ft, which seems less than bright of it.)  Are you
> sure those are the exact commands you issued?  Were you using the 7.3
> versions of pg_dump and pg_restore?
>

I just recreated the problem with a slightly different command.  The
problem was caused by using a list file to skip loading some function
definitions that were causing problems.  The command that loaded
everything into template1 was:

pg_restore -C -d template1 -L mpmx.lst -Ft mpmx.tar

The list file was created without doing a
It looks like the CREATE DATABASE call isn't done unless the -C flag is
included both when the list file is created and when doing the restore.
This works correctly:

pg_restore -C -l -Ft mpmx.tar > mpmx.lst
pg_restore -C -d mpmx -L mpmx.lst -Ft mpmx.tar

It would be good if pg_restore warned if the -C flag was included on the
list file generation or the

  - Ian



Re: pg_restore problem with 7.3.1

From
Tom Lane
Date:
Ian Burrell <ib@onsitetech.com> writes:
> I just recreated the problem with a slightly different command.  The
> problem was caused by using a list file to skip loading some function
> definitions that were causing problems.  The command that loaded
> everything into template1 was:

> pg_restore -C -d template1 -L mpmx.lst -Ft mpmx.tar

> It looks like the CREATE DATABASE call isn't done unless the -C flag is
> included both when the list file is created and when doing the restore.

Yeah, that seems to make sense.  I observe that -C causes an additional
entry to be made in the list file:

$ pg_restore -l r.tar >r.lst
$ pg_restore -C -l r.tar >r.lstc
$ diff r.lst r.lstc
13a14
> 1; 0 DATABASE regression postgres

and it makes sense that the actual CREATE DATABASE command would be
issued when that TOC entry is processed (if -C was given).

Offhand it seems to me that -l should produce this list-file entry
always, regardless of -C; and perhaps "pg_restore -C -L" should
complain if it doesn't find a DATABASE entry in the list (though I'm
unsure how hard that is to implement).

Philip, any comments?  Do you have time to fix this?

            regards, tom lane

Re: pg_restore problem with 7.3.1

From
Kevin Brown
Date:
Not receiving any admin messages as of now...this message is for
troubleshooting only.  Please ignore.


--
Kevin Brown                          kevin@sysexperts.com

Re: pg_restore problem with 7.3.1

From
Bruce Momjian
Date:
Did this ever get addressed?

---------------------------------------------------------------------------

Tom Lane wrote:
> Ian Burrell <ib@onsitetech.com> writes:
> > I just recreated the problem with a slightly different command.  The
> > problem was caused by using a list file to skip loading some function
> > definitions that were causing problems.  The command that loaded
> > everything into template1 was:
>
> > pg_restore -C -d template1 -L mpmx.lst -Ft mpmx.tar
>
> > It looks like the CREATE DATABASE call isn't done unless the -C flag is
> > included both when the list file is created and when doing the restore.
>
> Yeah, that seems to make sense.  I observe that -C causes an additional
> entry to be made in the list file:
>
> $ pg_restore -l r.tar >r.lst
> $ pg_restore -C -l r.tar >r.lstc
> $ diff r.lst r.lstc
> 13a14
> > 1; 0 DATABASE regression postgres
>
> and it makes sense that the actual CREATE DATABASE command would be
> issued when that TOC entry is processed (if -C was given).
>
> Offhand it seems to me that -l should produce this list-file entry
> always, regardless of -C; and perhaps "pg_restore -C -L" should
> complain if it doesn't find a DATABASE entry in the list (though I'm
> unsure how hard that is to implement).
>
> Philip, any comments?  Do you have time to fix this?
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073