The following bug has been logged on the website:
Bug reference: 13987
Logged by: Martin Klima
Email address: martin.klima@warhorsestudios.cz
PostgreSQL version: 9.4.1
Operating system: Warhorse
Description:
I created dump of the database with pg_dump. When restoring it via psql, I
got the error "permission denied for relation ...". Upon checking the
problem, I realized that in the SQL file generated by the pg_dump, there is
command REFRESH MATERIALIZED VIEW ... just before the section (at the very
end of the dump) where the rights for the tables are first revoked and then
granted. The table mentioned in the error message is one of the tables the
materialized view is running.
My theory is that the refresh locks the table and therefore the REVOKE ALL
command fails. When I commented out the REFRESH MATERIALIZED VIEW commands,
the restore run without a problem (I then refreshed the views manually).
The command I run to create the dump was:
pg_dump -h<ip_address> -ntrunk -v -f d:\temp\dump.sql ConfigDB
>d:\temp\dump.log 2>&1
And restore with:
psql --single-transaction -h<ip_address> -f d:\temp\dump.sql ConfigDB
>out.log 2>error.log
Cheers,
Martin