Postgresql – Are there limitations on using pg_dump on a materialized view such that the data won’t be included in the results

geometrymaterialized-viewpg-dumppostgispostgresql

I have a database, call it maps, which contains 2 schemas, a & b. In each schema I have a number of tables: a.t1, a.t2, b.t1, b.t2 (plus others). The column sets on each of these tables is different, but there are a number of columns in common.

I have defined a materialised view, a.mv, which brings in the common columns of the 4 tables listed, including a geometry column (which represents a geographic outline).

I want to backup the current contents of the view so that I can restore it on another server. To do this, I use this pg_dump command:

pg_dump -h hostname -U username -t a.mv -f mv.sql maps

What I get as a result is the SQL to define the table, but no data. The view definitely has data in it, because I can select from it in PgAdmin (it was created multiple days ago and the underlying tables haven't been changed since)

I can dump the underlying tables, including data, with (eg)

pg_dump -h hostname -U username -t a.t1 -f t1.sql maps

but not the view. From the limited matches I've found with googling this, what I'm trying should work, but I'm wondering if the presence of a geometry column in the dump is causing the issue (or this might be a complete red herring). FWIW, the total data in the view is fairly substantial – probably around 1GB. However, I've dumped all the underlying tables in schema a successfully (including the 2 tables referenced by the view, and others), which was larger (1.5GB)

Any ideas what the issue could be here? On the face of it, what I'm trying to do should work, but just doesn't and with no indicated errors.

Best Answer

You can not reload data manually to a MV, so it doesn't make much sense for pg_dump to dump the data itself out for a MV.

If you dump the underlying table plus the materialized view, the dump file should load correctly by loading the tables, then refreshing the materialized view from them. I don't think there is any simple way to give it just the MV name and have it include the dependencies, you have to track those yourself.

If you want the materialized view restored as a stand-alone table, you can stitch those together yourself. Get the definition of the materialized view from pg_dump -s -t a.mv, but then you will have to edit it to make it create a real table, not an mv. Then you can dump the data itself with

\copy (select * from mv) to mv.txt

And load it back on the other server with

\copy mv from mv.txt
Related Question