Postgresql - unrecognized configuration parameter

Question

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?

Answer 1

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.

Answer 2

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.