Category: PostgreSQL

To manage Binary Large Objects (BLOBS) in PostgreSQL, use the lo_unlink() function to remove them properly. Failing to do so can leave orphaned records, but vacuumlo can help clean them up.

PostgreSQL’s pg_amcheck is an essential tool for ensuring data integrity. Like Sherlock Holmes, it diligently detects anomalies, offering early detection of data corruption from hardware failures, software bugs, or system crashes. This tool, though not glamorous, is vital for proactive maintenance. Administrators can use pg_amcheck to identify issues.

PostgreSQL allows most DDL commands to be rolled back within a transaction block, unlike in many commercial database systems where DDL commands automatically commit the current transaction. With exceptions like DROP DATABASE and CREATE/DROP TABLESPACE, PostgreSQL ensures that all DDL operations are transactional, allowing for rollback as needed.

FerretDB is an open-source proxy that converts MongoDB 5.0+ wire protocol queries to SQL, using PostgreSQL or SQLite as a database engine. It functions as a drop-in replacement for MongoDB and is constantly updated to enhance compatibility and performance. Installation involves downloading the RPM package, configuring it to listen on port 27002, setting up PostgreSQL, modifying its settings, creating a database, and starting FerretDB. Utilizes PostgreSQL as the backend.

This guide provides a comprehensive approach to secure the PostgreSQL environment, including the database, Patroni, Etcd, and Pgbackrest. It covers key aspects such as authentication, encryption, auditing, parameter hardening, patch management, network security, and backup strategies along with securing Patroni REST APIs and Etcd’s SSL configuration. Additionally, it emphasizes the importance of securing PGBackrest endpoints with TLS encryption to protect sensitive data during backup processes, ensuring comprehensive security for the entire PostgreSQL database environment.

This blog post outlines the process of upgrading a PostgreSQL environment with no internet, covering major and minor upgrades, as well as updates to various extensions. It details the specific steps for upgrading each component, including Patroni, pgbackrest, PostGIS, pg_cron, and Oracle_FDW. The post emphasizes the importance of careful planning and thorough testing for major upgrades, along with considerations for downtime and data replication. Additionally, it provides guidance on memory resource management and configuration adjustments during the upgrade process.