CODEWITHSUNDEEP
sqlpostgresql
Paul Richter
Paul Richter

Reputation: 11072

Exporting from one schema and importing to another with pg_dump

I have a table called units, which exists in two separate schemas within the same database (we'll call them old_schema, and new_schema). The structure of the table in both schemas are identical. The only difference is that the units table in new_schema is presently empty.

I am attempting to export the data from this table in old_schema and import it into new_schema. I used pg_dump to handle the export, like so:

pg_dump -U username -p 5432 my_database -t old_schema.units -a > units.sql

I then attempted to import it using the following:

psql -U username -p 5432 my_database -f units.sql

Unfortunately, this appeared to try and reinsert back in to the old_schema. Looking at the generated sql file, it seems there is a line, which I think is causing this:

SET search_path = mysql_migration, pg_catalog;

I can, in fact, alter this line to read

SET search_path = public;

And this does prove successful, but I don't believe this is the "correct" way to accomplish this.

Question: When importing data via a script generated through pg_dump, how can I specify in to which schema the data should go without altering the generated file?

Upvotes: 2

Views: 6699

Answers (1)

khampson
khampson

Reputation: 15306

There are two main issues here based on the scenario you described.

  1. The difference in the schemas, to which you alluded.
  2. The fact that by dumping the whole table via pg_dump, you're dumping the table definition also, which will cause issues if the table is already present in the destination schema.

To dump only the data, if the table already exists in the destination database (which appears to be the case based on your scenario above), you can dump the table using pg_dump with the --data-only flag.

Then, to address the schema issue, I would recommend doing a search/replace (sed would be a quick way to do it) on the output sql file, replacing old_schema with new_schema.

That way, it will apply the data (which is all that would be in the file, not the table definition itself) to the table in new_schema.

If you need a solution on a broader level to support, say, dynamically named schemas, you can use the same search/replace trick with sed, but instead of replacing it with new_schema, replace it with some placeholder text, say, $$placeholder_schema$$ (something highly unlikely to appear as as token elsewhere in the file), and then, when you need to apply that file to a particular schema, use the original file as a template, copy it, and then modify the copy using sed or similar, replacing the placeholder token with the desired on-the-fly schema name.

You can set some options for psql on the command line, such as --set AUTOCOMMIT=off, however, a similar approach with SEARCH_PATH does not appear to have any effect.

Instead, it needs the form \set SEARCH_PATH to <path>, which can be specified with the -c option, but not in combination with -f (it's either or).

Given that, I think modifying the file with sed is probably the best all around option in this case for use with -f.

Upvotes: 4

Related Questions