Category: postgresql

Enterprise PostgreSQL Solutions

PostgreSQL High Availability: Setup and Uptime Considerations

My previous blog on “PostgreSQL High Availability: Considerations and Candidates” mostly talked about defining an HA considerations for PostgreSQL, RPO and RTO and briefly touched on some of the open source solutions available. Granted that I didn’t list them all and people shared some additional ones that I may end up reviewing as well. In…
Read more

Calling a Stored Procedure in PostgrSQL from JAVA and its Current Limitations

This is my third blog about Stored Procedure support in PostgreSQL, the previous two blogs are reachable from the HighGo CA blogs site https://www.highgo.ca/author/ahsan-h/. The first blog was introduction and usage of Stored Procedures and its difference with Stored Functions, the second blog focussed on creating and using Procedures with Definer and Invoker rights. The…
Read more

Technical Overview of pg_stat_monitor extension: A more detailed pg_stat_statements

Database monitoring is a very essential for most of the database system, it can prevent system crises, improve performance, database failure and auditing the traffic for identify any unwanted access. PostgreSQL has an extension called pg_stat_statements, which can capture a lot of query execution details. Recently, percona release a new extension called pg_stat_monitor which can…
Read more

How to Analyze a PostgreSQL Crash Dump File

1. Introduction In this blog post, I will talk about how to enable the generation of crash dump file (also known as core dump) and some common GDB commands to help a developer troubleshoot a crash-related issues within PostgreSQL and also other applications. Proper analysis of the issue normally will take time and certain degree…
Read more

Free Space Mapping file in details

1. Overview Previously, we discussed the MAIN fork file and corresponding extension at Heap file and page in details. This blog will explain a little bit more about the Free Space Mapping file and corresponding extension. 2. What is a Free Space Mapping file A Free Space Mapping, FSM, file is a file that keeps…
Read more

The way PG store null value in record

When inserting data into a table, you can specify that the value of a field is null. For example, for a table t (i int, j int, k int), we can execute insert into t values (8,1,6), or insert into t values (3, null, 7) to insert a record with a null value. This blog…
Read more

Heap file and page in details

1. Overview PostgreSQL is a great open source database, and many users chose it because of the efficiency of its central algorithms and data structures. As a software developer, I was always curious about how each part was done, such as the physical files storage. The reason is that I always see a lot of…
Read more

Configuring Pgpool-II watchdog: It’s going to be a lot easier

Watchdog is the high availability component of Pgpool-II. Over the past few releases watchdog has gotten a lot of attention from the Pgpool-II developer community and received lots of upgrades and stability improvements. One of the not very strong areas of pgpool-II watchdog is its configuration interface. Watchdog cluster requires quite a few config settings…
Read more

PostgreSQL WAL Archiving and Point-In-Time-Recovery

WAL is short for Write-Ahead-Log. Any change to the data is first recorded in a WAL file. The WAL files are mainly used by RDBMS as a way to achieve durability and consistency while writing data to storage systems. Before we move forward, let’s first see why we need a WAL archiving and Point in…
Read more

pgpool II 4.2 features

The pgpool II community is gearing up to release the Alpha version of its next major release; pgpool II 4.2. It is going to be another exciting release of pgpool II that is a middleware product and provides mission critical functionality like load balancing, high availability, connection pooling etc for PostgreSQL server. We have written…
Read more

PostgresConf.CN and PGCONF.Asia 2020 combined together to bring the best

The PostgreSQL community is anxiously waiting for an exciting online PostgreSQL conference taking place in November 2020. It would be a unique opportunity as PostgreConf.CN and PGCONF.Asia will be merged in order to provide wide range of topics covering various aspect of PostgreSQL ranging from Administration, Performance tuning, Hacking PostgreSQL, Security, Scalability, Foreign data wrappers…
Read more

In-Memory Table with Pluggable Storage API

1. Introduction This blog is to follow up on the post I published back in July, 2020 about achieving an in-memory table storage using PostgreSQL’s pluggable storage API. In the past few months, my team and I have made some progress and did a few POC patches to prove some of the unknowns and hypothesis…
Read more

The Share Invalid mechanism of CACHE in PostgreSQL

PostgreSQL will create some catalog and other caches for each connection request to store some queried data locally, which can speed up query efficiency. If a process undergoes a DDL operation that causes catalog to change, other running processes need to be notified that some cache data is invalid. We call this notification mechanism the…
Read more

PostgreSQL Backups

In a database production environment, a backup plays quite an essential role. The database server can fail for a number of reasons. It can happen because of hardware failure, software malfunction or just because of user error. Whatever the reason, when a live database goes down, a backup is essential in fixing and recovering it.…
Read more

Who is spending wal crazily

Recently, some people are asking why the wal of the database is consumed so quickly. This blog gives a simple way to analyze where the wal log is consumed, and you can get the reason for wal expansion. Note: There are two understandings about wal expansion: 1. Too many LSNs are consumed in a period;…
Read more

How to setup PostgreSQL on an IPv6 enabled network

1. Overview PostgreSQL is a great open source database, not only because it supports lot of database features, but also because it supports different network setup. For example, you can set it up on an IPv6 enabled network in just a few steps. This blog will demonstrate how to setup PostgreSQL on an IPv6 network…
Read more

TLS Related Updates in PostgreSQL 13

1. Introduction The upcoming major release of PostgreSQL 13 has several important behavioral updates related to the TLS implementation. These updates may have some to your current PostgreSQL security deployment if you were considering to upgrade to 13 when it officially releases. Today I would like to do a quick summary of these updates. 2.…
Read more

Consensus based failover with Pgpool-II

Pgpool-II probably is the most comprehensive clustering solution existing today for PostgreSQL. It provides a wide range of features like connection pooling, load balancing, automatic failover and high availability while using the Pgpool-II for load balancing and building a highly available PostgreSQL cluster is one of its most common use case. Since Pgpool-II is a…
Read more

PostgreSQL High Availability: The Considerations and Candidates

Almost every organisation that I interact with wants a high availability system for PostgreSQL. This clearly depicts an active trend toward an increase in utilising PostgreSQL for critical business applications. In some cases it is a move away from other major database systems like Oracle or even Teradata. It’s not possible to cover this topic…
Read more

Partitioning Improvements in PostgreSQL 13

The table partitioning feature in PostgreSQL has come a long way after the declarative partitioning syntax added to PostgreSQL 10. The partitioning feature in PostgreSQL was first added by PG 8.1 by Simon Rigs, it has based on the concept of table inheritance and using constraint exclusion to exclude inherited tables (not needed) from a…
Read more