Improve the performance of pg_dump pg_restore

PG_DUMP 9.6 Fc Format VS Fd Format

1. Increase maintenance_work_mem so you can build indexes in larger chunks
2. Turn off fsync during the restore. If your machine crashes, you'll start from scratch again anyway.
3. Disable index when Restoring 
4. autovacuum = off

Fc

-bash-4.1$time pg_dump -Fc fsdcm_external > /tmp/new_dump96_fsdcm_external.sql

Fd

-bash-4.1$time pg_dump -j 8 -Fd -f /tmp/newout.dir fsdcm_external

Time	Fc	Fd
real	44m32.553s	10m12.214s
user	43m28.341s	63m19.678s
sys	0m46.313s	0m39.869s

PG_RESTORE 9.6 Fc With no tunning deafultsetting postgres.conf || With no -j VS yes -j

With no -j

-bash-4.1$time pg_restore --format=c -C -d postgres /tmp/fsdcm_external.s

With yes -j

bash-4.1$time pg_restore -j 8 --format=c -C -d postgres /tmp/fsdcm_external.sql

Time	No j	yes J
real	58m53.599s	35m21.029s
user	11m0.415s	10m11.956s
sys	0m38.405s	0m42.460s

PG_RESTORE 9.6 With tunning settingpostgres.conf || Fc Format VS Fd Format

postgres.conf

work_mem= 32MB

shared_buffers= 4GB

maintenance_work_mem= 2GB

full_page_writes= off

autovacuum= off

wal_buffers= -1

Fc

-bash-4.1$time pg_restore -j 8 --format=c -C -d postgres /tmp/fsdcm_external.sql

Fd

-bash-4.1$time pg_restore -j 8 --format=d -C -d postgres /tmp/newout.dir/

Time	Fc Format	Fd Format
real	25m21.241s	26m18.635s
user	10m18.189s	11m25.439s
sys	0m43.944s	0m41.772s

the bottom line is

PG_DUMP | Always Used Format Direcroty WithJ option

-bash-4.1$time pg_dump -j 8 -Fd -f /tmp/newout.dir fsdcm_external

PG_RESTORE | Always Used tunning forpostgres.conf with Format Directory With J option

work_mem= 32MB

shared_buffers= 4GB

maintenance_work_mem= 2GB

full_page_writes= off

autovacuum= off

wal_buffers= -1

-bash-4.1$time pg_restore -j 8 --format=d -C -d postgres /tmp/newout.dir/