Issue
I'm restoring a database backup on a PostgreSQL database running PostgreSQL 16 or older and I'm getting the following error:
ERROR: unrecognized configuration parameter "transaction_timeout"
Resolution
The transaction_timeout PostgreSQL setting was introduced with PostgreSQL 17. Starting with this version, pg_dump (version 17.*) adds a statement to disable the transaction timeout (setting transaction_timeout = 0) at the beginning of every PostgreSQL database dump:
--
-- PostgreSQL database dump
--
(...)
-- Dumped by pg_dump version 17.2 (Postgres.app)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
(...)
The unrecognized configuration parameter "transaction_timeout" error indicates that you're restoring a database backup taken with pg_dump version 17.* on a database using an older PostgreSQL version that doesn't recognize the transaction_timeout setting.
pg_dump doesn't guarantee that its output/dumps can be loaded into a server of an older major version. Therefore, it is recommended to use pg_dump to transfer data to the same or newer version than the source database.
To minimize version incompatibility conflicts, use the pg_dump client version that matches the version of the database that you need to back up. Then, restore the backup with pg_restore on a target database using the same, or newer, PostgreSQL database and client versions.
Running specific pg_dump and pg_restore versions on Heroku dynos
If you're running pg_dump and/or pg_restore from your Heroku dynos, verify the default PostgreSQL client version available for your app stack and install any other necessary PostgreSQL client versions with the Apt buildpack. See: "How can I install a specific PostgreSQL client version on my dynos?".
Alternatively, if you need to restore a backup/dump taken with pg_dump version 17.* into a database using an older version, you'd need to manually edit the dump file to remove the syntax not understood by the older database. If the dump is in the compressed binary format, you can use pg_restore -f <output_file> <dump_file> to convert the dump to plain text format and then remove the SET transaction_timeout statement. Note that other version incompatibilities might still impact the data transfer.