Database Technologies

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

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

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

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 9.3  has lot of new improvement like the addition of new operators for JSON data type in postgreSQL, that prompted me to explore its features for NoSQL capabilities. MongoDB is one of NoSQL solutions that have gotten a great deal of attention in the NoSQL market. So, this time I thought to do some benchmarking with the NoSQL capability of JSON in MongoDB and the JSON datatype in PostgreSQL 9.3 For this benchmark, I have used the same machine with no optimization in installation of PostgreSQL and MongoDB (since I wanted to see how things work, out of box with default installation). And I used the sample data from MongoDB's site, around which I had developed the functions which can generate random data using the same sample for Mongo and for PostgreSQL. In this benchmarking, I have verified following: 1. PostgreSQL COPY Vs Mongo-Import 2. Data Disk Size of PostgreSQL and Mongo for same amount of data. 3. PostgreSQL INSERT Vs Mongo Insert Some specification befor...