can recs be transferred between DBs ?

On Tue, Apr 28, 2026 at 11:56 AM dfgpostgres <dfgpostgres3@gmail.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!

On Tue, Apr 28, 2026 at 8:56 AM dfgpostgres <dfgpostgres3@gmail.com> wrote:

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 ?

pg_dump / pg_restore are the core tools at your disposal. The ability to
specify --inserts and --on-conflict-do-nothing on pg_dump in particular
make at least attempting this against a copy of the backups quite appealing
before trying to write a more targeted transfer script.

David J.

On 4/28/26 8:56 AM, dfgpostgres 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...

How did you transfer the data to the new database and could you not use
the same process to transfer the beta data back?