public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: can recs be transferred between DBs ?
Date: Tue, 28 Apr 2026 12:00:09 -0400
Message-ID: <CANzqJaAYVcV+hZB1n5p6T21O6+j_K4aOPPgaPm13w9fGCwJN1A@mail.gmail.com> (raw)
In-Reply-To: <CAAcmDX9t2Od6DmPs3zUq=P2jDFhm-wiVsdF5=+yp=OtsYt1vxQ@mail.gmail.com>
References: <CAAcmDX9t2Od6DmPs3zUq=P2jDFhm-wiVsdF5=+yp=OtsYt1vxQ@mail.gmail.com>
On Tue, Apr 28, 2026 at 11:56 AM dfgpostgres <[email protected]> wrote:
> 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 ?
>
postgres_fdw might be what you want. That will let you INSERT INTO and
SELECT FROM remote (or in this case "remote") PG databases.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
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 using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: can recs be transferred between DBs ?
In-Reply-To: <CANzqJaAYVcV+hZB1n5p6T21O6+j_K4aOPPgaPm13w9fGCwJN1A@mail.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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox