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.

  • I've tried adding the -b (include large objects) and -v (verbose) flags. -b did nothing, with -v, the last status message was pg_dump: creating MATERIALIZED VIEW DATA "a.mv" – khafka Feb 6 '20 at 11:52

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
  • 1
    That was exactly why I wanted to do it - to then restore the data as a table on another system. I agree you can do it with copy, I was just bemused why pg_dump didn't work, given that the docs explicitly include mat views as dumpable - under the -t command: Dump only tables with names matching table. For this purpose, "table" includes views, materialized views, sequences, and foreign tables. If the content can't be exported with pg_dump, this comment is, at best, misleading – khafka Feb 10 '20 at 9:13

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.