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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- --bithead-- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
