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 1wHkrL-007U8O-3C for pgsql-general@arkaria.postgresql.org; Tue, 28 Apr 2026 16:00:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wHkrK-000VPo-01 for pgsql-general@arkaria.postgresql.org; Tue, 28 Apr 2026 16:00:26 +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 1wHkrJ-000VPg-27 for pgsql-general@lists.postgresql.org; Tue, 28 Apr 2026 16:00:25 +0000 Received: from mail-ot1-x32a.google.com ([2607:f8b0:4864:20::32a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wHkrH-00000003Y2S-30Ut for pgsql-general@postgresql.org; Tue, 28 Apr 2026 16:00:25 +0000 Received: by mail-ot1-x32a.google.com with SMTP id 46e09a7af769-7de431da8fbso5527574a34.1 for ; Tue, 28 Apr 2026 09:00:23 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1777392021; cv=none; d=google.com; s=arc-20240605; b=XfzMM9mzdfwRwqGqPuy4eAVn83b3ROQMQ3LlrAkGb/Ivbn2WchMtnmerb5FerAB9Fv 46xhWRzubZpTpu/huN0+FdWnYUqzV4zWXvByYbBJlGoqv7YDhBk9BzmcPk7qWdw08oNU /YpH10j7BRtgUVqczGq1YtoJNuh/ntxrullkyQLSQauUrWjD7swRNhwNGU4ID4O/FWYe P3hjmAIJTHS+A3QmdZKm2rKqaygF8SkSzuKD2o2H1UOilb8eVlZe8j397XS47asQTOHc a705SmNpFX1BwUJ+GM9Z5d6NX2k4xWGNPe+NeLN4fziS7iZFglPZPuKNn6Jm7iw1POhw gQLQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :dkim-signature; bh=J8JSluz32uFKUygtGa/8lTqiukZfqXGePbgdzQkTI8c=; fh=KNSq+t9BltSXFnT3Yof/aKGBtqxeA+bTALiYdvTslaY=; b=MvumYjBNvfksnmsfsr+FC125kMYgTiMk8zajdK+NWczhlXj4i/93P8Ae/Zi0xFpurT 4Uvpeo4ytyu/mMCPB2APoTPnVduj6NP4EiY21hZg+BX8jSgTspzP1/l/byefEdvPxPzZ cclNyyZbA5Kz3mqGDVu6O9f2QaIQ/7a5R4lZlE2u/U5vHunAmJXt0aoH4WezT27x6Pf/ SO4tyFd09FMRbYVGx5amlct4vZkydRZCwM/Y2Slzr99PeOOzID6Zoh7UYEb2q5TvHPWE JPsP+wtyK1SYukDOD7eSlbx09Qhlr8fBlJ8JodLwOL4gW4epevKEqEG4hElftV7UcCr5 eiOw==; 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=1777392021; x=1777996821; darn=postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=J8JSluz32uFKUygtGa/8lTqiukZfqXGePbgdzQkTI8c=; b=U0qxOzNBNYrxiUSQgxlG7weBTjS7Bw9RgrldfcKqcMILr6NZNlGhUgppUgFLLuhGTb zTObaSe4b0MfKKy4UR6MEJQDu/aucbDrTDWp6Xs+IryNMT8TXK4NE1FHy81Wz0hIfYwU 4PrDL0IYSphvR8uptvvjUgMGQexvzcYu/NXAWrqZSdPQcCtHHisALC3QTkC63kIt3Kx5 FzViAdIayc1wW8RshSxPUHWUCGtbV6MgHDjsGPUSM1DHa1fWRpmrvirC3KKUnEt8EFTa a2XMWua0fN32Zm64HhUkQskFX3NMjioMFByD94aEB3yRqkhka22vAUFt/UT7OU0qlp4b Toxg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1777392021; x=1777996821; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :x-gm-gg:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=J8JSluz32uFKUygtGa/8lTqiukZfqXGePbgdzQkTI8c=; b=iEZ8jtf/ww67TEGjDITQBfskceqx9Ma199OaWDZrjZFLC80XoJFE6PVbR/Or+Qgn1i 6zRe3AwGxnKKSfXGqqoOTyVYBuIEJqgXNouzYO0lKaQxOyT0swakOuS24zZF4DI11Ev5 UDft++98eAIQN1oIBfC1nNPfGOQnCaO8/PQ2PSeo4DO66E261Y/0wOcXYQN/LK3IwSVl //NJV1pjVeBVM30cyVdJTBqP1efVFtKVzSI6Qh2QOoAbjVJ651aXyLxE20UNF4wsppTs MmhHfZAzPZnDUGOmNAMXeN4AdJYqK0MAST6mnWon+fe09XbaKFtFzPPTFyQseHKeaMZO wDZw== X-Gm-Message-State: AOJu0Yw3m9WW1Ab6D0SADmYn1Lg9NB5682pLlzMhA93cKfkwdPYK3X1+ gIX+jIgP75UJft9O7/ZJOzbqWVekF9CbWfU6w+rltxXcT1AP0gH6ez8w4sVdQ5vDsqerqByKp1+ JbiYxB2uIe1PhSnvxowSX2JpOU2FmNhSE6A== X-Gm-Gg: AeBDiesJOrp+6s1tUHRwBza1iFVsqe+gKnKNiRZdP2bJzDo9eU2Pxay9BYdbQnp9PNA dcZLxRjJPaBGMoLeI053vC8s47IBBl6MlSFqXs9NtS95PvkejPzhpGsKkDVr5+M1MwwLWB48YRp /X4ayVHwSSVP0nHbnw90l0f8OPrH7D1+oSd9Rre06MpdMntlV119MnxcPnZBxh9SoVMSrmX6jVJ hKW7jWxsRGTTNCMS8z8sHBNYd3/Qzkzy07ypE0xslyWdmjmTZJpAEb5BskqU+nhgxhL1BKSosGv ZsS0p7MJ23V8JXAYQUo7DCBJK77ITA== X-Received: by 2002:a05:6830:6a8b:b0:7db:f6a1:3742 with SMTP id 46e09a7af769-7de9a1661camr2501421a34.28.1777392021378; Tue, 28 Apr 2026 09:00:21 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 28 Apr 2026 12:00:09 -0400 X-Gm-Features: AVHnY4J6MUj_D8jw6UYVmZn5SHlRp0NPfqXFtzqxxVIl7d0g9mx_raY63myxUnU Message-ID: Subject: Re: can recs be transferred between DBs ? To: pgsql-general Content-Type: multipart/alternative; boundary="0000000000000f9c0d06508754c4" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000f9c0d06508754c4 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Apr 28, 2026 at 11:56=E2=80=AFAM dfgpostgres wrote: > psql (15.3, server 15.15) on linux > > dvdb=3D# \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=3D# 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 ne= w > DB in the PG instance and put them all there leaving... > > dvdb=3D# 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=3D# 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 statement= s > 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 ? > postgres_fdw might be what you want. That will let you INSERT INTO and SELECT FROM remote (or in this case "remote") PG databases. --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000000f9c0d06508754c4 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Apr 28, 2026 at 11:56=E2=80=AFAM = dfgpostgres <dfgpostgres3@gmai= l.com> wrote:
=
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"
=C2=A0Column | =C2=A0 = =C2=A0 =C2=A0 Type =C2=A0 =C2=A0 =C2=A0 =C2=A0| Collation | Nullable | Defa= ult
--------+-------------------+-----------+----------+---------
= =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 | charac= ter 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 varying | =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 KEY, btree (pk)

dvd= b=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 | t= he_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 decided that it's time = to archive the 'beta' project.=C2=A0 So I create a new DB in the PG= instance and put them all there leaving...

dvdb=3D# select * from arch_restore order b= y 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 7 | ga= mma | the_data_7
=C2=A0 8 | gamma | the_data_8
=C2=A0 9 | gamma |= the_data_9
(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=A0data =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 | beta =C2=A0| the_data_6<= /span>
(9 rows)

The managers tell me = that they want to restore "beta" back to the main DB.=C2=A0
I know I could do this wi= th something like a perl script, making connections to both DBs and transfe= rring them over using select statements in "beta_archive" and ins= ert statements in the main DB.=C2=A0 But is there a better way, something i= nherent in PG SQL=C2=A0that allows me to do something like this ?

postgres_fdw mig= ht be what you want.=C2=A0 That will let you INSERT INTO and SELECT FROM re= mote (or in this case "remote") PG databases.=C2=A0
--
Death to <Redacted>, and= butter sauce.
Don't boil me, I'm still alive.
<= ;Redacted> lobster!
--0000000000000f9c0d06508754c4--