- +31612548743
In the Netherlands, every car older then then 4 years needs a mandatory yearly car. With this yearly checkup, we want to prevent dangerous situations with cars and unsafe cars on the road. This “APK”, translated as “General Periodic Inspection” would be a good idea for databases / infra structure.
As a consultant, I come have contact lot of different customers. From a database manegement point of view, I see a lot of common issues.
I would like explain to IT manager, that a yearly checkup of database would help to prevent issues with operational /production databases.
On the PostgreSQL.org website, there is a good explanation how to control LCM with PostgreSQL versions. There are major and minor versions in the PostgreSQL world.
The Lifecycle of a Major release is 5 years. Major upgrades are more complex. It has more impact on a database. You have to work with dump/release mechanism and/or with the pg_upgrade tool. Often this means more downtime.
Minor release are coming every quarter. The release dates for the upcoming periods can be found here. With the minor release the binaries are replaces. The internal structure of the database will be the same. Minor releases only contain fixes for frequently-encountered bugs, low-risk fixes, security issues, and data corruption problems
Some help on this:
Beside of the Lifecycle of PostgreSQL version I see a lot of differentiation between version of extensions. Often, extension versions are not complication to the PostgreSQL version. But also, if there are more customer databases, which each there own extension installed, there are a lot of differentiation.
Why not install the correct version of a extension for ALL databases for the particular PostgreSQL instance version?
There are a lot of good articles about “PostgreSQL security best practices”. Like this one of EnterpriseDB (EDB). Reading these articles is one. But, how to implement them in your organization?
Knowing that md5 is an old authentication method in your pg_hba.conf (host based access file) file is fine. But how to define a new standard and change this to the current (and new created) databases?
Are my certificates accurate? Are all Linux related settings correct related to PostgreSQL security rules?
The Center for Internet Security (CIS) benchmarks for PostgreSQL helps customers to check for security vulnerabilities. These benchmarks are available per PostgreSQL version. For PostgreSQL 16, it’s a PDF document of 174 pages. It would be nice to have an better interface between the CIS organization (PDF) and the customer to check the possible security violations (scripts, bash, sql) . I see it as a challenge for me as a consultant to develop a simple tool for customer to implement this PDF in a a simple check tool. Something for a next blog post.
Define and execute a backup on a database is one. To be able to restore a database is two! For a lot of customers is complexity starts with: Do I have a single customers database of a shared (customer) database.
With a single customer database I can easily do a Point-In-Time-Recovery (PITR). With a shared database, all customer database will go back in time…. Or, I have to restore the particular database to a separated backup server and then dump the content of the database and restore is logically in the production database.
These procedures , for backup and restore, must be checked yearly.
