I check pg_dump doc, and there seems to be an option that allows to export only views. But is there a way to dump a database without all the views?
1 Answer
There's no option that excludes all views. But there are workarounds.
If you isolate all your views in a single schema (as in create schema views_only...
), you can use
- either the
-n
or--schema=public
argument to select only the public schema (might not do what you want), - or the
-N
or--exclude-schema=views_only
.
If you follow a strict(ish) naming convention, you can use
- either the
-t
or--table=something
to select only the tables, - or the
-T
or--exclude-table=something
to exclude the views.
Be careful with this. In the relational model, base tables and views share a namespace for a reason--they're supposed to be interchangable. SQL generally honors that part of the relational model. Some people use prefixes for database objects, so "we know whether an object is a view, a table, or a stored procedure that returns a table". If you build views with names like "view_wibble", and later decide to replace that view with a table, you have the distasteful options of naming your table "view_wibble", or rewriting application code, stored procedures, other views, etc to use "tbl_wibble" instead of "view_wibble".
But you can use these command-line options more than once. So you can name each of the views you want to exclude. The is probably the best idea.
pg_dump ... --exclude-table=view1 --exclude-table=view2 --exclude-table=view3 ...