pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rob Sargent <robjsargent@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Tools for moving normalized data around
Date: Wed, 18 Jan 2023 13:45:00 -0700
Message-ID: <9fd9e2b5-b231-88fc-7f5c-c2794d98f4b1@gmail.com> (raw)
In-Reply-To: <26DC1033-FC6F-40C4-8E92-D90506FF9AD9@pendari.org>
References: <Y8hMt6ubTmf7kJXb@disp.intra.daemon.contact>
	<26DC1033-FC6F-40C4-8E92-D90506FF9AD9@pendari.org>

On 1/18/23 13:15, Gavan Schneider wrote:
> On 19 Jan 2023, at 6:47, Peter wrote:
>
>> Now I want to grab some part of the data, on a certain condition
>> (let's say all records belonging to user 'Bob', if there is a "user"
>> table somewhere at the tree-bottom), and move it to another database
>> with the very same layout - which is already populated with data
>> and runs a different instance of the same application.
>>
>> Grabbing the intended records is just some dead-simple Selects. But
>> then inserting them into the other database is not fun, because some
>> primary keys will likely collide.
>>
> A very small sliver in this problem:
> The key collision problem could be avoided if the unique and arbitrary keys were UUID
> Many of the other keys should be related to their respective “table of truth” so the migration is dependant on these being the same across the locations
>
> The rest TBA down thread :)
>
> Regards
>
> Gavan Schneider

You'll have to "know" the tree and order inserts accordingly. Starting 
with the independent tables, then their dependants, then /their/ 
dependants etc.
And if the ids are currently integers, any chance you can negate the in 
transit?  You'll have to disable any sequence that's in play.

view thread (5+ messages)

Message-ID: <9fd9e2b5-b231-88fc-7f5c-c2794d98f4b1@gmail.com>
Permalink:  ../9fd9e2b5-b231-88fc-7f5c-c2794d98f4b1@gmail.com/
Also on:    postgresql.org/message-id/9fd9e2b5-b231-88fc-7f5c-c2794d98f4b1@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: robjsargent@gmail.com
  Subject: Re: Tools for moving normalized data around
  In-Reply-To: <9fd9e2b5-b231-88fc-7f5c-c2794d98f4b1@gmail.com>
  References: <Y8hMt6ubTmf7kJXb@disp.intra.daemon.contact> <26DC1033-FC6F-40C4-8E92-D90506FF9AD9@pendari.org> <9fd9e2b5-b231-88fc-7f5c-c2794d98f4b1@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox