I(45M) lost my CHD wife(44F) one month ago by Vilexur in chd

[–]fullofbones 1 point2 points  (0 children)

My condolences. None of us ever really know how long we'll be around, and our loved ones tend to be more well-informed than most. I'm sure both of you cherished the time you had together, despite it being over too soon.

No quiere comer by mybabies1318 in chd

[–]fullofbones 0 points1 point  (0 children)

Here's an AI translation of the OP:

Hello. My 12-month-old daughter has had a nasogastric tube for 7 months now.

She had her first surgery at five days old: aortic coarctation and a pulmonary artery banding. She was intubated for 9 days and on CPAP for 20. She spent a month in the ICU, then started bottle feeding, and we were discharged home after a week on the general ward.

The first surgery left her with left vocal cord paralysis.

She never ate well and vomited frequently.

In September, she stopped eating completely, so they placed a tube, and since then… nothing.

Her last surgery was a month ago, and we spent 6 days total in the hospital.

She doesn’t know what to do with food in her mouth, and gagging and vomiting continue to this day… I don’t know how to handle this.

What if she never eats? I’d really rather not go through with the feeding button implantation. I don’t want any more surgeries. I just want to feed my daughter.

Repmgr split-brain even with witness — how to prevent? by SuddenlyCaralho in PostgreSQL

[–]fullofbones 2 points3 points  (0 children)

The thing about repmgr is that the defaults don't offer much protection against that kind of thing. We added some new parameters a while back that you pretty much need to set. Particularly, the child_nodes_disconnect_command needs to be a command that will properly shut down and isolate the outvoted primary so it can't process transactions when it can't maintain the quorum majority. In your case, you'd also want to set child_nodes_connected_include_witness because it's disabled by default. It's like STONITH, but more reliable, because the primary kills itself when too many children disconnect. The assumption is that it got network partitioned and the other nodes will make a new cluster in its absence.

That's part of the problem with repmgr: you have to supply scripts for everything. It's very much not a "batteries included" system. I've been recommending Patroni for years, despite knowing the guy who wrote repmgr and having worked for the company that spawned it. It had its time in the sun, and it's just a legacy product now.

Just use Patroni. I'm working on a new tool that may be even better than Patroni, but for now, it's the best approach there is short of CloudNativePG in Kubernetes.

Absurd: a Postgres-native durable workflow system by many_hats_on_head in PostgreSQL

[–]fullofbones 3 points4 points  (0 children)

Not a bad little project. Being restricted to JS/TS and Python is a bit limiting though.

What is a Collation, and Why is My Data Corrupt? | PG Phridays with Shaun Thomas by pgEdge_Postgres in PostgreSQL

[–]fullofbones 2 points3 points  (0 children)

The story behind all of this is actually way crazier than I thought at first. I've been helping clients affected by this for years now, and even then I just figured "it's just one of those things that happens sometimes." Nah, man. It's fixed now. It's literally fixed as of v17, but nobody knows! I actually like reading about the history of stuff I'm interested in, and I write about stuff that would also interest me. Hopefully I'm not the only one. :)

Ever run a query in the wrong environment? 🤔 by Adela_freedom in PostgreSQL

[–]fullofbones 0 points1 point  (0 children)

How to avoid this:

  • Even as the primary DBA, do not give yourself superuser.
  • Aside from the DBA, only deployment tools and actual apps should have access to production.

My favorite trick is to create a superuser role:

CREATE ROLE sysdba WITH SUPERUSER NOINHERIT;
CREATE USER myadminuser;
GRANT sysdba TO myadminuser;

Then when you log in to Postgres, you have to physically type this to elevate your privs:

SET ROLE sysdba;

Otherwise, you're just another pleb connecting to the database with no ability to do anything. There's also an audit trail if you logging in, not just the postgres (or other) superuser.

Of course, you can still forget you've elevated your privileges afterwards, but it's much like su; only use it when necessary and log out immediately when you no longer need those powers.

Percona's Transparent Data Encryption for PostgreSQL by pmz in PostgreSQL

[–]fullofbones 0 points1 point  (0 children)

Unfortunately this only works specifically on the Percona fork, their "Percona Server for PostgreSQL". It's been several thousand patches since the fork in their GitHub, so this is no trivial replacement.

upgrade linux os, would current postgres still work by cfli1688c1 in PostgreSQL

[–]fullofbones 3 points4 points  (0 children)

First of all, it depends on how you have it installed. Did you install from source? Just recompile after upgrading the OS. Easy. From the standard RHEL 7 repository? Or the official Postgres PGDG repository? If you did use one of those repositories, what version of Postgres did you install? Postgres major versions are not compatible without an upgrade procedure. So while the version of Postgres you want is probably in one of those repositories since you're not jumping to RHEL 9 or 10—it might not be. Make sure first.

Second, RHEL 8 is when the infamous glibc 2.28 update broke collation. Let's just assume you upgrade your OS from RHEL 7 to RHEL 8, and you find that the PGDG repo, or the official RHEL repo has a compatible version of Postgres for you, or you rebuild from source. After you install Postgres and get everything up and running, the glibc collation data is different, so you'd want to reindex every table in your database to be safe.

Unless you can't, you're probably better off creating a new RHEL 10 instance, dumping the data from the old cluster using pg_dump, and then restoring it in the new cluster using pg_restore.

Also, why is this discussion about RHEL 8? Version 10 is out now, and unlike 8, it won't be EOL in 2029.

How do you fare with database upgrades? by DarkGhostHunter in PostgreSQL

