pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump/restore problems
6+ messages / 4 participants
[nested] [flat]
* pg_dump/restore problems
@ 2010-02-15 18:25 Glen Brown <gkbrown22@gmail.com>
2010-10-26 15:33 ` Re: pg_dump/restore problems Bob Lunney <bob_lunney@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Glen Brown @ 2010-02-15 18:25 UTC (permalink / raw)
To: pgsql-admin
I am not sure where I should post this but I am running into problems trying
to restore a large table. I am running 8.4.1 on all servers. The table is
about 25gb in size and most of that is toasted. It has about 2.5m records.
When I dump this table using pg_dump -Fc it creates a 15 gb file. I am
trying to restore in into a database that has 100gb of free disk space and
it consumes it all and fails to finish the restore. The table is not
partitioned and has a few indexes on it. What can I do?
thanks
-glen
Glen Brown
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dump/restore problems
2010-02-15 18:25 pg_dump/restore problems Glen Brown <gkbrown22@gmail.com>
@ 2010-10-26 15:33 ` Bob Lunney <bob_lunney@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: Bob Lunney @ 2010-10-26 15:33 UTC (permalink / raw)
To: pgsql-admin@postgresql.org, Glen Brown <gkbrown22@gmail.com>
Glen,
Did you drop the indexes prior to the restore? If not, try doing so and recreating the indexes afterwards. That will also speed up the data load.
Bob Lunney
--- On Mon, 2/15/10, Glen Brown <gkbrown22@gmail.com> wrote:
From: Glen Brown <gkbrown22@gmail.com>
Subject: [ADMIN] pg_dump/restore problems
To: pgsql-admin@postgresql.org
Date: Monday, February 15, 2010, 1:25 PM
I am not sure where I should post this but I am running into problems trying to restore a large table. I am running 8.4.1 on all servers. The table is about 25gb in size and most of that is toasted. It has about 2.5m records. When I dump this table using pg_dump -Fc it creates a 15 gb file. I am trying to restore in into a database that has 100gb of free disk space and it consumes it all and fails to finish the restore. The table is not partitioned and has a few indexes on it. What can I do?
thanks
-glen
Glen Brown
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dump/restore problems
@ 2010-02-20 17:52 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-02-20 20:41 ` Re: pg_dump/restore problems Glen Brown <gkbrown22@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2010-02-20 17:52 UTC (permalink / raw)
To: gkbrown22@gmail.com; pgsql-admin
Glen Brown wrote:
> When I dump this table using pg_dump -Fc it creates a 15 gb file. I
> am trying to restore in into a database that has 100gb of free disk
> space and it consumes it all and fails to finish the restore.
What is the platform? (I remember having problems with large file
handling in PostgreSQL on Windows, back when I used Windows.)
Can you see where the space is going when this happens?
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dump/restore problems
2010-02-20 17:52 Re: pg_dump/restore problems Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-02-20 20:41 ` Glen Brown <gkbrown22@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Glen Brown @ 2010-02-20 20:41 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
I am using Ubuntu 8LTS on both systems. How can tell where the space is
going?
thanks for the help
-glen
Glen Brown
On Sat, Feb 20, 2010 at 9:52 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov
> wrote:
> Glen Brown wrote:
>
> > When I dump this table using pg_dump -Fc it creates a 15 gb file. I
> > am trying to restore in into a database that has 100gb of free disk
> > space and it consumes it all and fails to finish the restore.
>
> What is the platform? (I remember having problems with large file
> handling in PostgreSQL on Windows, back when I used Windows.)
>
> Can you see where the space is going when this happens?
>
> -Kevin
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dump/restore problems
@ 2010-02-21 01:59 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-02-21 02:35 ` Re: pg_dump/restore problems Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2010-02-21 01:59 UTC (permalink / raw)
To: gkbrown22@gmail.com; +Cc: pgsql-admin
Glen Brown wrote:
> I am using Ubuntu 8LTS on both systems. How can tell where the
> space is going?
Maybe someone has a more sophisticated way, but I'd be poking around
with "du -shx" requests against the contents of various directories
during the run. Maybe run "vmstat 1" in another shell, piping the
results to a file.
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dump/restore problems
2010-02-21 01:59 Re: pg_dump/restore problems Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-02-21 02:35 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Scott Marlowe @ 2010-02-21 02:35 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: gkbrown22@gmail.com; pgsql-admin
On Sat, Feb 20, 2010 at 6:59 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Glen Brown wrote:
>
>> I am using Ubuntu 8LTS on both systems. How can tell where the
>> space is going?
>
> Maybe someone has a more sophisticated way, but I'd be poking around
> with "du -shx" requests against the contents of various directories
> during the run. Maybe run "vmstat 1" in another shell, piping the
> results to a file.
Also look at iotop. Pretty sure it'll work on an up to date ubuntu 8.04 LTS.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-10-26 15:33 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-15 18:25 pg_dump/restore problems Glen Brown <gkbrown22@gmail.com>
2010-10-26 15:33 ` Bob Lunney <bob_lunney@yahoo.com>
2010-02-20 17:52 Re: pg_dump/restore problems Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-02-20 20:41 ` Glen Brown <gkbrown22@gmail.com>
2010-02-21 01:59 Re: pg_dump/restore problems Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-02-21 02:35 ` Scott Marlowe <scott.marlowe@gmail.com>
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