[GENERAL] pg_rewind issue

+ As 00150C7300A9 > 00150C740044 I'm confused why this WAL would be required? It's never been created on either server (although the same suffix does exist in timeline 14). Cheers, James Sewell (1) /usr/edb/as9.6/bin/pg_rewind --target-pgdata=/ppas/9.6/data/pg_data --source-server=

[GENERAL] pg_rewind issue

or archive management, which works fine the rest of the time. Can anyone think of a way this could be happening? Are we missing a step above? Cheers, James Sewell, -- -- The contents of this email are confidential and may be subject to legal or professional privilege and

Re: [GENERAL] Interesting streaming replication issue

_HINT records. Any ideas? Cheers, James James Sewell, PostgreSQL Team Lead / Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P *(+61) 2 8099 9000 <(+61)%202%208099%209000> *W* www.jirotech.com *F * (+61) 2 8099 9099 <(+61)%202%208099%209000

Re: [GENERAL] Interesting streaming replication issue

e log not only are they there, the standby has already retrieved them. It's then asking for the log again via the stream. -- James Sewell, PostgreSQL Team Lead / Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P *(+61) 2 8099 9000 *W* www.jirotech.com

Re: [GENERAL] Interesting streaming replication issue

; Although you are right - that would almost make sense if I had done that! Cheers, James > > Regards, > > Gunnar "Nick" Bluth > > Am 27. Juli 2017 05:00:17 MESZ schrieb James Sewell < > james.sew...@jirotech.com>: > >> Hi all, >> >> I&

[GENERAL] Interesting streaming replication issue

sing something here, this seems very odd. One thing I have noticed is it only seems to be caused after a lot of WAL is produced and the pg_xlog directory is sitting at max_wal_size James Sewell, PostgreSQL Team Lead / Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 20

Re: [GENERAL] Generalized pg_stat_statements?

ments gets above this number you lose information. To increase max you'd need a restart, which isn't ideal but I can't see any way round that. It wouldn't help you much - but it would be great for monitoring if there was a pg_stat_statements_agg table which presented total co

[GENERAL] Logical slot preservation after physical failover

oy feature in Enterprise (where HA clustering is the general rule), as it always comes with a high possibility of data loss. This is compounded as it seems logical replication sends transactions in sequence (ie xid 2 blocks xid 3 until it commits). Any thoughts / hints / hacks? Cheers, Jam

Re: [GENERAL] PostgreSQL corruption

Sadly this is for a customer who has 3000 of these in the field, the raid controller is on the motherboard. At least they know where to point the finger now! Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P

Re: [GENERAL] PostgreSQL corruption

lying. Breaking up the RAID and re-imaging with JBOD dropped this to 50 ops/sec - another question but still looking like a real result. So in this case it looks like the RAID controller wasn't disabling caching as advertised. Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect

Re: [GENERAL] PostgreSQL corruption

