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.
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 1 VMware Customizations for PostgreSQL 7 vFabric Postgres Virtual Appliance Enhancements 7 Deploying vFabric Postgres 8 Passwords in vFabric Postgres 8 2 Installing vFabric Postgres 11 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 3 vFabric Postgres Client Tools and
Using VMware vFabric Postgres Access the Graphical User Interface 39 Database Entity Management SQL Management 45 Index 4 40 47 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.
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.
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.
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.
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.
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. 3 a client package b server package c init package Log in to the new DBMS using the client software.
Chapter 2 Installing vFabric Postgres 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.
Using VMware vFabric Postgres What to do next You can now manage your vFabric Postgres environment. n n 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. To manage the virtual appliance, log in to the virtual appliance console or point your Web browser to https://your_vApp_IP:5480.
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.
Using VMware vFabric Postgres Prerequisites n Download the MSI installer from the VMware download site. The installer name is VMware-Postgres-9.3.2.0-xxxx-x64-CIS.msi, where xxxx is the build number. n You must run the MSI installer as a member of the Local Administrators group. Procedure 1 Log in as a Windows system administrator. 2 Open a Windows command prompt on the virtual machine or physical host on which you are going to install vFabric Postgres.
Chapter 2 Installing vFabric Postgres Option Description DB_OWNER_PASS="password" (Optional) This optional parameter lets you specify the DB_OWNER user's password. If you do not specify a password, DB_OWNER user's password is generated with a random string. Encapsulate the password you specify in double quotes, which the system discards. The password is stored in %APPDATA%\postgresql\pgpass.
Using VMware vFabric Postgres Install vFabric Postgres on vCloud Hybrid Service You can install vFabric Postgres on vCloud Hybrid Service. When you deploy vFabric Postgres using vCloud Hybrid Service, you create a vApp template. A vApp template is a virtual machine image that is loaded with an operating system, applications, and data. These templates ensure that virtual machines are consistently configured across an entire organization.
vFabric Postgres Client Tools and Libraries 3 You can download and install vFabric Postgres client tools for the operating system that you are using to manage and access vFabric Postgres databases. The command line front end to PostgreSQL, psql, is also included. NOTE Different client tools and libraries are available for vFabric Postgres. Go to the correct download location to download the tools and libraries you need.
Using VMware vFabric Postgres Table 3‑1. vFabric Postgres 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.
Chapter 3 vFabric Postgres Client Tools and Libraries For example, 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 vFabric Postgres installation process installs the vFabric Postgres ODBC driver.
Using VMware vFabric Postgres What to do next If you install both the x86 and the 64-bit vFabric Postgres client tools on a 64-bit Windows system, see “Add an x86 vFabric Postgres ODBC Data Source on Windows,” on page 22. If you are developing a custom application, relink with libpq. See “Relink Your Application with vFabric Postgres libpq,” on page 22.
Chapter 3 vFabric Postgres Client Tools and Libraries Procedure u Relink with vFabric Postgres based on your operating system. Operating System Relinking Process Linux a b c Read /opt/vmware/vpostgres/current/share/libpqdoc/README.vpostgres-libpq. 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/current/lib-public # mypgapp or Relink using the vFabric Postgres libpq. # gcc -o t t.
Using VMware vFabric Postgres 24 VMware, Inc.
Managing vFabric Postgres 4 After you have installed the vFabric Postgres DBMS and the client tools, you can perform a variety of management tasks. This chapter includes the following topics: n “Migrate PostgreSQL Data from Earlier Versions Into vFabric Postgres 9.
Using VMware vFabric Postgres 3 Restore the database on vFabric Postgres 9.3. /opt/vmware/vpostgres/current/bin/psql -f /var/vmware/vpostgres/current/mydb-backup 4 Vacuum the database after restore to make sure there is no error.
Chapter 4 Managing vFabric Postgres 3 Restart the service. $service vpostgres_mon start Stop and Start the vFabric Postgres Service for RPM Installations If you change the database configuration of your RPM installation, you can perform a configuration file reload or you can stop and start the vFabric Postgres service. If you installed the vFabric Postgres service using RPM files, the service is running within your virtual machine.
Using VMware vFabric Postgres Services that Accept Remote Connections The following vFabric Postgres services accept remote connections to the virtual appliance by default. Table 4‑2. vFabric Postgres Services that Accept Remote Commections Service Port Postgres service 5432 SSH service 22 VAMI (Virtual Appliance Management Infrastructure) Web Management UI. You can connect to port 5480 via https to update or reconfigure the appliance.
Chapter 4 Managing vFabric Postgres n Archive recovery. About pg_rman for vFabric Postgres The following features and operational details are specific to the implementation of pg_rman supplied with vFabric Postgres. n The vFabric Postgres virtual appliance is optimized with environment variables to minimize the command strings of pg_rman. The Postgres data directory and backup directory are set for the user postgres in the .bashrc file.
Using VMware vFabric Postgres n The pg_rman utility does not support taking backups from streams using only a network connection. The backup folder must be mounted locally. However, you can take snapshots of the Virtual Machine Disk (VMDK) containing your backups and export them. Incremental Backups Incremental backups capture the changes to the database after the incremental backup is taken.
Chapter 4 Managing vFabric Postgres Schedule Database Backups You can schedule automated backups of your vFabric Postgres databases to protect your data. You schedule vFabric Postgres backups to run periodically at fixed times, dates, or intervals using the cron utility. The cron files to schedule backups are located in /opt/vmware/vpostgres/current/etc. The files are pg_rman_daily.cron, pg_rman_weekly.cron and pg_rman_monthly.cron, and provide full backups daily, weekly, or monthly.
Using VMware vFabric Postgres n If a slave is disconnected for an extended period of time from the cluster, and it cannot get the necessary WAL files from the archives of the master, you must create a new base backup. This helps maintain the archives at a low size level (a size you can customize by changing the disk size of archives in the virtual machine settings) by keeping in memory only the WAL files needed by slaves to catch up with a master. n Recovery default is the latest time-line.
Chapter 4 Managing vFabric Postgres 2 To create a replication user, run the script /opt/vmware/vpostgres/current/scripts/create_replication_user replica_username. This example creates the replica user replicate. /opt/vmware/vpostgres/current/scripts/create_replication_user replicate What to do next You can now create a replica server using the replication user account. See “Create a Replica Server,” on page 33.
Using VMware vFabric Postgres Promote a Replica Database to Primary Database You can promote a replica (or slave) server to a primary (or master) server. If a primary database fails, you can manually promote one of the replica database instances to take the place of the primary database. Prerequisites Create a replication user account with which to create and promote replica server nodes. See “Create a Replication User Account,” on page 32. Procedure 1 Log in as the replication user.
Chapter 4 Managing vFabric Postgres Using Perl and Python Language Extensions You can use vFabric Postgres with the PL/Perl and PL/Python language extensions. You must make sure you are using the correct versions of the language and the operating system. PL/Python and vFabric Postgres The PL/Python vFabric Postgres extension is supported with the following Python versions and Linux distributions. Python version You must install the Python 2.6.x RPM on your system.
Using VMware vFabric Postgres Viewing Performance Statistics You can obtain vFabric Postgres performance statistics using Pgstatpack. Pgstatpack takes snapshots of various system values, and compares the differences between snapshots, letting you evaluate and tune your database's performance. Pgstatpack lets you collect, automate, store, and view vFabric Postgres performance data.
Chapter 4 Managing vFabric Postgres Prerequisites n Connect to the vFabric Postgres GUI. See “Access the Graphical User Interface,” on page 39. n To create a snapshot using Pgstatpack, you must first enable the Pgstatpack extension. See “Enable Pgstatpack,” on page 36. Procedure 1 Connect to the vFabric Postgres GUI, and click Enter SQL. 2 Create a snapshot using the SQL statement SELECT pgstatspack_create_snap('$DESCRIPTION'); in the Entry pane.
Using VMware vFabric Postgres Troubleshooting Guidelines Use the options listed in this section to analyze connection or performance problems. Client Cannot Connect If your client cannot connect to the vFabric Postgres appliance or to a vFabric Postgres server installed using RPMs, follow these steps to troubleshoot the issue. 1 Ping the server IP from your client. 2 Verify that Postgres is running by running the following command on the command line.
Using the Graphical User Interface 5 A graphical user interface for managing database entities and running SQL commands is available as part of vFabric Postgres. The interface is included with the virtual appliance, and can be installed as part of RPM installation. The tool supports database entity management and SQL management. Database Entity Management Database entity management includes creating, replacing, updating, and deleting database entities.
Using VMware vFabric Postgres Prerequisites n If you are using the virtual appliance (OVA), the GUI is preinstalled. n If you used the RPM installation process, you deploy a WAR file on your Tomcat server and access the GUI from there. See “Deploy the Graphical User Interface,” on page 39. Procedure 1 Access the GUI. from the vSphere Client or by using a URL.
Chapter 5 Using the Graphical User Interface 2 Specify database information in the Create Database dialog. Option 3 Description Name Name of the database (required) Owner Owner of the database. Always postgres for vFabric Postgres databases. Encoding Not currently supported. Tablespace Not currently supported. Connection Limit Number of connections. Defaults to -1, which is unlimited.
Using VMware vFabric Postgres 2 3 (Optional) Customize the operation. n To perform the Vacuum operation, uncheck the Analyze checkbox and click OK. n To perform the Analyze operation, uncheck the Vacuum checkbox and click OK. n To include the Full or Freeze operations with the Vacuum operation, check those checkboxes. Click OK to perform the selected operations. Create a Schema After you create a database, you set up its entities, starting with the database schema.
Chapter 5 Using the Graphical User Interface 6 (Optional) In Constraints, select the type of constraint, Foreign key, Unique, or Check, that applies to the new column. You can create foreign key constraints only if the schema has more than one table. 7 a Click Create. b Enter the conditions for the constraint, and click OK. c Click Next to continue, or click Finish to create the table.
Using VMware vFabric Postgres Procedure 1 In the left pane, click the Schemas arrow to expand it. 2 Click the arrow next to the schema to expand it. 3 Select Views in the left pane. All views under the schema appear in the list in the middle pane. 4 Right-click a view and select Open. The view appears in the left pane. 5 Click the View Data tab to examine the data associated with the view.
Chapter 5 Using the Graphical User Interface Change the postgres Database User Password You can change the password for the database user postgres from the console or from the vFabric Postgres GUI. Prerequisites Connect to the vFabric Postgres GUI. See “Access the Graphical User Interface,” on page 39. Procedure 1 In the vFabric Postgres GUI, click a database arrow to display catalogs, schemas, and db login users. 2 Click DB Login Users.
Using VMware vFabric Postgres 2 Select a schema and click Enter SQL. 3 Enter a SQL query in the entry pane, or click Open to open a SQL script file. 4 Click Execute to run the query. 5 Click Explain to view the query plan, runtime, and CPU cost. What to do next Adjust the SQL query, rerun, and reexamine the query plan to tune performance. 46 VMware, Inc.
Index A accounts 27 administer SQL 45 analyze SQL query plan 45 archive backup 30 archive recovery 29 archive_command script 31 auto-vacuum 42 B backup and recovery 28 backups disable 31 schedule 31 C checkpoint 7 client tools 19 configure auto-vacuum 42 constraint creation 44 create check constraints 42 create column constraints 42 create constraints 44 create database 40 create database schema 42 create foreign key 42 create snapshot 36 create SQL queries 45 create unique constraints 42 create views 43
Using VMware vFabric Postgres PL/Python 35 postgres user 27 PostgreSQL 5 PostgresSQL Recovery Manager 28 promote_replica_to_primary script 31 psql 19, 27 Q query plan 45 R relinking 22 remote connections 27 replication, Write-Ahead Logging (WAL), monitoring status 34 replication status 34 replication, about 31 requirements 12 root user 27 RPM, GUI 39 RPM Files 14 RPM service, stop and start 27 run_as_replica script 31 views, creating 43 vpostgres_mon start 26 vpostgres_mon stop 26 vSphere 5 deployment 1
