Re: transaction wrap around

2017-12-04 Thread Jeff Janes
On Mon, Dec 4, 2017 at 5:52 PM, John R Pierce wrote: > On 12/4/2017 2:21 PM, chris kim wrote: > >> >> >> How would I investigate if my database is nearing a transaction wrap >> around. >> > > > it would be screaming bloody murder in the log, for one. > > Unfortunately, that comes far too late to

Re: clean out ./data/base/pgsql_tmp

2017-12-06 Thread Jeff Janes
On Wed, Dec 6, 2017 at 8:18 PM, Dylan Luong wrote: > Hi > > > > We have an issue where one of the developers ran a large query that hung > was filling up the DATA drive very rapidly. From 50% usage to 95% in less > than 2hrs. > > It created a very large pgsql_tmp size (300GB). To stop the drive f

Re: clean out ./data/base/pgsql_tmp

2017-12-06 Thread Jeff Janes
On Wed, Dec 6, 2017 at 9:29 PM, Dylan Luong wrote: > Since the temp files are easily identifiable as it has the PID in the > fileaname. > Is it ok just manually deleting these files as the process has already > being killed. > I've done that before without issue. Cheers, Jeff

Re: transaction wrap around

2017-12-10 Thread Jeff Janes
On Tue, Dec 5, 2017 at 5:50 PM, Thomas Munro wrote: > On Tue, Dec 5, 2017 at 5:43 PM, Jeff Janes wrote: > > On Mon, Dec 4, 2017 at 5:52 PM, John R Pierce > wrote: > >> On 12/4/2017 2:21 PM, chris kim wrote: > >>> How would I investigate if my database is nearin

Re: Deadlock between concurrent index builds on different tables

2017-12-23 Thread Jeff Janes
On Fri, Dec 22, 2017 at 1:53 PM, Jeremy Finzel wrote: > I am attempting to build several indexes in parallel, guaranteeing that I > never build one on the same table twice. I understand I can't build two on > the same table at once or I will get a deadlock. However, I am also > getting a deadlo

Re: Use of Port 5433 with Postgresql 9.6

2018-01-01 Thread Jeff Janes
On Mon, Jan 1, 2018 at 12:42 PM, Graeme wrote: > If the default port for v9.6 is 5433, why does the utility pg_isready > still default to searching for 5432? The Ubuntu packages use 5433 if you already have something (either a different packaged version, or an unpackaged system) running on 5432

Re: Select for update / deadlock possibility?

2018-01-02 Thread Jeff Janes
On Tue, Jan 2, 2018 at 3:22 AM, Durumdara wrote: > Dear Members! > > I have to ask something that not clear for me from description, and I > can't simulate it. > > Is "select for update" atomic (as transactions) or it isn't? > > I want to avoid the deadlocks. > > If it's atomic, then I don't need

Re: Deadlock between concurrent index builds on different tables

2018-01-03 Thread Jeff Janes
On Tue, Dec 26, 2017 at 10:03 PM, Jeremy Finzel wrote: > > > Many thanks for the great and simple explanation. > > I was able to get this compiled, and ran the test before on stock 9.6.6, > then on this patched version. I indeed reproduced it on 9.6.6, but on the > patched version, it indeed fix

Re: Sv: Re: [GENERAL] pg_dump streaming fails - PostgreSQL 9.2

2018-01-09 Thread Jeff Janes
On Jan 9, 2018 03:49, "Andreas Joseph Krogh" wrote: På fredag 29. januar 2016 kl. 02:30:59, skrev Joshua D. Drake < j...@commandprompt.com>: On 01/28/2016 05:23 PM, drum.lu...@gmail.com wrote: > Hi there! > > I'm running this command: *(Trying to copy a full DB (2TB) from a > hot-standby server

Re: data-checksums

2018-01-09 Thread Jeff Janes
On Tue, Jan 9, 2018 at 12:58 PM, Rakesh Kumar wrote: > > > That said, imv anyway, the performance hit is small and having checksums > > is well worth it. > > I also would like to believe that the hit is small, but when PG official > document writes "noticeable performance penalty", it becomes dif

Re: Sv: Re: Sv: Re: Sv: Re: Sv: Re: data-checksums

2018-01-10 Thread Jeff Janes
On Wed, Jan 10, 2018 at 12:23 PM, Thomas Poty wrote: > Hello, > A question seems to be, according to me, important : > How a corruption, detected thanks to data-checksums, is fixed? > Take two full cold backups of the current mess you have, including the executables, and lock one of them away wh

Re: Using random() in update produces same random value for all

2018-01-22 Thread Jeff Janes
On Mon, Jan 22, 2018 at 9:16 AM, Tom Lane wrote: > Olleg Samoylov writes: > > Looked like random() is "volatile", but in subselect it works like > "stable". > > The point here is that that's an uncorrelated subselect --- ie, it > contains no outer references --- so it need not be, and is not, >

Re: Please help me understand unlogged tables

2018-01-31 Thread Jeff Janes
On Wed, Jan 31, 2018 at 8:52 AM, Alexander Stoddard < alexander.stodd...@gmail.com> wrote: > If a table is set to unlogged is it inherently non-durable? That, is any > crash or unsafe shutdown _must_ result in truncation upon recovery? > Yes. > I can imagine a table that is bulk loaded in a war

Re: PITR Multiple recoveries

2018-02-08 Thread Jeff Janes
On Thu, Feb 8, 2018 at 5:07 AM, Sébastien Boutté wrote: > Hi all, > > I'm trying to make my server doing PITR backup, i follow the rules on > https://www.postgresql.org/docs/9.5/static/continuous-archiving.html. > > On my local server, i would like to resync multiple times my local > database: >

Re: shared_buffers 8GB maximum

2018-02-16 Thread Jeff Janes
On Fri, Feb 16, 2018 at 2:36 PM, Vitaliy Garnashevich < vgarnashev...@gmail.com> wrote: > Hi All, > > I've seen the shared_buffers 8GB maximum recommendation repeated many > times. I have several questions in this regard. > > - Is this recommendation still true for recent versions of postgres? (e.

<    1   2