Re: pg_restore takes more time on creation of rules

Not looking for errors, but redirecting it to a file for posterity.

Were/are the rules being created, but just slowly?

On 5/22/19 11:02 AM, Mariel Cherkassky wrote:
> 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..)
>
> ‫בתאריך יום ד׳, 22 במאי 2019 ב-19:01 מאת ‪Ron‬‏ <‪ronljohnsonjr(at)gmail(dot)com
> <mailto:ronljohnsonjr(at)gmail(dot)com>‬‏>:‬
>
> I'd redirect stderr to a file and tail it to monitor progress.
>
> On 5/22/19 10:44 AM, Mariel Cherkassky wrote:
>> By rules I mean DB rules (simillar to triggers but different)
>>
>> ‫בתאריך יום ד׳, 22 במאי 2019 ב-18:41 מאת ‪Tomas Vondra‬‏
>> <‪tomas(dot)vondra(at)2ndquadrant(dot)com <mailto:tomas(dot)vondra(at)2ndquadrant(dot)com>‬‏>:‬
>>
>> 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
>>
>
> --
> Angular momentum makes the world go 'round.
>

--
Angular momentum makes the world go 'round.