How it Works
VMware
Loading...
V
Loading...
Loading...
vFabric Postgres - 9.1
User Guide
32 pgs453.14 Kb0
Table of contents
Loading...

VMware vFabric Postgres - 9.1 User Guide

Download
Page 1
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
Page 2
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.
Page 3

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
Page 4
4 VMware, Inc.
Page 5

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
Page 6
6 VMware, Inc.
Page 7

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 open­source 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.
Page 8
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.
Page 9

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
Page 10
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.
Page 11
Chapter 2 Installation Overview
4. Browse to the .ovf file
5. Accept the license agreement (EULA)
6. Wait for the appliance to deploy
7. Revise your resources as needed. This step is optional. The default configuration is:
n
Memory: 512 MB
n
vCPU: 1
n
Hard Disk 1 - Root disk: 2 GB (vmname.vmdk)
n
Hard Disk 2 - Data disk (PGDATA): 8 GB (vmname_1.vmdk)
n
Hard Disk 3 - Swap disk: 1 GB (vmname_2.vmdk)
n
Hard Disk 4 - Diagnostic/core disk: 1 GB (vmname_3.vmdk)
8. Set the network configuration if you want to use a static ip address, or leave the settings blank and the virtual machine with default to use dhcp settings.
You can use the following network configuration script to change the ip address of the deployed virtual machine: /opt/vmware/share/vami/vami_config_net.
9. Power on the new virtual machine.
10. Open the console and check the random password on the screen.
11. Log in as root, using the random password.
12. Change the password using /opt/aurora/sbin/set_password. This sets the password for both the root and the 'postgres' user (on both system and database).
Now you can connect to the database from a client.
Installing on Workstation 8.x
Use the following steps to install the vPostgres appliance on Workstation 8.x.
1. Select File -> Open.
2. Browse to the .ovf file, and double-click to open it.
3. Accept the license agreement (EULA).
4. Wait for the appliance to deploy.
5. Revise your resources as needed. This step is optional. The default configuration is:
n
Memory: 512 MB
n
vCPU: 1
n
Hard Disk 1 - Root disk: 2 GB (vmname.vmdk)
n
Hard Disk 2 - Data disk (PGDATA): 8 GB (vmname_1.vmdk)
n
Hard Disk 3 - Swap disk: 1 GB (vmname_2.vmdk)
n
Hard Disk 4 - Diagnostic/core disk: 1 GB (vmname_3.vmdk)
6. The network configuration is set to dhcp by default. Add network configuration values if you want to use a static ip address.
7. You can use the following network configuration script to change the ip address of the deployed virtual machine: /opt/vmware/share/vami/vami_config_net.
8. Power on the virtual machine.
VMware, Inc. 11
Page 12
9. Open the console and locate the random password on the screen.
10. Log in as root, using the random password.
11. Change the password using /opt/aurora/sbin/set_password. This sets the password for both the root and the 'postgres' user (on both system and database).
Now you can connect to this database from a client.
Installing on Fusion
1. Install the ovftool on your Fusion application.
2. In a terminal/command window, type ovftool path/aurora_dbvm-9.1.3.0-683886.ovf path/aurora_dbvm-9.1.3.0-683886.vmx to change the format.
3. Use the File ->Open command to select and open the .vmx file.
4. The appliance will install and create a vPostgres virtual machine.
5. Revise your resources as needed. This step is optional. The default configuration is:
n
Memory: 512 MB
n
vCPU: 1
n
Hard Disk 1 - Root disk: 2 GB (vmname.vmdk)
n
Hard Disk 2 - Data disk (PGDATA): 8 GB (vmname_1.vmdk)
n
Hard Disk 3 - Swap disk: 1 GB (vmname_2.vmdk)
n
Hard Disk 4 - Diagnostic/core disk: 1 GB (vmname_3.vmdk)
6. Set the network configuration if you want to use a static ip address, or leave the settings blank and the virtual machine with default to use dhcp settings.
You can use the following network configuration script to change the ip address of the deployed virtual machine: /opt/vmware/share/vami/vami_config_net.
7. Power on the virtual machine.
8. Open the console and locate the random password on the screen.
9. Log in as root, using the random password.
10. Change the password using /opt/aurora/sbin/set_password. This sets the password for both the root and the 'postgres' user (on both system and database).
Now you can connect to this database from a client.
Installing on Player 4.x
1. Use the File ->Open command to select and open the .vmx file.
2. The appliance will install and create a vPostgres virtual machine.
3. Revise your resources as needed. This step is optional. The default configuration is:
n
Memory: 512 MB
n
vCPU: 1
n
Hard Disk 1 - Root disk: 2 GB (vmname.vmdk)
n
Hard Disk 2 - Data disk (PGDATA): 8 GB (vmname_1.vmdk)
n
Hard Disk 3 - Swap disk: 1 GB (vmname_2.vmdk)
12 VMware, Inc.
Page 13
n
Hard Disk 4 - Diagnostic/core disk: 1 GB (vmname_3.vmdk)
4. Set the network configuration if you want to use a static ip address, or leave the settings blank and the virtual machine with default to use dhcp settings.
You can use the following network configuration script to change the ip address of the deployed virtual machine: /opt/vmware/share/vami/vami_config_net.
5. Power on the virtual machine.
6. Open the console and locate the random password on the screen.
7. Log in as root, using the random password.
8. Change the password using /opt/aurora/sbin/set_password. This sets the password for both the root and the 'postgres' user (on both system and database).
Now you can connect to this database from a client.

