PostgreSQL Tutorials

How to solve common and tricky PostgreSQL issues covering Go, Python, Java, Dockers, Kubernetes, Django, and other technologies

Recent Tutorials

PostgreSQL Query: Introduction, Explanation, and 50 Examples

September 30, 2020

In this article we are going to look at what an SQL query is and how to use it with PostgreSQL databases in different situations. We provide 50 examples of types of SQL, queries along with descriptions of their functions and how to use them in PostgreSQL. What is SQL? SQL is short for Structured...

How to convert Oracle to PostgreSQL Online

Kashif ZeeshanSeptember 28, 2020

This article discusses tools for migrating schema online from Oracle to PostgreSQL and EDB Postgres Advanced Server. EDB Postgres Advanced Server and PostgreSQL Challenges of converting database schemas online Why migrate to PostgreSQL from Oracle? Why migrate to EDB Postgres Advanced Server from Oracle? Online tools to convert Oracle databases How to convert database schemas using Ora2Pg How to convert schemas using Migration Portal and EDB Postgres Advanced Server How to migrate from Oracle to PostgreSQL using ora_migrator

How to deploy WordPress with highly available PostgreSQL

Mohsin EjazSeptember 22, 2020

This article provides instructions for installing PostgreSQL as the backend database for WordPress instead of the default MySQL. Introduction What is PG4WP? How to install WordPress with PostgreSQL on Centos7

How to Connect PostgreSQL using SQL Developer, Visual Studio and DBeaver

Phil HankinsonSeptember 15, 2020

So, you’ve installed your Postgres database, whether Community Edition, EDB Postgres Advanced Server or one of many other cloud, container, or Database as a Service offerings out there. Now what next? Well, the first thing you are going to do (once you have secured your new database) is access it...

How to implement Column and Row level security in PostgreSQL

Rajkumar RaghuwanshiSeptember 10, 2020

This article discusses how to add column-level and row-level security as components of table-level security to restrict users from accessing certain data. Column-level security Row-level security How to combine row-level security with column grants Application users vs. row-level security Row-level...

How to Auto Generate Hash Partitions In EDB Postgres Advanced Server

Beena EmersonSeptember 8, 2020

This article covers the new feature to auto create hash partitions in EDB Postgres Advanced Server 13. Partitions number Subpartitions number Store in command Add partition behaviour Modifying subpartition template Creating a large number of partitions can be tedious as we have to specify all the...

How to add PostgreSQL driver as a dependency in Maven

Mohsin EjazAugust 26, 2020

This article provides instructions for adding the PostgreSQL JDBC driver to a Java project using Maven. Introduction Why do we need PostgreSQL drivers? What is Maven? PostgreSQL JDBC driver and Maven repositories What does a POM file do? Step-by-step instructions for adding the PostgreSQL JDBC...

How to secure Job scheduling with DBMS_SCHEDULER in EDB Postgres Advanced Server

Raghavendra RaoAugust 24, 2020

EDB Postgres Advanced Server (EPAS), a component of the EDB Postgres Platform, provides native database compatibility with Oracle. EPAS database compatibility provides similar Oracle-like features so developers and operations staff can continue to leverage many existing PL/SQL development and...

How to handle duplicates and unique index conflicts when importing data into EDB Postgres Advanced Server with EDB Loader

Jeevan LadheAugust 13, 2020

Like most of the database vendors provide a way to bulk copy from files, EnterpriseDB has a nice tool at hand called EDB*Loader for EDB Postgres Advanced Server. It is a high-performance bulk data loader that provides an interface compatible with sql loader for EDB Postgres Advanced Server. You are...

How to setup EDB Postgres Backup and Recovery Tool to take PostgreSQL backup on Amazon AWS S3

Sunil NarainAugust 12, 2020

Abstract This article provides step-by-step instructions for using the EDB Postgres Backup and Recovery Tool (BART) to save PostgreSQL backups to Amazon AWS S3. Step 1: Install BART on master and standby servers Step 2: Install and configure S3FS-FUSE on master and standby servers Step 3: Configure...

pg_catcheck: How to diagnose system catalog corruption due to missing or inaccessible relation file in PostgreSQL

Rushabh LathiaAugust 7, 2020

This article introduces a new functionality for the EnterpriseDB tool pg_catcheck that can help detect missing or inaccessible relation files that cause “could not open file” errors. pg_catcheck New pg_catcheck relation functionality When your database throws an error like "could not open file base...

Creating a dblink from Oracle to PostgreSQL

Amit SharmaJune 29, 2020

This article shows how to create a dblink connection from Oracle to EDB Postgres Advanced Server or PostgreSQL, Installation Connection Testing connectivity There are several reasons where we might need to use both Oracle and PostgreSQL databases in a business environment. One common scenario is to...

