I have a pg_dump backup which represents a slightly older version of a client dataset. A newer schema is running on the client's production environment, and there is not a readily available tool to convert the old schema into the newer one.

The only difference between the newer schema and the one represented in the backup is that some of the columns have been renamed, and a few of the types marked as varchar have become text instead.

I was hoping there'd be something like a "mapping file" that would instruct PostgreSQL to import the data and rename/retype the columns so as to wind up with the correct result, but I think I may have to resort to pgScript instead.

Does PostgreSQL offer a facility that would make this sort of restoration operation possible?

  • Huh? PostgreSQL doesn't have a string datatype. Do you mean varchar ? Anyway: this doesn't make a ton of sense. If you restore a dump, you're generally going to be erasing the existing data anyway. So if you want to restore an old dataset you just do a DDL-and-data dump (the default) and restore with --clean; this drops all existing tables and deletes all data then restores the new schema and data. – Craig Ringer Aug 11 '14 at 1:55
  • @CraigRinger Sorry, yes, I was typing this late and mixed up the application datatypes with the db's. I meant varchar, will correct. I think you misunderstood me, though: the application expects the new schema, not the old one. So merely reloading the old schema from the dump won't work; a transformation is required. – John Feminella Aug 11 '14 at 12:03
  • Ah - the old schema is the backup. Rather. You will probably need to restore to a staging db and make any required transformations there. Then dump the result and restore to the real target. – Craig Ringer Aug 11 '14 at 12:38
  • If you are lucky enough, you have the diff from the old schema to the new one at hand. Do the restore as @Craig said and apply the diff. – dezso Aug 11 '14 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.