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?