-2

I have to move several tables from one remote database to another in PostgreSQL.

From my understanding the only way of doing it is via the pg_dump.

pg_dump -h <source_db_host> -U <source_db_user> -d <source_db_name> -f database_dump.sql

psql -h <target_db_host> -U <target_db_user> -d <target_db_name> -f database_dump.sql

However, it fails as the schema in destination database is different from the one in source, so I receive the error message that particular schema is not exist in source db.

Is there anyway how to change the destination schema?

Is there any other easier option for transferring the datasets?

jarlh
  • 42,561
  • 8
  • 45
  • 63
YIF99
  • 51
  • 1
  • 8
  • I'm not sure I understand what you are trying to do. Do you want to merge db1,db2,db3 on server A into db4 on server B? If so does db4 contain any tables etc yet? Do db1,db2,db3 have the same or different schemas? If the same what are you hoping to do with data in matching tables? – Richard Huxton Aug 04 '23 at 17:00
  • 1
    Your dump contains all tables from your source_db_name, not just a few of them. – Frank Heikens Aug 04 '23 at 17:21

1 Answers1

1

You can either use the schema option with pg_dump

pg_dump -h <source_db_host> -U <source_db_user> -d <source_db_name> --schema=schema_name -f database_dump.sql then include the schema name or you can use pg_dumpall to dump both the databases and the schemas.

pg_dumpall -h <source_db_host> -U <source_db_user> -f database_dumpall.sql

psql -h <target_db_host> -U <target_db_user> -f database_dumpall.sql

You can also take a look at the pg Docs to get more insight into pg_dump

Nnaemeka Daniel John
  • 333
  • 1
  • 7
  • or use `pg_dump --help`, which shows `-t, --table=PATTERN dump the specified table(s) only` which can be use to dump only 1 table – Luuk Aug 04 '23 at 18:16