The PostgreSQL 'Supply Cut' and Trust Issues in Software Supply Chain
PostgreSQL official repos cut off global mirror sync channels, open-source binaries supply disrupted, revealing the true colors of various database and cloud vendors.
Tags: PG-Admin
PGDG Cuts Off Mirror Sync Channel
PGDG cuts off FTP rsync sync channels, global mirror sites universally disconnected - this time they really strangled global users’ supply chain.
PostgreSQL Macro Query Optimization with pg_stat_statements
Query optimization is one of the core responsibilities of DBAs. This article introduces how to use metrics provided by pg_stat_statements for macro-level PostgreSQL query optimization.
How to Use pg_filedump for Data Recovery?
Backups are a DBA’s lifeline — but what if your PostgreSQL database has already exploded and you have no backups? Maybe pg_filedump can help you!
Localization and Collation Rules in PostgreSQL
What? Don’t know what COLLATION is? Remember one thing: using C COLLATE is always the right choice!
PostgreSQL Logical Replication Deep Dive
This article introduces the principles and best practices of logical replication in PostgreSQL 13.
A Methodology for Diagnosing PostgreSQL Slow Queries
Slow queries are the sworn enemy of OLTP databases. Here’s how to identify, analyze, and fix them using metrics (Pigsty dashboards), pg_stat_statements, and logs.
Incident-Report: Patroni Failure Due to Time Travel
Machine restarted due to failure, NTP service corrected PG time after PG startup, causing Patroni to fail to start.
Online Primary Key Column Type Change
How to change column types online, such as upgrading from INT to BIGINT?
Golden Monitoring Metrics: Errors, Latency, Throughput, Saturation
Understanding the golden monitoring metrics in PostgreSQL
Database Cluster Management Concepts and Entity Naming Conventions
Concepts and their naming are very important. Naming style reflects an engineer’s understanding of system architecture. Poorly defined concepts lead to communication confusion, while carelessly set names create unexpected additional burden. Therefore, they need careful design.
PostgreSQL's KPI
Managing databases is similar to managing people - both need KPIs (Key Performance Indicators). So what are database KPIs? This article introduces a way to measure PostgreSQL load: using a single horizontally comparable metric that is basically independent of workload type and machine type, called PG Load.
Online PostgreSQL Column Type Migration
How to modify PostgreSQL column types online? A general approach
Incident: PostgreSQL Extension Installation Causes Connection Failure
Today encountered an interesting case where a customer reported database connection issues caused by extensions.
PostgreSQL Common Replication Topology Plans
Replication is one of the core issues in system architecture.
Warm Standby: Using pg_receivewal
There are various backup strategies. Physical backups can usually be divided into four types.
Incident-Report: Connection-Pool Contamination Caused by pg_dump
Sometimes, interactions between components manifest in subtle ways. For example, using pg_dump to export data from a connection pool can cause connection pool contamination issues.
PostgreSQL Data Page Corruption Repair
Using binary editing to repair PostgreSQL data pages, and how to make a primary key query return two records.
Relation Bloat Monitoring and Management
PostgreSQL uses MVCC as its primary concurrency control technology. While it has many benefits, it also brings other effects, such as relation bloat.
Getting Started with PipelineDB
PipelineDB is a PostgreSQL extension for streaming analytics. Here’s how to install it and build continuous views over live data.
TimescaleDB Quick Start
TimescaleDB is a PostgreSQL extension plugin that provides time-series database functionality.
Incident-Report: Integer Overflow from Rapid Sequence Number Consumption
If you use Integer sequences on tables, you should consider potential overflow scenarios.
Incident-Report: PostgreSQL Transaction ID Wraparound
XID WrapAround is perhaps a unique type of failure specific to PostgreSQL
Monitoring Table Size in PostgreSQL
Tables in PostgreSQL correspond to many physical files. This article explains how to calculate the actual size of a table in PostgreSQL.
PgAdmin Installation and Configuration
PgAdmin is a GUI program for managing PostgreSQL, written in Python, but it’s quite dated and requires some additional configuration.
Incident-Report: Uneven Load Avalanche
Recently there was a perplexing incident where a database had half its data volume and load migrated away, but ended up being overwhelmed due to increased load.
Bash and psql Tips
Some tips for interacting between PostgreSQL and Bash.
PostgreSQL Routine Maintenance
Cars need oil changes, databases need maintenance. For PG, three important maintenance tasks: backup, repack, vacuum
Backup and Recovery Methods Overview
Backup is the foundation of a DBA’s livelihood. With backups, there’s no need to panic.
PgBackRest2 Documentation
PgBackRest is a set of PostgreSQL backup tools written in Perl
Pgbouncer Quick Start
Pgbouncer is a lightweight database connection pool. This guide covers basic Pgbouncer configuration, management, and usage.
PostgreSQL Server Log Regular Configuration
It’s recommended to configure PostgreSQL’s log format as CSV for easy analysis, and it can be directly imported into PostgreSQL data tables.
Testing Disk Performance with FIO
FIO is a convenient tool for testing disk I/O performance
Using sysbench to Test PostgreSQL Performance
Although PostgreSQL provides pgbench, sometimes you need sysbench to outperform MySQL.
Changing Engines Mid-Flight — PostgreSQL Zero-Downtime Data Migration
Data migration typically involves stopping services for updates. Zero-downtime data migration is a relatively advanced operation.
Finding Unused Indexes
Indexes are useful, but they’re not free. Unused indexes are a waste. Use these methods to identify unused indexes.
Batch Configure SSH Passwordless Login
Quick configuration for passwordless login to all machines
Wireshark Packet Capture Protocol Analysis
Wireshark is a very useful tool, especially suitable for analyzing network protocols. Here’s a simple introduction to using Wireshark for packet capture and PostgreSQL protocol analysis.
The Versatile file_fdw — Reading System Information from Your Database
With file_fdw, you can easily view operating system information, fetch network data, and feed various data sources into your database for unified viewing and management.
Common Linux Statistics CLI Tools
top, free, vmstat, iostat: Quick reference for four commonly used CLI tools
Installing PostGIS from Source
PostGIS is PostgreSQL’s killer extension, but compiling and installing it isn’t easy.
PostgreSQL MongoFDW Installation and Deployment
Recently had business requirements to access MongoDB through PostgreSQL FDW, but compiling MongoDB FDW is really a nightmare.