PostgreSQL upgrade.

2019-04-10 Thread Daulat Ram 
Hello team.

We have two node postgresql database version 9.6 with streaming replication 
which is running on docker environment, os Linux (Ubuntu) and we have to 
migrate on PostgresQL11. I need your suggestions & steps to compete the upgrade 
 process successfully.

Regards,
Daulat

Re: Commit(?) overhead

2019-04-10 Thread Duncan Kinnear 
- On 10 Apr, 2019, at 10:23 AM, Andres Freund [email protected] wrote:

> On 2019-04-09 17:12:27 -0500, Justin Pryzby wrote:
>> You could test that's the issue by comparing with fsync=off (please read what
>> that means and don't run your production cluster like that).
>> https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-FSYNC
> 
> I suggest testing it with synchronous_commit=off instead. That's about
> as fast for this type of workload, doesn't have cluster corruption
> issues, the window of a transaction not persisting in case of a crash is
> very small, and it can just set by any user in individual sessions.

Bingo! Adding 'SET LOCAL synchronous_commit TO OFF;' to my 'BEGIN; UPDATE ; 
COMMIT;' block has given me sub-1ms timings! Thanks Andres.

I'll probably leave the setting as that on my local machine. The option appears 
to be relatively safe, but my machine is just a dev test machine anyway.


Regards, 

Duncan Kinnear

Re: Commit(?) overhead

2019-04-10 Thread Laurenz Albe 
Duncan Kinnear wrote:
> Bingo! Adding 'SET LOCAL synchronous_commit TO OFF;' to my 'BEGIN; UPDATE 
> ; COMMIT;'
> block has given me sub-1ms timings! Thanks Andres.

That's a pretty clear indication that your I/O subsystem was overloaded.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: PostgreSQL upgrade.

2019-04-10 Thread Andreas Kretschmer 




Am 10.04.19 um 07:40 schrieb Daulat Ram:
We have two node postgresql database version 9.6 with streaming 
replication which is running on docker environment, os Linux (Ubuntu) 
and we have to migrate on PostgresQL11. I need your suggestions & 
steps to compete the upgrade  process successfully.


there are exists several ways to do that. You can take a normal dump and 
replay it in the new version, you can use pg_upgrade, and you can use a 
logical replication (using slony, londiste or pg_logical from 
2ndQuadrant). There is no 'standard way' to do that, all depends on your 
requirements and knowledge how to work with that tools.



Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com