Re: Re: how can i prevent materialized views from refreshing during pg_restore

I think this (pg_restore -l | pg_restore -L) will get me where I need to
go for now by inserting a small shell script in between that pushes the
materialized views to the end of the list, but then I will also have to
manage my own dependencies for the items that I re-sort (MatViews of
MatViews).
This pretty seriously limits the usefulness of materialized views for me.
For version 9.3.x, I'm likely to require MatView dependencies no more than
1 deep.
Thanks for the answer, I had no solution before that.

/Kirk

> bithead wrote
>> I asked a question over on StackOverflow, and Craig Ringer told me to
>> report it here.
>>
>> http://stackoverflow.com/questions/24413161/how-can-i-prevent-materialized-views-from-refreshing-during-pg-restore
>>
>> I have created a dump of the database using pg_dump in "custom" format
>> (-Fc). This format allows for pg_restore to be invoked with the "jobs"
>> option (-j8). The jobs options starts 8 processes, and restores the vast
>> majority of relations in my database within 10 minutes.
>>
>> I'm left with 4 processes. One of them is the refresh of a materialized
>> view, and the other 3 are indexes to be applied to 3 tables that the
>> materialized view uses as data sources. The indexes are "waiting"
>> according to pg_stat_activity, presumably because the REFRESH of the
>> materialized view is still accessing the source tables.
>>
>> When the indexes are in place, the refresh of the view only takes a
>> couple
>> of minutes. Because the indexes are not in place during the REFRESH, I
>> cut
>> the REFRESH process off at 17 hours, which made pg_restore fail.
>>
>> How can I
>>
>> Force the order of items so the indexes get created first
>> Turn off the refresh of the materialized view and do it manually
>> later
>> Manipulate the dump file in custom format to say "WITH NO DATA"
>> Intercept the REFRESH MATERIALIZED VIEW statement and throw it in
>> the
>> trash
>>
>> Or any other solution that gets the job done?
>>
>> I have a dump file that I'm willing to send to somebody that seems to
>> reproduce the problem pretty consistently.
>
> Have/can you try the "-l (el) & -L" options to pg_restore?
>
> http://www.postgresql.org/docs/9.3/static/app-pgrestore.html
>
> (example of usage is toward the bottom of the page)
>
> Basically re-order the command sequence so that the materialize runs as
> late
> as possible, or just disable it altogether.
>
> pg_dump/pg_restore should be taught to handle this better, which is the
> main
> reason why Craig had you post here ASAP, but to get it functional for now
> manual intervention will be necessary. In theory the "listing"
> capabilities
> should allow you to do what you need.
>
> David J.
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/how-can-i-prevent-materialized-views-from-refreshing-during-pg-restore-tp5809364p5809367.html
> Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
--bithead--