Restoring a PostgreSQL database from 14 to 15?

I have a PostgreSQL server running CentOS Linux release 7.5.1804 (Core), and just a couple days ago I upgraded three client workstations from 14.3.1 Studio to 15.0 Studio.

I have a `.backup` file of a PostgreSQL database that was created from back when I was running Resolve 14, and it was created by running the `pg_dump` command. I can't remember what exact version of 14 it was.

Of course, immediately after I upgraded to 15, I immediately got a request to restore this old 14 database...

Now, typically, restoring such a `.backup` file is easy--I'd just create a fresh, blank PostgreSQL database from the GUI on the client workstation, and then SSH into the CentOS server and run `pg_restore`. I'm getting an error when I try to do that now, though, and I suspect that it's because I'm creating a fresh blank 15 database and then trying to restore a 14 backup into that 15 database.

Code: Select all

pg_restore --host localhost --username postgres --single-transaction --clean --if-exists --dbname=redacted redacted_2018_04_26_07_19.backup
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3772; 2606 116959 CONSTRAINT Sm2TiItem_pkey postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  cannot drop constraint Sm2TiItem_pkey on table "Sm2TiItem" because other objects depend on it
DETAIL:  constraint Sm2TiCompositionTable_Sm2TiItem_fkey on table "Sm2TiCompositionTable" depends on index "Sm2TiItem_pkey"
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
    Command was: ALTER TABLE IF EXISTS ONLY public."Sm2TiItem" DROP CONSTRAINT IF EXISTS "Sm2TiItem_pkey";

So my question is: how could/should I actually restore this database? Do I need to uninstall 15.0, reinstall 14.3.1, restore the database with `pg_restore`, then uninstall 14.3.1 again and reinstall 15.0 again? I can do that, but it's a bit of a hassle. Might there be any easy way to modify the contents of the `.backup` file so as to "upgrade" it?

[Peter Chamberlain]

To my knowledge you can’t restore a backup database into another DB. Best to import it into v15 then upgrade it to a v15 DB if that didn’t happen at import.

Figured it out!

So, when using the `pg_restore` command, a blank database needs to already exist, and then the `.backup` file is restored into that blank database. See the PostgreSQL documentation: https://www.postgresql.org/docs/9.5/sta ... store.html

The problem that I was running into was that I was creating the new database with the Resolve 15 GUI, which I guess was formatting it with some parameters that only exist in 15. Trying to restore the data from a 14 database into a 15 database was almost certainly causing weird conflicts.

What actually worked was using the `createdb` command on the server to create a totally blank database, then using `pg_restore` as usual. Then, I connected to the database from the 15 GUI, and was able to upgrade the database from the 14 format to the 15 format. Voilà!