Installing vPostgres Using RPM Files

Use the .rpm files to install the vFabric Postgres Standard Edition database, if the you want to install the database on a new or exising virtual machine.
The vPostgres server software is currently only supported on the operating systems listed in the following table.
Chapter 2 Installation Overview
Table 2-3. Server Operating System Requirements
Red Hat Linux SUSE Linux
Supported Versions RHEL 6.2 (64-bit) SLES 11 sp1 (64-bit)
Before you install the .rpm files, create a new virtual machine running either the SUSE 11 sp1 operating system or the RHEL 6.2 operating system, or log onto a virtual machine where one of these operating systems is already running.
Download the RPM Files
Ensure that your virtual machine or Linux computer can see the web, and then download the following files from the VMware Web site.
n
vfabric-eula-9.1.3.0-683886.x86_64.rpm
n
VMware-vPostgres-client-9.1.3.0-683886.x86_64.rpm
n
VMware-vPostgres-9.1.3.0-683886.x86_64.rpm
n
VMware-vPostgres-server-tools-9.1.3.0-683886.x86_64.rpm (recommended)
n
VMware-vPostgres-server-extensions-9.1.3.0-683886.x86_64.rpm (recommended)
n
VMware-vPostgres-client-devel-9.1.3.0-683886.x86_64.rpm (optional)
n
VMware-vPostgres-server-devel-9.1.3.0-683886.x86_64.rpm (optional)
n
VMware-vPostgres-server-init-9.1.3.0-683886.x86_64.rpm
Note that if you install 32-bit binaries on a 64-bit system, you will need to install compatibility libraries as well. On RHEL6, use "yum install glibc.i686 nss-softokn-freebl.i686".
Install the RPM Files
Install each of the .rpm files using the rpm -ivh command, in the order shown below. You can install all of the files at once, with a single 'rpm -ivh' command.
VMware, Inc. 13
Page 14
Note that the files that contain 'devel' in the name are optional.
>rpm -ivh VMware-vPostgres-client-9.1.3.0-683886.x86_64.rpm VMware-vPostgres-9.1.3.0-683886.x86_64.rpm VMware-vPostgres-server-tools-9.1.3.0-683886.x86_64.rpm VMware-vPostgres-server-extensions-9.1.3.0-683886.x86_64.rpm VMware-vPostgres-client-devel-9.1.3.0-683886.x86_64.rpm VMware-vPostgres-server-devel-9.1.3.0-683886.x86_64.rpm VMware-vPostgres-server-init-9.1.3.0-683886.x86_64.rpm
After these files have been installed, you will have a database instance in the var/vmware/vpostgres/9.1/pgdata directory, where the user name is "postgres", and you will have a "postgres" user for your operating system.
Log into with the 60-day license key and set the password manually.
To set the password for the "postgres" database user (optional if you only need to connect to the database from the local host):
$/opt/vmware/vpostgres/current/bin/psql -c
To set the password for the "postgres" operating system user (optional):
Log in as "root".
$passwd postgres
14 VMware, Inc.
Page 15