How to Deploy Ansible Scripts for the EDB Postgres Platform

Kanchan MohiteyJune 23, 2020

Then, Ansible scripts were used for configuration setup of the Postgres database and tools like EDB Failover Manager (EFM) for high availability, Postgres Enterprise Manager (PEM) for monitoring the Postgres cluster, and EDB Backup and Recovery Tool (BART) for maintaining the backups.

Porting between Oracle and PostgreSQL

Arun GavhaneJune 22, 2020

This article discusses the possible complications that can arise when migrating databases from Oracle to PostgreSQL. The steps for migration are covered and issues and their fixes are identified: Approach to migration Schema migration Code migration Data migration When migrating database objects...

How to Monitor PostgreSQL Connections

Amit SharmaJune 20, 2020

This article discusses connections to PostgreSQL database servers. It first reviews the possible states for a connection and then shows how to identify and terminate connections that are lying idle and consuming resources. States of a connection Identifying the connection states and duration...

Trying on Many Hats: How to improve OLAP workload performance for PostgreSQL

Richard YenJune 14, 2020

This article offers tips for improving Online Analytical Processing (OLAP) performance for PostgreSQL. Sizing up the Situation Schema Planning Database Tuning Hardware Selection As the world’s most advanced open-source database, PostgreSQL is very versatile. With applications ranging from embedded...

How to deploy PostgreSQL along with Monitoring and high availability tools on VMware vSphere using Terraform

Kanchan MohiteyMay 14, 2020

This article outlines the steps for creating and configuring EDB Postgres on VMware vSphere using Terraform with deployment scripts available on GitHub.

How to Tune PostgreSQL GUC Parameters

Tushar AhujaMay 5, 2020

This article discusses GUC parameters that can be used for PostgreSQL tuning and how to configure them for improved performance.

Interval Partitioning in EDB Postgres Advanced Server: Auto-create a new partition when inserted data exceeds the range

Amul SulMay 5, 2020

EDB Postgres Advanced Server v12 (EPAS) introduces the Interval Partitioning feature. Interval partitioning allows a database to automatically create a new partition when newly inserted data exceeds the range of an existing partition.

How to replace Postgres Enterprise Manager self-signed certificates with certificates provided by Certificate Authority (CA) 

Shubham AgarwalMay 5, 2020

This article provides instructions for how to replace self-signed certificates for Postgres Enterprise Manager with authorized certificates issued by Certificate Authority.

PostgreSQL Replication and Automatic Failover Tutorial

Abbas ButtApril 30, 2020

PostgreSQL Replication is when data is copied from one database server to another. The source database server is usually called the Master server, whereas the database server receiving the copied data is called the Replica server.

Redefining triggers: Compound Triggers in EDB Postgres Advanced Server

Jeevan ChalkeApril 29, 2020

Are you bored with writing multiple triggers? Do you want the ability to share details between the various triggers’ timing points for the same table? Let's use the compound triggers feature added in EDB Postgres Advanced Server 12 (EPAS 12).

Extending PostgreSQL Monitoring in Postgres Enterprise Manager by Creating Custom Probes

Nidhi BhammarApril 24, 2020

This article defines a probe and provides instructions for creating custom probes for PostgreSQL using EDB Postgres Enterprise Manager (PEM).

What is Parallel Vacuum in PostgreSQL 13

Dilip KumarApril 17, 2020

In PostgreSQL, we already support parallelism of a SQL query which leverages multiple cores to execute the query faster. Vacuum is one of the most critical utility operations which helps in controlling bloat, one of the major problems for PostgreSQL DBAs. So, vacuum needs to run really fast to reduce the bloat as early as possible. In PG13, parallel vacuum is introduced which allows for parallel vacuuming of multiple indexes corresponding to a single table.

Why Django is so impressive for developing with PostgreSQL and Python 

Navnath GadakhApril 15, 2020

This post gives you a brief idea about why you should use PostgreSQL as a backend database with Django. It also describes how to use it and the benefits of using PostgreSQL with Django.

What is Multi-column Partitioning in PostgreSQL and How Pruning Occurs

Beena EmersonApril 8, 2020

This article covers how to create a multi-column partitioned table and how pruning occurs in such cases.

PostgreSQL query tuning checklist

Tushar AhujaApril 6, 2020

This article offers a checklist of tools available for improving PostgreSQL query performance.

How to tune PostgreSQL for memory

Tushar AhujaApril 6, 2020

This article looks at parameters that can be used to help manage memory in PostgreSQL. Recommended settings for each parameter are also provided.

How to use table partitioning to scale PostgreSQL

Rajkumar RaghuwanshiMarch 25, 2020

This article discusses table partitions, the benefits of using them to increase performance, and the types of partitions that can be used in PostgreSQL.

