I'd love to try out Oriole DB for the decoupled storage for running Postgres. Keeping all data on S3/Minio would ease my DB management for disks and remove any reliance on a remote filesystems for Postgres.
The current limitation of While OrioleDB tables and materialized views are stored incrementally in the S3 bucket, the history is kept forever. There is currently no mechanism to safely remove the old data. prevents me from running it fully though. I'll have to keep an eye on the dev / next release.
With 0.0.9 of declare_schema I'm starting to fail if migrations cannot be run. At the moment this tends to be room for improvement on implementation vs limitations in Postgres.
Current limitations:
I'm starting to get fairly confident in my usage of it. Going forward I hope to work more on docs and examples.
While upgrading to Postgres 17 I ran into a few problems in my setup:
pg_dump as well, so backups stopped for a few dayspg_dump for Postgres 17 (in some conditions? at least my setup) requires ALPN with TLS.From the release notes:
Allow TLS connections without requiring a network round-trip negotiation (Greg Stark, Heikki Linnakangas, Peter Eisentraut, Michael Paquier, Daniel Gustafsson)
This is enabled with the client-side option sslnegotiation=direct, requires ALPN, and only works on PostgreSQL 17 and later servers.
I run Traefik to proxy Postgres connections, taking advantage of TLS SNI so a single Postgres port can be opened in Traefik and it will route the connection to the appropriate Postgres instance. Traefik ... understandly... doesn't default to advertising that it supports postgresql service over TLS. This must be done explicitly.
In Traefik I was setting logs such as tls: client requested unsupported application protocols ([postgresql])
From pg_dump the log was SSL error: tlsv1 alert no application protocol "postgres"
Fixing this required configuring Traefik to explicitly say postgresql was supported.
# Dynamic configuration
[tls.options]
[tls.options.default]
alpnProtocols = ["http/1.1", "h2", "postgresql"]
This as documented, is dynamic configuration. It must go in a dynamic config file declaration, not the static. In my static config I needed to add
[providers]
[providers.file]
directory = "/local/dynamic"
watch = true
Where /local/dynamic is a dir that contains dynamic configuration. I was unable to get the alpnProtocols set with Nomad dynamic configuration. I always ran into invalid node options: string when Traefik tried to load the config from Consul. Maybe from this
Pleased again with SQLx tests while adding tests against Postgres to test migrations. Previously there wasn't any automated tests for "what does this lib pull from Postgres", I was doing that manually.
#[sqlx::test]
fn test_drop_foreign_key_constraint(pool: PgPool) {
crate::migrate_from_string(
r#"
CREATE TABLE items (id uuid NOT NULL, PRIMARY KEY(id));
CREATE TABLE test (id uuid, CONSTRAINT fk_id FOREIGN KEY(id) REFERENCES items(id))"#,
&pool,
)
.await
.expect("Setup");
let m = crate::generate_migrations_from_string(
r#"
CREATE TABLE items (id uuid NOT NULL, PRIMARY KEY(id));
CREATE TABLE test (id uuid)"#,
&pool,
)
.await
.expect("Migrate");
let alter = vec![r#"ALTER TABLE test DROP CONSTRAINT fk_id CASCADE"#];
assert_eq!(m, alter);
Really impressed with SQLx testing. Super simple to create tests that use the DB in a way that works well in dev and CI environments. I'm not using the migration feature but instead have my own setup to get the DB into the right state at the beginning of tests.
Making a pr to SQLx to add Postgres lquery arrays. This took less time than I expected to try and fix. More time was spent wrangling my various projects to use a local sqlx dependency.
Postgres ltree has a wonderful ? operator that will check an array of lquerys. I plan to use this to allow filtering multiple labels in my expense tracker.
ltree ? lquery[] → boolean
lquery[] ? ltree → boolean
Does ltree match any lquery in array?
Giving Mathesar a try for a "life CMS". I loved Notion databases for easily creating structured data and linking between items and have been trying various things to replace it. Maybe this will be it! I like that it's specifically Postgres (as that's most of my homelab). It's missing a good mobile interface and OIDC though.