vPostgres Client Tools and Libraries 3

You can use vPostgres client tools to print configuration parameters and to back up and restore vPostgres databases. The command line front end to PostgreSQL, psql, is also included.
The vPostgres client tools are based on the Postgres client database tools and are customized for vPostgres. Versions for Linux x86, 32 bit and 64 bit, and for Windows x86, 32 bit and 64 bit are available.
Linux
The Linux RPM includes ODBC drivers for vPostgres. The Linux ODBC driver requires unixODBC-2.3.1 or greater.
Windows
The vPostgres installer package for Windows includes ODBC and JDBC drivers for vPostgres.
The following vPostgres client database tools are included in the vPostgres client tools package.
Table 3-1. vPostgres Client Tools
Tool Description
pg_config Prints the current vPostgres installation's configuration
parameters.
pg_dump Backs up vPostgres databases
pg_restore Restores vPostgres databases from archives created by
pg_dump.
psql Command line based front end to PostgreSQL.
The vPostgres client tools ship with the following libraries.
Table 3-2. vPostgres Client Tool Libraries
Library Description
libpq.so (Linux) or libpq.dll (Windows) The C API to PostgreSQL. Libpq is the underlying engine for
several PostgreSQL APIs such as those written for C++, Perl, Python, Tcl, and ECPG.
psqlodbcw.so (Linux) or psqlodbc35w.dll (Windows) The ODBC driver for PostgreSQL.
VMware, Inc.
The vPostgres client tool libraries are customized for use with vPostgres databases, but you can use the standard Postgres libraries. To ensure that you link with the vPostgres libraries, do one of the following.
n
If you want to keep the standard Postgres libraries on your system, ensure that your LD_LIBRARY_PATH environment variable specifies the location of the vPostgres libraries first.
n
If you do not want to keep the standard Postgres libraries, remove them and ensure that your LD_LIBRARY_PATH environment variable points to the location of the vPostgres libraries on your system.
15
Page 16
This chapter includes the following topics:
n
“Client Tool Packages and Drivers,” on page 16
n
“Install the Client Tools Package,” on page 17
n
“Installing vPostgres Client Tools,” on page 18
n
“Add an x86 vPostgres ODBC Data Source on Windows,” on page 18
n
“Using vPostgres libpq with Your Application,” on page 18

Client Tool Packages and Drivers

Packages
If you plan to write code, and you plan on compiling an application to link with libpq, download both the client package and the development package.
You can download the client tool package for your platform from the VMware download site at http://www.vmware.com/go/download-datadirector.
Table 3-3. Client Tool Packages
Operating System Packages
Microsoft Windows Windows client tools packages are available for 32-bit and
64-bit systems.
Linux The following Linux client tools packages are available for
32-bit and 64-bit systems.
n
A JDBC package in ZIP format. The JDBC package provides the JDBC driver and sample code.
n
Client RPMs for 32-bit and 64-bit systems. The client RPMs provide platform-appropriate binaries, ODBC driver, and libraries.
n
Client development RPMs for 32-bit and 64-bit systems. The client development RPMs provide platform­appropriate include files for application development.
Client Drivers
The vPostgres client tools package includes a JDBC driver and an ODBC driver customized for vPostgres. Use the vPostgres JDBC or ODBC drivers, not the standard Postgres drivers, to connect to Data Director.
JDBC Driver
After installation, you can find the JDBC driver in the following locations.
Table 3-4. Location of JDBC Driver
Operating System JDBC Driver Location
Microsoft Windows
Linux
C:\Program Files\VMware\vPostgres\9.1\JDBC
/opt/vmware/vpostgres/9.1/JDBC
The Samples directory contains a simple Java example and README file that show how to connect to Data Director using JDBC.
16 VMware, Inc.
Page 17
Chapter 3 vPostgres Client Tools and Libraries
If your application uses the JDBC driver to access a database, and you install the application as /usr/local/lib/myapp.jar and the PostgreSQL JDBC driver as /usr/local/pgsql/share/java/postgresql.jar, you run the application as follows.
export CLASSPATH=/usr/local/lib/myapp.jar:/usr/local/pgsql/share/java/postgr esql.jar:.java MyApp
ODBC Driver
The vPostgres installation process installs the vPostgres ODBC driver. To verify the Windows ODBC driver installation, go to Start > Administrative Tools > Data Sources (ODBC), and click the Drivers tab. The VMware vPostgres ODBC driver appears in the list of installed ODBC drivers.

