pg_restore takes more time on creation of rules

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org, pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: pg_restore takes more time on creation of rules
Date: 2019-05-22 15:26:49
Message-ID: CA+t6e1=OWhBrXhyzQdTTdsobo9x82DaGH6J+euuGSAvLERYzcg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-performance

Hey,
I'm trying to restore a cluster (9.2) from 3 binary dumps (pg_dump -Fc).
Each dump contains only one database.
The sizes :
A-10GB
B-20GB
C-5GB.

For unclear reason the restore of the third database is taking alot of
time. It isnt stuck but it continues creating db rules. This database has
more then 400K rules.

I changed a few postgresql.conf parameters :
shared_buffers = 2GB
effective_cache_size = 65GB
checkpoint_segments =20
checkpoint_completion_target = 0.9
maintenance_work_mem = 10GB
checkpoint_timeout=30min
work_mem=64MB
autovacuum = off
full_page_writes=off
wal_buffers=50MB

my machine has 31 cpu and 130GB of ram.

Any idea why the restore of the two dbs takes about 15 minutes while the
third db which is the smallest takes more than 1 hour ?
I restore the dump with pg_restore with 5 jobs (-j).

I know that it is an old version, just trying to help..

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tomas Vondra 2019-05-22 15:41:02 Re: pg_restore takes more time on creation of rules
Previous Message Tomas Vondra 2019-05-21 16:37:27 Re: Trying to handle db corruption 9.6

Browse pgsql-performance by date

  From Date Subject
Next Message Tomas Vondra 2019-05-22 15:41:02 Re: pg_restore takes more time on creation of rules
Previous Message Dmitry Dolgov 2019-05-22 13:15:16 Re: Log size in bytes of query result