Re: [GENERAL] Move rows from one database to other

Thomas: On Thu, Feb 23, 2017 at 4:25 PM, Thomas Güttler wrote: > This sound good. Is there a name for this trick, to find more details? Not that I know of. It's really old, basic stuff, with many variations possible. I've being doing variation of it since the

Re: [GENERAL] Move rows from one database to other

Thomas: On Thu, Feb 23, 2017 at 4:26 PM, Thomas Güttler wrote: > Am 23.02.2017 um 13:44 schrieb Leknín Řepánek: >> Maybe you can do something like >> WITH cte AS ( >> DELETE FROM t1 WHERE cond >> RETURNINIG * >> ) >> INSERT into t2 >> SELECT * FROM cte; >> >> To

Re: [GENERAL] Move rows from one database to other

Thomas: On Thu, Feb 23, 2017 at 4:16 PM, Thomas Güttler wrote: > Am 22.02.2017 um 16:00 schrieb Adrian Klaver: >> only written on a successful transfer. To improve the chances of >> successful transfer more smaller transfer batches >> rather then larger transfers.

Re: [GENERAL] Move rows from one database to other

Am 23.02.2017 um 13:44 schrieb Leknín Řepánek: Maybe you can do something like WITH cte AS ( DELETE FROM t1 WHERE cond RETURNINIG * ) INSERT into t2 SELECT * FROM cte; To move rows between tables with combination with fdw_postgres and foreign table. ... this way you don't need the second

Re: [GENERAL] Move rows from one database to other

Am 23.02.2017 um 10:33 schrieb Francisco Olarte: Thomas: On Wed, Feb 22, 2017 at 1:51 PM, Thomas Güttler wrote: I want to **move** the data. The data should get deleted on the satellite after transfer. I don't know how to delete the data which was copied, since

Re: [GENERAL] Move rows from one database to other

Am 22.02.2017 um 16:00 schrieb Adrian Klaver: On 02/22/2017 04:51 AM, Thomas Güttler wrote: I have other concerns: atomar transaction. Movement should happen completely or not all. I don't think you can do this reliable (atomic transaction) with "copy table_name". You can if you wrap it in

Re: [GENERAL] Move rows from one database to other

Maybe you can do something like WITH cte AS ( DELETE FROM t1 WHERE cond RETURNINIG * ) INSERT into t2 SELECT * FROM cte; To move rows between tables with combination with fdw_postgres and foreign table. Je; On Thu, Feb 23, 2017 at 10:33:27AM +0100, Francisco Olarte wrote: > Thomas: > > On

Re: [GENERAL] Move rows from one database to other

Thomas: On Wed, Feb 22, 2017 at 1:51 PM, Thomas Güttler wrote: > I want to **move** the data. The data should get deleted on the satellite > after transfer. > I don't know how to delete the data which was copied, since inserts can > happen during the copy statement.

Re: [GENERAL] Move rows from one database to other

On 02/22/2017 04:51 AM, Thomas Güttler wrote: I have other concerns: atomar transaction. Movement should happen completely or not all. I don't think you can do this reliable (atomic transaction) with "copy table_name". You can if you wrap it in a transaction: I want to **move** the data. The

Re: [GENERAL] Move rows from one database to other

I have other concerns: atomar transaction. Movement should happen completely or not all. I don't think you can do this reliable (atomic transaction) with "copy table_name". You can if you wrap it in a transaction: I want to **move** the data. The data should get deleted on the satellite

Re: [GENERAL] Move rows from one database to other

I misunderstood your original intent, I thought this was a one time process to move data to the central database. Given that it is to be a continuous process a FDW may not be the answer, one of the reasons being the above question. You will be denied the data in the remote table during the

Re: [GENERAL] Move rows from one database to other

On 02/21/2017 08:06 AM, Thomas Güttler wrote: Am 21.02.2017 um 15:27 schrieb William Ivanski: You can try OmniDB: http://www.omnidb.com.br/en_index.aspx OmniDB has a Convert feature, where you can set a data transfer, even if the target table exists. I am unsure if omnidb is the right tool

Re: [GENERAL] Move rows from one database to other

On 02/21/2017 07:53 AM, Thomas Güttler wrote: Am 21.02.2017 um 15:12 schrieb Adrian Klaver: On 02/21/2017 12:53 AM, Thomas Güttler wrote: I want to move table rows from one database to an central database. You actually talking about moving from ~100 databases to the central database,

Re: [GENERAL] Move rows from one database to other

On Tue, Feb 21, 2017 at 11:10 AM, Thomas Güttler < guettl...@thomas-guettler.de> wrote: > >> Depending on how much data you want to move, and if the tables have the >> same structure, you might also want to consider >> using >> pg_dump -a >> >> OR >> multiple instances of >> >> on satellite >>

Re: [GENERAL] Move rows from one database to other

Depending on how much data you want to move, and if the tables have the same structure, you might also want to consider using pg_dump -a OR multiple instances of on satellite COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | PROGRAM 'command' | STDOUT } [ [

Re: [GENERAL] Move rows from one database to other

Am 21.02.2017 um 15:27 schrieb William Ivanski: You can try OmniDB: http://www.omnidb.com.br/en_index.aspx OmniDB has a Convert feature, where you can set a data transfer, even if the target table exists. I am unsure if omnidb is the right tool here. I don't need a GUI. The movement of

Re: [GENERAL] Move rows from one database to other

On 02/21/2017 12:53 AM, Thomas Güttler wrote: I want to move table rows from one database to an central database. Both run PostgreSQL. How to solve this with PostgreSQL? Should have added earlier. This is a specific case of the more general case of ETL(Extract/Transform/Load). There are

Re: [GENERAL] Move rows from one database to other

Am 21.02.2017 um 15:12 schrieb Adrian Klaver: On 02/21/2017 12:53 AM, Thomas Güttler wrote: I want to move table rows from one database to an central database. You actually talking about moving from ~100 databases to the central database, correct? Both run PostgreSQL. Are all the

Re: [GENERAL] Move rows from one database to other

Il 21/02/2017 15:38, Melvin Davidson ha scritto: Depending on how much data you want to move, and if the tables

Re: [GENERAL] Move rows from one database to other

On Tue, Feb 21, 2017 at 9:27 AM, William Ivanski wrote: > You can try OmniDB: http://www.omnidb.com.br/en_index.aspx > > OmniDB has a Convert feature, where you can set a data transfer, even if > the target table exists. > > Em ter, 21 de fev de 2017 às 11:18, Adrian

Re: [GENERAL] Move rows from one database to other

You can try OmniDB: http://www.omnidb.com.br/en_index.aspx OmniDB has a Convert feature, where you can set a data transfer, even if the target table exists. Em ter, 21 de fev de 2017 às 11:18, Adrian Klaver escreveu: > On 02/21/2017 12:53 AM, Thomas Güttler wrote: >

Re: [GENERAL] Move rows from one database to other

On 02/21/2017 12:53 AM, Thomas Güttler wrote: I want to move table rows from one database to an central database. You actually talking about moving from ~100 databases to the central database, correct? Both run PostgreSQL. Are all the Postgres instances the same version and what is the

[GENERAL] Move rows from one database to other

I want to move table rows from one database to an central database. Both run PostgreSQL. My use case looks like this: There are N satellite databases in different data centers. N is about 100 at the moment. There is one central database. I need a way to reliably move rows from the satellite