pg_restore error when restoring from another computer

[GuillaumeBringard]

Hello

I'm trying to restore a database backup on another computer, with the following command :

/usr/local/Cellar/postgresql@13/13.10/bin/pg_restore --host localhost --username postgres --password --single-transaction --clean --if-exists --dbname=test_restore_resolve /Users/me/Desktop/resolve_2023_02_21_09_00_From_Rocky.backup I end up with the following error in the Terminal :

pg_restore: while INITIALIZING: pg_restore: error: could not execute query: ERROR: unrecognized configuration parameter "idle_in_transaction_session_timeout" Command was: SET idle_in_transaction_session_timeout = 0;

Do you know if -and where and how- it is a parameter that I need to set on the inital server (the one that hosts the database and does the backup) or on the client I'm trying to restore on ?

[sethgoldin]

A cursory Google search shows this: https://stackoverflow.com/q/41528168

Perhaps you were using 9.5 on the one machine and you're using brew's version of 13 on this other machine? Try migrating only between matching versions.

[GuillaumeBringard]

Thanks for the reply @sethgoldin :-)

On the original server (Rocky Linux) when I ask postgres=# show server_version; it answers server_version ---------------- 13.7

You were right : on the iMac I'm trying to restore, there was still an old 9.5 server running. I was using the 13 version of the pg_restore but it was trying to restore on this 9.5 postgresql old local server.

So I got rid of this 9.5 (actually I went bezerk, since there were no database at all in it, I deleted the whole "/Library/PostgreSQL/" folder and restarted the iMac). I also uninstalled the postgresql 13 "Homebrew" version I had installed, to avoid confusion.

Then I downloaded and installed the DaVinci Resolve Project Server 18, as expected it installed Postgresql 13 in the process there : "/Library/PostgreSQL/13/"

Then I created an empty database via : sudo su - postgres and postgres$ /Library/PostgreSQL/13/bin/createdb test_restore_resolve (this part I could probably have done directly from DaVinci Resolve GUI)

Finally I've been able to restore the .backup file on the new database via /Library/PostgreSQL/13/bin/pg_restore --host localhost --username postgres --password --single-transaction --clean --if-exists --dbname=test_restore_resolve /Users/me/Desktop/resolve_2023_02_21_09_00_From_Rocky.backup

Long story short : thanks again @sethgoldin for pointing me in the right direction :-)