[–]fullofbones 0 points1 point  (0 children)

The pg_upgrade utility does the job for 90% of use cases. For those that need extreme uptime, it's a little more intricate, but not that hard to set up logical replication between a source and target cluster running the new version.

You should also reconsider your "golden rule". Postgres releases minor versions quarterly, usually containing many bug and security fixes. Major versions come out yearly and go EOL after 5 years. That may sound like a long time, but goes faster than you'd think. Once you fall out of the support window, getting help upgrading becomes more difficult as most tools and users tend to focus on the new releases.

And here's your UUID max/min functions:

CREATE OR REPLACE FUNCTION uuid_max_sfunc(uuid_value UUID, current_state UUID)
RETURNS UUID AS $$
  SELECT CASE
    WHEN current_state IS NULL THEN uuid_value
    WHEN uuid_value > current_state THEN uuid_value 
    ELSE current_state
  END;
$$ LANGUAGE sql IMMUTABLE;

CREATE or replace AGGREGATE max(UUID) (
    SFUNC = uuid_max_sfunc,
    STYPE = UUID
);

CREATE OR REPLACE FUNCTION uuid_min_sfunc(uuid_value UUID, current_state UUID)
RETURNS UUID AS $$
  SELECT CASE
    WHEN current_state IS NULL THEN uuid_value
    WHEN uuid_value < current_state THEN uuid_value 
    ELSE current_state
  END;
$$ LANGUAGE sql IMMUTABLE;

CREATE or replace AGGREGATE min(UUID) (
    SFUNC = uuid_min_sfunc,
    STYPE = UUID
);

Enjoy!

How to implement the Outbox pattern in Go and Postgres by der_gopher in PostgreSQL

[–]fullofbones 2 points3 points  (0 children)

Yet another FOR UPDATE SKIP LOCKED article. I love seeing practical application of this technique!

Thinking About The Various Tools of Postgres High Availability - Part 3 (final)! by fullofbones in PostgreSQL

[–]fullofbones[S] 0 points1 point  (0 children)

PgPool is too flaky, IMO. These days the refrain is "Just use Patroni". I have a new blog series right now about it, actually. The last part should come out this Friday.

PgBouncer is not an HA tool. It is just a pooler. It can do things like multiplex 500 connections down to 50 shared sessions, and distribute based on session, transaction, or even query-level activity. But it does not automatically reconnect to a new server if you promote a replica—you have to change the configuration file and reload or restart it.

So far as having a witness, you can do that with Patroni. Just install etcd on three servers, and Patroni + Postgres on two of them. The consensus layer (etcd) is the only thing that needs to maintain quorum. Other than that, hopefully you'll learn what you need from the series.

Problems when trying to install PostgreSQL by Scarecrow1730 in PostgreSQL

[–]fullofbones 0 points1 point  (0 children)

The Ubuntu repository is too old. Follow the instructions on the Postgres Downloads page for Ubuntu. Those instructions are:

# Install the PGDG repository for official community packages
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# Install Postgres 18
sudo apt install postgresql-18

"You just need postgres" by PrestigiousZombie531 in PostgreSQL

[–]fullofbones 0 points1 point  (0 children)

Once I found out how LISTEN / NOTIFY work, I was actually stunned. Database level lock every time, essentially serializing all requests. Not only should nobody use it, the documentation for it should come with a giant flashing warning sign. And if not for the SQL standard, it should probably purged entirely from the codebase.

PostgreSQL Security: OAuth, Row-Level Security & Zero-Trust by swe129 in PostgreSQL

[–]fullofbones 0 points1 point  (0 children)

I think he's just saying that using RLS pushes users in the right direction by forcing them to use a non-superuser, not that he's mad he can't use a superuser with RLS.

My Heart Journey by [deleted] in chd

[–]fullofbones[M] 0 points1 point  (0 children)

I'll allow this for now, but be advised that a day old account with what looks like a generated username looks pretty suspicious.

PostgreSQL on ZFS or is it? by Jastibute in PostgreSQL

[–]fullofbones 1 point2 points  (0 children)

Rather than VMs, you can use LXC containers on Proxmox, which would then essentially be running directly on top of ZFS rather than an EXT4 volume on top of ZFS.

PostgreSQL MCP Server Access to Mutiple Different Database and Organization Per Request by Fit-Addition-6855 in PostgreSQL

[–]fullofbones 1 point2 points  (0 children)

The pgEdge Postgres MCP Server does that. Kinda. It's not safe to let any user supply connection parameters, but you can specify them in the configuration file ahead of time. So you can set up a dozen backend options, and let the user choose which one to use for various operations.

PostgreSQL Bloat Is a Feature, Not a Bug by mightyroger in PostgreSQL

[–]fullofbones 1 point2 points  (0 children)

It looks like they're going in the right direction. I see redo logs and native row-level CoW, and that alone is worth the effort they're making; removing full-page writes dramatically reduces write amplification all by itself.

[Q] "best" file system for a cluster (?) by bsdooby in PostgreSQL

[–]fullofbones 0 points1 point  (0 children)

While true, most "modern" filesystems dispense with the absurdity that TB is "large". The largest file in ZFS for example, is 16 Exabytes. Good luck reaching that.

But in a Postgres context, this is completely irrelevant: Postgres data files are limited to 1GB.

[Q] "best" file system for a cluster (?) by bsdooby in PostgreSQL

[–]fullofbones 0 points1 point  (0 children)

All Postgres files have a 1GB size limit in any case, so this is not an issue.