Re: Merging two database dumps

Thanks for the clarification

On Wed, Jun 13, 2018 at 9:32 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 06/13/2018 06:21 AM, Alex O'Ree wrote:
>
>> Desired behavior is to just log the error and continue the import using
>> pgdump based copy commands
>>
>
> Each COPY is atomic so if any part of it fails the whole thing fails, so
> you will not be able to achieve what you want that way.
>
>
>> The servers are not on the same network. Sneaker net is the only way
>>
>> On Wed, Jun 13, 2018, 7:42 AM Andreas Kretschmer <andreas(at)a-kretschmer(dot)de
>> <mailto:andreas(at)a-kretschmer(dot)de>> wrote:
>>
>>
>>
>> Am 13.06.2018 um 13:17 schrieb Alex O'Ree:
>> > I have a situation with multiple postgres servers running all
>> with the
>> > same databases and table structure. I need to periodically export
>> the
>> > data from each of there then merge them all into a single server.
>> On
>> > occasion, it's feasible for the same record (primary key) to be
>> stored
>> > in two or more servers
>>
>> what should happen in this case?
>>
>> >
>> > I was using pgdump without the --insert option however I just
>> noticed
>> > that pgrestore will stop inserting into a table when the conflict
>> > occurs, leaving me with an incomplete set.
>> >
>>
>> Other solution:
>>
>> * create the tables on the destination server without the PK or with
>> an
>> other, new PK (maybe SERIAL)
>> * use logical replication to replicate the table from all your
>> source-db's to the destination table, see more here:
>> https://www.2ndquadrant.com/en/resources/pglogical/
>>
>> your problem seems as a typical task for logical replication to me.
>> You
>> needs 9.4 at least.
>>
>>
>> Regards, Andreas
>>
>> -- 2ndQuadrant - The PostgreSQL Support Company.
>> www.2ndQuadrant.com <http://www.2ndQuadrant.com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>