PostgreSQL

12 September 2024 00:33

so at the backend service, if I have like primary is listening to 5432, and replica is listening to 5433, I'll change both to 6432, right??

PostgreSQL

12 September 2024 00:29

First, you have to configure pgboucer to work with local posgtes on each node.

PostgreSQL

12 September 2024 00:25

If I want to have pg bouncer working with patroni and HA proxy, I wanna like integrate pg bouncer with them.

So my question is, do I have to install and configure pgbouncer on each node of postgreSQL patroni works?? also, do I have to install it on the same node on which etcd is running? and how to integrate pgbouncer with patroni and HA-proxy?? that's my question though

PostgreSQL

12 September 2024 00:21

Yes, I totally understand this, but I am asking for something else here
.

PostgreSQL

12 September 2024 00:13

okay, now if I have 3 nodes managed by etcd and patroni, I wanna now to install and configure pg-bouncer with them, so do I need to setup on each node patroni runs?? Do I need also to setup pg bouncer on etcd node??

and how can I integrate it with patroni to work together with also HA proxy?

PostgreSQL

12 September 2024 00:03

On failover or swithover haproxy can correctly route connection to master or standbys automatically.

PostgreSQL

11 September 2024 23:57

If app connects to haproxy it doesn't know about infrastructure behind proxy. When haproxy receives a connection it then desides which postgres is master or slave and routes connection to that postgres depending on port on which initial connection comes.

PostgreSQL

11 September 2024 22:55

No, haproxy and connection pooler have a different roles.

haproxy can route read and write requests to different HA cluster PG nodes, and connection pooler reduses number of connections to PG server itself.

You can find articles about haproxy, patroni and postgres on percona site, some of them I used for setting up my infrastructure.

PostgreSQL

11 September 2024 20:35

... and many more pesky little things, yeah

PostgreSQL

11 September 2024 13:32

It's anything but trivial. I've had a brief chat on the topic with Andres Freund years ago. His main concern back them were HOT updates (otherwise it would "just" be disabling VACUUM and keeping track of XIDs indefinitely).

I reckon the work done for incremental backups can provide a lot of the basic infrastructure needed for temporal features (i.e., what was the state of the cluster at PIT xyz), but that might just be wishful thinking on my side 😏

PostgreSQL

11 September 2024 11:38

This looks mostly fine, but I'd suggest to add SET search_path = ... to function definition parameters. Otherwise changes to search_path may be devastating.

PostgreSQL

11 September 2024 11:32

I’m not one of those few 😁

PostgreSQL

11 September 2024 11:23

Umm...can anyone please help?

PostgreSQL

11 September 2024 11:17

And how exactly would those tell you *who* deleted a record? 😎

Don't get me wrong, I'd fancy them too, but more for things like dynamic prices (don't save the items' price with a bill, but read the items' price at that time via "AS OF SYSTEM TIME").
But for auditing? Naaaay

PostgreSQL

11 September 2024 10:38

Basically to record the logs of whatever happens in one table in another

PostgreSQL

12 September 2024 00:30

And then you will change haproxy config: replace 5432 port to 6432.

PostgreSQL

12 September 2024 00:28

You will install pgboucer near each posgtres. You will not install it on the etcd hosts.

PostgreSQL

12 September 2024 00:23

What are you asking then?

PostgreSQL

12 September 2024 00:17

At the end of the day, if you only have haproxy or pgbouncer running in one place, that your application talks to. Then you still have a single point of failure.

PostgreSQL

12 September 2024 00:05

So, I need only to connect the application to the HA proxy server, right?

PostgreSQL

12 September 2024 00:00

So, you need to configure haproxy and then connection string of application. In my system 5000 port of haproxy route connections to master postgres, 5001 to sync standby, and 5002 to async standby.

PostgreSQL

11 September 2024 23:45

Okay, I mean from does the application knows on which node to connect when failover is happenings? Do I need to configure anything from the application side about this?

PostgreSQL

11 September 2024 22:34

If I use HA proxy to work with Patroni, does it work exactly like Pg-pool or pg-Bouncer??

And in case of automatic failover done by patroni, do I have to configure the application to write to the primary node and read on replica? does it needs any configuration to map to the HA proxy instead of patroni or postgreSQL instead?

PostgreSQL

11 September 2024 19:54

There's also the issue of tracking on a tuple level what epoch the xid belongs to for frozen tuples.

PostgreSQL

11 September 2024 11:39

There was work being done to bring it back to v17. That is the one I was eyeing. Unfortunately, progress seemed to stall so…

PostgreSQL

11 September 2024 11:37

Well, now you are 😏

PostgreSQL

11 September 2024 11:29

Only if you add a GENERATED ALWAYS column. And/or a trigger... and then you can also just implement the above ;-)
Or use one of those:
https://wiki.postgresql.org/wiki/Temporal_Extensions

Or go back to 6.2 (only few are aware that PG had temporal features back in the days):
https://www.postgresql.org/docs/6.3/c0503.htm

PostgreSQL

11 September 2024 11:21

Yeah mine is not strictly auditing. Just history tracking and knowing changes

Interesting tho. If users/roles were set up correctly, couldn’t one get it to log that? I’m just curious🤔

PostgreSQL

11 September 2024 11:04

Stuff like this is why I wish temporal tables were available in PostgreSQL

PostgreSQL

11 September 2024 10:37

The aim is put in any create, update, deleted items from a table called material_detail_stg into pm_change_logs