Re: pg_dump slower than pg_restore

On Thu, Jul 3, 2014 at 10:04 AM, 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.
>

Are you dumping to a slower disk/storage than the database is using?

What does top -c look like during the dump vs. the restore? I.e. can you
identify the differing bottlenecks in the two cases and dig in further from
there.

My backup script runs vacuumlo, then vacuum, then analyze, then pg_dump
> --format=c --oids $DB

How long does the backup take if you don't vacuum/analyze? On some of my
dbs that would be a huge part of the time. Honestly I'd pull the
vacuum/analyze out of the backup and into a different (nightly) task,
there's no real benefit doing as part of the backup task, other than just
getting it done. Analyzing after the restore would be a better time to do
it, to freshen up the statistics.