it. A C one would be the best I suppose as it could exactly mimic PostgreSQL. Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P *(+61) 2 8099 9000 <(+61)%202%208099%209000> *W* www.jirotech.com *F * (+

[GENERAL] PostgreSQL corruption

9.5 stable with checksums Can anyone think of anything else we should be considering / testing / factoring in? Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P *(+61) 2 8099 9000 <(+61)%202%2080

Re: [GENERAL] Sequential parallel scan / agg

ontains a data-modifying >operation either at the top level or within a CTE, no parallel plans for >that query will be generated. This is a limitation of the current >implementation which could be lifted in a future release. > > Sad, but looks to hold from this testing!

Re: [GENERAL] Sequential parallel scan / agg

Hi, Yes, same result (non-parallel seq scan) Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P *(+61) 2 8099 9000 <(+61)%202%208099%209000> *W* www.jirotech.com *F * (+61) 2 8099 9099 <(+61)%2

[GENERAL] Sequential parallel scan / agg

00:00'::timestamp without time zone)) -> Parallel Seq Scan on meter_read_2016_03_29 mr_1 (cost=0.00..3756417.28 rows=23280094 width=0) Filter: ((timestamp_rounded >= '2016-03-28 00:00:00'::timestamp without time zone) AND (timestamp_roun

Re: [GENERAL] Critical failure of standby

Hi, No, this was a one off in a network split situation. I'll check the startup when I get a chance - thanks for the help. Cheers, James Sewell, Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P *(+61) 2 8099 9000 <(+61)%202%208099%209

Re: [GENERAL] Critical failure of standby

Hey Sameer, As per the logs there was a crash of one standby, which seems to have corrupted that standby and the two cascading standby. - No backups - Full page writes enabled - Fsync enabled Cheers, James Sewell, Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road

Re: [GENERAL] Critical failure of standby

again. Cheers, James Sewell, Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P *(+61) 2 8099 9000 <(+61)%202%208099%209000> *W* www.jirotech.com *F * (+61) 2 8099 9099 <(+61)%202%208099%209000> On Tue, Aug 16, 2016 at 12:36 PM, John R Pierc

Re: [GENERAL] Critical failure of standby

ion though right? - it's just not getting WALs. Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P *(+61) 2 8099 9000 <(+61)%202%208099%209000> *W* www.jirotech.com *F * (+61) 2 8099 9099 <(+61)%20

Re: [GENERAL] Critical failure of standby

rvers?. Here is a link to a diagram of the current environment: http://imgur.com/a/MoKMo I'll look into patching for a core-dump. Cheers, James Sewell, Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P *(+61) 2 8099 9000 <(+61)%2

Re: [GENERAL] Critical failure of standby

Hello, I double posted this (posted once from an unregistered email and assumed it would be junked). I'm continuing all discussion on the other thread now. Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 20

Re: [GENERAL] Critical failure of standby

anything abnormal I think the key looks like the (legitimate) loss of network to the Prod master, then: (0:XX000)FATAL: invalid memory alloc request size 3445219328 Everything seems to go wrong from there. Are WAL segments checked for integrity once they are received? James Sewell, PostgreSQL

[GENERAL] Critical failure of standby

the standby? Cheers, James Sewell, Solutions Architect Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009 *P *(+61) 2 8099 9000 <(+61)%202%208099%209000> *W* www.jirotech.com *F * (+61) 2 8099 9099 <(+61)%202%208099%209000> -- -- The content

[GENERAL] Standby crash

choose an earlier recovery target. Does anyone have any ideas? It looks to me like some sort of bug / error with the replication protocol or maybe some corruption on the master which wasn't noticed and fed across? If that's the case would checksums help here? Are the computed on the stan

[GENERAL] INDEX ONLY scan with expression index

Hello, Would anyone be able to shed some light on why expression based indexes can't be used for an index only scan? I've found a few comments saying this is the case, and I've proven it is the case in reality - but I can't seem to find the why. Cheers, James Sewell,

Re: [GENERAL] Fwd: dblink_connect fails

Oops forgot to reply back to this one in the Christmas shutdown. It turned out in this (new) install on Windows Postgres was running as the Network Service user (??). This was causing the issue, changed to Postgres and I was all good. Cheers, James Sewell, Solutions Architect

[GENERAL] Fwd: dblink_connect fails

Oops left off the list. -- Forwarded message -- From: *James Sewell* Date: Thursday, 17 December 2015 Subject: dblink_connect fails To: Joe Conway On Thu, Dec 17, 2015 at 12:11 PM, Joe Conway > wrote: > On 12/16/2015 04:53 PM, James Sewell wrote: > >

Re: [GENERAL] dblink_connect fails

No it is not. Just in case I tried setting it to 'postgres', logged in without -U (doesn't work without PGUSER set) and tried the operation again. Same result. Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level

[GENERAL] dblink_connect fails

t for being on 9.4.0) which this works on. Cheers, James Sewell, Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+61) 3 8370 8099 -- -- The contents of this ema

[GENERAL] WAL log archival on standby

. This would result in a doubling of my network traffic. Ideally I'd prefer to conserve this bandwidth and write the files to a local archive on any active standby. Cheers, James Sewell, Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 300

Re: [GENERAL] Streaming replication and an archivelog

by stopping them ever knowing about history files. Can anyone see any problems with this approach? Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 *W* www.lisasoft.com

Re: [GENERAL] Streaming replication and an archivelog

the server forked off from that timeline at B/BE60. 2015-05-08 16:23:11 AEST @ ( 0 0)LOG: startup process (PID 21893) exited with exit code 1 2015-05-08 16:23:11 AEST @ ( 0 0)LOG: aborting startup due to startup process failure Cheers, James Sewell, PostgreSQL Team Lead / Solutions Ar

[GENERAL] Streaming replication and an archivelog

switches, not archive timeline switches. Obviously if I am not a streaming replica I need to follow archive timeline switches so I don't break PIT recovery. Possible? James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St,

Re: [GENERAL] Partitioning

all index values? Cheers, James On Monday, 19 January 2015, John R Pierce wrote: > On 1/18/2015 11:13 PM, James Sewell wrote: > >> >> Each index is about 2.5GB, I suspect I am trying to read a these into >> memory in entirety. >> > > an 11GB table wit

Re: [GENERAL] Partitioning

< horiguchi.kyot...@lab.ntt.co.jp> wrote: > Hello, > > an 2015 14:13:37 +1100, James Sewell > wrote in < > cankgpbs8gypq3tqgkdjtd+n-w1rkq5uo97h3tuhg5ewakr6...@mail.gmail.com > > > > Sadly not ... I still hit all the tables. > > | 5.9.4. Partitioning and Constrai

Re: [GENERAL] Partitioning

Sadly not ... I still hit all the tables. Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+61) 3 8370 8099 On Mon, Jan 19, 2015 at 1:54 PM

[GENERAL] Partitioning

he planner doesn't know the outcome of my subquery at plan time? Any tricks I am overlooking? James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+

Re: [GENERAL] WAL archive on slave

