On Wed, May 22, 2019 at 06:26:49PM +0300, Mariel Cherkassky wrote:
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.
What do you mean by "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).
Well, presumably the third database has complexity in other places, possibly spending a lot of time on CPU, while the other databases don't have such issue. What would help is a CPU profile, e.g. from perf. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
