PostgreSQL

15 Nov 2024 11:54

Spammers are getting smart gosh..

PostgreSQL

15 Nov 2024 11:40

All: apparently @Jasmine_dgFVvfK liked a lot of stuff, which I can neither remove nor ban her (she's not in the group apparently). I apologize, and maybe some other admin has a clue how to get rid of that kind of spam?

PostgreSQL

15 Nov 2024 11:01

Your pg_control is probably a garbage.

(Or you are pointing pg_rewind to something that's not a postgres).

PostgreSQL

14 Nov 2024 12:02

Hi people
Does any one need Postgres job support or interview support please ping me

PostgreSQL

14 Nov 2024 11:05

How to migrate data from csv file to aurora postgres

PostgreSQL

13 Nov 2024 12:17

I am new to Postgres.Postgres was installed & working fine..pgagent is installed & jobs are created and scheduled.but the jobs are not starting as per the schedule..i tried simple insert statement also..it's also not at all working...if we run the same code in the query window.it's executing.No errors in error log also.we are using Centos OS.

PostgreSQL

13 Nov 2024 10:42

That's generally a good idea, however using transaction mode isn't trivial. Much less trivial than understanding one's own pooler config, at least 😏

PostgreSQL

13 Nov 2024 09:19

You have 2 datasources
40*14 = 560
20*14 = 280
That's 820 max connections. Apparently they get used enough in a round-robin fashion that they don't get earmarked as idle by the pools.
I reckon

SELECT now()-state_change FROM pg_stat_activity;

PostgreSQL

13 Nov 2024 09:00

spring.datasource.tomcat.initial-size=10
spring.datasource.tomcat.max-wait=30000
spring.datasource.tomcat.max-active=40
spring.datasource.tomcat.max-idle=5
spring.datasource.tomcat.min-idle=4

spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.idle-timeout=100000
spring.datasource.hikari.max-lifetime=1000

This I already set but the problem persists. Could you please guide?

PostgreSQL

13 Nov 2024 08:37

Check your pooler, postgresql will not close idle connections

PostgreSQL

13 Nov 2024 08:32

Psql uses one connection and \c changes the current db but doesn't open any additional one

PostgreSQL

13 Nov 2024 08:07

It depends on many factors like database size, hardware, write vs read workload, the kind of queries you're doing etc.

PostgreSQL

13 Nov 2024 07:56

You should share about what your workload is and the issues you're having

PostgreSQL

13 Nov 2024 04:13

spring.datasource.tomcat.initial-size=10
spring.datasource.tomcat.max-wait=30000
spring.datasource.tomcat.max-active=40
spring.datasource.tomcat.max-idle=5
spring.datasource.tomcat.min-idle=4

spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.idle-timeout=100000
spring.datasource.hikari.max-lifetime=1000

This is the hicaricp conf

PostgreSQL

12 Nov 2024 17:51

You probably have a client side connection pool (e.g. HikariCP) and configured that to use up to 800 connections. They usually default to actually use them if they get them 🤷‍♂

PostgreSQL

15 Nov 2024 11:42

And can we get rid of the likes?

PostgreSQL

15 Nov 2024 11:33

Try running pg_controldata on your $PGDATA and see if that produces some reasonable results.

PostgreSQL

15 Nov 2024 10:01

Hi guys, i am facing this error when using pg_rewind ‘WAL segment size must be a power of two between 1 MB and 1 GB, but the control file specifies 131072 byte’
Any idea why ?

PostgreSQL

14 Nov 2024 11:49

## csv with header
$ psql -U$db_user -h$db_host -p$db_port -d DB_NAME \
  -c "\COPY TB_NAME FROM 'data_sample.csv' WITH (FORMAT CSV, header);"

## csv without header
$ psql -U$db_user -h$db_host -p$db_port -d DB_NAME \
  -c "\COPY TB_NAME FROM 'data_sample.csv' WITH (FORMAT CSV);"

## csv without header, specify column
$ psql -U$db_user -h$db_host -p$db_port -d DB_NAME \
  -c "\COPY TB_NAME(COL1,COL2) FROM 'data_sample.csv' WITH (FORMAT CSV);"

PostgreSQL

13 Nov 2024 20:42

pgagent might have died. you probably want to check if it's still running.

PostgreSQL

13 Nov 2024 12:02

Yes needs a bit of understanding of pgbouncer. It works best with these type of application pools. App developers usually spawn large number of kubernetes pods with significantly tiny connection pools (compared to good old days of bare-metal) and hence large number of idle connections. Session pooling isn't good enough for such mechanism. Generally those with managed services cope by scaling instance memory but if it's self hosted then pgbouncer is the way.

PostgreSQL

13 Nov 2024 09:33

What prevents you from using pgbouncer additionally to HikariCP? We use pgbouncer in transaction mode in addition to application connection pool. That keeps idle sessions under control.

PostgreSQL

13 Nov 2024 09:08

I'm not familiar with that. I suggest looking for more specialised or professional support

PostgreSQL

13 Nov 2024 08:37

(there is no reason to do so)

PostgreSQL

13 Nov 2024 08:35

Ok. As per my knowledge postgresql doesn't have an inbuilt feature to close idle connection (v12). I already set the max pool size and timeout setting in hicaricp. Still more than 1000 connections are idle . How to resolve this?

PostgreSQL

13 Nov 2024 08:13

There are also other parametrrs likes random_page_cost

PostgreSQL

13 Nov 2024 08:05

As of now,we are migration data from Oracle to postgres. So before going to live we have to tune these parameters. Is it possible for based on database size?

PostgreSQL

13 Nov 2024 07:53

Hello All,
Anyone have experience on tuning parameters please help me on below parameterd. After migrate from Oracle to postgres, we need to tune these parameters.

shared_buffers
huge_pages
huge_page_size
temp_buffers
work_mem
hash_mem_multiplier
maintenance_work_mem
autovacuum_work_mem
vacuum_buffer_usage_limit
logical_decoding_work_mem
min_dynamic_shared_memory
max_parallel_workers_per_gather
max_parallel_maintenance_workers
max_parallel_workers
max_worker_processes

PostgreSQL

13 Nov 2024 04:10

Yes. tomcat and hikaricp .. in hikaricp max_connection is 20 in 14 applications.
But I want to know from a postgresql perspective, how it creates connection and closes it. Suppose I write psql -U postgres.. it creates a new connection, now if I do \c mydb.. will it create a new connection or use the existing one?

PostgreSQL

12 Nov 2024 13:54

How connections are created in postgresql when connected by jdbc. In my case it suddenly reaches 800 where the max_connection is 1000. I don't know what's happening in the backend. If anyone knows plz help