Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wHknl-007U5B-0M for pgsql-general@arkaria.postgresql.org; Tue, 28 Apr 2026 15:56:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wHknj-000Sa8-2o for pgsql-general@arkaria.postgresql.org; Tue, 28 Apr 2026 15:56:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wHknj-000SZz-1d for pgsql-general@lists.postgresql.org; Tue, 28 Apr 2026 15:56:43 +0000 Received: from mail-ej1-x632.google.com ([2a00:1450:4864:20::632]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wHknh-00000003Xzk-2C8f for pgsql-general@postgresql.org; Tue, 28 Apr 2026 15:56:43 +0000 Received: by mail-ej1-x632.google.com with SMTP id a640c23a62f3a-b9c603ec2dfso1485197166b.1 for ; Tue, 28 Apr 2026 08:56:41 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1777391800; cv=none; d=google.com; s=arc-20240605; b=HFqjxmNJ5osCh5rtGo9KkbTj3T/iPJ7I08GXuKRbiV7RfM1x7+RlUYKs91HFv7YHv8 gqix89aE7T5msdXUZc8FHtVQmU1UtVgr6cH59NaIn+7nmcnfc5AHy7G3n7ImLDJrnAA/ HmLoVeWGAqNeQFmu8UXkh5EE3Ucr04sZcw0w/FHhidZ1/P0ucVsXx1gUD4RH363JYuXA RaC76GAcRiUctLJ5INpc03jntnc58TZqu0SAE9sRSW/udE/UGBPCTF10Nkhy10UJfHqk ayilA6+NkyDqxclYxUCoa29hBkq6ocjRabvThN7L3rnorPMHJbhTKMQSq3GgfMlVZr1z L8WA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:mime-version:dkim-signature; bh=JMfjctH6/n5egQ3GExGVeYFqClKdSDhpQeniPhMui5U=; fh=x9Jmr8+FtiCQKUvo/7/je6SkcsG+V48pI/LmEXQOSec=; b=h/qNGph8HJ5NCsrFrGSNxapEJYby53p3TifMVxrMCam4FIF+dd771mTJtwhUvCv4zh GlQsuBJVP5EGVlhdIX6iPAo5WIgif5oroIlid2ncTF0ZvgVyTYxg3GySdPy7WFHXQF3/ DxDQzgQZgmJKB5e2QlnjcRTAbPSgcm41fVV1DIb0YFfNot2HPowPDhvaXAzKd3XnDewg 1FGpYpct4AJ9wdeTSFKnDF25WMr4bF0YkAODeC2uA5NjhvLq3DoU5XVL9ff3XtMultFJ Gj5kMcvAMV5JiWIQ/azyhilFLf8hNLi47we9GO0/1nS0IoTD8YetwxMSMGNHQpQRO8HJ yjDQ==; darn=postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1777391800; x=1777996600; darn=postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=JMfjctH6/n5egQ3GExGVeYFqClKdSDhpQeniPhMui5U=; b=fMJRVBSrf6lG75/h+eW8C+uAdvD6YpL/Xj4CJ/8yB9Qx8YGnX++aMKiEuIdYCIR5YN ju/OdxMe9D+8+Zh6HtHljoJ9qsxjrJmTW95Be8C1MHa0Dy/+NYB2Qe3l2uIYzNb01+7m bTG5eOwhV25g0gkThqwUb/QjQrM25M9tKGt2ioiZ6ZsiDnYjwTAZH3l+Kc9XuLHh/PmV jFFfYhdXh20PIza2XKymJBZuoxpH9cq1YCeRWCzb1lO2iupMlHVKHnb1wMxN9vgn3DQT xNhn4RiDTk+dv75/tO3qAuUVuglAtiC+3L4pTWInIOBsYCdSPjU7CNcYHmBGmw+7sZIK ou7A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1777391800; x=1777996600; h=to:subject:message-id:date:from:mime-version:x-gm-gg :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=JMfjctH6/n5egQ3GExGVeYFqClKdSDhpQeniPhMui5U=; b=Ymx/zV3PphERHNRWQwtf/Mm0c1J1rl49oRUIIGyr6HXe1Xc5FOlnEB/C/BtfDzFSjm 8Dot22EpPaP6Ce/mfMly6HpO9gr8Y3S1FylchYPGaYoVR8ysh52woPJUv+TjudQZyICV msE0U6kIkKNzlglNHemMcQ2iOx2TwEgYksfN2ft57jO2kNr/2iw8rjjo6RwQAmC7VqNJ Dge5BvyrUvf7NljPamMnHPbNkPlCGaxmrjEQyN9Wjln8/o/SRl4NB1pLjOFZdt3hxeQn RDGCm4Lbs9ER7W33sBbNaeM8pwGFFBgzAsbnX2c4zzGkH8Rj4N+0zNQQ07V3RmHFJ3qR 49AA== X-Gm-Message-State: AOJu0YyUGp5HlNstp0tVebPLArLU8555iCwNlCFLKLQr2ktRh3GvSAQ2 WcwSOcuEDy0XjYhvKvQ1rl6bYkUgQZoKFo/18AGwptTsiDTENKxUM+cIyLUWunBTpPYQEfp3zNA 4/C2cH9C+CZde4qAe2Xz4zCgZeL0os3zjYPQ0 X-Gm-Gg: AeBDiesGGK8mr78x7LYS43gWXYzQvb4V+VVH0EgPFgJquVddxT9vaKPQAi42lq5bCvM 96miO/gSu/dYQVzzto3paplLHUnTgmjck9KDSfe5K6i5KMH7KSpq5ow8ROo+rQejYGe5cv5/bX5 gUw/O6mA/QGIa69l6nw5DcoRFrNJLPT2PXr5BAu9aaKeAe56ciNnvs6JcuSi28cqLRBqyOxvFlJ 9dYDJ1lczyUKmP8sdZxp7sWSV2H+eK3qMRM8vHcVRnMyyc5EGh0gC1lPFch7oOI9/B7V1y2d2aq K604zsSbJT58IiENBaM= X-Received: by 2002:a17:906:6206:b0:bab:812f:a894 with SMTP id a640c23a62f3a-bb8026bba48mr229312466b.20.1777391800042; Tue, 28 Apr 2026 08:56:40 -0700 (PDT) MIME-Version: 1.0 From: dfgpostgres Date: Tue, 28 Apr 2026 11:56:28 -0400 X-Gm-Features: AVHnY4K5a3GyX3lDeZF9VyGmV_atF2eZPcMBLr8RNI_QFaAsmO0AWTLehmmI9Tc Message-ID: Subject: can recs be transferred between DBs ? To: pgsql-general@postgresql.org Content-Type: multipart/alternative; boundary="000000000000de48a806508746fd" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000de48a806508746fd Content-Type: text/plain; charset="UTF-8" psql (15.3, server 15.15) on linux dvdb=# \d arch_restore Table "misc.arch_restore" Column | Type | Collation | Nullable | Default --------+-------------------+-----------+----------+--------- pk | integer | | not null | proj | character varying | | | data | character varying | | | Indexes: "arch_restore_pkey" PRIMARY KEY, btree (pk) dvdb=# select * from arch_restore order by pk; pk | proj | data ----+-------+------------ 1 | alpha | the_data_1 2 | alpha | the_data_2 3 | alpha | the_data_3 4 | beta | the_data_4 5 | beta | the_data_5 6 | beta | the_data_6 7 | gamma | the_data_7 8 | gamma | the_data_8 9 | gamma | the_data_9 (9 rows) I decided that it's time to archive the 'beta' project. So I create a new DB in the PG instance and put them all there leaving... dvdb=# select * from arch_restore order by pk; pk | proj | data ----+-------+------------ 1 | alpha | the_data_1 2 | alpha | the_data_2 3 | alpha | the_data_3 7 | gamma | the_data_7 8 | gamma | the_data_8 9 | gamma | the_data_9 (9 rows) And in the "beta_archive" DB I have the beta proj recs... dvdb=# select * from arch_restore order by pk; pk | proj | data ----+-------+------------ 4 | beta | the_data_4 5 | beta | the_data_5 6 | beta | the_data_6 (9 rows) The managers tell me that they want to restore "beta" back to the main DB. I know I could do this with something like a perl script, making connections to both DBs and transferring them over using select statements in "beta_archive" and insert statements in the main DB. But is there a better way, something inherent in PG SQL that allows me to do something like this ? Thanks in Advance --000000000000de48a806508746fd Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
psql (15.3, server 15.15) on linux

dvdb=3D# \d arch_restore
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Table "misc.arch_restore"<= br>=C2=A0Column | =C2=A0 =C2=A0 =C2=A0 Type =C2=A0 =C2=A0 =C2=A0 =C2=A0| Co= llation | Nullable | Default
--------+-------------------+-----------+-= ---------+---------
=C2=A0pk =C2=A0 =C2=A0 | integer =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | not null |
=C2= =A0proj =C2=A0 | character varying | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
=C2=A0data =C2=A0 | character varyi= ng | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0|
Indexes:
=C2=A0 =C2=A0 "arch_restore_pkey" PRIMARY KE= Y, btree (pk)

dvdb=3D# select * from arch_restore order by pk;
= =C2=A0pk | proj =C2=A0| =C2=A0 =C2=A0data =C2=A0 =C2=A0
----+-------+---= ---------
=C2=A0 1 | alpha | the_data_1
=C2=A0 2 | alpha | the_data_2=
=C2=A0 3 | alpha | the_data_3
=C2=A0 4 | beta =C2=A0| the_data_4
= =C2=A0 5 | beta =C2=A0| the_data_5
=C2=A0 6 | beta =C2=A0| the_data_6=C2=A0 7 | gamma | the_data_7
=C2=A0 8 | gamma | the_data_8
=C2=A0 9= | gamma | the_data_9
(9 rows)

I dec= ided that it's time to archive the 'beta' project.=C2=A0 So I c= reate a new DB in the PG instance and put them all there leaving...

dvdb=3D# select * f= rom arch_restore order by pk;
=C2=A0pk | proj =C2=A0| =C2=A0 =C2=A0dat= a =C2=A0 =C2=A0
----+-------+------------
=C2=A0 1 | alpha | the_= data_1
=C2=A0 2 | alpha | the_data_2
=C2=A0 3 | alpha | the_data_= 3
=C2=A0 7 | gamma | the_data_7
=C2=A0 8 | gamma | the_data_8
=C2=A0 9 | gamma | the_data_9
<= span style=3D"font-family:monospace">(9 rows)

And in the "beta_archive" DB I have the beta proj recs...

dvdb=3D# select = * from arch_restore order by pk;
= =C2=A0pk | proj =C2=A0| =C2=A0 =C2=A0= data =C2=A0 =C2=A0
----+-------+------------
=C2=A0 4 | beta =C2= =A0| the_data_4
=C2=A0 5 | beta =C2=A0| the_data_5
=C2=A0 6 | bet= a =C2=A0| the_data_6
(9 rows)

The managers tell me that they want to restore "beta" back to th= e main DB.=C2=A0
I k= now I could do this with something like a perl script, making connections t= o both DBs and transferring them over using select statements in "beta= _archive" and insert statements in the main DB.=C2=A0 But is there a b= etter way, something inherent in PG SQL=C2=A0that allows me to do something= like this ?

Thanks in Advance
--000000000000de48a806508746fd--