Database Technologies

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 ...

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....

The people using PostgreSQL and the Streaming Replication feature seem to ask many of the same questions: 1. How best to monitor Streaming Replication? 2. What is the best way to do that? 3. Are there alternatives, when monitoring on Standby, to using the pg_stat_replication view on Master? 4. How should I calculate replication lag-time, in seconds, minutes, etc.? In light of these commonly asked questions, I thought a blog would help. The following are some methods I’ve found to be useful. Monitoring is critical for large infrastructure deployments where you have Streaming Replication for: 1. Disaster recovery 2. Streaming Replication is for High Availability 3. Load balancing, when using Streaming Replication with Hot Standby PostgreSQL has some building blocks for replication monitoring, and the following are some important functions and views which can be use for monitoring the replication: 1. pg_stat_replication view on master/primary server.    This view ...

PostgreSQL has some useful functions which can be use to know about the privilege of a user on a particular Database object. Those functions is available in following link: http://www.postgresql.org/docs/9.1/static/functions-info.html Functions has_*_privilege in PostgreSQL/PPAS is good to know about privilege a user has on one database objects and these function returns boolean value true or false. Since, DBAs/Users are interested in listing objects and privileges of a Database User and currently PostgreSQL doesn't have a view, which DBA can use to list users privileges on objects for a particular database. Therefore, I thought about making some functions, which can be used to list users privileges, based on what is available in PostgreSQL/PPAS 9.1. These are basic functions and can be expanded, as per need, to show more privileges like WITH GRANT OPTION. Following are functions which can use to get the privileges of a particular user: 1. Function for table privileges: C...