PostgreSQL Tutorials
How to solve common and tricky PostgreSQL issues covering Go, Python, Java, Dockers, Kubernetes, Django, and other technologies
How to solve common and tricky PostgreSQL issues covering Go, Python, Java, Dockers, Kubernetes, Django, and other technologies
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...
September 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
September 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
September 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...
September 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...
September 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...
August 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...
August 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...
August 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...
August 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...
August 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...
June 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...
June 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.
June 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...
June 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...
June 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...
May 14, 2020
This article outlines the steps for creating and configuring EDB Postgres on VMware vSphere using Terraform with deployment scripts available on GitHub.
May 5, 2020
This article discusses GUC parameters that can be used for PostgreSQL tuning and how to configure them for improved performance.
May 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.
May 5, 2020
This article provides instructions for how to replace self-signed certificates for Postgres Enterprise Manager with authorized certificates issued by Certificate Authority.
April 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.
April 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).
April 24, 2020
This article defines a probe and provides instructions for creating custom probes for PostgreSQL using EDB Postgres Enterprise Manager (PEM).
April 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.
April 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.
April 8, 2020
This article covers how to create a multi-column partitioned table and how pruning occurs in such cases.
April 6, 2020
This article offers a checklist of tools available for improving PostgreSQL query performance.
April 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.
March 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.
March 25, 2020
This article introduces multicolumn indexes and explains how they can by used to make large queries run faster.
March 24, 2020
This article discusses the advantages of faceted search and how to create a faceted search using Django and PostgreSQL.
March 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.
March 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.
March 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.
March 23, 2020
This article looks at the UNPIVOT function in Oracle and how to reproduce its result using PostgreSQL.
March 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.
March 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.
March 18, 2020
This article discusses binary large objects (Blobs) in Oracle and how to convert them into formats that PostgreSQL is able to use.
March 5, 2020
This article gives five examples of converting procedures from Oracle’s PL/SQL procedural language into PostgreSQL’s PL/pgSQL language.
March 5, 2020
This article provides three methods for importing data from an Oracle database into PostgreSQL.
March 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.
March 3, 2020
This article discusses heterogeneous connections and how to set up a connection from Oracle to PostgreSQL using one.
March 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).
February 21, 2020
This article demonstrates how to use the PostgreSQL client interface psqlODBC driver in Oracle to access PostgreSQL data.
February 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.
February 19, 2020
This article reviews the steps necessary for connecting to a PostgreSQL database using PHP.
February 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.
February 19, 2020
This tutorial provides instructions and an example for calling a PostgreSQL stored procedure from a PHP application.
February 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.
February 19, 2020
This article explains how to get started using pgAdmin 4 as a management tool for PostgreSQL.