PostgreSQL – How to Use pg_restore for a View

postgresql

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

Best Answer

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)

to

                   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

Related Question