From: | Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Detect supported SET parameters when pg_restore is run |
Date: | 2016-09-27 01:56:22 |
Message-ID: | CAKOSWNkz0RfvBvsv+CRPC5z47seCwo9saHcAzPrxDOzYvfoL_A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)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
Attachment | Content-Type | Size |
---|---|---|
detect_supported_set_parameters_for_pgrestore.001.patch | application/octet-stream | 1.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2016-09-27 02:05:20 | Re: Stopping logical replication protocol |
Previous Message | Amit Langote | 2016-09-27 01:41:53 | Re: pgbench - allow to store select results into variables |