Postgresql

I recently migrated mpazari.com, a Turkish motorcycle classifieds site, from MySQL 8 to PostgreSQL 12. The application is a Spring Boot 4 / Java 25 GraalVM native image using hand-written SQL. The migration involved moving the existing database, adapting the MySQL-specific queries, and testing the application against a real production snapshot. The migration was completed with approximately three minutes of downtime. The main steps were: Create the PostgreSQL schema. Copy the MySQL data into PostgreSQL. Update the application queries for PostgreSQL. Run the test suite and verify the row counts. Stop the application, perform the final copy, switch the JDBC URL, and restart it. PostgreSQL’s stricter typing exposed a few old data and SQL issues that MySQL had silently accepted. The test suite helped find and fix those issues before the production cutover. ...

This guide walks you through running PostgreSQL in Testcontainers with SSL enabled and client-certificate authentication (verify-full). It explains: How to generate a CA, server, and client certificates. How to prepare certificate/key files and the right formats/permissions. How to configure PostgreSQL (postgresql.conf and pg_hba.conf) to require client certificates. How to wire everything up in Testcontainers. How to connect from Java (JDBC) and optionally from the psql CLI. Everything here is based on the code in this repository, particularly: ...

PostgreSQL 🐘 is a robust database, but its MVCC (Multi-Version Concurrency Control) design can sometimes lead to a subtle issue: table and index bloat. If left unchecked, bloat wastes storage, increases I/O, and can slow queries down. In this post, I’ll walk through: What bloat is and why it happens Different ways to measure it How to keep an eye on it without killing your database A few habits to reduce or fix it What is bloat? 🧹 Table bloat: Every update creates a new row version, and the old one becomes dead. Those dead tuples sit there until vacuum cleans them. Index bloat: Even when dead tuples are gone from the table, their index entries may stick around until an index vacuum or reindex. Why care? ...

When implementing secure PostgreSQL connections, certificate creation forms the foundation of your PKI infrastructure. Whether you’re setting up a development environment or deploying enterprise-grade systems, understanding proper certificate creation practices is crucial. This guide explores certificate creation from the simple OpenSSL approach to enterprise-grade practices employed by major financial institutions like Credit Suisse/UBS and media companies like BBC. Related Reading: For implementing DN-based certificate authentication in PostgreSQL, see our DN Authentication guide. ...

Today, I’m diving into Distinguished Name (DN) authentication-a powerful feature that enables certificate-based authentication when the Common Name (CN) in your client certificate doesn’t match your PostgreSQL username. This approach is essential in enterprise environments where certificate naming conventions don’t align with database user naming requirements. Version Compatibility: The clientname=DN feature was introduced in PostgreSQL 14. If you’re using PostgreSQL 13 or earlier versions, this DN authentication method will not work and you’ll need to use traditional CN-based certificate authentication instead. ...