Thread
-
can recs be transferred between DBs ?
dfgpostgres <dfgpostgres3@gmail.com> — 2026-04-28T15:56:28Z
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 ? Thanks in Advance -
Re: can recs be transferred between DBs ?
Ron Johnson <ronljohnsonjr@gmail.com> — 2026-04-28T16:00:09Z
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!
-
Re: can recs be transferred between DBs ?
David G. Johnston <david.g.johnston@gmail.com> — 2026-04-28T16:05:31Z
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.
-
Re: can recs be transferred between DBs ?
Adrian Klaver <adrian.klaver@aklaver.com> — 2026-04-28T16:27:07Z
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? > > Thanks in Advance