Re: can recs be transferred between DBs ?

On Tue, Apr 28, 2026 at 11:56 AM dfgpostgres <dfgpostgres3(at)gmail(dot)com> 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!