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!
