pg_restore hangs on materialized view [SEC=UNCLASSIFIED]

[r***@dpc.vic.gov.au]

I have made a backup of a database using the pgAdmin backup command.
Attempting to restore it to a different server using the pgAdmin restore
command results in the process hanging.

I tried using the pg_restore utility in order to get a better output of
the restore process. The restore gets to a particular materialized view
and then just hangs. There's no kind of error message, so I don't know
what is causing the problem. I have checked the view on the original
server and it seems fine.

At the moment all I can think to do is to try to exclude the materialized
views from the dump file and recreate them manually.

Can anyone suggest a way of diagnosing and fixing this problem.

Rohan
Melbourne
Australia

----------------------------------------------------------------------
Please consider the environment before printing this email
Notice: This email and any attachments may be confidential and may contain
copyright or privileged material. You must not copy, disclose, distribute, store
or otherwise use this material without permission. Any personal information in
this email must be handled in accordance with the Privacy and Data Protection Act
2014 (Vic) and applicable laws. If you are not the intended recipient, please
notify the sender immediately and destroy all copies of this email and any
attachments. Unless otherwise stated, this email and any attachment do not
represent government policy or constitute official government correspondence. The
State does not accept liability in connection with computer viruses, data
corruption, delay, interruption, unauthorised access or use.

[Laurenz Albe]

I have made a backup of a database using the pgAdmin backup command. Attempting to
restore it to a different server using the pgAdmin restore command results in the
process hanging.
I tried using the pg_restore utility in order to get a better output of the restore process.
The restore gets to a particular materialized view and then just hangs. There's no kind
of error message, so I don't know what is causing the problem. I have checked the view
on the original server and it seems fine.
At the moment all I can think to do is to try to exclude the materialized views from
the dump file and recreate them manually.

Can you tell us the definition of the materialized view and the objects its
query is using? Odds are that it is just taking a long time to build.
Is it defined on a foreign table or something similar?

Yours,
Laurenz Albe
Yours

[Ed Behn]

Is it possible that the reason the view is taking so long to build that
there is one or more relevant indexes in one of the source tables that
hasn't been built? That could slow down the creation of the view.

You could try commenting out the code that creates the view, using that to
create the rest of the DB and then add the view.

Post by r***@dpc.vic.gov.au

Post by r***@dpc.vic.gov.au
I have made a backup of a database using the pgAdmin backup command.

Attempting to

Post by r***@dpc.vic.gov.au
restore it to a different server using the pgAdmin restore command

results in the

Post by r***@dpc.vic.gov.au
process hanging.
I tried using the pg_restore utility in order to get a better output of

the restore process.

Post by r***@dpc.vic.gov.au
The restore gets to a particular materialized view and then just hangs.

There's no kind

Post by r***@dpc.vic.gov.au
of error message, so I don't know what is causing the problem. I have

checked the view

Post by r***@dpc.vic.gov.au
on the original server and it seems fine.
At the moment all I can think to do is to try to exclude the

materialized views from

Post by r***@dpc.vic.gov.au
the dump file and recreate them manually.

Can you tell us the definition of the materialized view and the objects its
query is using? Odds are that it is just taking a long time to build.
Is it defined on a foreign table or something similar?
Yours,
Laurenz Albe
Yours
--
Cybertec | https://www.cybertec-postgresql.com

Post by r***@dpc.vic.gov.au
Please consider the environment before printing this email

Please consider the environment before wasting precious electrons.

[r***@dpc.vic.gov.au]

Having done some more investigation on this, it appears that the problem
is simply that the materialized views are two expensive computationally to
be run on a laptop. We build them on a server with 16GB of RAM, but the
problem occurred when developers wanted a local copy of the database on
their laptops with only 8GB or less. This wasn't immediately apparent as
the views are fairly simple.

Thanks to everyone for their responses.

Rohan Parkes



From: Ed Behn <***@behn.us>
To: Laurenz Albe <***@cybertec.at>,
Cc: Rohan Parkes/***@DPC, pgsql-***@lists.postgresql.org
Date: 23/06/2018 12:09 AM
Subject: Re: pg_restore hangs on materialized view
[SEC=UNCLASSIFIED]



Is it possible that the reason the view is taking so long to build that
there is one or more relevant indexes in one of the source tables that
hasn't been built? That could slow down the creation of the view.

You could try commenting out the code that creates the view, using that to
create the rest of the DB and then add the view.

I have made a backup of a database using the pgAdmin backup command. Attempting to
restore it to a different server using the pgAdmin restore command results in the
process hanging.
I tried using the pg_restore utility in order to get a better output of the restore process.
The restore gets to a particular materialized view and then just hangs. There's no kind
of error message, so I don't know what is causing the problem. I have checked the view
on the original server and it seems fine.
At the moment all I can think to do is to try to exclude the

materialized views from

the dump file and recreate them manually.

Can you tell us the definition of the materialized view and the objects
its
query is using? Odds are that it is just taking a long time to build.
Is it defined on a foreign table or something similar?

Yours,
Laurenz Albe
Yours
--
Cybertec | https://www.cybertec-postgresql.com

Please consider the environment before printing this email

Please consider the environment before wasting precious electrons.


----------------------------------------------------------------------
Please consider the environment before printing this email
Notice: This email and any attachments may be confidential and may contain
copyright or privileged material. You must not copy, disclose, distribute, store
or otherwise use this material without permission. Any personal information in
this email must be handled in accordance with the Privacy and Data Protection Act
2014 (Vic) and applicable laws. If you are not the intended recipient, please
notify the sender immediately and destroy all copies of this email and any
attachments. Unless otherwise stated, this email and any attachment do not
represent government policy or constitute official government correspondence. The
State does not accept liability in connection with computer viruses, data
corruption, delay, interruption, unauthorised access or use.