VMware vFabric Postgres - 9.3.2 User Guide
Using VMware vFabric Postgres
vFabric Postgres 9.3.2
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-001393-00
Using VMware vFabric Postgres
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–2014 VMware, Inc. All rights reserved. Copyright and trademark information.
VMware, Inc.
3401 Hillview Ave.
Palo Alto, CA 94304
www.vmware.com
2 VMware, Inc.
Contents
Preface 5
VMware Customizations for PostgreSQL 7
1
vFabric Postgres Virtual Appliance Enhancements 7
Deploying vFabric Postgres 8
Passwords in vFabric Postgres 8
Installing vFabric Postgres 11
2
Installation Overview 11
System Requirements 12
Deploy the OVA File 13
Install vFabric Postgres Using RPM Files 14
Install vFabric Postgres as a Windows Service 15
Uninstall the vFabric Postgres Windows Service 17
Install vFabric Postgres on vCloud Hybrid Service 18
vFabric Postgres Client Tools and Libraries 19
3
Overview of Tools and Libraries 19
Client Tool Packages and Drivers 20
Install the Client Tools Package 21
Add an x86 vFabric Postgres ODBC Data Source on Windows 22
Relink Your Application with vFabric Postgres libpq 22
VMware, Inc.
Managing vFabric Postgres 25
4
Migrate PostgreSQL Data from Earlier Versions Into vFabric Postgres 9.3 25
Migrate PostgreSQL Data Into vFabric Postgres 26
Restarting the vFabric Postgres Service 26
Connection to a vFabric Postgres Database 27
Accounts and Services 27
Safeguarding Data 28
About vFabric Postgres Replication 31
Create a Replication User Account 32
Create a Replica Server 33
Promote a Replica Database to Primary Database 34
Monitoring Replication Status 34
Using Perl and Python Language Extensions 35
Viewing Performance Statistics 36
Troubleshooting Guidelines 38
Using the Graphical User Interface 39
5
Deploy the Graphical User Interface 39
3
Using VMware vFabric Postgres
Access the Graphical User Interface 39
Database Entity Management 40
SQL Management 45
Index 47
4 VMware, Inc.
Preface
Using VMware vFabric Postgres provides information about installing and using a VMware vFabric Postgres
Standard Edition DBMS.
Intended Audience
This information is intended for anyone who wants to install or use a vFabric Postgres Standard Edition
DBMS. The information is written for experienced Windows or Linux system administrators who are
familiar with virtual machine technology and datacenter operations.
Related Publications
The vFabric Suite documentation has information about the components of the vFabric suite.
For information about managing vFabric Postgres databases, see the public PostgreSQL documentation at
http://www.postgresql.org/docs/. Because vFabric Postgres is compatible with PostgreSQL, you can manage
vFabric Postgres databases using the information in that documentation.
To access the current versions of VMware documentation, go to http://www.vmware.com/support/pubs.
VMware, Inc.
5
Using VMware vFabric Postgres
6 VMware, Inc.
VMware Customizations for
PostgreSQL 1
VMware vFabric Postgres is an ACID-compliant, ANSI-SQL-compliant transactional, relational database
management system that is designed for the virtual environment and optimized for vSphere. It is based on
the PostgreSQL open-source relational database and is compatible with PostgreSQL.
vFabric Postgres databases are managed by a DBMS that consists of a server and a client. VMware supports
all standard PostgreSQL connection tools and interaction methods, and optionally allows you to use a GUI
tool for database management.
This chapter includes the following topics:
“vFabric Postgres Virtual Appliance Enhancements,” on page 7
n
“Deploying vFabric Postgres,” on page 8
n
“Passwords in vFabric Postgres,” on page 8
n
vFabric Postgres Virtual Appliance Enhancements
VMware vFabric Postgres virtual appliance includes features that are not available with the open source
PostgreSQL DBMS.
The virtual appliance include the following enhancements
Automatic Tuning
If you deploy the vFabric Postgres appliance, associated vFabric Postgres
databases have higher default values than standard PostgreSQL databases
for many critical settings, including shared_buffers, checkpoint_segments,
and wal_buffers. The higher default values improve out-of-box
VMware, Inc. 7
Using VMware vFabric Postgres
vFabric Postgres performance with a slight increase in disk space and
memory requirements. The result is that users of an embedded
vFabric Postgres database can more easily tune the database for their
workload.
If you are using vFabric Postgres, and you use the RPM installation, these
changes to default values are not made.
Separate XLog Files and
Archive Files
Replication Scripts
PGDATA Environment
Variable
Files of pg_xlog are stored on a different partition than that used by the main
database to better leverage I/O activity on the server, and provide strict
control over the storage space allowed for WAL files. This strategy is used as
well for the archive to cover cases where a partition disk failure would
prevent the reuse of archived WAL files for recovery. WAL archiving is
enabled on a given node when a standby node is enabled using the
replication scripts that connect to this node.
A set of scripts that simplify the management of replication between
vFabric Postgres nodes: slave creation, node promotion, and replication
monitoring. The scripts are located in the
folder /opt/vmware/vpostgres/current/scripts.
The virtual appliance provides the PGDATA environment variable, which
specifies the directory to use for data storage. The value is set
to /var/vmware/vpostgres/current/pgdata.
Deploying vFabric Postgres
You can deploy a vFabric Postgres DBMS as a virtual appliance (OVA file) or by using RPM packages.
You can deploy the virtual appliance to create a virtual machine with the operating system (SLES 11, SP
n
2 64-bit Linux), a vFabric Postgres server, and a vFabric Postgres client preinstalled. The appliance
version of the vFabric Postgres database includes VMware virtualization technology.
You can use RPM to deploy vFabric Postgres. Use RPM installation with a physical host, or create a
n
virtual machine and install one of the supported operating system that are listed on the datasheet. Use -
ivh commands to install the RPMs. You can use this method to install the vFabric Postgres server and
client software.
You can use a Microsoft Windows Installer (MSI) to install vFabric Postgres on a Windows physical
n
host, or create a virtual machine using a supported Windows operating system. The software is
installed as a Windows service.
Passwords in vFabric Postgres
You specify the passwords for vFabric Postgres users during deployment, and can change passwords for
each user individually at a later time.
Passwords differ slightly depending on whether you deploy the OVF or perform an RPM installation.
OVA Deployment
After deployment of a vFabric Postgres OVA, three users are defined.
8 VMware, Inc.
Chapter 1 VMware Customizations for PostgreSQL
Table 1‑1. vFabric Postgres Users for OVF Deployment
User Name User Type
root operating system user
postgres operating system user
postgres database user
You can specify a single password for all three users during deployment of the OVA. If you did not specify
a password during deployment, you are prompted for a password when you access the virtual appliance
console for the first time.
NOTE Remote login for root operating system user is disabled. If you use SSH to log in you must do so as
the postgres operating system user.
For automated deployments, you can use ovftool --prop:Password=secret.
RPM Installation
During RPM installation, the installer creates the following users.
Table 1‑2. vFabric Postgres Users for RPM installation
User Name User Type
postgres operating system user
postgres database user
With RPM installation, no initial password is set for the postgres operating system user or the postgres
database user. The root user already exists before the RPM installation and its password is set using Linux
commands.
To set the postgres operating system user password, log in as root and use the passwd command to set a
n
new password.
passwd postgres
To set the postgres database user password, log in as the postgres operating system user and use the
n
alter command.
/opt/vmware/vpostgres/current/bin/psql -c "alter user postgres with password 'your-
password'"
Changing Passwords After Installation
After installation, you can change passwords as follows.
Use the /opt/aurora/sbin/set_password command to change the password for all three users.
n
Use the passwd command to change passwords individually for the system users.
n
Use the following command to change the password for the postgres database user.
n
/opt/vmware/vpostgres/current/bin/psql -c "alter user postgres with password 'yourpassword'"
VMware, Inc. 9
Using VMware vFabric Postgres
10 VMware, Inc.
Installing vFabric Postgres 2
Before you install vFabric Postgres, review the requirements and the deployment or installation process.
This chapter includes the following topics:
“Installation Overview,” on page 11
n
“System Requirements,” on page 12
n
“Deploy the OVA File,” on page 13
n
“Install vFabric Postgres Using RPM Files,” on page 14
n
“Install vFabric Postgres as a Windows Service,” on page 15
n
“Uninstall the vFabric Postgres Windows Service,” on page 17
n
“Install vFabric Postgres on vCloud Hybrid Service,” on page 18
n
Installation Overview
The vFabric Postgres server and client software are distributed together. You can either deploy an Open
Source Virtual Appliance (OVA) file or install a series of RPM packages.
Virtual Appliance Deployment Overview
The process of deploying a vFabric Postgres virtual appliance is similar on all the different supported
virtualization platforms.
1 Install one of the VMware virtualization platforms such as vSphere 5.1 or later, VMware Workstation
9.x, VMware Fusion 5.x, or VMware Player 5.x.
NOTE For a production system, only vSphere 5.1 or later is supported.
2 Deploy the virtual appliance.
3 To manage the new DBMS, log in to the virtual appliance console and use the preinstalled psql tool or
point your Web browser to https://your_vApp_IP:8443.
4 To manage the virtual appliance, log in to the virtual appliance console or point your Web browser to
https://your_vApp_IP:5480.
VMware, Inc.
11
Using VMware vFabric Postgres
RPM Deployment Overview
The process of installing the vFabric Postgres DBMS from RPM packages consists of the following high-level
tasks.
1 Make sure the host or virtual machine that you want to use is running a supported operating systems
and meets all the other requirements.
2 Download and install the client, server, and init RPM files.
a client package
b server package
c init package
3 Log in to the new DBMS using the client software.
System Requirements
You can deploy the virtual appliance and install the RPM packages on several operating systems.
Supported Platforms for OVA Deployment
For the virtual appliance (OVA), several virtualization platforms are supported during development, but
support is more limited during production.
Development
Production
While you develop your application and run tests, you can deploy the virtual
appliance on the latest edition of any VMware virtualization platform,
including VMware vSphere 5.1 or later, VMware Workstation 9.x, VMware
Fusion 5.x, or VMware Player 5.x.
In a production environment, you must install vFabric Postgres on VMware
vSphere 5.1 ESXi or later.
Resource Requirements for RPM Installation
If you install the RPM files, you must have a physical host or a virtual machine that meets the following
minimum requirements.
RAM
CPUs or vCPUs
Disk Space
512 MB
1 or more
12 GB
Resource Requirements for OVA Deployment
The virtual appliance requires 1GB of RAM. See the vSphere documentation for information on the required
memory overhead.
Operating Systems
The vFabric Postgres server software is currently supported on the following operating systems.
Red Hat Linux
SUSE Linux
Oracle Enterprise Linux
12 VMware, Inc.
RHEL 6.2 (64 bit) and 6.4 (64 bit)
SLES 11 SP 1 (64 bit), SLES 11 SP2 (64 bit), or SLES 11 SP3 (64 bit)
OEL 6
Database Clients
The vFabric Postgres product includes JDBC, ODBC, and LIBPQ drivers.
Database clients for Windows, Linux, and Mac OS X, in both 32 bit and 64 bit versions, are included.
Many community PostgreSQL clients, such as Npgsql, and psycopg2 are also supported in both 32-bit and
64-bit configurations. Client drivers for Npgsql are included.
Deploy the OVA File
You can deploy the OVA file on vSphere 5.1 ESXi or later for use during development or for production
environments. In addition, can deploy the OVA file on VMware Workstation 9.x, VMware Fusion 5.x, or
VMware Player 5.x for use during development.
This topic describes the process when you use the vSphere Web Client with vSphere. If you are using the
vSphere Client, or one of the other VMware products, the process is similar but the prompts might differ
slightly.
Prerequisites
Download the OVA file from the VMware download site.
Chapter 2 Installing vFabric Postgres
Procedure
1 Connect to a vCenter Server with the vSphere Web Client.
2 Right-click an inventory object that is a valid parent object of a virtual machine, such as a datacenter,
folder, cluster, resource pool, or host and select Deploy OVF Template.
3 If prompted, download the client plug-in.
You have to close all browsers to download the plug-in.
4 Respond to the wizard prompts.
Screen Action
Select Source
Review Details
Accept EULAS
Select name and folder
Select storage
Setup networks
Customize template
Ready to Complete
Specify the location of the OVA file.
Review the OVA information.
Review and accept the license agreement.
Specify the name and location for the virtual appliance.
Select the storage for the virtual appliance. You can use the pull-down
menu to change the disk format.
Map the networks used in the OVF template to networks in your inventory
and select
Specify the password that you want to use initially for the three users that
the OVA file defines. A minimum of six characters is required.
Review the settings and click Finish to start deployment.
When deployment completes, the virtual appliance powers on.
5 (Optional) If you did not specify a password during deployment, specify it now. Right-click the virtual
appliance and select Open Console and enter the initial password for all user accounts.
a Click Edit Settings.
b Click the Options tab and select Properties under vApp Options.
c Enter the initial passworkd in the text box on the right.
VMware, Inc. 13
Using VMware vFabric Postgres
What to do next
You can now manage your vFabric Postgres environment.
To manage the new DBMS, log in to the virtual appliance console and use the preinstalled psql tool or
n
point your Web browser to https://your_vApp_IP:8443.
To manage the virtual appliance, log in to the virtual appliance console or point your Web browser to
n
https://your_vApp_IP:5480.
Install vFabric Postgres Using RPM Files
If you want to install vFabric Postgres on a virtual machine or on a physical host, you can use the RPM
installation process.
Different editions of the vFabric Postgres server software are supported on different operating systems. See
the datasheet for information.
The vFabric Postgres RPM packages are relocatable, meaning they can be installed into a different directory.
The relocatable directory paths are /opt/vmware/vpostgres and /var/vmware/vpostgres. By default, RPM
installs packages in these directories. You can override this during the RPM installation using the command
rpm --prefix directory_name. For example, you can install the packages in rpm --prefix /usr/opt.
Prerequisites
Create a new virtual machine running a supported operating system, or log in to a virtual machine
n
where one of these operating systems is currently running. You can also install the RPMs on a physical
host that runs one of the supported operating systems.
Verify that you have access to the Internet to download the RPM packages.
n
If you install 32-bit binaries on a 64-bit system, install compatibility libraries as well. On RHEL6, use yum
n
install glibc.i686 nss-softokn-freebl.i686.
Procedure
1 Download at a minimum the following ZIP files from the VMware download site.
ZIP file for vFabric Postgres
n
ZIP file for the vFabric Postgres client tools and libraries
n
ZIP file for the vFabric Postgres JDBC driver
n
Optional components, 32-bit client RPMs, and client tools for Windows, Macintosh, ODBC, and JDBC
are also available on the download site.
2 Unzip the ZIP files and install each of the RPM files using the rpm -ivh command, in the order shown
below, or install all files at once with a single command.
>rpm -ivh
VMware-VMware-Postgres-osslibs-server-version.x86_64.rpm
VMware-VMware-Postgres-osslibs-version.x86_64.rpm
VMware-VMware-Postgres-libs-version.x86_64.rpm
VMware-Postgres-server-version.x86_64.rpm
The RPM installation creates the directory /opt/vmware/vpostgres/, and the postgres user for the
database and operating system.
NOTE The postgres database user, and the postgres operating system user, are two different users
accounts.
14 VMware, Inc.
Chapter 2 Installing vFabric Postgres
3 Create a folder labeled pgdata, and assign it to the postgres user.
mkdir /var/vmware/vpostgres/9.3/pgdata
chmod 755 /var/vmware/vpostgres/9.3/pgdata
chown postgres /var/vmware/vpostgres/9.3/pgdata
4 Change to the postgres user.
su postgres
5 Initialize the database using the init command.
cd /opt/vmware/vpostgres/currect/bin
./initdb -D /var/vmware/vpostgres/9.3/pgdata
6 Start the database using either the pg_ctl command or the postgres command.
To start the database using the postgres command, use the following command syntax.
n
./postgres -D /var/vmware/vpostgres/9.3/pgdata
To start the database using the pg_ctl command, use the following command syntax.
n
./pg_ctl start -D /var/vmware/vpostgres/9.3/pgdata
7 You can now connect to the database as the postgres user using the psql command.
./psql -U postgres
8 (Optional) Create a database to test the installation.
This example creates the database test1.
postgres=#CREATE DATABASE test1;
CREATE DATABASE
9 If you want to you use the GUI, you can download a ZIP file that contains the vpgdbem.war file from the
vFabric Postgres download site and move the file to the webapps directory of your Tomcat server.
You can then use the vpgdbem URI to access the GUI. For example, if your Tomcat server is installed
for port 8080, you can access the GUI at http://ipaddress:8080/vpgdbem.
What to do next
You can set the passwords for the postgres operating system and database user. See “Passwords in vFabric
Postgres,” on page 8.
Install vFabric Postgres as a Windows Service
You can install vFabric Postgres using the Microsoft Windows Installer (MSI) on a Windows physical host,
or create a virtual machine using a supported Windows operating system.
The MSI installer lets you install the Windows 64-bit (x64) version of vFabric Postgres as a Windows service.
The supported operating systems are Windows 2008, 2008 R2, and Windows 2012.
You can run the MSI installer either by double-clicking the executable file on your Windows desktop, or by
running it from the command prompt and supplying custom installation parameters. If you choose to install
vFabric Postgres by double-clicking the executable file, the installation uses the default values for directory
paths, the database user name and password, and the database name.
A single vFabric Postgres database server (identified by a Windows service name) can serve multiple
databases. Each database is identified using a unique database name. By default, when you create a
vFabric Postgres database server instance, a system database labeled postgres is created. Do not use the
postgres system database to store your application's data. Instead, create additional databases for use with
your applications.
VMware, Inc. 15
Loading...