Home > Blogs > VMware vFabric Blog > Category Archives: Postgres

Category Archives: Postgres

How-To: Build a Geographic Database with PostGIS and vPostgres

Posted on by
2

Mobile Location Based Services are on the rise. After several false starts back in the mid 2000s, every mobile user now depends on their phones to tell them where they are, where their friends are, and to engage with social media like Facebook and Foursquare.  A report by Juniper Research suggests this market is expected to breach over $12 billion next year, where it hardly existed a few years ago at all.

This is in part because mobile apps have become ubiquitous now. In order to remain relevant, businesses need to interact socially and have a web store to remain accessible to their wandering customers.

Building a geographically aware application from scratch sounds daunting and like a lot of initial data setup. It doesn’t have to be. Products like vFabric Postgres (vPostgres) can be used along with the PostGIS extensions to perform geographic-style queries. Then,  public data and an open source visualizer can be used to transform the query into a meaningful result for your application or end user.

Continue reading →

PostgreSQL Security Release Now Available

Posted on by
Reply

IMPORTANT SECURITY UPDATE NOW AVAILABLE

Download VMware vFabric Postgres Security Update
Download immediately

The PostgreSQL Project has released an important security update for all supported versions including v9.2.4, v9.1.9, v9.0.13, and v8.4.17. Likewise, VMware has also released updated versions of the vFabric Postgres distribution.

This release includes a fix for a high-exposure security vulnerability (CVE-2013-1899) that is considered so important, the PostgreSQL project pre-announced this release last week and closed development to protect the vulnerability from being exploited before the threat had an update readily available to protect users against it.

All users are strongly urged to apply the update as soon as it is available. Continue reading →

How to Perform Security Updates on vFabric Postgres

Posted on by
Reply

The PostgreSQL community announced last week that an important security update will be released on April 4, 2013. This release will include a fix for a high-exposure security vulnerability and all users are strongly urged to apply the update as soon as it is available. Knowing how disruptive urgent security updates can be to IT and developers, the PostgreSQL community issued advanced warning in the hopes that it would ease the impact to day-to-day operations while helping as many companies as possible to adopt the update quickly.

As such, we would like to take the time to remind us all how important these security updates are to your business, and how to apply them most efficiently for vFabric Postgres.

The Cost of Missing Security Updates

Maintenance and security software updates are essential in extending application longevity as well as in keeping the confidence of customers who use services based on the application.

When big data disasters hit, the impacts quickly move beyond financial and affect reputation and trust. Databases are a particular area of concern. A recent article titled, “Making Database Security Your No. 1 2013 Resolution,” cited a Verizon study that showed only 10 percent of total security spend goes into database protection, while 92 percent of stolen data comes out of databases.

According to the seventh annual U.S. Cost of a Data Breach report from Ponemon Institute, the cost of an average data breach was $5.5M in 2011 or $194 per record. While $5.5M may not sound like a lot to some companies, losing one million records at a cost of $194 per record adds up. Continue reading →

Tips and Tricks for Internal Use of Your vFabric Postgres VM

Posted on by
Reply

Ever since VMware released our new version of the popular PostgreSQL, we have been working hard to publish a series of informative articles on what’s new in the vFabric Postgres (vPostgres) 9.2 release. There are a number of cool things like major performance and scale improvements, elastic memory for vPostgres, contributions to open source, master-slave clusters, and new GUI capabilities. In this post, we are going to dig into some tips and tricks for internal use of a vFabric Postgres virtual machine, talk about scripts, and explain the management of the network interface.

First, to see what we are talking about, it would be helpful to login to your vFabric Postgres VM. vPostgres supports SSL by default (see last weeks post on securing your vPostgres deployment for more security tips). Therefore, it is pretty easy to connect to a server and set things up inside for those who really want to personalize things at a very low level (like pg_bha.conf for connection restrictions). After the first initialization, you can connect either as user postgres or root with the same password you used at first boot.

As with every other system, it is never really recommended to connect with the user root for security reasons. By connecting with user postgres you will find the following things once connected. Continue reading →

Securing your vFabric PostgreSQL VM

Posted on by
Reply

Especially in today’s world, security is top of mind for app developers, DBAs, and CIOs alike. One of the benefits that VMware strives to include in every product is a system of reasonable defaults for security. This generally means that users should expect a reasonably secure middleware application when they deploy a VMware app by default.

vFabric Postgres (vPostgres) is no different. There are not that many security settings in vFabric Postgres. However, there are a few things you can look at as options to further harden your deployment, and of course, the virtual machine that you are deploying them on, particularly if it is exposed to an external environment.

SSL Connection Restrictions

vFabric Postgres has as default users postgres and root, and both can connect to the virtual machine with SSL. If you want to restrict access to the virtual machine for certain users or a group of users, here is some advice to follow:

1. In order to restrict SSL connection only to the members of the group vFabric (the user postgres is a member of this group by default), add this line at in /etc/ssh/sshd_config.

AllowGroups vfabric

