Re: restore a specific schema from physical backup

On 7/29/16 5:31 PM, Rakesh Kumar wrote:
>> Are you saying that?:
>>
>> 1) You ran pg_basebackup against a live cluster and sent the output to
>> another location.
>>
>> 2) At the other location the cluster is not in use.
>>
>> 3) You want to grab the contents of the inactive cluster directly off the
>> disk.
>>
>> If that is the case, then no it is not possible without making the cluster
>> live.
>>
>> If you mean something else then more details are needed.
>
> 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.

--
-David
david@pgmasters.net