PostgreSQL Tools

In this Post, let’s take a closer look at the classification of PostgreSQL peripheral tools and the most used tools in each category. Most of the tools we share are open source, and the commercial cost of using these tools is relatively small.

These PostgreSQL tools can be roughly divided into the following categories:

1. Backup and recovery tools
2. Monitoring tools
3. Logic and trigger-based replication tools
4. Multi-master replication tool
5. High availability and failover tools
6. Connection pool tool
7. Table partitioning tool
8. Migration tool

Backup and recovery tools

Barman

Barman (Backup and Recovery Manager, Backup and Recovery Manager) is an open source management tool for disaster recovery of PostgreSQL database, written in Python and and maintained by 2nd Quadrant Company (2ndQuadrant). Barman enables enterprises to perform remote backups of multiple business-critical databases and assists DBAs in performing data recovery in the event of a disaster.

Barman is used for physical backup of PostgreSQL database, which is very similar to Oracle’s backup and recovery tool RMAN.

Barman is developed and maintained by 2ndQuadrant , and is released under the GNU GPL 3 agreement.

The main functions and goals of Barman are as follows:
Full physical hot standby of PostgreSQL server
Point-in-time recovery (PITR)
Manage multiple PostgreSQL servers
Perform remote backup via rsync/SSH or pg_basebackup (including 9.2+ standby server)
Supports local and remote (via SSH) recovery
Support WAL archiving and streaming replication
Support synchronous WAL streaming replication (“zero data loss”, PRO=0)
Incremental backup and recovery
Parallel backup and restore
The centralized management of WAL files enhances the integration with the standby server
Manage retention policies for backup and WAL files
Get server status and information
WAL file compression (bzip2, gzip or custom)
Manage basic backups and WAL files through the backup directory
Simple single INI configuration file
Written entirely in Python
PGDATA and table space position movement during recovery
Back up general information and disk occupation information
Server backup diagnostics
Integration with standard archiving tools (eg tar)
Hook script before/after backup
Local storage of metadata

Barman vs. pg_dump

pg_dump is the official tool for performing PostgreSQL logical backups. Logical backup is a snapshot of all objects and data in the database. In general, performing logical backups is a good practice, but it is not sufficient for disaster recovery.
If you perform a logical backup every day at 3 am, and then a system failure occurs at 6 pm, you may lose 15 hours of transaction data. For most critical business scenarios, this is unacceptable.
On the other hand, Barman, using PostgreSQL physical backup technology, can guarantee a very low recovery point objective (RPO). PostgreSQL-based business systems recommend the use of physical backups for disaster recovery, and Barman can provide simple and easy backup management and reliable recovery.

EDB BART

EDB BART (Backup and Recovery Tool) is a key component of enterprise-level PostgreSQL data management strategy. BART provides a retention strategy and point-in-time recovery implementation for large-scale deployment of PostgreSQL services. BART version 2.0 provides block-level incremental backups.

PgBackRest

The main purpose of the pgBackRest tool is to make a simple and reliable backup and recovery tool to seamlessly access large-scale databases and workloads. pgBackRest gave up other traditional backup tools relying on tar and rsync, its backup function is implemented from within the software, and uses the client protocol to interact with the remote server. Removed the dependence on tar and rsync, so that it can better deal with the backup challenges for specific databases. The client remote protocol is more flexible, and the protocol can limit the connection type as required to ensure a safer backup process.

Monitoring tools

PoWA

PoWA (PostgreSQL Workload Analyzer) is a PostgreSQL workload analysis tool. It collects performance data and provides real-time icons and pictures to help us monitor and tune the PostgreSQL server. It is very similar to Oracle AWR or SQL Server MDW.

PgCluu

pgCluu is a PostgreSQL performance monitoring and auditing tool. It displays all the statistical information you collected from the PostgreSQL database cluster in the form of a view. It can display a complete database cluster information and system usage information.

Pgwatch2

Pgwatch2 is one of the easiest tools to monitor PostgreSQL database. It is based on Grafana and provides out-of-the-box monitoring functions for the PostgreSQL database. Because it has been integrated into the container, we don’t have to worry about various dependencies and complicated installation steps. The monitoring can be built in a few minutes, and everything has been configured in advance. We only need to configure the database connection to monitor to run normal monitoring operations.

Logic and trigger-based replication tools

pgLogical

pglogical is a logical replication tool implemented in the form of PostgreSQL extension plug-ins. The integration is perfect, without using any triggers and external programs. As an alternative to physical replication, the plug-in adopts a publish/subscribe model for selective replication, which is an effective way to copy data.

Slony-I

Slony-I is an implementation of PostgreSQL’s multi-slave replication system that supports cascading replication. The main purpose of developing Slony-I is to achieve master-slave replication. This replication system contains all the features and capabilities required for a reasonably configured slave system in a large database system.

