Author: David Zhang

Feed: Planet PostgreSQL. 1. Overview PostgreSQL supports many System Information Functions, such as Session Information Functions, Access Privilege Inquiry Functions, Schema Visibility Inquiry Functions, System Catalog Information Functions, Transaction ID and Snapshot Information Functions, etc. However, you may want build some special functions and integrate them into the PostgreSQL. This blog is going to walk through the steps about how to build your own System Information Functions into PostgreSQL. 2. Analyze the requirement Since there are so many functions built-in PostgreSQL already, you should perform some research and analysis before you decide to create a new one. In this blog, ... Read More

Feed: Planet PostgreSQL. 1. Overview PostgreSQL supports different type of replications, i.e. logical and physical, and there are many tutorials discussed about the replications. This blog is a simply walk-through of the WAL Streaming replication using the latest Postgresql-13 on Ubuntu 18.04. 2. Install Postgresql-13 from source code In this blog, we will install Postgresql from the source code, so that this environment can be used for later development. Simply prepare two Ubuntu 18.04 using VirtualBox with below network settings. Primary: 192.168.0.181 Standby: 192.168.0.182 It is pretty simple to install postgres from source code. Here we checkout the Postgresql-13 stable ... Read More

Feed: Planet PostgreSQL. 1. Overview PostgreSQL is a great open source database developed and maintained by many great software engineers around the world. In each release, there are many features added to this open source database. For example, one feature is very helpful for developer is backtrace_functions introduced in PostgreSQL 13 , which allows a developer to dump out the backtrace when certain errors happened on the server. In this blog, I am going to explain it in a little bit more detail. 2. What is backtrace_functions? The backtrace_functions option is an option introduced for developers as it is described ... Read More

Feed: Planet PostgreSQL. 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 track of the availability of free space of a page. Within each FSM is a binary tree, stored in an array with one byte per node. The FSM is used to quickly locate a page with enough free space to hold a ... Read More

Feed: Planet PostgreSQL. 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 files and folders were created after a simple initdb command. For example, $ ls -ltr /home/user/pgdata drwx------ 4 user user 4096 Oct 8 13:38 pg_logical drwx------ 5 user user 4096 Oct 8 13:38 base drwx------ 2 user user 4096 Oct 8 13:38 ... Read More

Feed: Planet PostgreSQL. 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 in Linux. Before we dive into the detail, let’s discuss a little bit IPv6. IPv6 was developed by the Internet Engineering Task Force (IETF) in late 1998 and was intended to replace IPv4. With the IPv4 address exhaustion issue, after about two ... Read More

Feed: Planet PostgreSQL. 1. Overview Whenever I tried to study PostgreSQL source code a little deeper, I always wanted to find some tools to help me understand better as I really don’t want to read the code line by line for a particular feature, but at the same time, I really wanted to figure it out quickly. Simply put, I was kind of “lazy” at that movement. Luckily, I found one very simple way to help me when I was studying the Geometric Types in PostgreSQL. This simple method helped me find out a bug exist since 1997. Thanks to ... Read More

Feed: Planet PostgreSQL. 1. Overview PostgreSQL is an open-source RDMS and running across many platforms including Linux (all recent distributions), Windows, FreeBSD, OpenBSD, NetBSD, Mac OS X, AIX, HP/UX, IRIX, Solaris, Tru64 Unix, and UnixWare. There are many discussions about how to build Postgres and extensions from source code on a Linux-like environment, but sometimes, a developer may want to quickly setup a Windows environment to check a feature for cross-platform support. This blog is going to explain how to build Postgres and extensions from source code on Windows platforms and it was tested on Windows 7, 10 and 2019 ... Read More

Feed: Planet PostgreSQL. 1. Overview “Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance.” This is the highlight of PostgreSQL in a sentence from Wikipedia. Yes, the extensibility of PostgreSQL is extremely useful when you have some special requirements. This blog will discuss how to use logical decoding output plugin to replicate multiple PostgreSQL servers to a single MongoDB server and the environment setup using docker compose for a quick Proof of Concept. 2. Logical decoding output plugin Start from version 9.5, PostgreSQL provides a solution to allow users to write their own ... Read More

Feed: Planet PostgreSQL. 1. Overview In previous two blogs, we explained how to setup Kerberos, and how to configure PostgreSQL to support GSSAPI user authentication. This blog will be focusing on how to check GSSAPI authentication, encryption and user principal information when given different connection options. 2. pg_stat_gssapi view According to the official PostgreSQL document, “PostgreSQL supports GSSAPI for use as either an encrypted, authenticated layer, or for authentication only.“ To check the authentication, encryption and user principal, we need to use pg_stat_gssapi view, which is a dynamic statistics views containing one row per backend and showing the information about GSSAPI authentication and ... Read More