I'm DB newb doing a software upgrade, and my predecessor's original migration plan may not have been as well thought out as I thought...

The Plan was to use our backup, created via

pg_dump -U derp -h db-host -Fc derp > backup

Flash a clean VM with an upgraded software, with its fancy new schema and use

pg_restore -v -U derp -n public -c -1 -h db-host -d derp backup

to load it with all its fancy old data.

Unfortunately I'm getting errors because of the updated schema, with new columns that create new relations. "cannot drop constrain X on table Y because other objects depend on it"

I tried using -a (data only) but that fails fast because there are many lookup tables with now duplicated keys.

Can I somehow import the existing copy?

If not, can I alter the new database schema in a way that will allow my old dump to work?

If not.... can I make a different, more useful copy of the old database?

If I'm asking really dumb questions, what should I be asking?

  • Where is this "fancy" new schema supposed to be coming from? Your restore brings the old schema with it. Well, it is trying to, anyway. Was your predecessor's migration ever tested and did it ever work? – jjanes May 24 at 15:47
  • @jjanes did it ever work? Probably not, at least not as well as he intended it to. The new schema is the system's default, laid down by the installer. It's an updated version of the old one, a few more columns, maybe fresh tables, etc. We have incremental update scriptlets so I should have no problem updating the old to the new, if I can only get it imported properly – InfernalRapture May 24 at 15:51
  • It sounded like you still have the old database around, don't you? Just run the scriplets on the old database, then do the dump and reload after that. It sounds like you are trying to upgrade two independent things (app software with schema implications, and database server software version) at the same time, which is generally a bad idea. – jjanes May 24 at 16:03

It sounds you are dealing with some 3rd party's application. In other words, the app vendor and your predecessor are not the same person.

The 3rd party would generally provide you with two things, an installer to use for new customers, and an upgrade process to upgrade the schema between versions of their app for existing customers. You either need to use one, or the other, not some bastardized combination between them. Restoring a dump of the old schema+data over the top of a newly-initialized schema for new customers would not be expected to work, unless the vendor said it does.

The vendor might also provide instructions on how to upgrade your PostgreSQL version, either by itself or coupled with upgrading the application version. If they do, then find and follow those instructions. If they don't then you have a few options.

  • Go back to using the old database. Run the upgrade scripts against it. Worry about migrating that to a new database server later.

  • Create an empty database cluster (completely empty, not using the vendor's new-customer initialization stuff) and restore to that database cluster. The easiest way to do that is probably to use the "-C" (note upper case) flag to pg_restore. This will create the new database for you, with the name, encoding, etc. which was recorded in the dmp file. Alternatively, perhaps you just use your existing cluster (initialized with the app vendors new-customer schema) and add the -C flag to your restore. What that will do is drop the database they provided in-bulk (which will get around the dependency problems) and recreate it from what is in the dump file, meaning you get the old schema not the new one. Either way, you will now have the old application schema running in a new PostgreSQL database. Once this is working, then run the application upgrade script against this.

  • Ask the 3rd party app vendor what to do.

  • Step 2 there, was what I needed to resolve my issue, I modified the "recover" scripts to drop/recreate a blank database, use the -C for the import, and then run the scriplets to get the schema current. – InfernalRapture May 24 at 21:19

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.