pgbouncer bug?

Not sure if this is the right place to post this, but if not someone please point me in the right direction. My issue is with pgbouncer 1.14. This does not seem to happen on 1.13. If I do a service pgbouncer restart, then anytime I try to connect to my databases via pgbouncer, I get ERROR: no s

Re: Table with many NULLS for indexed column yields strange query plan

Seqscans are not disabled. Also, this is PostgreSQL 10.11 if that helps. Costs are as follows: seq_page_cost --- 1 random_page_cost -- 1.5 It is odd that it does not just do a seqscan on table3. It's a very small table... only like 36 rows. I'd think the pla

Table with many NULLS for indexed column yields strange query plan

I have a query like this: SELECT "table1".* FROM "table1" INNER JOIN "table2" ON "table2"."table1_id" = "table1"."id" INNER JOIN "table3" ON "table3"."id" = "table2"."table3_id" WHERE "table3"."number" = '' AND ("table2"."type") IN ('Standard') ; table2 has a large number of NULLS in the col

Re: BitmapAnd on correlated column?

>Extended statistics will tell PostgreSQL that it is very unlikely >that the first condition will contribute significantly, but that >is no proof that the condition can be omitted, so the optimizer >cannot just skip the condition. Granted it cannot skip the condition, but that doesn't mean that it

Re: BitmapAnd on correlated column?

yes. where and is a typo. -- Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

BitmapAnd on correlated column?

I'm running the following query on Postgres version 10.8: SELECT count(*) FROM test_table WHERE and id_column_1 IN (9954, 9690, 9689, 9688) AND id_column_2 IN (75328, 51448, 48060, 48065, 51803, 51449, 51802, 48064, 48061, 48062, 48059, 48063, 121834, 326649, 303167, 303249, 336838, 334172, 30341

Re: Out of Memory

All right.. one more thing here. Any suggestions for how to set overcommit on a postgres db server with 16 GB of RAM and no swap? I think I want vm.overcommit_memory = 2, as I understand that prevents the OOM killer from zapping me. Is 100% the right way to go for overcommit_ratio? Is there a

Re: Out of Memory

I think I figured it out: vm.overcommit_memory = 2 vm.overcommit_ratio = 50 Only allows me to use 50% of my RAM... ugh! I have 16 GB, so when only 8 is left, I start seeing OOM. Will increase this setting and see if it helps. Thanks everyone for the help. Greig -- Sent from: http://www.pos

Re: Out of Memory

Tom Lane-2 wrote > greigwise < > greigwise@ > > writes: >> Is it possible that the fact that my stack size is limited is what is >> causing my issue? > > No. If you were hitting that limit you'd get a message specifically > talking about stack. >

Re: Out of Memory

There is also this: -bash-4.2$ prlimit -p 6590 RESOURCE DESCRIPTION SOFT HARD UNITS AS address space limitunlimited unlimited bytes CORE max core file size 0 unlimited blocks CPUCPU time

Re: Out of Memory

Tom Lane-2 wrote > greigwise < > greigwise@ > > writes: >> If I have nearly 8 GB of memory left, why am I getting out of memory >> errors? > > Probably the postmaster is running under restrictive ulimit settings. > > regards, tom

Re: Out of Memory

Well, I've been unsuccessful so far on creating a standalone test. I have put some scripting in place to capture some additional information on the server with the out of memory issues. I have a script which just periodically dumps the output of free -m to a text file. So, the output of fr

Re: Out of Memory

Well, we are 64-bit I'll see if I can make some kind of self contained test to repeat it. Thanks, Greig -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Out of Memory

ulimit -a for the postgres user shows memory unlimited. numactl --hardware gives command not found. Thanks again. Greig -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Out of Memory

Hello, I'm running postgres 9.6.10 on Centos 7. Seeing the occasional out of memory error trying to run a query. In the logs I see something like this: Grand total: 462104832 bytes in 795 blocks; 142439136 free (819860 chunks); 319665696 used 2018-09-20 18:08:01 UTC 5ba3e1a2.7a8a dbname E

Re: pg_basebackup: could not receive data from WAL stream

I should also add that when it fails, it's always right at the very end of the backup when it's very nearly done or maybe even after it's done. Thanks again. Greig -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

pg_basebackup: could not receive data from WAL stream

Hello. On postgresql 10.5, my pg_basebackup is failing with this error: pg_basebackup: could not receive data from WAL stream: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request In the postgres log files, I'm seei

Errors with physical replication

Hello. We are on Postgresql version 9.6.6. We have 2 EC2 instances in different Amazon regions and we are doing physical replication via VPN. It all seems to work just fine most of the time. I'm noticing in the logs that we have recurring erros (maybe 10 or 12 times per day) that look like t

postgres on physical replica crashes

Hello. I've had several instances where postgres on my physical replica under version 9.6.6 is crashing with messages like the following in the logs: 2018-04-18 05:43:26 UTC dbname 5acf5e4a.6918 dbuser DETAIL: The postmaster has commanded this server process to roll back the current transaction

pglogical lag due to replication timeout

Hello all. I've recently configured logical replication on postgres 9.6.5 using pglogical 2.1.1. The problem I'm seeing is pretty constant stream of these errors on the service side in the postgres logs: LOG: terminating walsender process due to replication timeout After so many of these, I ca

Re: pglogical in postgres 9.6

No need! I figured it out. Had to put this "synchronize_data := false" on the create_subscription call. Weird that there seem to be redundant parameters for this; one on the replication set add and one on the create subscription. Maybe I'm not quite understanding the usage on those or something

Re: pglogical in postgres 9.6

hireology_tmp=# \dx List of installed extensions Name | Version | Schema | Description --+-+--+

Re: pglogical in postgres 9.6

I downloaded from git and compiled from source. Based on the pglogical.control file, it appears to be 2.0.0. Is there a better way to determine the version? Thanks, Greig -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: pglogical in postgres 9.6

I downloaded it from git and compiled from source just today. Looks like it's 2.0.0 based on the pglogical.control file that was in the source. I'm not sure how else I'd know. Greig -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

pglogical in postgres 9.6

Hello. I'm trying to get pglogical setup between 2 postgres 9.6 instances. I can get everything replicating over fine, my problem is that I can't seem to get it to work in the mode where it does not try to copy all the data over initially. On the source side, I'm doing this: psql -U hireology