Slony-I is mainly designed for data center and backup site scenarios. In this scenario, all nodes are usually required to be available.

Bucardo

Bucardo is a PostgreSQL asynchronous replication system that allows the configuration of multiple masters and multiple slave operations. It was developed by Jon Jensen and Greg Sabino of Backcountry.com.

Multi-master replication tool

BDR

Postgres-BDR (Bi-Directional Replication for PostgreSQL) is the world’s first open-source PostgreSQL multi-master replication system, the purpose is to strengthen the production environment. Open sourced and maintained by the 2nd Quadrant company, BDR is specially designed for geographically distributed cluster environments. It uses a funny asynchronous logic replication method to support the distribution of 2 to more than 48 nodes between different regions.

High availability and failover tools

Repmgr

Repmgr is an open-source tool for PostgreSQL server cluster replication management and failover. It expands the built-in hot-standby capability of PostgreSQL, and can set up hot backup servers, monitor replication, and perform management tasks (failover, manual switching, etc.). repmgr was developed by the 2nd Quadrant company.

PAF

PAF (PostgreSQL Automatic Failover-Automatic Failover Tool) is an OCF resource agent that contributed to PostgreSQL. Its initial purpose is to clarify rules in Pacemaker management and PostgreSQL to make things simple, documented, and effective. If your PostgreSQL cluster is enabled for internal streaming replication, PAF is exposed to the current status of each PostgreSQL instance node of Pacemaker: which is the master, which is the slave, which is stopped, which is chasing the replication status, and so on. If the master node fails, Pacemaker defaults to recovering the failed master node first. If the failure is not recoverable, PAF will select the best one from the slave nodes (closest to the failed master node data) and promote it to the new master node.

Patroni

Patroni is a template that uses Python to provide you with a customized, highly available solution for maximum usability. Its configuration information is stored in things like ZooKeeper, etcd, or Consul. If DBAs, DevOps engineers or SRE are looking for a solution to quickly deploy highly available PostgreSQL in the data center or other uses, Patroni can help.

Stolon

Stolon is a cloud-native PostgreSQL high-availability management tool. The reason why it is cloud-native is that it can provide high availability for PostgreSQL inside the container (Kubernetes integration), and also supports other kinds of infrastructure (for example cloud IaaS, old-style infrastructure, etc.)

Connection Pooling Tools

PgBouncer

PgBouncer is a connection pool tool developed by Skype developers in 2007. In the years since then, the project has been improved by many developers, but no matter how it changes, its role in reducing the cost of PostgreSQL connections has not changed. PgBouncer allows client access to PostgreSQL database operations greater than the maximum number of connections it can provide. It essentially only tracks every client connection, and then based on the configuration information, creates some client connections and serves client access based on the principle of the first-in-first service.

PgPool-II

pgpool-II is also a connection pool, we are usually used to call it pgpool. It is another popular connection broker, which was released about a year before PgBouncer (released in the second half of 2006). The scope of use of pgpool is very close. The functions it can provide include query-based replication, connection pool function, load balancing, parallel query, etc. An important specificity of pgpool is the connection pool. If we have two PostgreSQL servers, we want to use a virtual IP so that the client will not feel the impact of the main database switch. Sometimes, in order to move the IP address between servers, you first need to remove the IP from the main database server and then rebuild it on another one, which will interrupt the active link and cause the temporary service to be unavailable. With pgpool, the server can be cached until another server is promoted. pgpool will handle failover internally. From the perspective of applications and clients, the database never seems to be offline.

Table partitioning tool

Pg_Partman

pg_partman is an extension of PostgreSQL, used to create and manage time-based or sequence-based table partitions. Multi-level sub-partitions are also supported. Both sub-tables and triggers are managed by the extension itself. Tables that already have data can easily add fine-grained partitions. An optional retention strategy can automatically delete partitions that are no longer needed. The background work process (BGW) can automatically run partition maintenance to perform tasks regularly, without relying on programs such as linux cron for external maintenance.

pg_Pathman

pg_pathman is an open source extension plug-in of PostgreSQL Pro, which can provide optimized partition solutions for large distributed databases. Using pg_pathman can partition large databases without downtime, speed up query of partitioned tables, dynamically manage and add partitions, add external tables to partitions, operate joint partitions, etc.

Migration tool

Ora2pg

Ora2Pg is a free tool for migrating Oracle or MySQL databases to PostgreSQL. It can connect to the Oracle database, and then automatically scan and export the source table structure or data, and convert it into a PostgreSQL database SQL script. Ora2Pg can be used as the reverse engineering of the Oracle database, which is used for scenarios such as large enterprise database migration or Oracle data replication to the PostgreSQL database. It is easy to use, does not require any Oracle database background, all you need to do is to establish a connection with the Oracle database.

Thanks for reading this post.