Continue reading →

Exploring the New Database Server GUI Features in vFabric Postgres 9.2

Posted on by
Reply

The vFabric Postgres 9.2 release seriously upped the user interface (UI) experience. In our post last week, we talked about the built in, VM-based GUI to help manage the system, network, and updates. This week, we’d like to take you through the changes your DBAs and developers will see when using the updated database server—listing the databases on the server, seeing database global data, and drilling into processes and locks. All of this comes out of the box with the vFabric Postgres appliance.

Connecting to the Database Management Interface

Once your vFabric Postgres server is up and running, you can connect to its database management interface using this URL in a web browser. The connection is made with https, port 8443 at the IP address or domain for the server: Continue reading →

How to Use the New GUI Features in vFabric Postgres 9.2

Posted on by
3

People have said code is poetry and information is beautiful, but let’s talk about graphical user interfaces (GUI) and ease of use.

The PostgreSQL community has a multitude of choices when it comes to GUIs versus CLI. For vFabric Postgres, we’ve added some cool, new GUI tools.

In part one of this two-part series on vFabric Postgres user interfaces, we look at the built-in GUI capabilities within the VM of the latest release—vFabric Postgres 9.2. Based on VMware Studio, vFabric Postgres 9.2 uses an instinctive user interface to fully control the behavior of the database virtual machine and allows you to perform basic tasks that are normally associated with an OS. In part 2, we will cover the key features of the database server’s new GUI and explain how to get overview-level information and see both processes and locks. Continue reading →

Scaling for the Information Explosion: Master-Slave Cluster with vFabric Postgres 9.2 on vSphere

Posted on by
1

We are living in the age of the Information Explosion. Data growth is accelerating at 50% year over year. In fact, more data will be generated in the next 4 years than in the history of the world. And people are addicted to information, staying connected for up to 12 hours a day and seeing more than 34 billion bits of information every day—the equivalent of reading two books.

Earlier this year, VMware CTO Richard McDougall stated in his 2013 predictions on big data that this year will be the year that ‘delete’ becomes forbidden. As a result, IT is challenged to scale a volume of data that is a moving target, with the only promise that it will continue to grow exponentially.

While vFabric Postgres 9.2 is already known to have a significant performance gains, the ability to create master-slave clusters adds more power to the architecture mix. Master-slave clusters provide extra assurances for data backups and failover, but also they can be used to distribute the workload for queries and to help applications scale.

Below, we will explain how set up and test a master-slave cluster in Postgres 9.2.

Continue reading →

Behind the Scenes: Patching PostgreSQL for Performance–vFabric Team Contributes to Open Source

Posted on by
2

After PostgreSQL 9.2 was released, users that relied on PostgreSQL for scale, may have noticed a performance hit. In fact, the PostgreSQL community alongside the VMware vFabric Postgres team, was able to prove that the new version demonstrated a 10% performance hit over version 9.1. As part of the VMware Postgres team, we wanted to fix this problem for our own distribution, but as mentioned in previous posts, we also wanted to contribute our fixes back to the common core.  This post provides additional detail on how this problem was identified and how we worked with the open source PostgreSQL community to restore performance.

Background on the Performance Issue in PostgreSQL 9.2

Last year, during routine regression testing of vFabric Postgres, we found that PostgreSQL 9.2, the latest major release of PostgreSQL, demonstrated a significant performance regression from version 9.1. Using DBT-2, an open-source and fair-use implementation of TPC-C benchmark [1], we noticed a 10% performance degradation, which we then reported to the community [2].

To troubleshoot the problem we used git bisect to find the type of commit that caused the performance problem and cross-examined the statistical profiles using oprofile. As it turns out, the regression was caused by a commit that changed the way memory was allocated when SPI queries were executed. The commit was intended to reduce the number of allocations for queries using a cached plan at the cost of more logistics work. However, according to the DBT-2 test, we could see that this tradeoff was unfavorable for dynamic queries. So to fix it, we would need reintroduce the original tradeoff on its intended queries using conditions [3].

We proposed the fix to the wider PostgreSQL community and the ensuing discussion led to a refined resolution which was implemented in a patch [4]. This patch has been back-ported to the latest PostgreSQL 9.2.3 release and is included in the latest vFabric Postgres release [5]. Continue reading →

Introducing A New Reference Architecture That Will Speed Knowledge & Development of Modern Cloud Applications

Posted on by
4

Technology is evolving at breakneck speeds.

Universally, applications are faster, deal with large data sets, and provide more compelling user experiences than ever before.

Competition is steep.

As a result, competitive organizations demand that IT leaders speed the rate of new application innovation and development.  IT must rise to the challenge or face competitive threats, missed business opportunities, and lose momentum within their user base. In short, IT leaders and providers that do not accelerate will face a backlash from executives.

In order to meet these challenges, IT is renovating application architectures to thrive in the cloud. This is an organization-wide change involving people redirection, process redesign, and technology exploitation. For many, there is a steep learning curve. Continue reading →