Basically if I didnt redirect stderr i should be redirected to the screen ? I didnt see any errors, I used -v (verbose) when I run the restore, and I see that it restores the rules (but a lot of them..)
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