Install the Client Tools Package

Prerequisites
Download the package. See “Client Tool Packages and Drivers,” on page 16.
The ESXi host for Data Director is up and running.
Procedure
1 Install the package.
Operating System Installation Process
Linux
Windows
2 Ensure that your PATH environment variable includes the location of the vPostgres client tools, for
example C:\Program Files\VMware\vPostgres\1.0\bin
Run the RPM package by using the following command.
rpm -ivh pathToClientRpms
pathToClientRpms is the full pathname of the RPM package location on your system. The default installed location is /opt/vmware/vpostgres/9.1.
Use -Uvh instead of -ivh if you perform an upgrade.
Double-click the installer to start the installer. Accept the license agreement and confirm the install location. Installation proceeds. The default installed location is \Program Files\VMware\vPostgres\9.1\. If you install the x86 vPostgres client tools on a Windows 64-bit system, the Windows installer places the client tools in \Program Files (x86)\VMware\vPostgres\9.1\.
What to do next
If you install both the x86 and the 64 bit vPostgres client tools on the system, you can add an x86 ODBC data source. See “Add an x86 vPostgres ODBC Data Source on Windows,” on page 18.
If you are developing a custom application, relink with libpq. See “Using vPostgres libpq with Your
Application,” on page 18.
VMware, Inc. 17
Page 18

Installing vPostgres Client Tools

You can install the vPostgres Client Tools on Windows or Linux systems. The package includes drivers customized for vPostgres. You can install only the base package, or install the development RPMs as well.

Add an x86 vPostgres ODBC Data Source on Windows

If you install both the x86 and the 64-bit vPostgres client tools on the same 64-bit Windows system, you must explicitly add an x86 ODBC data source.
Prerequisites
Install the x86 and the 64-bit vPostgres client tools.
Procedure
1 In Windows Explorer, go to C:\Windows\SysWOW64\.
2 Double-click Odbcad32.exe.
3 Select the System DNS tab and click Add.
4 Click the VMware vPosgres PostgreSQL Unicode 32bit data source.
5 Click Finish.

Using vPostgres libpq with Your Application

If you want to use an existing Postgres application with vPostgres, you can relink the application.
Prerequisites
Install the vPostgres client tools. You do not have to install the development RPMs if you relink.
Because our libpq.so is dynamically linked with libssl, the static ld linker doesn't recognize the rpath of $ORIGIN. Therefore, it is important to specify the rpath when building. See the Linux example in the table below for the correct syntax.
18 VMware, Inc.
Page 19
Procedure
u
Relink with vPostgres based on your operating system.
Operating System Relinking Process
Linux
Windows
a See /opt/vmware/vpostgres/1.0/share/libpq-
doc/README.vpostgres-libpq.
b Override the dynamic library search path by
adding /opt/vmware/vpostgres/current/lib-public to LD_LIBRARY_PATH.
# export LD_LIBRARY_PATH=/opt/vmware/vpostgres/
1.0/lib-public # mypgapp
- or -
c Relink using the vPostgres libpq.
# gcc -o t t.c -L/opt/vmware/vpostgres/current /lib -Wl,'-rpath=/opt/vmware/vpostgres/current/lib' -lpq
Copy the libpq and other libraries into the directory of the application binaries and relink.
By default , the libraries and header files are in the following locations.
Chapter 3 vPostgres Client Tools and Libraries
Development libraries
libpgport.lib and libpq.lib libraries
libpq header files
C:\Program Files\VMware\vPostgres\9.1\dev
C:\Program Files\VMware\vPostgres\9.1\dev\lib
C:\Program Files\VMware\vPostgres\9.1\dev\include
VMware, Inc. 19
Page 20
20 VMware, Inc.
Page 21

