From: | Eduardo Morras <emorrasg(at)yahoo(dot)es> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: pg_dump slower than pg_restore |
Date: | 2014-07-03 18:47:09 |
Message-ID: | 20140703204709.a464ae7c7146ec033d477d89@yahoo.es |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, 03 Jul 2014 10:04:12 -0700
David Wall <d(dot)wall(at)computer(dot)org> wrote:
> I'm running PG 9.3.4 on CentOS 6.4 and noted that backing up my
> database takes much longer than restoring it.
>
> That seems counter-intuitive to me because it seems like reading from
> a database should generally be faster than writing to it.
>
> I have a database that pg_database_size reports as 18GB, and
> resulting dump is about 13GB in 27 files (split creates them as
> 512MB).
>
> A pg_dump backup -- with most of the data stored as large objects --
> takes about 5 hours.
>
> But restoring that dump takes about 2 hours. So it's taking 2.5
> times longer to back it up than to restore it.
>
> My backup script runs vacuumlo, then vacuum, then analyze, then
> pg_dump
> --format=c --oids $DB
>
> I actually push pg_dump output through gzip, gpg and split on 512MB
> files, but they shouldn't matter too much I figure as I have to run
> cat, gpg and gunzip before pg_restore. In fact, my restore should
> have been at a disadvantage because I used '-v' and showed the
> results to my ssh term over the Internet which includes a line for
> each LOID, and the postgresql.conf had 'ddl' logging on (which I
> suspect I can turn off in future restores to speed things up a bit).
>
> Is there something that might be wrong about my configuration that
> the backup is slower than the restore?
No, there's nothing wrong. All transparent compressed objects stored in database, toast, lo, etc.. is transparently decompressed while pg_dump access them and then you gzip it again. I don't know why it doesn't dump the compressed data directly.
>
> Thanks,
> David
--- ---
Eduardo Morras <emorrasg(at)yahoo(dot)es>
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2014-07-03 18:49:04 | Re: Not able to understand how to write group by |
Previous Message | Arup Rakshit | 2014-07-03 18:00:45 | Re: Not able to understand how to write group by |