Thread: Dump / restore question
Hello, I have a dump file from "pg_dump dbname > dump_file" of about 115 Mb that takes a few seconds to create and takes several hours to restore using "psql dbname < dump_file". I can't imagine I'm doing this correctly! What am I missing? Garo. =-=-==-=-=-== Xapnet Internet Solutions 1501 Powell St., Suite N Emeryville, CA 94608 Tel - (510) 655-9771 Fax - (510) 655-9775 Web - http://www.xapnet.com
On Sun, Oct 27, 2002 at 13:49:27 -0800, Garo Hussenjian <garo@xapnet.com> wrote: > Hello, > > I have a dump file from "pg_dump dbname > dump_file" of about 115 Mb that > takes a few seconds to create and takes several hours to restore using "psql > dbname < dump_file". > > I can't imagine I'm doing this correctly! What am I missing? Enforcing constraints and updating indexes can take a lot of time. You might get a significant speed up by not creating indexes or enforcing constraints (foreign keys probably being the bigest worry) until after the data gets loaded.
Hello all, Happy New Year's Eve Eve! I am curious if anyone knows offhand how to dump/restore a database without indexes and how to dump/restore just the indexes without the database... I am asking because it is taking many hours to restore a 115 Meg dump file and I have to do it again tomorrow, while not having so many hours to do it! I have only primary key constraints and several indexes, but no foreign key constraints, so I'm mainly concerned with the indexes. I tried "pg_restore --rearrange dump_file" but I get the error: pg_restore: [archiver] input file does not appear to be a valid archive Is this because I did not dump with the -F (format) option? Do I need to use "pg_dump -F t" for tar format? Any help will greatly improve my chances of enjoying the New Year celebration having completed this task a little sooner! Garo. on 10/27/02 2:13 PM, Bruno Wolff III at bruno@wolff.to wrote: > On Sun, Oct 27, 2002 at 13:49:27 -0800, > Garo Hussenjian <garo@xapnet.com> wrote: >> Hello, >> >> I have a dump file from "pg_dump dbname > dump_file" of about 115 Mb that >> takes a few seconds to create and takes several hours to restore using "psql >> dbname < dump_file". >> >> I can't imagine I'm doing this correctly! What am I missing? > > Enforcing constraints and updating indexes can take a lot of time. > > You might get a significant speed up by not creating indexes or enforcing > constraints (foreign keys probably being the bigest worry) until after > the data gets loaded. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > =-=-==-=-=-== Xapnet Internet Solutions 1501 Powell St., Suite N Emeryville, CA 94608 Tel - (510) 655-9771 Fax - (510) 655-9775 Web - http://www.xapnet.com
Garo Hussenjian <garo@xapnet.com> writes:
> I tried "pg_restore --rearrange dump_file" but I get the error:
> pg_restore: [archiver] input file does not appear to be a valid archive
> Is this because I did not dump with the -F (format) option?
If you used no -F option, pg_dump defaults to a plain SQL-script dump,
which you must needs feed to psql to restore. pg_restore only works
with -Ft or -Fc output of pg_dump.
If you don't want to restore indexes, my advice would be to run pg_dump
twice with -s (schema only) and -a (data only) options. Then manually
edit the -s output to remove index definitions, along with anything else
you don't need. Then restore both parts via psql. This is low-tech but
gives you pretty complete control over what happens.
regards, tom lane
Update: This time I used: "pg_dump -F t -d db_name -f dump_file.tar" I no longer get the archiver error, but when restoring with: "pg_restore -r -x -v -F t -d db_name -f dump_file.tar" The process has been running for a half hour but barely (0.0 %CPU 0.0 %MEM 0:00.01 TIME)... Shouldn't there be more activity? If I log into the database using psql, there are no relations showing. Any ideas what I'm doing wrong? Garo. on 12/30/02 9:44 PM, Garo Hussenjian at garo@xapnet.com wrote: > Hello all, > > Happy New Year's Eve Eve! > > I am curious if anyone knows offhand how to dump/restore a database without > indexes and how to dump/restore just the indexes without the database... > > I am asking because it is taking many hours to restore a 115 Meg dump file > and I have to do it again tomorrow, while not having so many hours to do it! > I have only primary key constraints and several indexes, but no foreign key > constraints, so I'm mainly concerned with the indexes. > > I tried "pg_restore --rearrange dump_file" but I get the error: > > pg_restore: [archiver] input file does not appear to be a valid archive > > Is this because I did not dump with the -F (format) option? Do I need to use > "pg_dump -F t" for tar format? > > Any help will greatly improve my chances of enjoying the New Year > celebration having completed this task a little sooner! > > Garo. > > on 10/27/02 2:13 PM, Bruno Wolff III at bruno@wolff.to wrote: > >> On Sun, Oct 27, 2002 at 13:49:27 -0800, >> Garo Hussenjian <garo@xapnet.com> wrote: >>> Hello, >>> >>> I have a dump file from "pg_dump dbname > dump_file" of about 115 Mb that >>> takes a few seconds to create and takes several hours to restore using "psql >>> dbname < dump_file". >>> >>> I can't imagine I'm doing this correctly! What am I missing? >> >> Enforcing constraints and updating indexes can take a lot of time. >> >> You might get a significant speed up by not creating indexes or enforcing >> constraints (foreign keys probably being the bigest worry) until after >> the data gets loaded. >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >> > > =-=-==-=-=-== > > Xapnet Internet Solutions > 1501 Powell St., Suite N > Emeryville, CA 94608 > > Tel - (510) 655-9771 > Fax - (510) 655-9775 > Web - http://www.xapnet.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > =-=-==-=-=-== Xapnet Internet Solutions 1501 Powell St., Suite N Emeryville, CA 94608 Tel - (510) 655-9771 Fax - (510) 655-9775 Web - http://www.xapnet.com
Thanks, Tom I suppose if I made two copies of the -s dump I could alter one to create the tables and the other to create the indexes. I can then sandwich the -a dump between the others... Pretty low-tech indeed, but better than waiting 16 hours... What about the -r option to pg_restore? Shouldn't this have the same effect? It seems to be hanging indefinitely when I try to use it (see my second posting)... Garo. on 12/30/02 10:44 PM, Tom Lane at tgl@sss.pgh.pa.us wrote: > Garo Hussenjian <garo@xapnet.com> writes: >> I tried "pg_restore --rearrange dump_file" but I get the error: >> pg_restore: [archiver] input file does not appear to be a valid archive >> Is this because I did not dump with the -F (format) option? > > If you used no -F option, pg_dump defaults to a plain SQL-script dump, > which you must needs feed to psql to restore. pg_restore only works > with -Ft or -Fc output of pg_dump. > > If you don't want to restore indexes, my advice would be to run pg_dump > twice with -s (schema only) and -a (data only) options. Then manually > edit the -s output to remove index definitions, along with anything else > you don't need. Then restore both parts via psql. This is low-tech but > gives you pretty complete control over what happens. > > regards, tom lane > =-=-==-=-=-== Xapnet Internet Solutions 1501 Powell St., Suite N Emeryville, CA 94608 Tel - (510) 655-9771 Fax - (510) 655-9775 Web - http://www.xapnet.com
Hi All, When I try to download a copy of postgresql from this site: http://www.au.postgresql.org/sitess.html I keep getting this message: (for ftp://planetmirror.com/pub/postgresql/v7.3/postgresql-7.3.tar.gz) "The page cannot be displayed The page you are looking for is currently unavailable. The Web site might experiencing technical difficulties, or you may need to adjust your browser settings." and when I try the HTTP method for http://public.planetmirror.com/pub/postgresql/v7.3/postgresql-7.3.tar.gz I keep getting: (From PlanetMirror) The file or directory you requested - /pub/postgresql/v7.3/postgresql- 7.3.tar.gz - could not be found. Any ideas as to what it wrong? (BTW I seem to get these kinds of messages from a number of Postgresql mirrors - it's the oddest thing. I don't get these kinds of messages from other sites - like MySql for example.) Regards Goffredo