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.
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