pg_restore error: unrecognized configuration parameter "transaction_timeout"

Applies to

• Liquibase Enterprise (Datical DB)

Conditions

• PostgreSQL Database v16 or lower
• Postgres Command Line Utilities v17+

Issue Summary

Liquibase Enterprise (Datical DB) uses the Postgres Command Line Utilities pg_dump and pg_restore to handle backup/restore processes for Postgres databases. While these utilities are typically backwards compatible, the error 'unrecognized configuration parameter "transaction_timeout"' can occur when attempting to restore a Postgres v16 or lower database with v17+ Postgres Command Line Utilities.

Error Message

stderr: pg_restore: error: could not execute query: ERROR: unrecognized configuration parameter "transaction_timeout"
Command was: SET transaction_timeout = 0;
pg_restore: warning: errors ignored on restore: 1

Root Cause

In v17+ of the Postgres Command Line Utilities, pg_dump includes the configuration parameter "transaction_timeout" in the archive file of the database, whether or not that configuration is set. When running pg_restore on a v16 or older Postgres database, the configuration is not recognized, causing the restore process to fail.

The resolution below will allow the backup file to be used, but the issue will occur again until either the database is upgraded to Postgres v17+, or the Postgres Command Line Utilities used by the backup process are downgraded to v16 to match the database version.

Resolution

Manually edit the backup file to remove the line SET transaction_timeout = 0;. This will allow the database restoration process to proceed as usual the next time it is triggered.

Related Article(s)

[Error] Exception occurred syncing caching tables in datical server

[Error] Cannot open backup device, The system cannot find the file specified