VMware vFabric Postgres - 9.1 User Guide
vFabric Postgres Standard Edition User
Guide
vFabric Postgres 9.1
This document supports the version of each product listed and
supports all subsequent versions until the document is replaced
by a new edition. To check for more recent editions of this
document, see http://www.vmware.com/support/pubs.
EN-000872-00
vFabric Postgres Standard Edition User Guide
You can find the most up-to-date technical documentation on the VMware Web site at:
http://www.vmware.com/support/
The VMware Web site also provides the latest product updates.
If you have comments about this documentation, submit your feedback to:
docfeedback@vmware.com
Copyright © 2012 VMware, Inc. All rights reserved. This product is protected by U.S. and international copyright and intellectual
property laws. VMware products are covered by one or more patents listed at http://www.vmware.com/go/patents.
VMware is a registered trademark or trademark of VMware, Inc. in the United States and/or other jurisdictions. All other marks
and names mentioned herein may be trademarks of their respective companies.
VMware, Inc.
3401 Hillview Ave.
Palo Alto, CA 94304
www.vmware.com
2 VMware, Inc.
Contents
Preface 5
VMware Customizations for Postgres 7
1
Differences Between vFabric Postgres and PostgreSQL Databases 7
Installation Overview 9
2
Installing vPostgres as a Virtual Appliance 10
Installing vPostgres Using RPM Files 13
vPostgres Client Tools and Libraries 15
3
Client Tool Packages and Drivers 16
Install the Client Tools Package 17
Installing vPostgres Client Tools 18
Add an x86 vPostgres ODBC Data Source on Windows 18
Using vPostgres libpq with Your Application 18
Restarting the Service 21
4
Adding a License Key 23
5
Connection to a vPostgres Database with JDBC or psql 25
6
Security and Services 27
7
Troubleshooting 29
8
Index 31
VMware, Inc. 3
vFabric Postgres Standard Edition User Guide
4 VMware, Inc.
Preface
The vFabric Postgres Standard Edition User Guide provides information about installing and using VMware
vFabric Postgres Standard Edition database. You can use the database alone, or as part of the VMware vFabric
Data Director Suite.
The vFabric Postgres Standard Edition database may also be referred to in this manual by the official nickname
of 'vPostgres'.
Revision History
The vFabric Postgres Standard Edition User Guide is revised with each release of the product or when necessary.
A revised version can contain minor or major changes.
Table 1. Revision History
Revision Date Description
22 May 2012 Version 9.1
Intended Audience
This information is intended for anyone who wants to install or use a vFabric Postgres Standard Edition
database. The information is written for experienced Windows or Linux system administrators who are
familiar with virtual machine technology and datacenter operations.
Related Publications
The Data Director Administration Guide contains information about using the vFabric Data Director to manage
your vPostgres databases.
While you can use your vFabric Postgres Standard Edition database as a stand-alone product (independent of
vFabric Data Director), these products are compatible and are designed to work together.
To access the current versions of these and other VMware books, go to
http://www.vmware.com/support/pubs.
VMware, Inc.
5
vFabric Postgres Standard Edition User Guide
6 VMware, Inc.
VMware Customizations for Postgres 1
VMware vFabric Postgres (vPostgres) is an ACID-compliant, ANSI-SQL-compliant transactional, relational
database designed for the virtual environment and optimized for vSphere. It is based on the Postgres opensource relational database and is compatible with Postgres SQL dialect and tools. Its features include elastic
database memory, database-aware high availability, and automated database configuration. vFabric Postgres
contains enhancements and customizations for Data Director.
Differences Between vFabric Postgres and PostgreSQL Databases
The VMware vFabric Postgres database includes memory, checksum, and performace features that are not
available on open source Postgres/PostgreSQL databases. The section describes the differences in more detail.
You can deploy a vPostgres database as a virtual applicance or using RPM files.
n
Deploying a virtual applicance from a VMware Virtualization Platform results in an embedded vPostgres
deployment. This method creates a virtual machine with the operating system (SLES 11, sp1 64-bit Linux),
vPostgres server, and client preinstalled. The appliance version of the vFabric Postgres Standard Edition
database includes VMware virtualization technology.
n
Using the -ivh command line option with RPM files to deploy a vPostgres stand-alone database results in
an embedded vPostgres deployment. In this option, you must create a virtual machine and install an SLES
11 (sp1) or RHEL 6.2 operating system followed by the -ivh commands mentioned above. You can use this
method to install the vPostgres server and client software.
vFabric Postgres Enhancements
VMware vFabric Postgres enhancements include elastic database memory, ease of tuning, checksums, and
configurable checkpoint intervals.
Elastic Database
Memory
VMware, Inc. 7
Elastic database memory enables vPostgres to run with graceful performance
degradation under heavy over-commitment of memory. vPostgres participates
in memory resource management with the guest operating system and vSphere
Hypervisor to achieve elastic database memory.
vFabric Postgres Standard Edition User Guide
vPostgres monitors requests for memory from vSphere Hypervisor as well as
swap activity within the guest operating system. When the hypervisor or the
guest operating system need more memory, the vPostgres buffer manager
shrinks the database buffer pool to make memory available. When more
memory becomes available, the buffer manager increases the amount of
memory dedicated to the buffer pool.
Elastic database memory is enabled by default in the virtual appliance, but is
disabled in the RPM files.
Ease of Tuning When
Deployed As Appliance
vPostgres databases that are deployed as an applicance have higher default
values than standard Postgres databases for many critical settings, including
shared_buffers, checkpoint_segments, and wal_buffers. The higher default
values improve out-of-the-box vPostgres performance with a slight increase in
disk space and memory requirements. The result is that users of embedded
vPostgres databases can more easily tune the database for their workload.
Automatic Checksums
vPostgres performs checksums on each write operation to tables or indexes.
Performing checksums on each write ensures that when vPostgres retrieves
data, that data is clean.
Checksums are enabled by default.
Checkpoint Tuning
vPostgres improves on the Postgres algorithm, to make the tuning more
dynamic.
In I/O-constrained environments, periods of heavy checkpointing activity often
alternate with periods of light checkpoint activity.
Therefore, vPostgres performs dynamic tuning of checkpoint parameters so
that rapid changes in available I/O bandwidth or changes in the database
workload reduce the likelihood of database throughput oscillations.
Checkpoint tuning is enabled by default
Differences for vPostgres Databases within the Data Director Suite
vPostgres databases that are deployed with Data Director differ from stand-alone vPostgres databases in
several ways.
Simplified Configuration
To configure Data Director deployed vPostgres databases, users select a
database configuration template, and then customize settings such as name,
storage allocation, database owner account, and backup template. Data
Director updates the postgresql.conf file’s tunable parameters.
No Superuser Admin
Privileges
Each Data Director database user has only database administrator rights. This
means tools that require superuser privileges, such as COPY FROM, COPY TO,
and tools that operate on the system catalog, do not work.
No pg_dumpall
The pg_dumpall tool requires superuser privileges, and does not work with
Data Director deployed vPostgres databases.
No CREATE or DROP
Database Privileges
Data Director users have privileges only within the database. You cannot use
database client tools to create or drop databases. Use the Data Director UI to
create and drop databases.
Object Naming
Database names must use alphanumeric characters, underline (‘_’) or hyphen
(‘-’).
For object names, only the standard Postgres requirements apply.
8 VMware, Inc.
Installation Overview 2
This section discusses the system requirements for installing the vFabric Postgres Standard Edition (vPostgres)
software, followed by an overview of the installation steps.
The vPostgres server and client software is distributed together as an Open Source Virtual Appliance (.ova)
file, and as a series of .rpm files.
System Requirements
You can install the server .ova file on the latest edition of any VMware Virtualization Platform, such as VMware
vSphere 5.x, VMware Workstation 8.x, VMware Fusion 4.x, or VMware Player 4.x to perform development
tasks and run tests.
In a production environment, it must be installed on VMware vSphere 5.x.
The host where you install the .ova file or the virtual machine where you install the .rpm files must include
the resources listed in the following table.
Table 2-1. Resource Requirements
RAM vCPUs Disk Space
Minimum 512 MB 1 12 GB
VMware, Inc.
The vPostgres product provides custom JDBC, ODBC, and LIBPQ drivers that work with both Standard Edition
(stand alone) and Studio (Data Director integrated) deployments.
Table 2-2. Supported Database Clients
Included Clients 32-bit 64-bit
Windows X X
Linux X X
MAC OSX X X
Many community PostgreSQL clients, such as Npgsql, and psycopg2 are also supported in both 32- and 64-bit
configurations.
Note that if you are using the 'File DSN' option to create an ODBC connection, you must observe the following
syntax rules when adding the server name.
The keywords and their values should not contain the []{}(),;?*=!@ characters. The value of the DSN keyword
cannot consist only of blanks and should not contain leading blanks. Because of the registry grammar,
keywords and data source names cannot contain the backslash (\) character. Spaces are not allowed around
the equal sign in the keyword-value pair.
9
vFabric Postgres Standard Edition User Guide
Installation Process
Read the steps below to get an overview of the installation process. Then proceed to download the necessary
files.
1 Install one of the VMware Virtualization Platforms such as vSphere 5.x, Workstation 8.x, Fusion 4.x, or
Player 4.x.
2 Open the VMware application and choose the aurora_dbvm-9.1.3.0-683886.ova file when asked by the
'File ->Open' or 'File-Deploy OVF' command (depending on your virtualization platform).
3 If you want to build the vPostgres database on an existing virtual machine, make sure the virtual machine
is running one of the supported operating systems and then download and install the client, server, and
init .rpm files that are included in this release (client package first, then server package, init, and eula),
instead of using the appliance file.
4 Install the software for one of the supported clients.
5 Install the Client Tools.
6 Log into the new database using your new database client.
For more detailed steps about installing the .ova file for each VMware application, see Installing vPostgres as
a Virtual Appliance.
This chapter includes the following topics:
n
“Installing vPostgres as a Virtual Appliance,” on page 10
n
“Installing vPostgres Using RPM Files,” on page 13
Installing vPostgres as a Virtual Appliance
Many of the VMware Virtualization Platforms allow you to deploy vPostgres as a virtual appliance, which
creates a virtual machine and then loads specific software onto that virtual machine.
You can install vPostgres as an .ova file on VMware vSphere 5.x (recommened for a production system),
VMware Workstation 8.x, VMware Fusion 4.x or VMware Player 4.x.
Note: If you want to install vPostgres on an existing virtual machine, you can use the .rpm files to install the
vPostgres database.
See Installing vPostgres Using RPM Files, if you want to deploy vPostgres using RPM files.
After you have installed the appliance, you will have a new virtual machine running the SUSE 11 sp1 operating
system with vPostgres server and client database software. You will also have four virtual hard disks: hard
disk 1 (root), hard disk 2 (PGDATA), hard disk 3 (swap) and hard disk 4 (diagnostic/core).
These hard disks are created in the order mentioned above, and are assigned names that match your virtual
machine name, but have slightly different disk numbering (see the following steps for the default disk creation).
The following steps assume you have already downloaded the vPostgres appliance (.ova) file and extracted
the archive to a directory that contains the following files.
Installing on vSphere 5.x
Use the following steps to install the vPostgres appliance on vSphere 5.x.
1. Power on your vSphere host.
2. Connect to the host with your vSphere client.
3. Select File -> Deploy OVF Template
10 VMware, Inc.
Loading...