Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

[David G. Johnston]

On Thursday, June 29, 2017, Ken Tanzer  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.

Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

[Ken Tanzer]

On Thu, Jun 29, 2017 at 9:34 AM, Jeff Janes  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
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

[Jeff Janes]

On Thu, Jun 29, 2017 at 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  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

Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

[Adrian Klaver]

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 > 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.kla...@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

Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

[Ken Tanzer]

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  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


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

[Tom Lane]

Alvaro Herrera  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

Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

[David G. Johnston]

On Wednesday, June 28, 2017, Alvaro Herrera 
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.

Re: [GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

[Alvaro Herrera]

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 Herrerahttps://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

[GENERAL] 9.6 parameters messing up my 9.2 pg_dump/pg_restore

[Ken Tanzer]

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

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.