Re: Merging two database dumps

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