Re: restore a specific schema from physical backup

* David Steele (david(at)pgmasters(dot)net) wrote:
> On 7/29/16 5:31 PM, Rakesh Kumar wrote:
> > Sure.
> >
> > 1 - You ran pg_basebackup on node-1 against a live cluster and store
> > it on NFS or tape.
> > 2 - Do a restore on node-2 from the backup taken on (1), but only for
> > a subset of the database
> > (schema/database)
> > 3- Put the cluster live on node-2 after (2) completes. Essentially the
> > cluster will now be a small
> > subset of cluster on node-1.
> >
> > Benefit: If I have to restore only 5% of entire db, it should be lot faster.
>
> pgBackRest allows specified databases to be restored from a cluster backup:
>
> http://www.pgbackrest.org/user-guide.html#restore/option-db-include
>
> I know you are interested in schema-level restores but this is the
> closest thing that I know of.

We have discussed providing the ability to restore a subset of a
database from a physical backup, but it's far from trivial. Working out
what files contain the catalog requires first reading through
pg_filenode.map and then understanding the structures of the relevant
catalogs. Only then will you know what schemas and tables exist and
what their relfilenode's are, which is necessary to perform the restore
of those objects.

Of course, WAL replay still has to be performed also, to reach a
consistent backup point. We've worked out how to get that to work,
though if you have a lot of WAL then that can still take a bit of time
and disk space.

With sufficient interest and resources, we might be able to make it
happen, but I wouldn't expect it near-term. Until then, at least the
database-level option, as David mentioned, can be used.

Thanks!

Stephen