Restarting the Service 4

When you modify vPostgres configuration files, you need to stop and restart the vPostgres database service. This section describes how to stop and restart the service, depending on whether you installed it as an appliance or with .rpm files.
Stopping and Starting the Appliance Service
If you installed the vPostgres database service as an appliance, use the following commands to stop and then restart the service. For the appliance service, these commands also stop and start VMware's High Availability monitor process that makes sure the database process is up and running.
To Stop the Process:
$service aurora_mon stop
To Start the Process:
$service aurora_mon start
Stopping and Starting the RPM Service
VMware, Inc.
If you installed the vPostgres database service using RPM files, the vPostgres service is running within your virtual machine. Use the following commands to stop and then restart the vPostgres service. Note that in this configuration, the vPostgres service does not include VMware's High Availability monitor feature.
To Stop the Process:
$service vpostgres stop
To Start the Process:
$service vpostgres start
21
Page 22
22 VMware, Inc.
Page 23

Adding a License Key 5

The vPostgres database supports two mechanisms for validating the license: local (using a serial number in a file that you create in the virtual machine) and server-based (validating the license by communicating with the vFabric Suite license server).
Installing a Local vPostgres Standard Edition License
You can install a local vPostgres serial number in the guest operating system of your virtual machine using the following procedure.
1 Log in as the root user.
2 Create the /etc/opt/vmware/vfabric/ vf.vpg-serial-numbers.txt file in the virtual machine (using your valid
license number).
# echo "XXXXX-XXXXX-XXXXX-XXXXX-XXXXX [quantity=1, expiration=Permanent ]" >> /etc/opt/vmware/vfabric/vf.vpg-serial-numbers.txt
3 Change the group and file permissions.
# chgrp vfabric /etc/opt/vmware/vfabric/vf.vpg-serial-numbers.txt # chmod 640 /etc/opt/vmware/vfabric/vf.vpg-serial-numbers.txt
Using a Server-Based License
If vPostgres does not find this file, it will attempt to use the vFabric license server. If the license server does not respond, vPostgres will fall back to using the remainder of the evaluation license.
See the vFabric Suite 5.1 documentation located at
https://www.vmware.com/support/pubs/vfabric51/index.jsp?topic=/ com.vmware.vfabric.web-server.5.1/web-server/install.html
for more information about vFabric licenses.
VMware, Inc.
23
Page 24
24 VMware, Inc.
Page 25
Connection to a vPostgres Database
with JDBC or psql 6
If you do not want to use the Data Director UI to manage your vPostgres database, you can instead connect with JDBC or with psql.
Connecting with JDBC
The JDBC connection string has the following format.
jdbc:postgresql://{UUID}.
The curly brackets, {}, are part of the connection string and enclose the UUID.
For example, assume that you use JDBC to connect to a database with the following characteristics.
Database name
Host name
UUID
User name
You can use the following JDBC connection string.
jdbc:postgresql://{d35f7ab1-d70e-4d98-c121-122f68e4ab60}.w1-devtest-22.dev.mycorp.com/mydb? user=dbowner
Connecting with psql
The psql connection string has the following format.
psql -h {UUID}.DB_Name_Service_IP -p 5432 -d DB_name -U db_user_name
For example, assume that you use psql to connect to a database with the following characteristics.
UUID
DB Name Service port
host_name/rdb_name
myDB
w1-devtest-22.dev.mycorp.com
d35f7ab1-d70e-4d98-c121-122f68e4ab60
dbowner
1234-5678-9012-3456
5432
?user=<user name>
VMware, Inc.
DB Name Server IP address
database user name
You can use the following psql command to connect to the database.
$ psql -h {1234-5678-9012-3456}.10.0.0.1 -p 5432 -d myDB -U dbuser
Psql connects to myDB and prompts for the password, and logs you in. You can enter psql commands.
10.0.0.1
dbuser
25
Page 26
Loading Data with psql
Use the psql command \COPY (back slash-COPY) to load data into vPostgres databases. Do not use the psql
COPY FROM or COPY TO command. The COPY FROM (or COPY TO) command must run on the server with superuser
privileges and cannot load data into remote clients such as vPostgres. The \COPY FROM and \COPY TO commands use the client session to read and write from local files. These commands support relative paths, and use the psql communication layer to load data to, or from, the server.
For example, the following psql command loads data from a text file, shelterlists.txt, into the vPostgres database table shelters in the schema rescues.
Querying vPostgres with psql
After you connect to a vPostgres database, you can query the database. For example, using psql, connect to the database and type the psql command at the database prompt.
You can run SQL script files using the psql -f SQL_script_filename command, where the file named SQL_script_filename contains a series of SQL commands. Psql reads the file, runs the SQL commands, and exits.
26 VMware, Inc.
Page 27

