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

PostgreSQL

11 September 2024 09:21

Paste there the trigger let's check

PostgreSQL

10 September 2024 22:21

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html

PostgreSQL

10 September 2024 11:21

Can you describe your problem in more detail?

PostgreSQL

10 September 2024 09:57

Error in postgresql while import file please help

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

11 September 2024 10:36

-- Create the trigger function
CREATE OR REPLACE FUNCTION log_material_changes()
RETURNS TRIGGER AS $$
DECLARE
    v_action TEXT;
    v_old_value TEXT;
    v_new_value TEXT;
    v_field TEXT;
    v_user_name TEXT;
    v_mpn TEXT;
    v_spn TEXT;
    v_physicaltype TEXT;
BEGIN
    -- Determine the action type
    IF TG_OP = 'INSERT' THEN
        v_action := 'CREATE';
        v_old_value := NULL;
        v_new_value := ROW(NEW.*)::TEXT;  -- Capturing new values
    ELSIF TG_OP = 'UPDATE' THEN
        v_action := 'MODIFY';
        v_old_value := ROW(OLD.*)::TEXT;  -- Capturing old values
        v_new_value := ROW(NEW.*)::TEXT;  -- Capturing new values
    ELSIF TG_OP = 'DELETE' THEN
        v_action := 'DELETE';
        v_old_value := ROW(OLD.*)::TEXT;  -- Capturing old values
        v_new_value := NULL;
    END IF;
 
    -- Assume user information and other context is available through session variables or application context
    v_user_name := current_user;  -- Modify this line if user context is provided differently
    v_mpn := COALESCE(NEW.manufacturer_part_number, OLD.manufacturer_part_number);
    v_spn := COALESCE(NEW.supplier_part_number, OLD.supplier_part_number);
    v_physicaltype := COALESCE(NEW.physicaltype, OLD.physicaltype);
 
    -- Insert the log record into pm_change_logs
    INSERT INTO pm_change_logs (
        module, action, user_id, created_at, updated_at, is_deleted, item_id,
        old_value, new_value, field, date, user_name, approver, mpn, spn,
        physicaltype, comments
    )
    VALUES (
        'material_detail_stg', v_action, NULL, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP,
        FALSE, COALESCE(NEW.item_id, OLD.item_id), v_old_value, v_new_value,
        NULL, CURRENT_TIMESTAMP, v_user_name, NULL, v_mpn, v_spn,
        v_physicaltype, NULL
    );
 
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
-- Create the trigger on material_detail_stg table
CREATE TRIGGER trg_material_changes
AFTER INSERT OR UPDATE OR DELETE ON material_detail_stg
FOR EACH ROW EXECUTE FUNCTION log_material_changes();

PostgreSQL

11 September 2024 09:00

Hi, need help from someone to check if a trigger function i have written is correct

PostgreSQL

10 September 2024 17:55

Any document for PostgreSQL upgrade on AWS RDS ?

PostgreSQL

10 September 2024 10:24

Hey everyone,

I’ve been working as a DBA/Data Engineer for the past 6 years, and I’m trying to figure out what area I should enrich my knowledge in during my free time. I work with PostgreSQL, MySQL, and MongoDB (both infrastructure and application-level). I mainly use NiFi and Python, and I work in AWS, GCP, and on-premises environments.

I have above-average knowledge of Linux, so I’m capable of handling many tasks that fall under IT/DevOps roles independently. I’ve mostly learned everything on my own, and now I’m looking for areas where I should deepen my expertise or learn something new.

I’m considering the following options and would appreciate your help:

Learn Oracle
Get AWS/GCP Certification as a Cloud Data Engineer
Continue specializing in PostgreSQL, MySQL, and MongoDB
I’d love your suggestions and advice 🙂

PostgreSQL

09 September 2024 16:58

PostgreSQL Person of the Week interview with: Philippe Noël

https://postgresql.life/post/philippe_noel/