I am getting the error of:

pg_restore: [archiver (db)] Error while INITIALIZING:
pg_restore: [archiver (db)] could not execute query: ERROR: unrecognized configuration parameter "idle_in_transaction_session_timeout"
Command was: SET idle_in_transaction_session_timeout = 0;

when i am trying to restore the data using

pg_restore -h -U postgres -d newdb05aug19 -1  dirfrmt;

The postgres version is PostgreSQL 9.3.23

The pg_dump version is pg_dump (PostgreSQL) 9.3.23

The pg_restore version is pg_restore (PostgreSQL) 9.3.23

  • 2
    Looks like you are either connecting to a modified fork of PostgreSQL, or the server version is less than 9.6. Please add details to the question that tell the version of pg_dump used to create the dump, the version of pg_restore used and the version of the PostgreSQL server. – Laurenz Albe Aug 5 '19 at 10:34
  • 1
    Sounds as if the dump was taken from a newer (supported) version of Postgres and isn't compatible with the (unsupported) version 9.3 – a_horse_with_no_name Aug 5 '19 at 11:04
  • As you said, The dump file is from version 9.5, I faced these errors while dumping into the server containing postgres version 9.3. But, When dumped into the same or higher version it works fine. @a_horse_with_no_name – akhilesh kedarisetty Nov 8 '19 at 9:25

The pg_dump version is pg_dump (PostgreSQL) 9.3.23

Is that the program which was used to generate the "dirfmrt" directory? It doesn't matter what pg_dump you have install locally, if the backup file came from somewhere else.

The version of the running postgresql server which was dumped, and the version of pg_dump used to dump it, are included in the dump. You can see them (as comments) using something like this:

pg_restore dirfrmt | head -n6

The pg_restore version is pg_restore (PostgreSQL) 9.3.23

While you may have that version of pg_restore installed somewhere in your system, it does not seem to be the one you are actually using to do the restore. Whether SET idle_in_transaction_session_timeout = 0; is emitted depends on the version of pg_restore used, and 9.3 does not emit it.

  • As you said, Actually this error is because of version mismatch. The backup file is from recent postgres version, And i have tried to restore it in 9.3. But, When tried in the versions 9.5 and 9.6 process was clean. Thanks for the info...!!!! – akhilesh kedarisetty Nov 8 '19 at 9:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.