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

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.

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

On Tue, Feb 10, 2015 at 02:50:16PM +0100, Marcel Ruff wrote:
>On 10.02.2015 13:36, Martin Steer wrote:
>
>    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.

You can use your where condition in db1 to create the export-pa tables.
Then dump them and restore them to db2.

M.

Hi,

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?

Thank you
Marcel

Hi,

I would do this way: 

After the first step select the required records into another tables with CREATE TABLE patmp AS SELECT * FROM pa WHERE ...

Move these temporary tables to the prod database by dump and restore and transfer the content into the prod tables with INSERT INTO pa SELECT * FROM patmp.

Done.

Regards,

Sándor Daku

On 10 Feb 2015, at 09:39, Marcel Ruff <mr@marcelruff.info> wrote:

Hi,

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?

Thank you
Marcel