Postgres Tutorials

Postgres Tutorials

Tips and tricks to use PostgreSQL with Go, Python, Java, Dockers, Kubernetes, Django, and other technologies.

Tushar Ahuja |
November 6, 2019

With PostgreSQL v12,  “recovery.conf” is no longer valid. Even if someone were to create a recovery.conf file manually and keep it under the data directory, the server is not going to start and will throw the following error

Ankit Shukla |
July 22, 2019

In this blog post we will go trough the steps to replace httpd self signed SSL certificates with trusted CA signed cerficates in Postgres Enterprise Manager

Richard Yen |
July 15, 2019

PostgreSQL is “The World’s Most Advanced Open Source Database,” and I believe it. In my 10+ years of working with it, it’s been solid, serving up data to SaaS loads of over 1000 queries per second, rarely going down, surviving all manner of accusations of corruption (which turned out to be...

Bruce Momjian |
July 1, 2019

One of the major attractions of Postgres is the ability to stop using database software controlled by a single vendor. Single-vendor control means a single entity controls the software, tools, training, and support. There are sometimes options from other vendors, but they are usually hampered...

Phil Allsopp |
May 8, 2019

PostGIS is a spatial extender for Postgres and adds more geometric types and spatial functionality to Postgres. You can use PostGIS to easily calculate distances between geometric objects, examples of which are points, lines, and polygons; you can easily calculate areas, whether one or more...

Haris Iftikhar |
March 27, 2019

Quickly (less than five minutes) install and run a default EPAS 11 cluster on RHEL 6 or RHEL 7

Jamie Watt |
March 12, 2019

These days, I have the good fortune of leading an amazing cluster of teams as the head of EnterpriseDB's Global Support Services. I talk to customers every day about who we are, what sets us apart, and how we can help to make you successful once you've dipped your feet in the Postgres world.

Vishal Sawale |
February 22, 2019

This video provides detailed information about using pgBouncer as a connection pooler for limiting the number of connections from the PEM Agent towards the Postgres Enterprise Manager™ (PEM) server on non-Windows machine. The video is comprised of three configuration steps

Richard Yen |
January 14, 2019

I’ve come across this scenario maybe twice in the past eight years of using Streaming Replication in Postgres: replication on a standby refuses to proceed because a WAL file has already been removed from the primary server, so it executes Plan B by attempting to fetch the relevant WAL file using...

Richard Yen |
January 9, 2019

Postgres is up and running, and things are humming along. Replication is working, vacuums are running, and there are no idle transactions in sight. You poke around your logs and make sure things are clean, but you notice a little warning

Ashutosh Sharma |
December 6, 2018

"Transition tables for Triggers" introduced in PostgreSQL V10 which allows us to create transition tables using the REFERENCING clause in the CREATE TRIGGER statement to store the old and new version of tuples.

Kuntal Ghosh |
December 6, 2018

When data are naturally aligned, CPU can perform read and write to memory efficiently. Hence, each data type in PostgreSQL has a specific alignment requirement. When multiple attributes are stored consecutively in a tuple, padding is inserted before an attribute so that it begins from the...

Beena Emerson |
November 26, 2018

Partitioning in Postgresql eases handling large volumes of data. This feature has greatly improved with the introduction of declarative partitioning in PostgreSQL 10, paving way for better query optimization and execution techniques on partitioned tables.

Bruce Momjian |
October 5, 2018

I have already covered switchover and failover. In the case of failover, the old primary is offline so there are no options for migrating clients from the old primary to the new primary. If there is a switchover, there are options for client migration. Assuming you are using streaming...

Bruce Momjian |
October 2, 2018

With the rsa keys created in my previous blog entry, we can now properly sign rows to provide integrity and non-repudiation, which we did not have before. To show this, let's create a modified version of the previous schema by renaming the last column to signature:

Rafia S |
July 31, 2018

In the previous blog, we saw that parallel index scans leads to significantly improves the performance of quite a few TPC-H queries. It is customary to analyze if its sister operator, namely index-only scan will benefit similarly when parallelized.

Sunil Narain |
July 17, 2018

SSL (Secured Sockets Layer) also known as TLS (Transport Layer Security) is a standard security technology for establishing encrypted connection...

Robert M. Haas |
July 12, 2018

I admit it: I invented force_parallel_mode. I believed then, and still believe now, that it is valuable for testing purposes. Certainly, testing using force_parallel_mode=on or force_parallel_mode=regress has uncovered many bugs in PostgreSQL's parallel query support that would otherwise have...

Dann Westermann |
May 24, 2018

When you followed the last post you should have two pgpool instances and one EDB Postgres instance running in OpenShift. pgpool is responsible for the load balancing, meaning: Send write requests to the master instance and spread read requests over all instances.

Dann Westermann |
May 24, 2018

In the last two posts, we deployed an EDB database container and two pgpool instances and then scaled that up to include a read only replica. In this post will use a ConfigMap to adjust parameters in postgresql.conf as you will probably need to do that when you start using the EDB containers in...

Ahsan Hadi |
May 23, 2018

he Pgpool II Community releases a major version every year and minor versions several times in a year. In the last few releases, a lot of emphasis and focus was given to improving the existing functionality of pgpool II, making it more stable, robust and able to handle complex real-world...

Ahsan Hadi |
April 30, 2018

One question that comes up very often is which version of Pgpool II is supported with which database server? I would like to address this question in this post.

Jan Karremans |
January 30, 2018

This is the final episode of this short series of blog posts on some of my drivers for moving to Postgres from Oracle. Please do read Part I and Part II of the series if you have not done so. It discussed the topics “History”, “More recently”, “The switch to Postgres”, “Realization”, “Pricing...

Jan Karremans |
January 12, 2018

Continuing this short series of blog posts on some of my drivers for moving to Postgres from Oracle. Please read Part I of the series if you have not done so. It discussed the topics “History”, “More recently” and “The switch to Postgres”.

Jan Karremans |
January 12, 2018

I have started with databases quite early on in my career. RMS by Datapoint… was it really a database? Well, at least sort of. It held data in a central storage, but it was a typical serial “database”. Interestingly enough, some of this stuff is maintained up to today. (Talk about longevity!)...

Robert M. Haas |
January 5, 2018

In a recent blog post, I talked about why every system that implements MVCC needs some scheme for removing old row versions, and how VACUUM meets that need for PostgreSQL. In this post, I’d like to examine the history of VACUUM improvements in recent years, the state of VACUUM as it exists in...

Robert M. Haas |
December 18, 2017

Experienced PostgreSQL users and developers rattle off the terms “MVCC” and “VACUUM” as if everyone should know what they are and how they work, but in fact many people don’t.

Bruce Momjian |
December 5, 2017

Someone at a conference mentioned they were using the invisible xmin column to perform updates. I was initially skeptical of this approach, but once he explained the purpose, it made sense,

...

Ranjeet Dhumal |
November 15, 2017

Knowing how to debug is a critical aspect of every application development life cycle. Debugging database bianaries allow you to not only recognize that an exception has occurred, but also traverses through the database binaries execution until the culprit code can be located.

Richard Yen |
October 27, 2017

I recently had a few customers ask me about using a query for monitoring SMR XDB lag, so they can keep tabs on replication progress and set up notifications.

Ameen Abbas |
September 17, 2017

I covered vacuum basics in my earlier post. In this post, I'm covering the importance of aggressive autovacuum. Database Administrators who just started their career might have doubts in their mind

Richard Yen |
August 24, 2017

I recently had a customer who was trying to figure out why his VACUUM-for-wraparound was taking so long–he had a 6TB database, and the VACUUM had been running for over 4 days.

Adam Wright |
August 23, 2017

If your organization uses EDB Postgres in Amazon Web Services and you want to leverage advanced EDB Backup and Recovery Tool (BART) features such as incremental backup, one way to accomplish this is with Amazon Elastic File System (EFS)

Richard Yen |
August 18, 2017

PgBouncer is a great tool for improving database performance with connection pooling. I've been using it for many years, since it first became available in 2007. Since then, several improvements have been implemented, including the ability to use auth_type=hba, which implements a PG-like HBA...

Richard Yen |
August 18, 2017

nterpriseDB Failover Manager (EFM) is a great tool to automate failover/switchover if you use Postgres' streaming replication feature. Not only do you get High Availability (HA), you can do so with just a few simple commands to make it all happen very quickly.

Dave Page |
July 25, 2017

For those that aren't aware, pgAdmin 4 is the fourth complete rewrite of pgAdmin since the first code was written way back in 1998(!). One of the major aims of the technology change we made for the new version was to help attract new developers as we always found them extremely hard to find when...

Vibhor Kumar |
July 6, 2017

In the past, I posted a blog on the concept of creating a Global Temporary Table (GTT) for migration from Oracle to EDB Postgres. In that blog I had shared the following characteristics a Global Temporary Table

Timothy Steward |
June 4, 2017

Quickly (less than five minutes) install and run EPAS 9.5/9.6 on RHEL 5/6 and demonstrate using SQL Protect with that Postgres cluster.

Timothy Steward |
June 4, 2017

This guide is intended to get you up and started with a default installation of pgPool in front of a master and streaming replica. There is much more to pgPool that is not covered in this quickstart, the intent here is only to get you a simple configuration working as quickly as possible.