Postgresql - unrecognized configuration parameter

PostgresqlPsql

Postgresql Problem Overview


I exported a postgresql database from an external server, and attempted to import it into my local server but got this error:

unrecognized configuration parameter "idle_in_transaction_session_timeout"

Does this kind of error mean that the two servers are using different versions of postgresql? I looked into that, and the external server is running:

version
PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit	

and my server is running:

version
PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.2) 5.4.0 20160609, 64-bit	

Pretty much the same thing. Is there a site where you can see all of the valid config parameters for each version? And is there a way to sync up two databases like this, so incompatibilities like this get patched up automatically?

Postgresql Solutions


Solution 1 - Postgresql

According to Postgresql 9.6 Release Notes the idle_in_transaction_session_timeout parameter was introduced in version 9.6.

> E.2.3.1.10. Server Configuration > > Allow sessions to be terminated automatically if they are in > idle-in-transaction state for too long (Vik Fearing) > > This behavior is controlled by the new configuration parameter > idle_in_transaction_session_timeout. It can be useful to prevent > forgotten transactions from holding locks or preventing vacuum cleanup > for too long.

Since you are using version 9.5 on the server, the parameter is not recognized.

It's possible that you used version 9.6 of the Postgresql client to export data from the the source 9.5 server and the parameter was introduced in the dump file. If this was the case I would recommend using a 9.5 client version to export and import the data.

Solution 2 - Postgresql

The accepted answer is the way to go, but if for some reason you can not upgrade version, here is a workaround.

  1. Export using plain text. You probably want to use compression too. pg_dump -F c -Z 9 dbname > file.zip
  2. Before import, we need to remove the offending parameter. To do that we can use zcat and grep. zcat file.zip | grep -vw "idle_in_transaction_session_timeout" | psql -d newdb

Note that there are drawbacks using psql instead of pg_import. For instance, one can not use the -j to import concurrently.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionHorse O'HoulihanView Question on Stackoverflow
Solution 1 - PostgresqlcjungelView Answer on Stackoverflow
Solution 2 - PostgresqlTravis StevensView Answer on Stackoverflow