Enterprise PostgreSQL Solutions

How to backup multiple tablespaces with pg_basebackup

I was doing some testing recently with parallel backup feature along with backup manifest feature which was one of the last feature that got committed to PostgreSQL 13. Parallel backup is not committed to PG yet, it is currently under discussion in the hackers community. I was trying to take parallel backup of a database…
Read more

Can Sequence Relation be Logically Replicated?

1. Introduction I have noticed that there is a page on the offical PostgreSQL documentation (https://www.postgresql.org/docs/current/logical-replication-restrictions.html) that states several restrictions to the current logical replication design. One of the restrictions is about sequence relation type where any changes associated with a sequence is not logically replicated to the subscriber or to the decoding plugin. This…
Read more

Stored Procedures also have rights

This is a follow-up of my recent blog tittled “Stored Procedures in PG 11 – Better late then never” posted on highgo.ca and also on planet postgres. It is available at https://www.highgo.ca/2020/04/10/stored-procedures-in-pg-11-better-late-then-never/. In this short blog titled “Stored Procedures also have rights”, I will be discussing the definer and invoker rights for stored procedures, the…
Read more

The Origin in PostgreSQL logical decoding

While studying the WAL structure, I came across a keyword named “origin” in the WAL log that I found intriguing. There is not much details on PG official documentation on this keyword in the WAL record and its functions. Hence I set upon the research in the code to find more about this secret data,…
Read more

Replicate multiple PostgreSQL servers to a single MongoDB server using logical decoding output plugin

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…
Read more

Stored Procedures in PG 11 – Better late then never

I was going to give a short talk on this subject in pgconf New York 2020, however unfortunately the conference like many other conference this year got cancelled due to COVID-19. So I decided to write a short blog about this schema object introduced in PG 11. As a Oracle database developer / DBA in…
Read more

PostgreSQL GSSAPI Authentication with Kerberos part-3: the status of authentication, encryption and user principal

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…
Read more

PostgreSQL_WAL_Evolution_Part_II

This is the second part of the topic, the more historical version is described in the previous part, and here is the vitality of wal in PostgreSQL which born replication、logical replication and more performance related configure, let’s continue to redo it. 1. Replication(V9.0) Replication is implemented here, and many corresponding GUC are added for replication.…
Read more

PostgreSQL_WAL_Evolution_Part_I

WAL is one of the most important parts of PostgreSQL., WAL records all the database activity. Hense we can regard wal as a change roadmap of the history of PostgreSQL database, and the crash recovery, logical replication etc aren’t possible without WAL. The following picture describes the various wal related GUC (based on PG12) involved…
Read more

PostgreSQL GSSAPI Authentication with Kerberos part-2: PostgreSQL Configuration

1. Overview In previous blog, we have setup Kerberos, added all required principals and verified each principal. This blog will explain all the necessary configuration, i.e. postgresql.conf, pg_hba.conf and pg_ident.conf, in PostgreSQL for user authentication using GSSAPI with Kerberos. 2. Build PostgreSQL with GSSAPI The official PostgreSQL release for Ubuntu has GSSAPI enabled for user authentication with Kerberos, however if you want…
Read more

Optimizing SQL – Step 1: EXPLAIN in PostgreSQL – Part 1

This is my first blog in a series of SQL optimization blogs. So expect some basic information in here along with some nice insights. My aim is to help you walk through a complete process of understanding and optimizing queries for improved performance. A PostgreSQL server attempts to find the most effective way of building…
Read more

Logical Replication Between PostgreSQL and MongoDB

1. Introduction PostgreSQL and MongoDB are two popular open source relational (SQL) and non-relational (NoSQL) databases available today. Both are maintained by groups of very experienced development teams globally and are widely used in many popular industries for adminitration and analytical purposes. MongoDB is a NoSQL Document-oriented Database which stores the data in form of…
Read more

Atomic commit and Atomic visibility for PostgreSQL. Explained!

There is a lot of interest and discussions lately in the PostgreSQL world to make it a scale-out solution. Among other possible solutions, one of the most promising ones is to implement the sharding using FDW and table partitioning for distributing the data on multiple servers. As of now, PostgreSQL can only fetch the data…
Read more

PostgreSQL GSSAPI Authentication with Kerberos part-1: how to setup Kerberos on Ubuntu

1. Overview PostgreSQL supports many secure ways to authenticate users, and one typical way is to use GSSAPI with Kerberos. However, when I was trying to investigate one issue which is related with GSSAPI in PostgreSQL, I couldn’t find a tutorial that I can follow to setup the environment easily. After some effort spent on…
Read more

Horizontal Scalability / Sharding in PostgreSQL Core – Missing pieces of the puzzle

Back in August 2019, I wrote multiple blogs with the title of “Horizontal scalability with Sharding in PostgreSQL – Where it is going Part 1 .. 3”. Little has happened since then, the purpose of this blog is discuss the important missing pieces of the puzzle, what are the minimum set of features needed to…
Read more

Parallel Vacuum in Upcoming PostgreSQL 13

Any performance feature in PostgreSQL that mentions the “parallel” keyword excites me. So it was natural that parallel vacuum took my complete attention when it was committed in January 2020.  For the sake of simplicity, we are not going to distinguish between index cleanup or index vacuum in this blog and simply use the term…
Read more

Setting up SSL certificate authentication with Pgpool-II

Version 4.0 of Pgpool-II added some very exciting security and authentication features to make it more relevant and useful for enterprise environments. It adds support for providing encrypted passwords in pool_passwd and configuration files and lets the administrators chose different authentication methods to be used for the Client<–>Pgpool-II and Pgpool-II<–>PostgreSQL authentications.The two new authentication methods added in…
Read more

Have An Eye On Locks Of PostgreSQL

The lock is an essential part of a database system. In PostgreSQL, there are various locks, such as table lock, row lock, page lock, transaction lock, advisory lock, etc. Some of these locks are automatically added to complete the database functions during the operation of the database system, and some are manually added for the…
Read more

Plan for 2020 – With continued focus on making community Postgres stronger

HighGo Software is committed to working and contributing to the PostgreSQL community, most of the features that are added to HighGo Postgres are also submitted for the community Postgres. The features will take longer to get into the community release due to the community process. We fully appreciate the tremendous value that we are getting…
Read more

HighGo Software (Canada) finished the year 2019 with releasing the first version of HighGo Postgres Database.

HighGo Software (Canada) recently released its first version of HighGo Postgres database server engine.  The GA version of HighGo Postgres i.e. HG-PGSQL 1.1 was released in Dec 2019 on the official website, the product can be downloadable via YUM from the HighGo Software (Canada) products page, please see the link below. HG-PGSQL 1.1 is built…
Read more