Moving data into a different schema between postgreSQL databases

Question

I have a the same table in two databases within one postgres server.

I would like to know if it is possible to copy a table under schemaA in DB1 to schemaB in DB2. Or alternatively, copy the data from a table under schemaA in DB1 to the equivalent empty schemaB in DB2.

I have researched a found the pg_dump code to be effective, however I would need to rename the schema to the destination schema before executing...

    pg_dump -t schemaAt.table 'DB1' | psql 'DB2'

I am also wondering if 'pg_restore' command can be used to to restore the data only into a new schema?

Any help would be much appreciated

Thanks

Does the new schema exist in the old database? If not, you can simply dump/restore (which would create the "old" name in the "new" database), then rename the schema to the new name. Or simply use a foreign table from db2.schemab.table to point to db1.schemaa.table then you don't need to copy anything. Or merge both databases into one. — user330315
– user330315, Commented Aug 19, 2020 at 8:36

Martijn Pieters · Accepted Answer · 2022-10-07 12:21:07Z

1

Yes, you are right, the only way to do it involves renaming the schema because the dump always contains the source schema name. But this can be done easily like this.

./pg_dump -a -t schemaA.table -U postgres -d DB1 |sed 's/schemaA/schemaB/g'|  psql DB2 -h localhost -p 5432 -U postgres

The above command will copy the table schemaA.table data from Db1 to pre-existing table schemaB.table in DB2.

edited Oct 7, 2022 at 12:21

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

answered Aug 19, 2020 at 8:21

Amjad Shahzad

8327 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sys · Accepted Answer · 2020-08-18 10:02:33Z

0

COPY command will be useful, for data only. There are options.

answered Aug 18, 2020 at 10:02

sys

3403 silver badges17 bronze badges

Comments

Nik · Accepted Answer · 2020-08-19 09:33:56Z

0

If you want to avoid dealing with commands, best thing would be, export data from DB1 in CSV or Text file and use classy GUI wizard of pgAdmin tool to import data in Postgres.

answered Aug 19, 2020 at 9:33

Nik

4611 gold badge9 silver badges22 bronze badges

Collectives™ on Stack Overflow

Moving data into a different schema between postgreSQL databases

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related