Thread: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore
Hi. I've got a CentOS server with 9.2 and 9.6 both running. (Both from PGDG). I've got a cron job that transfers data from one DB to another, that recently stopped working, and I traced it to my installing 9.6. The dump comand is pretty straightforward:
pg_dump -c -O -t "${prefix}*"...
But at the top it sets a bunch of parameters, some of which are unrecognized by 9.2, which then throws an error and causes my transaction to fail. Top of the dump file:
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.2.21
-- Dumped by pg_dump version 9.6.3
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET row_security = off;
And output from running pg_restore:
BEGIN
SET
ERROR: unrecognized configuration parameter "lock_timeout"
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
...
I didn't see any options for dealing with this, though I'm hoping I'm missing something easy or obvious. Any suggestions or help would be appreciated. Thanks.
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
Thanks for the responses. For me, using the 9.2 binary was the winner. Shoulda thought of that!
Well sure, I can see it increases your chances of getting _something_ restored. But there's also a lot to be said for ensuring that _all_ your data restored, and did so correctly, no?
On Wed, Jun 28, 2017 at 1:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Generally speaking, it helps a lot if you don't insist on restoring the
output in a single transaction. In this case, that would allow the
restore to ignore the new parameters and move on.
regards, tom lane
Cheers,
Ken
--
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
On Thu, Jun 29, 2017 at 9:34 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
-- Well sure, I can see it increases your chances of getting _something_ restored. But there's also a lot to be said for ensuring that _all_ your data restored, and did so correctly, no?Record the errors, and look through them to decide if they are important or not.
I'd still rather have the data be correct, or not at all. It also greatly increases the chances someone will notice it, and let me know about it.
But better yet, use v9.2 of pg_dump to dump things out of a 9.2 server which you want to load to another 9.2 server. Don't be at the mercy of your $PATH.
Yep, that's the direction I went.
(Or even more better yet, upgrade the servers from 9.2 to 9.6, and then use 9.6's pg_dump)
On the todo list. I don't imagine though that I'm the only one who would install a newer version of PG, do some testing, and then upgrade DBs to the newer version, and possibly not do it all immediately and at once.
I think it's great and impressive that you can install and run two versions simultaneously, but I have found a couple gotchas in the process. Maybe those are documented somewhere, but if so I haven't seen it. The issues I hit all had fairly easy solutions, but I'd humbly suggest that a "things to watch out for when running multiple versions of Postgres concurrently" might be a useful document.
Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
learn more about AGENCY or
follow the discussion.