Sample Header Ad - 728x90

Are there limitations on using pg_dump on a materialized view such that the data won't be included in the results?

0 votes
1 answer
4531 views
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.
Asked by khafka (79 rep)
Feb 6, 2020, 11:36 AM
Last activity: Jan 8, 2025, 01:03 PM