Re: How to get some table entries from backup instance back to production instance

On 10.02.2015 13:36, Martin Steer wrote:
> On Tue, Feb 10, 2015 at 09:39:25AM +0100, Marcel Ruff wrote:
>>
>> I have 4 tables with 1:n relation:
>>
>> grandpa -> pa -> child -> grandchild
>>
>> Now I need to copy an instance of "pa" with his descendants
>> from my backup (a postgres dump) back to prod.
>> "grandchild" has a blob column (containing fotos)
>>
>> Which options do I have?
>>
>> 1) Import the backup dump in a separate postgres instance
>> 2) Do some magic query which created insert statements
>> 3) Run the insert statements on the prod server
>>
>> What is the best approach, and how does a query look like which
>> returns nicely formatted insert lines?
>
> You want to copy some tables from one db (or dump) to another?
>
> Perhaps you should look at the man pages for pg_dump and pg_restore.
> pg_restore allows the loading of a single table, or group of tables,
> into a specified database.
>
> M
No, only some instances of "pa" with its children.
My end user deleted by mistake some "pa"s and now wants to have
them back.

If I can use pg_dump / pg_restore with some where condition it would
be the perfect solution, but after reading the man page (as you suggested)
it is not possible.

The smartest solution would be if the query in the backup DB formats the result set
as nice insert statements and I could directly pipe it into the production DB ...

Thanks
Marcel