9.6 parameters messing up my 9.2 pg_dump/pg_restore

Started by Ken Tanzeralmost 9 years ago9 messagesgeneral

ken.tanzer@gmail.com

almost 9 years ago

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
*http://agency-software.org/ <http://agency-software.org/>*
*https://agency-software.org/demo/client
<https://agency-software.org/demo/client>*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

Alvaro Herrera

alvherre@2ndquadrant.com

almost 9 years ago

In reply to: Ken Tanzer (#1)

Re: 9.6 parameters messing up my 9.2 pg_dump/pg_restore

Ken Tanzer wrote:

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.

pg_dump doesn't promise that its output is compatible with servers older
than itself. I'm afraid you're stuck with filtering the output somehow
to remove or maybe comment out those lines.

--
ï¿½lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David G. Johnston

david.g.johnston@gmail.com

almost 9 years ago

In reply to: Alvaro Herrera (#2)

Re: 9.6 parameters messing up my 9.2 pg_dump/pg_restore

On Wednesday, June 28, 2017, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

Ken Tanzer wrote:

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.

pg_dump doesn't promise that its output is compatible with servers older
than itself. I'm afraid you're stuck with filtering the output somehow
to remove or maybe comment out those lines.

Or explicitly use the 9.2 pg_dump instead of finding the 9.6 one in your
path.

David J.

Tom Lane

tgl@sss.pgh.pa.us

almost 9 years ago

In reply to: Alvaro Herrera (#2)

Re: 9.6 parameters messing up my 9.2 pg_dump/pg_restore

Alvaro Herrera <alvherre@2ndquadrant.com> writes:

Ken Tanzer wrote:

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.

pg_dump doesn't promise that its output is compatible with servers older
than itself. I'm afraid you're stuck with filtering the output somehow
to remove or maybe comment out those lines.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Ken Tanzer

ken.tanzer@gmail.com

almost 9 years ago

In reply to: Tom Lane (#4)

Re: 9.6 parameters messing up my 9.2 pg_dump/pg_restore

Thanks for the responses. For me, using the 9.2 binary was the winner.
Shoulda thought of that!

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

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?

Cheers,
Ken

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

Adrian Klaver

adrian.klaver@aklaver.com

almost 9 years ago

In reply to: Ken Tanzer (#5)

Re: 9.6 parameters messing up my 9.2 pg_dump/pg_restore

On 06/29/2017 12:05 AM, Ken Tanzer wrote:

Thanks for the responses. For me, using the 9.2 binary was the winner.
Shoulda thought of that!

On Wed, Jun 28, 2017 at 1:30 PM, Tom Lane <tgl@sss.pgh.pa.us
<mailto: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

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?

If you are using -l to pg_restore then you are also doing
--exit-on-error. In the case you showed(ERROR: unrecognized
configuration parameter "lock_timeout") that will not affect the data.
In fact in most cases that I have run across ERROR's are more
informational then data affecting.

Cheers,
Ken

--

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jeff Janes

jeff.janes@gmail.com

almost 9 years ago

In reply to: Ken Tanzer (#5)

Re: 9.6 parameters messing up my 9.2 pg_dump/pg_restore

On Thu, Jun 29, 2017 at 12:05 AM, Ken Tanzer <ken.tanzer@gmail.com> wrote:

Thanks for the responses. For me, using the 9.2 binary was the winner.
Shoulda thought of that!

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

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.

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.

(Or even more better yet, upgrade the servers from 9.2 to 9.6, and then use
9.6's pg_dump)

Cheers,

Jeff

Ken Tanzer

ken.tanzer@gmail.com

almost 9 years ago

In reply to: Jeff Janes (#7)

Re: 9.6 parameters messing up my 9.2 pg_dump/pg_restore

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

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

David G. Johnston

david.g.johnston@gmail.com

almost 9 years ago

In reply to: Ken Tanzer (#8)

Re: 9.6 parameters messing up my 9.2 pg_dump/pg_restore

On Thursday, June 29, 2017, Ken Tanzer <ken.tanzer@gmail.com> wrote:

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.

You can always add something to the wiki. It's going to be distribution
specific which makes adding it to the docs less desirable.

David J.