PostgreSQL

15 Oct 2024 16:04

I think pgpool 2 is doing it here

PostgreSQL

15 Oct 2024 16:00

Hang on, i thought it did that, i think i'm getting confused with another pooler

PostgreSQL

15 Oct 2024 15:53

https://www.pgbouncer.org/features.html

PostgreSQL

15 Oct 2024 15:50

For example, statement pooling might be a good idea when you have a lot of connections doing single-statement (no transactions) queries

PostgreSQL

15 Oct 2024 15:47

Then I guess the problem with HA-Proxy server, does it work with you and ur able to access it and see the dashboard?

PostgreSQL

15 Oct 2024 15:42

Do u have a connection pooling?

PostgreSQL

15 Oct 2024 15:39

it is on it's own, but like 50 connections made big load on the primary, and when I removed it, and accessed the primary directly, the same load happens "only" after 300 connections

PostgreSQL

15 Oct 2024 15:35

I'm unable to properly configure HA-Proxy, it actually created more load on the server itself.
do you know any good guide for better configuration of it for Patroni?

thanks

PostgreSQL

15 Oct 2024 15:16

Hello,
I have a cluster of pg (using patroni) at the moment PG14 we wish to upgrade to 16.

The main usage is mostly writing data (metadata and states0, and even some of selects are part of the insert/update transaction operations to determine some stages of a stateless operations.

I have many connections to the db at the peak usage of the system, that generate big load on the primary (can arrive to 200 tasks waiting).

All of the servers (three) are on "bare metal" (self managed proxmox).

The system must be responsive in few milliseconds (the lower the better) with the stateless states and metadata, so it is impossible to use MQ/Task queue and waiting for writing in bulks.

Do you know a good tool/proxy to manage such usage to reduce if possible some of the load, or better way to manage cluster of primary-primary-secondary?

Thank you

PostgreSQL

15 Oct 2024 15:00

Did you *check* pg_stat_archiver?
Also, check if (and why) you have archive_mode = always set.
If not, maybe you have a replication slot lingering on the replica?

PostgreSQL

15 Oct 2024 14:23

It's already implemented, but purge is for archive path how it's related to pg_wal..

PostgreSQL

15 Oct 2024 12:46

Can you ping me personally?

PostgreSQL

15 Oct 2024 12:44

Well not the best way, just "my" preferred way :)

PostgreSQL

15 Oct 2024 12:39

Not sure I need to check

PostgreSQL

15 Oct 2024 12:37

You used pg_dump, right?

PostgreSQL

15 Oct 2024 16:04

https://www.pgpool.net/docs/pgpool-II-3.7.4/en/html/index.html

PostgreSQL

15 Oct 2024 15:57

But this one doesn't route transactional based on whether they are read or write... so I guess still the issue here...?🤔

PostgreSQL

15 Oct 2024 15:53

in my case there are transactions and also prepared statements.

What can be used?

PostgreSQL

15 Oct 2024 15:49

I think haproxy is a bit unfit for "serious" setups where your proxy/pooler needs to know whether the query is read only or write

PostgreSQL

15 Oct 2024 15:44

yes, psycopg 3 connection pool, but 4 databases on the actual server that each has connection pool on it's own

PostgreSQL

15 Oct 2024 15:41

I checked the last archive status,it was a month ago. But I can see today wal files in archive path..

PostgreSQL

15 Oct 2024 15:37

Why u found it difficult, I used HAproxy with Patroni, and it should implemented on a separate node other than the patroni nodes as it consume utilisation from the cpu to avoid overheads. I mean install and configure HA proxy on a separate node (Server) or vm

PostgreSQL

15 Oct 2024 15:32

I use for myself, HA proxy for managing the read write operational and pg bouncer for user connections

PostgreSQL

15 Oct 2024 15:12

That's right but when archive process fails it's going to keep archive segments at pg_wal directory. Other options are inactive replication slot, uncommitted two phase commit sentences or if you are setting wal_keep_size with high value

PostgreSQL

15 Oct 2024 14:34

Probably, the exact answer was in the logs of psql or pg_restore (depending on the dump format).

I'd suggest remove the database, restore it one more time and read the logs carfully.

PostgreSQL

15 Oct 2024 12:46

I'm currently busy doing other (paid) stuff, you can use my pointers and elaborate further on them

PostgreSQL

15 Oct 2024 12:44

Ok, tell me the the easy steps to take the back up from another server and restore it on another one without getting any errors?

PostgreSQL

15 Oct 2024 12:42

The best way to check in this case is to:
- pg_dump with the --schema-only flag to export only the schema (and not the data) in sql format
- start a fresh cluster with 13.12
- try to import your dump (\i <yourfile.sql> from psql)

If you get a stream of "ALTER" and "CREATE" statements then the problem lies elsewhere
If you get errors, then investigate on the line that caused it

PostgreSQL

15 Oct 2024 12:38

Were the constraints dependent on other objects (functions, extensions, ...)?

PostgreSQL

15 Oct 2024 12:34

Hi all,
I have taken the back up of a database from postgres 13.3 and I restored it in postgres 13.12..

But for some tables, constraints were missed(not restored in postgres 13.12)..

Anybody can help me why they are not restored?