What's the best way to restore a view?

  1. pg_dump -h hostname -d databasename -Fc > db.dump
  2. DROP VIEW viewname;
  3. pg_restore -h hostname -d databasename -t viewname db.dump This does not error, but also doesn't seem to work. Have also tried it with the -n schema option. In verbose mode, it says pg_resore: implied data-only restore.

Is it possible to restore a view via pg_restore

  • Hm, that's ugly. I don't see why -t should imply --data-only, especially when -n does not. I'll raise this on pgsql-bugs. – Craig Ringer Apr 1 '15 at 1:24
  • @CraigRinger, thank you. Restoring from the full dump works, but I wanted to be selective with the view and wasn't sure if I was doing something wrong. – vol7ron Apr 1 '15 at 1:52
  • As a workaround you can do a --schema-only dump without the -d dbname argument, to a text file. Then edit it to extract just the view definition. I'm looking at this now and will post a link to the bugs post when done. – Craig Ringer Apr 1 '15 at 1:58
  • I was dumping as SQL inserts before and manually restoring, but I was hoping to avoid some of this manual processing (oh well) :) – vol7ron Apr 1 '15 at 2:02
  • As a workaround: why don't you use select pg_get_viewdef('viewname') to extract the source of the view? I don't think pg_dump does it any different. – a_horse_with_no_name Apr 1 '15 at 6:13

In PostgreSQL 9.4 or older there does not appear to be a way to do this.

Here's the issue report I just posted.

If you want to patch pg_restore it looks pretty trivial, just add checking for under the test for ropt->selTypes in _tocEntryRequired (in pg_backup_archiver.c), changing:

                   strcmp(te->desc, "TABLE DATA") == 0)


                   strcmp(te->desc, "TABLE DATA") == 0 ||
                   strcmp(te->desc, "VIEW") == 0)

Here's the full patch: https://gist.github.com/ringerc/1743cfad34694fc9b9a3

You don't have to compile and install all of PostgreSQL for this, you can just compile a custom pg_restore if you want.

update: Here's the -hackers post with the proposed patch.

update2: This is now fixed in 9.6 according to the docs

| improve this answer | |

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.