Re: [GENERAL] Understanding Postgres Memory Usage

Okay, I got a semi-reproducible test case: https://gist.github.com/luhn/2b35a9b31255e3a6a2e6a06d1213dfc9 The one caveat is that the memory rise only happens when using a HashAggregate query plan (included in the gist), which I can't find a way to get Postgres to reliably use. If you need it, I co

Re: [GENERAL] LOG: could not fork new process for connection: Cannot allocate memory

we have a pooling on the application level. however we never had this issues before this start happning since last couple of days in past we had over 2300 sessions but no issues. On Thu, Aug 25, 2016 at 5:29 PM, John R Pierce wrote: > On 8/25/2016 5:10 PM, Ahsan Ali wrote: > >> yes it is older h

Re: [GENERAL] LOG: could not fork new process for connection: Cannot allocate memory

On 8/25/2016 5:10 PM, Ahsan Ali wrote: yes it is older however we do apply security patches now a then. redhat doesn't really support mixing packages from different releases, they only test things with all packages from the same snapshot. "yum update" should bring the whole system up to cur

Re: [GENERAL] incorrect checksum detected on "global/pg_filenode.map" when VACUUM FULL is executed

On Fri, Aug 26, 2016 at 9:09 AM, Tatsuki Kadomoto wrote: > Does Postgre have any mechanism to correct the mapping file automatically? When the relation mapping changes, the file gets rewritten at transaction commit. I guess that the error you saw previously (be careful, that's a corruption!) got

Re: [GENERAL] Understanding Postgres Memory Usage

Theron Luhn writes: > Okay, here's the output: > https://gist.github.com/luhn/a39db625ba5eed90946dd4a196d12220 Hm, well the only thing there that looks even slightly out of the ordinary is the amount of free space in TopMemoryContext itself: TopMemoryContext: 3525712 total in 432 blocks; 3444272

Re: [GENERAL] LOG: could not fork new process for connection: Cannot allocate memory

yes it is older however we do apply security patches now a then. regarding max connection its the application design however it does not have that many active session. postgres=# select count(*) from pg_stat_activity; count --- 1818 Please let me know if you like to see any other logs and

Re: [GENERAL] incorrect checksum detected on "global/pg_filenode.map" when VACUUM FULL is executed

Michael, In my case, it only detected once. Does Postgre have any mechanism to correct the mapping file automatically? Regards, Tatsuki Sent from my ASUS オリジナルのメッセージ 送信元:Michael Paquier 送信済み:Fri, 26 Aug 2016 09:05:53 +0900 送信先:Tatsuki Kadomoto Cc:Kevin Grittner ,John R Pierce

Re: [GENERAL] incorrect checksum detected on "global/pg_filenode.map" when VACUUM FULL is executed

On Thu, Aug 25, 2016 at 9:48 PM, Tatsuki Kadomoto wrote: > Does this mean it's a kind of expected to face checksum error when the > mapping file is relocated by VACUUM FULL? > I faced the checksum error exactly when VACUUM FULL was running and it has > never been detected until now. No, it's no

Re: [GENERAL] LOG: could not fork new process for connection: Cannot allocate memory

On 8/25/2016 3:54 PM, Ahsan Ali wrote: Red Hat Enterprise Linux Server release 6.3 (Santiago) that was released in June 2012, you're missing 4+ years of bug fixes, 6.8 is current. max_connections = 3000 thats insanely high for most purposes unless you have several 100 CPU cores. otherwi

Re: [GENERAL] LOG: could not fork new process for connection: Cannot allocate memory

Hi John, Thanks for replying. Below are all the details I am using psql (PostgreSQL) 9.5.2 Error we got in postgresql log* 2016-08-25 11:15:55 PDT [60739]: [10282-1] LOG: could not fork new process for connection: Cannot allocate memory 2016-08-25 11:15:55 PDT [60739]: [10283-1] LOG: co

Re: [GENERAL] Unable to log in current local time EST

On 08/25/2016 11:36 AM, Alex Lai wrote: > Dear All, > > I have my log_line_prefix set to > log_line_prefix = '[%d]%p %x %c[%l] %t %i' > in postgresql.conf > > psql -c 'show timezone' > TimeZone > > US/Eastern > > tail -2 omi_acps.log ; date > [sipsdb]20180 0 57ab7dcd.4ed4[1189571

Re: [GENERAL] Unable to log in current local time EST

Hi, On Thu, 2016-08-25 at 11:36 -0400, Alex Lai wrote: > I have my log_line_prefix set to > log_line_prefix = '[%d]%p %x %c[%l] %t %i' > in postgresql.conf > > psql -c 'show timezone' >   TimeZone > >  US/Eastern > > tail -2 omi_acps.log ; date > [sipsdb]20180 0 57ab7dcd.4ed4[11895

Re: [GENERAL] Understanding Postgres Memory Usage

On 8/25/2016 9:58 AM, Theron Luhn wrote: > I do not remember exact formula, but it should be something like “work_mem*max_connections + shared_buffers” and it should be around 80% of your machine RAM (minus RAM used by other processes and kernel). It will save you from OOM. a single query

[GENERAL] Unable to log in current local time EST

Dear All, I have my log_line_prefix set to log_line_prefix = '[%d]%p %x %c[%l] %t %i' in postgresql.conf psql -c 'show timezone' TimeZone US/Eastern tail -2 omi_acps.log ; date [sipsdb]20180 0 57ab7dcd.4ed4[11895717] 2016-08-25 15:32:45 GMT SELECTLOG: duration: 0.326 ms [sipsdb]

Re: [GENERAL] Understanding Postgres Memory Usage

Hi Ilya, > Are you talking about buffers/cache increased? AFAIK this memory is used by kernel as buffer before any block device (HDD for example). If I'm reading the output correctly, buffers/cached do not increase. I'm looking at the 248MB -> 312MB under the "used" column in the "-/+ buffers/ca

[GENERAL] Fwd: [Snowball-discuss] Greek stemmer

This is a chance to add default configuration for Greek language if somebody with good knowledge could follow this development. Oleg -- Forwarded message -- From: Oleg Smirnov Date: Thu, Aug 25, 2016 at 5:26 PM Subject: [Snowball-discuss] Greek stemmer To: "snowball-discu." Hi

Re: [GENERAL] Understanding Postgres Memory Usage

Okay, here's the output: https://gist.github.com/luhn/a39db625ba5eed90946dd4a196d12220 — Theron On Thu, Aug 25, 2016 at 12:34 PM, Tom Lane wrote: > Theron Luhn writes: > >> It would be worth using plain old top to watch this process. We have > >> enough experience with that to be pretty sure

Re: [GENERAL] LOG: could not fork new process for connection: Cannot allocate memory

On 8/25/2016 11:49 AM, Ahsan Ali wrote: I having my production server service intruption because of this "Cannot allocate memory" error in the db log. could you paste the whole error message? what version of postgres is this? what OS (if linux, distribution) version is this? older version

Re: [GENERAL] Understanding Postgres Memory Usage

Theron Luhn writes: >> It would be worth using plain old top to watch this process. We have >> enough experience with that to be pretty sure how to interpret its >> numbers: "RES minus SHR" is the value to be worried about. > Sure thing. > https://gist.github.com/luhn/e09522d524354d96d297b153d

[GENERAL] LOG: could not fork new process for connection: Cannot allocate memory

Hi Support I having my production server service intruption because of this "Cannot allocate memory" error in the db log. because of this error even DBA's cant login to the db server unless we kill one of the existing sessions. Please help me resolve this issue. 1. $ grep Commit /proc/meminf

Re: [GENERAL] Understanding Postgres Memory Usage

> It would be worth using plain old top to watch this process. We have > enough experience with that to be pretty sure how to interpret its > numbers: "RES minus SHR" is the value to be worried about. Sure thing. https://gist.github.com/luhn/e09522d524354d96d297b153d1479c 13#file-top-txt RES -

Re: [GENERAL] Understanding Postgres Memory Usage

On Thu, Aug 25, 2016 at 1:25 PM, Tom Lane wrote: > Hmm. I find it mighty suspicious that the USS, PSS, and RSS numbers are > all increasing to pretty much the same tune, ie from very little to circa > 100MB. I think there is a decent chance that smem is not doing what it > says on the tin, and

Re: [GENERAL] Understanding Postgres Memory Usage

Theron Luhn writes: >> If it's not an outright leak, it's probably consumption of cache space. >> We cache stuff that we've read from system catalogs, so sessions that >> touch lots of tables (like thousands) can grow due to that. Another >> possible source of large cache consumption is calling l

Re: [GENERAL] ON CONFLICT does not support deferrable unique constraints

On Thu, Aug 25, 2016 at 12:29 AM, Francisco Olarte wrote: > That been said, I'm not sure making it ( deferred constraint act like > immediate ones during upserts ) work is even a good idea. If it can be > conditionally enabled with a simple set and implemented in very few ( > < 20 ) lines of code,

Re: [GENERAL] Understanding Postgres Memory Usage

> 9.3.which? We do fix memory leaks from time to time ... 9.3.14 > If it's not an outright leak, it's probably consumption of cache space. > We cache stuff that we've read from system catalogs, so sessions that > touch lots of tables (like thousands) can grow due to that. Another > possible sou

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

Hi Arnaud: On Thu, Aug 25, 2016 at 4:35 PM, arnaud gaboury wrote: >> Are this all the contents of you pg_hba.conf? Note order matters, all >> non comment ( or at least the host ones ) need to be checked . > Here is the whole content: > 79 local thetradinghall mailman

Re: [GENERAL] Updated RUM-index and support for bigint as part of index

På torsdag 25. august 2016 kl. 18:12:34, skrev Oleg Bartunov < obartu...@gmail.com >: Andreas,   sorry for delay, it looks like a bug to me, could you please, share your dataset with me, so I could reproduce the behaviour.     I'll send you a Google Drive link on your

Re: [GENERAL] Understanding Postgres Memory Usage

Theron Luhn writes: > I have an application that uses Postgres 9.3 as the primary datastore. 9.3.which? We do fix memory leaks from time to time ... > Some of these queries use quite a bit of memory. I've observed a > "high-water mark" behavior in memory usage: running a query increases the >

Re: [GENERAL] Updated RUM-index and support for bigint as part of index

Andreas, sorry for delay, it looks like a bug to me, could you please, share your dataset with me, so I could reproduce the behaviour. Regards, Oleg On Sun, Aug 7, 2016 at 11:05 AM, Andreas Joseph Krogh wrote: > På søndag 07. august 2016 kl. 08:27:06, skrev Oleg Bartunov < > obartu...@gmail.co

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

On Thu, Aug 25, 2016 at 4:28 PM, arnaud gaboury wrote: > On Thu, Aug 25, 2016 at 4:26 PM, Ilya Kazakevich > wrote: >>>I entered this line in pg_hab.conf: >> Are you sure your file name is correct and it is really used by postgres? > I think so as another service (Postfix) is running and working.

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

On Thu, Aug 25, 2016 at 5:50 PM, Francisco Olarte wrote: > Hi Arnaud: > > On Thu, Aug 25, 2016 at 4:35 PM, arnaud gaboury > wrote: >>> Are this all the contents of you pg_hba.conf? Note order matters, all >>> non comment ( or at least the host ones ) need to be checked . >> Here is the whole cont

Re: [GENERAL] Understanding Postgres Memory Usage

$ free -h # Before the query total used free sharedbuffers cached Mem: 7.8G 5.2G 2.6G 212M90M 4.9G -/+ buffers/cache: 248M 7.6G Swap: 0B 0B 0B $ free -h # After the query

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

On Thu, Aug 25, 2016 at 5:43 PM, Francisco Olarte wrote: > On Thu, Aug 25, 2016 at 4:28 PM, arnaud gaboury > wrote: >> On Thu, Aug 25, 2016 at 4:26 PM, Ilya Kazakevich >> wrote: I entered this line in pg_hab.conf: >>> Are you sure your file name is correct and it is really used by postgres?

[GENERAL] Understanding Postgres Memory Usage

I have an application that uses Postgres 9.3 as the primary datastore. Like any real-life application, it's not all roses—There are many ugly, convoluted, and inefficient queries. Some of these queries use quite a bit of memory. I've observed a "high-water mark" behavior in memory usage: running

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

On Thu, Aug 25, 2016 at 5:26 PM, Joshua D. Drake wrote: > On 08/25/2016 07:44 AM, arnaud gaboury wrote: >> >> On Thu, Aug 25, 2016 at 4:38 PM, Joshua D. Drake >> wrote: > > >>> Did you reload PostgreSQL? That is how you tell PostgreSQL to reread the >>> pg_hba.conf. >>> >>> FTR: I have deployed M

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

On 08/25/2016 07:44 AM, arnaud gaboury wrote: On Thu, Aug 25, 2016 at 4:38 PM, Joshua D. Drake wrote: Did you reload PostgreSQL? That is how you tell PostgreSQL to reread the pg_hba.conf. FTR: I have deployed Mattermost and it works wonderfully. The issue is solved (see my replies). By any

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

% psql --host=127.0.0.1/32 --dbname=mattermost --username=mmuser psql: could not translate host name "127.0.0.1/32" to address: Name or service not known % psql --host=127.0.0.1/24 --dbname=mat

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

On Thu, Aug 25, 2016 at 4:27 PM, Melvin Davidson wrote: > > > On Thu, Aug 25, 2016 at 10:18 AM, arnaud gaboury > wrote: > >> I am deploying mattermost on my machine following their documentation[0]. >> >> My machine network settings: >> -- >> $ ip a >> 1: lo: mtu

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

On 08/25/2016 07:18 AM, arnaud gaboury wrote: I am deploying mattermost on my machine following their documentation[0]. There is a public IP with a domain name (http works OK). I entered this line in pg_hab.conf: I assume you mean pg_hba.conf -- host matte

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

On Thu, Aug 25, 2016 at 4:38 PM, Joshua D. Drake wrote: > On 08/25/2016 07:18 AM, arnaud gaboury wrote: >> >> I am deploying mattermost on my machine following their documentation[0]. > > >> There is a public IP with a domain name (http works OK). >> >> I entered this line in pg_hab.conf: > > > I

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

On Thu, Aug 25, 2016 at 4:37 PM, Ilya Kazakevich wrote: > > % psql --host=127.0.0.1/32 --dbname=mattermost --username=mmuser > psql: could not translate host name "127.0.0.1/32" to address: Name or > service not known > % psql --host=127.0.0.1/24 --dbname=mattermost --username=mmuser > psql: could

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

On Thu, Aug 25, 2016 at 4:29 PM, Francisco Olarte wrote: > Hi Arnaud: > On Thu, Aug 25, 2016 at 4:18 PM, arnaud gaboury > wrote: >> There is a public IP with a domain name (http works OK). > Nice to know, but does not matter if all you use is 127.0.0.1 > > >> I entered this line in pg_hab.conf: >

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

On Thu, Aug 25, 2016 at 4:32 PM, Tom Lane wrote: > arnaud gaboury writes: >> I entered this line in pg_hab.conf: >> -- >> host mattermost mmuser 127.0.0.1 md5 > >> What am I doing wrong? > > Looking in the postmaster log for complaints about pg_

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

On Thu, Aug 25, 2016 at 10:28 AM, arnaud gaboury wrote: > On Thu, Aug 25, 2016 at 4:26 PM, Ilya Kazakevich > wrote: > >>I entered this line in pg_hab.conf: > > Are you sure your file name is correct and it is really used by postgres? > > I think so as another service (Postfix) is running and wor

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

>How can I verify ? Can you connect as postgres (superuser)? If yes, connect and type "show hba_file;" If no, try adding "local all postgres peer" or even "local all postgres trust" to this file and restart postgres. Check again. -- Sent via pgsql-general mailing list (pgsql-general@postgresq

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

arnaud gaboury writes: > I entered this line in pg_hab.conf: > -- > host mattermost mmuser 127.0.0.1 md5 > What am I doing wrong? Looking in the postmaster log for complaints about pg_hba.conf would probably have helped you diagnose this. But

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

Hi Arnaud: On Thu, Aug 25, 2016 at 4:18 PM, arnaud gaboury wrote: > There is a public IP with a domain name (http works OK). Nice to know, but does not matter if all you use is 127.0.0.1 > I entered this line in pg_hab.conf: Have you checked the filename? you are saying HAB, but it is HBA ( Hos

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

On Thu, Aug 25, 2016 at 10:18 AM, arnaud gaboury wrote: > I am deploying mattermost on my machine following their documentation[0]. > > My machine network settings: > -- > $ ip a > 1: lo: mtu 65536 qdisc noqueue state UNKNOWN > group default qlen 1 > link/loop

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

On Thu, Aug 25, 2016 at 4:26 PM, Ilya Kazakevich wrote: >>I entered this line in pg_hab.conf: > Are you sure your file name is correct and it is really used by postgres? I think so as another service (Postfix) is running and working. How can I verify ? > > > Ilya Kazakevich > > JetBrains > http:

Re: [GENERAL] pg_hba.conf : bad entry for ADDRESS

>I entered this line in pg_hab.conf: Are you sure your file name is correct and it is really used by postgres? Ilya Kazakevich JetBrains http://www.jetbrains.com The Drive to Develop -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

[GENERAL] pg_hba.conf : bad entry for ADDRESS

I am deploying mattermost on my machine following their documentation[0]. My machine network settings: -- $ ip a 1: lo: mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope h

Re: [GENERAL] incorrect checksum detected on "global/pg_filenode.map" when VACUUM FULL is executed

Michael, I have read the comment in relmapper.c. === Therefore mapped catalogs can only be relocated by operations such as VACUUM FULL and CLUSTER, which make no transactionally-significant changes: it must be safe for the new file to replace the old, even if the transaction itself aborts. ==

[GENERAL] BDR: Recover from "FATAL: mismatch in worker state" without restarting postgres

Hello all, After uninstalling a BDR node, it becomes not possible to join it again. The following log appears in loop: <<< 2016-08-25 10:17:08 [ll101] postgres info [11709]: [14620-1] LOG: starting background worker process "bdr (6287997142852742670,1,19526,)->bdr (6223672436788445259,2," #local4

Re: [GENERAL] corruption in indexes under heavy load

On 8/25/2016 1:50 AM, Russell Keane wrote: We’re writing a large amount of data to a number of tables in PG 9.3 on Windows Server 2012 R2 and then, immediately after, creating a number of indexes (there are no indexes during the initial data write). The data we’re writing exists in files on t

Re: [GENERAL] corruption in indexes under heavy load

On 25 August 2016 at 09:50, Russell Keane wrote: > We’re fairly convinced the issue lies with the actual storage but I was > wondering if there is anything within PG that would be affected by the high > latency and result in corrupt indexes. Nothing we know of, at this time. -- Simon Riggs

[GENERAL] corruption in indexes under heavy load

We're writing a large amount of data to a number of tables in PG 9.3 on Windows Server 2012 R2 and then, immediately after, creating a number of indexes (there are no indexes during the initial data write). The data we're writing exists in files on the same drive as PG's data. During the index c

Re: [GENERAL] ON CONFLICT does not support deferrable unique constraints

On Wed, Aug 24, 2016 at 9:22 PM, Andreas Joseph Krogh wrote: > As a developer I want it to "just work", if there's an error of any kind then > abort the transaction, just as it was non-deferrable. Everybody wants everything to "just work", for their own ( in a lot of cases unspecified even to th