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.
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.
Contents Preface 5 1 VMware Customizations for Postgres 7 Differences Between vFabric Postgres and PostgreSQL Databases 7 2 Installation Overview 9 Installing vPostgres as a Virtual Appliance Installing vPostgres Using RPM Files 13 10 3 vPostgres Client Tools and Libraries 15 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 4 Restarting the S
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.
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 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.
2 Installation Overview 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.
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).
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.
vFabric Postgres Standard Edition User Guide 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.
Chapter 2 Installation Overview 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.
vFabric Postgres Standard Edition User Guide 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.
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.
vFabric Postgres Standard Edition User Guide 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
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.
vFabric Postgres Standard Edition User Guide 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.
Chapter 3 vPostgres Client Tools and Libraries Procedure u Relink with vPostgres based on your operating system. Operating System Relinking Process Linux a See /opt/vmware/vpostgres/1.0/share/libpqdoc/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 c - or Relink using the vPostgres libpq. # gcc -o t t.
vFabric Postgres Standard Edition User Guide 20 VMware, Inc.
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.
vFabric Postgres Standard Edition User Guide 22 VMware, Inc.
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.
vFabric Postgres Standard Edition User Guide 24 VMware, Inc.
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}.host_name/rdb_name?user= 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.
vFabric Postgres Standard Edition User Guide 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.
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.
vFabric Postgres Standard Edition User Guide 28 VMware, Inc.
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.
vFabric Postgres Standard Edition User Guide 30 VMware, Inc.
Index A V appliance, installation 10 vFabric Postgres, enhancements 7 C W checkpoint 7 checksum 7 client tools, installing 18 Windows packages 16 X x86 vPostgres ODBC 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 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
vFabric Postgres Standard Edition User Guide 32 VMware, Inc.
