I use a single #Postgres instance in my #k8s clusters, with separate users, passwords, and databases in that instance for each application. It’s simpler to manage and reduces complexity, and my databases don’t benefit from being spread across nodes (they’re small and place few demands on the system). So the same instance hosts the databases for synapse and diaspora, for example.

Today I discovered that objects in the public schema of each database (which is where they’re created by default) are accessible to all users on the database, unless they’re specifically revoked. So the system wasn’t as secure as I thought it was.

You can change this behavior with REVOKE ALL ON DATABASE <name> FROM PUBLIC;. Then only users granted access (or the owner) will have access. You can also create objects in non-public schemas, but that can be challenging.

One more thing to automate.

Upgraded the pod to #postgres 16.2 and it worked perfectly, because #Postgresql always works perfectly.

The pod is now running #postgres 16.1. As expected there are no issues.

I started messing about with #postgres FTS in #Diaspora.

First, create a search column:

alter table posts add column fts_text_en tsvector generated always as (to_tsvector ('english', text )) stored;


Then, create a GIN a index on the new column:

create index posts_fts_text_en_idx on posts using gin(fts_text_en);


Next, query the column:

select id from posts where fts_text_en @@ websearch_to_tsquery('english', 'Linux');


Fast, simple. Prolly should integrate it into the search bar.

This pod is now running #postgresql 16. Seems speedier.

#postgres #podmin

Now running #postgres 15.4. No issues. #postgresql

I guess it’s just #postgres then.

https://medium.com/@imashadowphantom/mariadb-com-is-dead-long-live-mariadb-org-b8a0ca50a637

I'm taking the pod offline for an hour or so while I upgrade #Postgres.

https://www.amazingcto.com/postgres-for-everything/

#postgres

Upgraded to #postgres 14.6 without a hitch.

I upgraded the #postgres instance that the pod uses to use WAL-G 2.0.0, which looks to be a fairly substantial upgrade. For me it initially meant upgrading the encryption key used for backups from 20 bytes to 25 bytes (which pretty much necessitated starting a new stream of backups); everything else seems to be about the same.

WAL-G allows point-in-time-recovery (PITR) of Postgres databases to be stored on S3-compatible storage (including the FOSS Minio S3 server). It's continuous, leveraging Postgres' built-in write-ahead log (WAL) to provide the source data for the backups. WAL-G then compresses and (optionally) encrypts the data before sending it to your S3 storage.

If you're running a #Diaspora pod, you owe it to yourself and to your users to maintain reliable offsite backups. Once WAL-G is configured you can pretty much forget about it, except for occasional testing to make sure the backups are recoverable.

GitHub - wal-g/wal-g: Archival and Restoration for Postgres

Archival and Restoration for Postgres. Contribute to wal-g/wal-g development by creating an account on GitHub.

Bumped the pod up to #postgres 14.3. No issues, as one would expect.

#postgresql #podmin #diaspora

Decided to spin up a local k3s cluster running on my (ARM64) laptop. Another interesting bit about the Docker environment is how easy it is to migrate configurations across platforms.

I'll add that spinning up a cluster in k3s is just running a single command per node; one for the master node and one for each of the server nodes. It's trivial to automate and completes in seconds.

Now I'm messing around with #ceph for managing high-availability #storage (filesystem and #s3) and #stolon for high-availability #postgres.

#docker #kubernetes #k3s #k8s #arm64 #buildx #ha

#postgresql focusing on the wrong things... to help corporations remove dissenters https://www.postgresql.org/about/news/german-translation-of-community-code-of-conduct-posted-2414/ #psql #postgres

German Translation of Community Code of Conduct Posted

The PostgreSQL Community Code of Conduct Committee is pleased to announce that the German translation of the Code of Conduct …

"pg_back is a tool that can dump all your databases to files, with the configuration of the server and roles/tablespaces definitions." https://www.postgresql.org/about/news/pg_back-210-released-2413/ #postgresql #psql #postgres

Finally got around to upgrading #postgres on my #diaspora pod to 14.2. Works like a champ, and no backup/restore required from earlier 14.x versions.

PostgreSQL: German #PostgreSQL Conference 2022 & Call for Papers ⚓ https://www.postgresql.org/about/news/german-postgresql-conference-2022-call-for-papers-2399/ ䷉ #postgres #de #germany #db

#PostgreSQL : #pgDay Paris #2022 — Schedule published ⚓ https://www.postgresql.org/about/news/pgday-paris-2022-schedule-published-2395/ ䷉ #postgres #psql

● NEWS ● #Rlang #Databases ☞ How To Connect R Shiny to #Postgres Database – The Definite Guide https://www.r-bloggers.com/2022/01/how-to-connect-r-shiny-to-postgres-database-the-definite-guide/

How To Connect R Shiny to Postgres Database – The Definite Guide

Managing database connections can be messy at times. It’s always easier to read and write to local CSV files. That doesn’t mean it’s the right thing to do, as most production environments have data stored in one or multiple databases. As a data...

"The pgagroal community is happy to announce version 1.4.0." https://www.postgresql.org/about/news/pgagroal-140-2390/ #pgagroal #psql #postgres #postgresql