Thread: pg_restore problem with 7.3.1
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
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
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
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
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
Not receiving any admin messages as of now...this message is for troubleshooting only. Please ignore. -- Kevin Brown kevin@sysexperts.com
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