Hackers, At work we use several major versions of PostgreSQL, and developers use non-local clusters for developing and debugging. We do dump/restore schemas/data via custom/dir formats and we have to keep several client versions for 9.2, 9.4 and 9.5 versions on local workstations because after pg_restore95 connects to 9.2, it fails when it sets run-time parameters via SET:
vitaly@work 01:21:26 ~ $ pg_restore95 --host DEV_HOST_9_2 -d DBNAME --data-only -e -1 -Fc arhcivefile Password: pg_restore95: [archiver (db)] Error while INITIALIZING: pg_restore95: [archiver (db)] could not execute query: ERROR: unrecognized configuration parameter "lock_timeout" Command was: SET lock_timeout = 0; Of course, it can be fixed avoiding "--single-transaction", but if there is inconsistent schema (or stricter constraints) part of schema/data is already changed/inserted and a lot of errors are generated for the next pg_restore run. The pd_dump has checks in "setup_connection" function to detect what to send after connection is done for dumping, but there is no checks in _doSetFixedOutputState for restoring. If there are checks it is possible to use a single version pg_dump96/pg_restore96 to dump/restore, for example 9.2->9.2 as well as 9.4->9.4 and so on. The only trouble we have is in "SET" block and after some research I discovered it is possible not to send unsupported SET options to the database. Please, find attached simple patch. For restoring to stdout (or dumping to a plain SQL file) I left current behavior: all options in the SET block are written. Also I left "SET row_security = on;" if "enable_row_security" is set to break restoring to a DB non-supported version. -- Best regards, Vitaly Burovoy
detect_supported_set_parameters_for_pgrestore.001.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers