Postgresql

At work we have a pretty standard stack for the backend. A Django monolith app with relationnal DB, PostgreSQL. When revamping our test infrastructure, I traded reliablity for speed, using sqlite with spatialite. Setup of spatialite on macOS can be quite cumbersome to make it work with python (I’m skipping this part here). The setup turn out to be not so simple because I wanted: setup of 3 DB. fast setup/teardown. It turns out pytest-postgresql, which will starts a postgresql cluster on your machine, is way faster than using testcontainer. ...

QGIS is able to save your layers “styles” to a postgresql databases. By style, QGIS means all your layer customization (like labels, fields, form etc…). The annoying part is: by default, the user who created the style is the only one able to open it. Actually QGIS store the (postgres) user who created the style in the db, and filter it QGIS side 1. So, here is a little hack to make QGIS happy and still being able to open style whatever the user request it. ...

I see a lot project using timestamp data type in there databases. I tend to think that this is a bad idea. Dev Is Not Prod Your local setup may have a timezone which reflect your physical location. This may not be the case of your server(s), which could have a specific timezone, or, UTC time. Which is better by the way (more on than later). So you will experience bug and issues if you do not pay attention to it. ...

I recently migrate a PostgreSQL DB from an ubuntu server VM to Azure PostgreSQL. If you use this DBaaS before, you may have noticed you need to add @<my_instance_name> after your login. For exemple if you create a test_azure_db, your url for this PostgreSQL instance is test_azure_db.postgres.database.azure.com. And imagine the name of your user is remi, to login you need to do psql -h test_azure_db.postgres.database.azure.com -U remi@test_azure. I don’t know what sort of magic Azure is doing, but it gave me an idea. You are a software editor company. Every company now have a SaaS product. But then you have this client, that have special needs, so you create a db for him. And then, Sales sell access to the db (or a replica) “for BI purpose” they say. But, hey, in PostgreSQL there are no way to limit the db a user can see. Plus this client want to be superuser, you know, reasons. ...

Recently I though about (re)starting a blog, but didn’t find subject to fill it. Then I got a new job with tons of news subject to learn and I need a place to store it, maybe it will help some. Enjoy. Right Management in Postgresql @Fibrea we use postgresql a lot, it’s the heart of our system. Operating this DB day to day is quite challenging some time. So here is what I understand from my experience and reading on right management in postgresql. ...