Database Technologies

In the course of my work at EnterpriseDB, migrating Oracle databases to EnterpriseDB's Postgres Plus Advanced Server is a common task. However, now and then we encounter unique situations. While working on a migration project recently, we encountered a new use case for RLS (Row level Security). The customer had a centralized database where it stored a huge number of transactions. These transactions are performed by different business units located in different parts of the world. There are certain types of transactions that should not be visible even if they are being queried by the same company. That is where RLS comes in. With RSL, specific transactions, or kinds of transactions, that can remain visible are mapped back to an attribute in the table. The customer needed the application to authenticate users and set the context for which records in the database become visible for a specific session. In its deployment of Oracle, the customer had used the functions/procedure in t

PLV8 is a programming language that lets users write stored procedures and triggers in JavaScript and store them in their Postgres database. This allows application programmers to write a lot of their server-side programming in the same language they use to build their web client applications.  Fewer languages to learn usually means fewer mistakes and faster time to completion.  The extensive language support is one of many reasons why Postgres’ use across the world is increasing lately.  The recent addition of document data support with JSON and JSONB data types in PostgreSQL, and in Postgres Plus Advanced Server from EnterpriseDB, is the main reason for the increasing interest in the PL/V8 language extension. Below are the steps you need to compile PLV8 with Postgres Plus Advanced Server 9.3/9.4. To get started, here are the prerequisites: 1. A supported version of PostgreSQL or Postgres Plus Advanced Server, such as versions 9.1 and higher. 2. V8 version 3.14.5 3. g++ version

EnterpriseDB recently launched a new tool for backup and recovery – named simply EDB Backup and Recovery Tool, or  BART . This tool makes the DBA’s life easier by simplifying the tasks for managing their Postgres physical backup and recovery tasks, whether they are PostgreSQL or Postgres Plus Advanced Server deployments. BART has the following advantages over custom scripts for managing backups: 1.  It’s stable and it uses the tool pg_basebackup to take a physical backup. This tool has been well defined and is well-supported by the PostgreSQL community. 2.  It catalogs all of the backups users are taking, which is important in terms of:     i.  Listing the type of backups used    ii.  Listing the status of those backups with server information. 3.  BART also provides functionality to restore backups, with all required archived WAL files. So automation around this tool will make DBAs’ lives easier for restore and recovery. 4.  BART provides an option to validate your backup

PostgreSQL 9.3 has two key software updates making switchover/switchback easier in High Availability configurations. First, let’s address the software patches and their descriptions: 1. First patch was committed by Fujii Masao. Patch commit#  985bd7d49726c9f178558491d31a570d47340459 With this patch, the walsender process tries to send all outstanding WAL records to the standby in replication when the user shuts down the master. This means:  a. All WAL records are synced between two servers after the clean shutdown of the master  b. After promoting the standby to new master, the user can restart the stopped master as new standby without a fresh backup from new master. 2. Second patch was committed by Heikki Linnakangas in PostgreSQL 9.3. Patch commit# abfd192b1b5ba5216ac4b1f31dcd553106304b19 Before PostgreSQL version 9.3, streaming replication used to stop replicating if the timeline on the primary didn’t match the standby. This generally happens when the user promotes