Re: restore a specific schema from physical backup

On 07/29/2016 02: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.

So no:

https://www.postgresql.org/docs/9.5/static/app-pgbasebackup.html
"pg_basebackup makes a binary copy of the database cluster files, while
making sure the system is put in and out of backup mode automatically.
Backups are always taken of the entire database cluster; it is not
possible to back up individual databases or database objects. For
individual database backups, a tool such as pg_dump must be used."

If you want to do that with built in tools then you will need to use
pg_dump.

I would suggest pg_dump -Fc as you have more options of what to do when
restoring objects. Remember pg_dump only works on individual databases
within a cluster.


>


--
Adrian Klaver
adrian.klaver@aklaver.com