Security and Services 7

This section provides information about the security policies and mechanisms that are supported by the vFabric Postgres database.
Since you can set the parameters as you create a vFabric Postgres database, you can create configurations that are not mentioned in this section. But if you install the vPostgres database as an appliance, the connections that are created and used by default as described here.
Services that Accept Remote Connections
The vPostgres virtual appliance supports one network interface by default (dhcp) and contains the following services that accept remote connections.
n
The Postgres service on port 5432.
n
SSH service on port 22.
n
The VAMI Web Management UI on port 5480. You can connect to port 5480 via https to update or reconfigure the appliance.
n
The VAMI SFCB broker on ports 5488 and 5489.
Accounts Created During Installation
The "root" and "postgres" users are created when the vPostgres database is deployed.
n
The "root" user can log into the appliance from the guest console using the same random password as the postgres user. Remote ssh logins are disabled for root. Database access is also disabled for root.
n
The "postgres" user is a database administrator account. This user can log into the appliance from the guest console, log in remotely using ssh, or connect to the database service on port 5432. The initial password for the "postgres" user is randomly generated and displayed on the guest console. You can use the /opt/aurora/sbin/set_password utility to change the password for the "postgres" user.
VMware, Inc.
27
Page 28
28 VMware, Inc.
Page 29

Troubleshooting 8

Use the options listed in this section to analyze connection or performance problems.
Issue: Client Cannot Connect
Perform the following steps if your client cannot connect to the vPostgres 9.1 Appliance.
Ping the server IP from your client.
Verify that Postgres 9.1 is using "ps ax | grep post".
Try to connect a local PostgresSQL client to the database.
Review the database logs in /var/vmware/vpostgres/9.1/pgdata/pg_log
Issue: Database Transactions Per Second are Less Than Expected
Make sure your PGDATA VMDK is on a high-performance datastore.
Look for missing indexes in your SQL queries.
Analyze concurrent queries for conflicts.
Increase the number of vCPUs and/or memory.
VMware, Inc.
Test by turning off synchronous_commit in var/vmware/vpostgres/9.1/pgdata/postgresql.conf and restarting the appliance.
29
Page 30
30 VMware, Inc.
Page 31

Index

A
appliance, installation 10
C
checkpoint 7 checksum 7 client tools, installing 18
D
differences 7
E
elastic database memory 7 enhancements 7
I
installing client tools 17
J
JDBC 25
L
libpq 18 linking 18 Linux packages 16
V
vFabric Postgres, enhancements 7
W
Windows packages 16
X
x86 vPostgres ODBC 18
O
OSBC data source 18
P
packages 16 pg_config 15 pg_dump 15 pg_dumpall 7 pg_restore 15 psql 15, 25 psql editing inline 7
R
relinking 18 RPM Files 13
S
superuser privileges 7
VMware, Inc. 31
Page 32
32 VMware, Inc.
Loading...
Buy Points How it Works FAQ Contact Us Questions and Suggestions Privacy Policy Cookie Policy Terms of Use