I used pg_restore to restore a 50 GB db from a directory backup using the following command which used 4 jobs:
pg_restore -d analytics -U postgres -j 4 -v "D:\Program Files\PostgreSQL\10\backups\Analytics_08_2018__7_53_21.36.compressed"
I ran this from a command line. The restore took about 2 hours longer than a non-parallel restore. it seemed to hang on creating indexes at the end of the restore job
pg_restore: launching item 2817 INDEX nidx_bigrams_inc_hits pg_restore: creating INDEX "public.nidx_bigrams_inc_hits" pg_restore: finished item 2965 TABLE DATA trigrams pg_restore: launching item 2822 INDEX nidx_trigrams_inc_hits pg_restore: creating INDEX "public.nidx_trigrams_inc_hits" pg_restore: finished item 2823 INDEX nidx_unigrams_inc_hits pg_restore: finished item 2822 INDEX nidx_trigrams_inc_hits pg_restore: finished item 2817 INDEX nidx_bigrams_inc_hits pg_restore: finished main parallel loop
Each of these pg_restore "creating index" jobs had a status of "idle" in pg_stat_activity. Another pg_restore job was "idle" on commit.
I was hoping that the parallel restore would complete much faster than the default restore and it seemed to be doing that until it hung there after about 15 minutes. There is not much else happening on this server. The indexes seemed complete when I checked them before the job finished i.e. they were the same size as the original database indexes. Is there any particular reason why a parallel restore job would take longer to finalize/commit as opposed to the non-parallel restore? I'd want to be prepared for that in a disaster recovery situation and rule out parallel if I cannot rely on the job duration.
By the way I am aware of file system based backups as opposed to logical, but I am only interested in logical for the time being.