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?

Headwater

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.

Tenorrhaphy

That must be it. I made the backup with Postgresql Maestro (its an admin tool like pgadmin) on my Windows host which has 9.6 installed. But the guest machine has 9.5. The database I backed up is running 9.5 too, so looks like Maestro doesn't take into consideration the version of the external server when its making a backup. – Baronet

I have PostgreSQL v13.1 and I still see the error – Handcuff

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.

Ranger

Answer 3

I got the same error below:

ERROR: unrecognized configuration parameter "num"

When I didn't use . to set a custom option as shown below:

SET num = 2;

Or, when I used the custom option which I've never set (even once) in the session as shown below:

SELECT current_setting('my.num');

So, I used . as shown below, then I could set a custom option without error:

SET my.num = 2;

Then, I could use the custom option without error as shown below:

postgres=# SELECT current_setting('my.num');
 current_setting
-----------------
 2
(1 row)

Laurenelaurens

Answer 4

I would recommend checking your pg_restore options; the normal behaviour would be to ignore this error.

Have you perhaps added -1 or --exit-on-error?

Niersteiner

Answer 5

On PostgreSQL 16, the error might appear upon creating database. Seem to be the database was not created, but after right click to PostgreSQL 16 and select disconnect from the server, the database you created will appear.

Home