Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pIFIy-0000Ju-P6 for pgsql-general@arkaria.postgresql.org; Wed, 18 Jan 2023 20:45:08 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pIFIx-0005t4-MM for pgsql-general@arkaria.postgresql.org; Wed, 18 Jan 2023 20:45:07 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pIFIx-0005su-Bq for pgsql-general@lists.postgresql.org; Wed, 18 Jan 2023 20:45:07 +0000 Received: from mail-pj1-x1029.google.com ([2607:f8b0:4864:20::1029]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pIFIv-0007Bq-BE for pgsql-general@lists.postgresql.org; Wed, 18 Jan 2023 20:45:06 +0000 Received: by mail-pj1-x1029.google.com with SMTP id d8so275081pjc.3 for ; Wed, 18 Jan 2023 12:45:04 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=gRvtOyOEV3hVtBTGjY6vRATjqJjZCJ4sXt5FAXt30Vc=; b=Jqr9NckuDlTbtnuM2iP/PD5cFDFKZbYTPwydsjJoDWAWNYd/kh1io9AfjI5ea6Cwyi zUtMkzMT0vkj1zzxH+7yM7reyGkZ/GK2ho+5ahRkvzvuwMJ3beOB3HMMPswLIg3RVRZq Zl6UQesMdXqmgthgRT6wXcwYBhbtx3NVmvtxZQ6TDyTD6GxgLqinriVc2E6PlAXV1vBG z61EGI1OiQPg611XB779GUUsoH1cbKcQ89MevfeL2tFjwwV4M6gVc0OFgkj/ntzSV22x coPDe6OvrJz8lpmTWXLFWVS6pWUA0urnK+9WuL2MfPv6rCMAwFfUpRMfINoTWM3nDNgu Qd+A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=gRvtOyOEV3hVtBTGjY6vRATjqJjZCJ4sXt5FAXt30Vc=; b=V2Y0Y61CVEhjW02gcsE2qnkQxl4QPGtBWBYN+zmMz6kbPEQgO8j8ryHnopHfVhmCZ5 OpcPiixB5MRxSYi1twbKcycB5AsOIOq4NqWTGC1OYojxyh8Eqtb6np7v7YEHj76eKKP+ wf+fE0cdMlSpdGRVPJDxnyK84MQfYtTGheCh0+xPS6Q9SVwdQjsnv15fFWqwO88LBq5v m4Hda+FQelU7lr3SY4EcDSHG89V8Q4r3a+rpU0er/0jgW3JqzlCrYR1rZbiRr129i7cU /hcTJMhBtbmkyNSM1ICOA8eqNX3GqIlKbXQT5yqXQPRUYeYA4NuGQ2kqkVHfqnVZEeEL HohQ== X-Gm-Message-State: AFqh2koUMKA+4IAF0wHUkX10DdM7oTs9MwUMTAEqY+epZv0DvO9RWftN ATsj+CFB86ir6XJTUOHWT3jRHRAifbU= X-Google-Smtp-Source: AMrXdXtfRaX7jE1BgVnTT6sYKSbp0WXvnXuqJDy38mIm+SJvDQSemyyG+we3TY/DsziaJfLGRCyabQ== X-Received: by 2002:a17:902:ea94:b0:194:c128:1c92 with SMTP id x20-20020a170902ea9400b00194c1281c92mr2275926plb.65.1674074702310; Wed, 18 Jan 2023 12:45:02 -0800 (PST) Received: from ?IPV6:2601:681:5a00:f790:91b0:e89d:37be:4f94? ([2601:681:5a00:f790:91b0:e89d:37be:4f94]) by smtp.gmail.com with ESMTPSA id f5-20020a170902684500b0019488a36e2asm7439333pln.277.2023.01.18.12.45.01 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 18 Jan 2023 12:45:01 -0800 (PST) Content-Type: multipart/alternative; boundary="------------gBfVY0v3SgMB3G0J0o0Y7tDl" Message-ID: <9fd9e2b5-b231-88fc-7f5c-c2794d98f4b1@gmail.com> Date: Wed, 18 Jan 2023 13:45:00 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.4.2 Subject: Re: Tools for moving normalized data around Content-Language: en-CA To: pgsql-general@lists.postgresql.org References: <26DC1033-FC6F-40C4-8E92-D90506FF9AD9@pendari.org> From: Rob Sargent In-Reply-To: <26DC1033-FC6F-40C4-8E92-D90506FF9AD9@pendari.org> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------gBfVY0v3SgMB3G0J0o0Y7tDl Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------gBfVY0v3SgMB3G0J0o0Y7tDl Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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.

--------------gBfVY0v3SgMB3G0J0o0Y7tDl--