Category: postgresql

Enterprise PostgreSQL Solutions

Various Restoration Techniques Using PostgreSQL Point-In-Time Recovery

Introduction This blog is aimed at beginners trying to learn the basics of PostgreSQL but already have some experience under their belt. For this tutorial, we will assume you have PostgreSQL correctly installed on Ubuntu. All of these steps were done using PostgreSQL 16 (development version) and Ubuntu 22.10. We’ll go over 3 different restoration…
Read more

The Rise of Serverless Database

Introduction Serverless architecture has been gaining popularity in the world of technology. With its promise of reduced infrastructure costs and increased scalability, serverless architecture has become a go-to solution for many companies, big and small. This popular architecture makes developers not to consider issues such as server management, scalability, backup, failover or anything infrastructure-related issues.…
Read more

Setting Up PostgreSQL Failover and Failback, the Right Way!

Introduction This blog was written to help beginners understand and set up server replication in PostgreSQL using failover and failback. Much of the information found online about this topic, while detailed, is out of date. Many changes have been made to how failover and failback are configured in recent versions of PostgreSQL. In this blog,…
Read more

Setting Up a PostgreSQL Replica Server Locally

Getting Started This blog is aimed at beginners who want to practice the fundamentals of database replication in PostgreSQL but who might not have access to a remote server. I believe it is essential when learning a new technology to go through examples on one’s own machine in order to solidify concepts. This can be…
Read more

Setup an all-in-one Ceph Storage Cluster on one machine

1. Overview Ceph is an open-source software-defined storage platform that implements object storage on a single distributed computer cluster and provides 3-in-1 interfaces for object, block, and file-level storage. A Ceph Storage Cluster is a collection of Ceph Monitors, Ceph Managers, Ceph Metadata Servers, and OSDs that work together to store and replicate data for…
Read more

TLS setup on Postgres 15 – Common Practice

1.0 Introduction TLS is one of the most commonly used security protocol in most applications but also least understood. In this blog, I will briefly explain the concept of TLS and how it can be configured to Postgres version 15 compiled with compatible OpenSSL library. 2.0 PostgreSQL Server Side Settings These are the TLS settings…
Read more

The smgr interface in PostgreSQL

1. Overview In my previous blog posts, I explained how the amazing buffer tag works in PostgreSQL and how to set up a shared storage using the Lustre network file system. In this blog, I will explain the storage interface provided by PostgreSQL and an idea to experience the storage interface, namely, smgr, the storage…
Read more

Serverless Database Research and Problems Everyone Wants to Solve

1.0 Introduction There are several solutions out there that can solve distributed database issues (such as Citus) and solutions out there that can solve high availability and database clustering issues (such as patroni). Yes, they do solve distributed and database cluster issues but at the same time make database maintenance and debugging more complicated. Very…
Read more

Large Object in PostgreSQL

1. Overview Sometimes, you may need to manage large objects, i.e. CLOB, BLOB and BFILE, using PostgreSQL. There are two ways to deal with large objects in PostgreSQL: one is to use existing data type, i.e. bytea for binary large object, and text for character-based large object; another is to use pg_largeobject; This blog will…
Read more

Global Unique Index ATTACH Support and Its Potential Deficiency

1.0 Introduction In my previous post here, I introduced the global unique index feature that my colleague, David, and I work together and explained how global unique index guarantees cross-partition uniqueness during CREATE. In this blog, I will explain how we implement cross-partition uniqueness with ATTACH and a potential deficiency in this approach. 2.0 Global…
Read more

Global Index, benchmark with pgbench

1. Overview Followed my previous blog, Global Index, a different approach, we posted our initial Global Unique Index POC to Postgres community for open discussion about this approach. Now, this blog is trying to explain how the benchmark was performed using pgbench on this initial Global Unique Index POC. 2. Build global index Before running…
Read more

Cross-partition Uniqueness Guarantee with Global Unique Index

1.0 Introduction My colleague, David, recently published a post “Global Index, a different approach” that describes the work that we are doing to implement global unique index in an approach that does not change current PostgreSQL’s partitioning framework, while allowing cross-partition uniqueness constraint. To implement this, we must first know how PostgreSQL currently ensures uniqueness…
Read more

Global Index, a different approach

1. Overview A few years ago, there was a proposal about adding the global index support to PostgreSQL for partitioned table. Following that proposal, there were many discussions and also an initial version POC to demonstrate the possibility, the technical challenges and the potential benefits, etc. However, the global index feature is still not available…
Read more

How Unique Index Works in PG

1.0 Introduction Recently I have been involved in creating a solution that guarantees cross-partition uniqueness within a partitioned table consisting multiple child tables. Some people refer to this feature as a global index and there has been some discussion about it in the email thread here. Though the idea is good, the approach sparks a…
Read more

How to Set Up NFS and Run PG on it

Introduction Network File System (NFS) is a distributed file system protocol that allows a user on a client node to access files residing on a server node over network much like local storage is accessed. Today in this blog, I will share how to set up both NFSv4 server and client on CentOS7 and run…
Read more

How to setup Lustre file system and run Postgres on it

1. Overview Similar to PostgreSQL, Lustre file system is also an open source project which started about 20 years ago. According to Wikipedia, Lustre file system is a type of parallel distributed file system, and is designed for large-scale cluster computing with native Remote Direct Memory Access (RDMA) support. Lustre file systems are scalable and…
Read more

Maintain a custom PG to PG Connection With libpq’s COPY protocol

1. Introduction Recently in my development work, a custom connection is required to be maintained between a PG backend on primary and another PG backends on standby nodes to communicate custom data in addition to the existing walsender/walreceiver connection that streams WAL data. Of course, I could just create a new standalone backend and maintain…
Read more

One idea of accessing Primary’s buffer blocks

1. Overview PostgreSQL is a great open source project for many reasons. One of the reasons I like it is because of the design of buffer blocks addressing. In this blog, I am going to explain a possible way to share a Primary’s buffer blocks with a Standby. If you want to know more about…
Read more

Understand PG’s MVCC Visibility Basic Check Rules

1. Introduction PostgreSQL’s MultiVersion Concurrency Control (MVCC) is an “advanced technique for improving database performance in a multi-user environment” according to Vadim Mikheev. This technique requires multiple “versions” of the same data tuple exist in the system governed by snapshots taken during different time periods. In other words, under such technique, it is PG’s responsibility…
Read more

How to do backup and restore for PostgreSQL using pg_rman

1. Overview PostgreSQL is a very popular open-source relational database management system, and it is widely used in many different production environments. To maintain the production environment always functioning, you need to a lot tools, and one of the tools must to have been backup and restore. This blog is going to introduce one backup…
Read more