the incorrect timeline. Although potentially merging the archive logs favoring the new master's logs might fix this? Either way, it seems the answer to my question is no. Thanks for the help! -James Sewell On Monday, 10 February 2014, Albe Laurenz wrote: > James Sewell wrote: > >

Re: [GENERAL] WAL archive on slave

tory? Is there a limitation I'm not thinking about here? Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+61) 3 8370 8099 On Mon, Feb 10, 20

Re: [GENERAL] WAL archive on slave

cause this? Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+61) 3 8370 8099 On Fri, Feb 7, 2014 at 7:30 PM, Albe Laurenz wrote: > James Sew

[GENERAL] WAL archive on slave

archive in this case and A had writes after the promotion then we would get timeline errors. As far as I can tell, using the WAL archive from B would resolve this issue. Or have I missed something here? Cheers, Cheers, James Sewell, PostgreSQL Team Lead / Solutions

Re: [GENERAL] pg_dumpall from a script

Oh I missed that, I skimmed and thought it was the same as \set Turns out it's not and it's exactly what I want! Thanks! James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370

Re: [GENERAL] pg_dumpall from a script

to the \! command, and PL/PGSQL is a trusted language. I might investigate PL/PythonU, would the untrusted mean I might be able to do this sort of thing (run arbitrary commands?) If not, I'll just make a wrapper and do the backup there. Cheers, James Sewell, PostgreSQL Team Lead / Solu

Re: [GENERAL] pg_dumpall from a script

ion in the database (applying a patch). Hope that's a bit clearer! Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 * **W* www.lisasoft.com *F *(+61) 3 8370 8099 On Tue,

[GENERAL] pg_dumpall from a script

Doesn't seem to work. Any ideas? James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 * **W* www.lisasoft.com *F *(+61) 3 8370 8099 -- -- The conte

[GENERAL] Stop execution without ERROR

or (return value of 0, no ERROR in output) I am currently using an EXCEPTION, which satisfies a, but not b. Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 * **W* www.lisasoft.com

Re: [GENERAL] pg_largeobjects

(either by default from the table permissions or as a new GRANT option). Thoughts on this? Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P* (03) 8370 8000 * **W* www.lisasoft.com *F*(03) 8370

[GENERAL] pg_largeobjects

999); ERROR: permission denied for large object 51414 James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P* (03) 8370 8000 * **W* www.lisasoft.com *F*(03) 8370 8000 -- --

Re: [GENERAL] Replication Postgre > Oracle

://forums.enterprisedb.com/forums/list.page or contact EDB directly for pre-sales advice. Cheers, James Sewell PostgreSQL Team Lead / Solutions Architect _ [image: http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png] Level 2, 50 Queen St

Re: [GENERAL] Snapshot backups

determines where to start WAL replay from the backup_label NOT from pg_control (as usual) Cheers, James James Sewell PostgreSQL Team Lead / Solutions Architect _ [image: http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png] Level 2, 50 Queen

Re: [GENERAL] Snapshot backups

ter a crash, and will result in log replay to get to the latest possible consistent state? I thought PostgreSQL guaranteed a consistent state after a crash, am I mistaken about this? James James Sewell PostgreSQL Team Lead / Solutions Architect _ [image:

Re: [GENERAL] Snapshot backups

ds to ensure a valid backup when using filesystem snapshots (assuming I get the order correct)- worst case scenario wouldn't it be the same as a crash and cause an automatic roll-forward? Cheers, James James Sewell PostgreSQL Team Lead / Solutions Architect ___

[GENERAL] LDAP referrals

at the real root cause if my broken LDAP server, but is there a way to disable chasing referrals? Cheers, James Sewell James Sewell PostgreSQL Team Lead / Solutions Architect _ [image: http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png]

Re: [GENERAL] Snapshot backups

e PGDATA snapshot is equivalent to a crash/restart (disk at a discrete point in time). I can understand if log replay might take longer, but I am struggling to see how it could result in an inconsistent state? As I said I know this isn't best practice, but just want to understand how it works.

Re: [GENERAL] LDAP authentication timing out

Hey, Thanks for the reply Magnus. I'm getting some packet captures now - I just thought I'd throw this out there in case anyone else had faced similar problems. This is EDB PPAS, I'm following up with them in parallel. Cheers, James Sewell James Sewell Solu

[GENERAL] Snapshot backups

- trigger log switch There is nothing in here that is *required* from a backup point of view. Am I missing anything? James Sewell Solutions Architect _ [image: http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png] Level 2, 50 Queen St, Melbourne

[GENERAL] LDAP authentication timing out

application and the same bind DN with no problems. Cheers, James Sewell PostgreSQL Team Lead / Solutions Architect _ [image: http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png] Level 2, 50 Queen St, Melbourne, VIC, 3000 P: 0

[GENERAL] ROLE based DEFAULT PRIVILEGES

connected to psql as). Reading the documentation again, this is actually what is stated. Is there a way to achieve this? I want role_a to maintain this level of access as new tables are created (all creators will be in role_b). James Sewell Solutions Architect