2

I am trying to restore a database that includes some materialized views. I am migrating from a standalone postgres installation to RDS.

To troubleshoot the restore process, I have split the restore into 5 steps using the --section and the -L option. The steps are

  1. Restore the pre-data section, but without materialized views, -j 1
  2. Restore the data section in parallel, -j 5
  3. Restore the post-data section in parallel, -j 5
  4. Restore the materialized views, creation section (pre-data), -j 1
  5. Restore the materialized views, refresh section (post-data) in parallel -j 5

Steps 1-4 runs without any problems. In step 5 I run into trouble. The step takes forever and if I list the pg_stat_activity I can see two processes that runs the same REFRESH MATERIALIZED VIEW

-[ RECORD 3 ]---------------------------------------------------
pid     | 17244
query   | REFRESH MATERIALIZED VIEW home_property_data;
        | 
        | 
        | 
waiting | f
... snip ...
-[ RECORD 7 ]---------------------------------------------------
pid     | 17243
query   | REFRESH MATERIALIZED VIEW home_property_data;
        | 
waiting | f

If I read the documentation for the MATERIALIZED views it looks like that they cannot be updated in parallel. Am I doing the restore wrong ?

  • You're running the step 5 in parallel. Is there a chance the list of parallel jobs to be run include refreshing home_property_data twice as two separate jobs? – Ziggy Crueltyfree Zeitgeister Apr 22 '16 at 2:20
  • "If I read the documentation for the MATERIALIZED views it looks like that they cannot be updated in parallel." can you please link or quote where you get this impression from. – alfonx Oct 27 '16 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.