Restoring macOS PostgreSQL database on Windows doesn’t work

I’m running DaVinci Resolve Project Server 17.1 on macOS Big Sur (v11.3.1).

I needed to restore some of the databases on a Windows 10 machine and installed DaVinci Resolve Project Server 17.1 for Windows that comes with PostgreSQL 9.5.4-1. However, the PostgreSQL on macOS is version 9.5.19 and the backups made on macOS can’t be restored in PostgreSQL 9.5.4 on Windows. The only way I could make it work was to install a newer version of PostgreSQL on Windows.

Is this expected? Are later versions of PostgreSQL officially supported by BMD or are users on their own if they update?

If you do install a newer version, there’s also the issue that PostgreSQL 9.5.x is deprecated. 9.6.22 is currently the last supported version if you want to download compiled binaries from the manufacturer.

It depend on how you made that backup. pg_dump -Fc type backups should be fine. If you just copied $PGDATA then of course not.

PG is using semantic versioning, so always use the last patch release e.g 9.5.x

Personally I'd be surprised if using version 13 wouldn't work out of the box. But if you have no idea about PG and how to debug things stick with whatever BM lists as supported.

Backups are made using the DaVinci Resolve Project Server as I imagined that would be the most compatible way (and supported by BMD). Not sure which parameters they use but I guess I can try to find out.

Whether it works or not is one thing, but I also want to know what BMD supports.

I compared a backup made in the DaVinci Resolve Project Server with a manual backup made using pg_dump in different formats.

The backup made using "pg_dump -Fc" matches perfectly, the only difference was two bytes in the header of the file.

So then what does pg_restore complain about? What is not working? Sure you can use funny wrapper tools, but if things fail you need to look what fails below.

What you need todo below funny GUI tools is pg_dump_all -g > globals.sql
pg_dump -Fc

On the new machine
psql - f globals.sql
pg_restore

I wasn't meaning for this topic to be about troubleshooting as I already solved it by updating to a newer version of PostgreSQL. I want to know what BMDs input is on this as their own Project Server application is no longer compatible between macOS and Windows. This can be a huge problem if you can't restore your backups using the BMD recommended versions of the operating systems. If I install DaVinci Resolve Project Server on any supported machine I expect it work.

The Project Server simply says the restore failed. If I restore using pgAdmin I get this output from pg_restore:

Code: Select all

pg_restore: [archiver] unsupported version (1.13) in file header

Obviously pg_restore version needs to be equal or newer than the version used to create the custom format dump.

The PG version on Windows is irrelevant, you just need access to such a pg_restore version somewhere on your network. The most obvious choice would be to just use the one on the Mac and direct it to the Windows box using --host. That also saves the time to move dumps around.

But ok, I understand you created this thread to cry and wine about different patch versions of PG on different platforms. Please continue.

Wow, take it easy. No, it's about what version BMD bundles in their download. Maybe official support by BMD isn't important to you, but I need to know that a backup I make can easily be restored by someone else in a completely different network.

created this thread to cry and wine

I prefer Gin & Tonic.