How to make queries faster with multicolumn indexes

Ranjeet DhumalMarch 25, 2020

This article introduces multicolumn indexes and explains how they can by used to make large queries run faster.

How to implement faceted search with Django and PostgreSQL

Navnath GadakhMarch 24, 2020

This article discusses the advantages of faceted search and how to create a faceted search using Django and PostgreSQL.

How does PostgreSQL master-replica failover work?

Amit SharmaMarch 24, 2020

This article looks at the processes involved in master-replica failover in PostgreSQL and the options and commands that are available for configuring it.

How to load a sample database with schemas and data in PostgreSQL

Arun GavhaneMarch 23, 2020

This article discusses how to make a backup of a database using pg_dump in PostgreSQL and provides examples of how to make and restore the backups in different formats.

How to implement repmgr for PostgreSQL automatic failover

Ranjeet DhumalMarch 23, 2020

This article discusses the open source tool repmgr (Replication Manager) and how to set up and configure it for automatic failover in PostgreSQL.

How to Migrate Oracle's UNPIVOT to PostgreSQL

Thom BrownMarch 23, 2020

This article looks at the UNPIVOT function in Oracle and how to reproduce its result using PostgreSQL.

How to test unlogged tables for performance in PostgreSQL

Tushar AhujaMarch 19, 2020

This article looks at unlogged tables in PostgreSQL and reviews performance differences between unlogged tables and ordinary and temporary tables, as well as crash recovery.

How to deploy PostgreSQL and EDB Postgres Platform on AWS using Terraform automation scripts 

Kanchan MohiteyMarch 19, 2020

With these deployment scripts, you can set up the PostgreSQL database of your choice (PG/EDB Postgres), and, in addition, the scripts will deploy and configure the tools that would take care of high availability and automatic failover and also set up monitoring for the PostgreSQL cluster. We will use Terraform automation scripts to achieve this and deploy the cluster on AWS.

How to convert Oracle Blobs to PostgreSQL

Tushar AhujaMarch 18, 2020

This article discusses binary large objects (Blobs) in Oracle and how to convert them into formats that PostgreSQL is able to use.

How to easily convert Oracle procedures to PostgreSQL

Tushar AhujaMarch 5, 2020

This article gives five examples of converting procedures from Oracle’s PL/SQL procedural language into PostgreSQL’s PL/pgSQL language.

How to import data from Oracle into PostgreSQL

Tushar AhujaMarch 5, 2020

This article provides three methods for importing data from an Oracle database into PostgreSQL.

Strategy for migrating partitioned Oracle database tables to PostgreSQL

Rajkumar RaghuwanshiMarch 5, 2020

This article highlights differences in table partitioning between Oracle and PostgreSQL and provides key points to consider before migration partitioned data between them.

How to configure heterogeneous connections from Oracle to PostgreSQL

Tushar AhujaMarch 3, 2020

This article discusses heterogeneous connections and how to set up a connection from Oracle to PostgreSQL using one.

Partitioning commands in PostgreSQL compatible with Oracle databases

Shivam DhapatkarMarch 3, 2020

This article provides information about the types and benefits of table partitioning and using the table partitioning syntax compatible with Oracle databases supported by EDB’s Advanced Server (EDB’s Postgres database).

Accessing PostgreSQL databases using psqlODBC in Oracle

Raghavendra RaoFebruary 21, 2020

This article demonstrates how to use the PostgreSQL client interface psqlODBC driver in Oracle to access PostgreSQL data.

How to quickly build an API using Node.js & PostgreSQL

EDB TeamFebruary 20, 2020

This article describes how you can use Node.js and PostgreSQL to create an API and provides an example for how to create a table.

Connecting to PostgreSQL using PHP

Abhishek DebFebruary 19, 2020

This article reviews the steps necessary for connecting to a PostgreSQL database using PHP.

How to use clauses when querying data (FROM, WHERE, GROUP BY, HAVING, BETWEEN, LIKE, FETCH)

Arun GavhaneFebruary 19, 2020

This article discusses the use of clauses in PostgreSQL queries to filter and organize data in results. It introduces different clauses and provides examples of their usage.

How to call PostgreSQL stored procedures from a PHP application

Abbas ButtFebruary 19, 2020

This tutorial provides instructions and an example for calling a PostgreSQL stored procedure from a PHP application.

How to work with Blobs when using PostgreSQL and PHP

Abbas ButtFebruary 19, 2020

This tutorial provides instructions and an example for converting large files like images into binary large objects (blobs) that can be stored in a PostgreSQL database using PHP.

pgAdmin, a comparable tool to PL/SQL Developer for PostgreSQL

Shivam DhapatkarFebruary 19, 2020

This article explains how to get started using pgAdmin 4 as a management tool for PostgreSQL.