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.

  • 1
    Are you using spinny disks? – Philᵀᴹ Aug 1 '18 at 9:05
  • Spinny disks? ...Yeah Phil – mikelus Aug 1 '18 at 9:47

It's probably just database contention.

Doing a parallel dump, you postgres can write to many different files at once, so the load can be spread around.

Doing a parallel restore, postgres has to manage all that [heavy-weight] traffic coming in from jobs+1 connections, all filling up data pages in the buffer cache and writing them to disk, all at once. To create indexes, you need to read data pages back in and scan them; doing this for lots of tables at once puts a lot of pressure on the buffer cache ... more work for postgres.

Also, you've specified four jobs - does your database [server] have enough CPUs / cores to support that? If not then the extra jobs will still get spun up, but will suffer contention at the operating system level, as they get switched in an out of memory as the [poor] machine tries to run them all "at the same time". On smaller servers, it may be that you get [some] benefit from running with two jobs, but more than that and the benefits are rapidly outweighed by the cost.

| improve this answer | |
  • Its an 8 core machine with 64gb ram, nothing else happening there at the moment. – mikelus Aug 1 '18 at 12:17
  • I dropped it down to 3 jobs and it completed in 20 minutes – mikelus Aug 1 '18 at 13:23
  • 2
    Just to add to Phill answer, only one worker will work on a relation at any given moment. Which means that if you have a huge table and several tiny ones, you might only see very little speed increase. It will be much more interesting if you have quite a few tables of similar size. – KookieMonster Aug 2 '18 at 12:37

Another reason this may happen is that pg_restore requires you to enter the password multiple times (once for each process). For example, if this is your command:

pg_restore --jobs=8 --clean -d example -C --no-owner --no-acl -U example -h -W db.dump 
Password: Password: Password: Password: Password: Password: Password: Password: 

The final line requires you to enter your password followed by the return key eight times, or ten times in total.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.