Database Technologies

Customers who moved/migrated their database from Oracle to PPAS frequently ask for Global Temporary Table in PPAS. Currently, PPAS doesn't support Global Temporary tables. However, there is a way user can achieve this functionality in PPAS. Before we continue with the implementation, lets first understand characteristics of Global Temporary Table. Following are the important characteristics of Global Temporary Table. 1. Global Temporary Table gives predefined structure for storing data. 2. It's an unlogged table which means any activity on this table will not be logged. 3. The data in a global temporary table are private, such that data inserted by a session can only be accessed by that session. Based on the above characteristics of Global Temporary Table AKA GTT, we can define similar kind of work by using the following method: 1. Create UNLOGGED TABLE in PPAS, which activity won't be logged. 2. Create Row Level Security in such a way that session should be...

Security has always been a great concern of Enterprises. Especially, if you have crucial information stored in the database, you would always prefer to have high security around it. Over the years, technologies have evolved and provided better solutions around it. If you have very sensitive information, people try to keep this information encrypted so, that in case, somebody gets access of the system, then they cannot view this information, if they are not authorized. For managing sensitive information, Enterprises use multiple methods: 1. Encrypting specific information. If you are PPAS users, you would like to use DBMS_CRYPTO package which provides a way of encrypting sensitive information in databases. For more information, please refer following link: http://www.enterprisedb.com/docs/en/9.4/oracompat/Database_Compatibility_for_Oracle_Developers_Guide.1.178.html# For PostgreSQL, users can use pgcrypto module. 2. Transparent Data Encryption (TDE) is anot...

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

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