Installing or Upgrading VMware Postgres

Prerequisites
Installing the Postgres Server RPM
Installing the VMware Postgres Client RPM
Installing the VMware Postgres JDBC Driver
Installing the VMware Postgres Extensions
- PL/Java
- PL/R
- PostGIS
Upgrading a VMware Postgres Installation

This topic describes how to download and install or upgrade components of the VMware Postgres software distribution. The VMware Postgres software components are provided in 4 downloadable packages:

Postgres Server RPM – installs or upgrades all server and client components. This includes the PostgreSQL database, pgBackRest, Patroni, psql, and the ODBC driver.
Postgres Clients RPM – installs or upgrades only the client components, psql and the ODBC driver.
JDBC Driver for VMware Postgres – provides only the JDBC driver as a compressed ZIP file. This can be used on any machine as necessary for developing Java clients to PostgreSQL.
VMware Postgres Extensions – installs the RPM packages for VMware Postgres PL/Java, VMware Postgres PL/R, and PostGIS.

Prerequisites

Your deployment systems must meet the system requirements described in Supported Platforms.
You must have root permissions to install or upgrade the software.
Ensure that you have not installed Red Hat Enterprise Linux PostgreSQL on any system where you want to install VMware Postgres Server or VMware Postgres Clients. VMware Postgres software cannot be installed alongside RHEL PostgreSQL at this time.
The VMware Postgres PL/Java Extension requires a Java development kit to be installed. The extension is tested with OpenJDK 1.8.0. See Installing the VMware Postgres PL/Java Extension.
The VMware Postgres PL/R Extension requires R to be installed before the RPM can be installed. See Installing the VMware Postgres PL/R Extension.

Installing the Postgres Server RPM

Perform this procedure on each host that will run VMware Postgres. Note that this process also installs the psql and ODBC driver client components to each machine:

Download the VMware Postgres Server RPM distribution from VMware Tanzu Network. The Postgres Server download filename has the format: vmware-postgres-<postgres-version>-<vmware-version>.<platform>.<architecture>.zip, for example vmware-postgres-10.18.0.el7.x86_64.zip.
Log in to your system as root, or use sudo in the next step to acquire root privileges.

Navigate to the downloaded RPM file and install it using the yum utility. For example:

$ cd ~/Downloads/vmware-postgres-10
$ yum install ./vmware-postgres10-10.18-0.el7.x86_64.rpm

The VMware Postgres RPM installation creates the postgres user and sets the PGDATA and PATH environment variables in ~postgres/.bash_profile. It also creates the directory /var/lib/pgsql/data, owned by the postgres user, which you can specify for the data directory when you initialize your PostgreSQL instance. This example initializes the data directory:
```
# su --login postgres
$ source ~/.bash_profile
$ initdb -D /var/lib/pgsql/data
```
If you are creating a High Availability configuration, you will create the required Postgres instances as part of the pg_auto_failover setup. For more details refer to Creating a High Availability cluster.
If you are creating a single non-HA Postgres server, start it by running:
```
$ postgres -D /var/lib/pgsql/data >/var/lib/pgsql/data/logfile 2>&1 &
```
Continue to set up and start the PostgreSQL server, as described in Server Setup and Operation in the PostgreSQL documentation.
Follow the instructions in Configuring and Using VMware Postgres to configure PostgreSQL with pgBackRest, psqlODBC, and pgjdbc.

Installing the VMware Postgres Client RPM

Perform this procedure on each host that will act only as a client to the VMware Postgres instance.

Important: Do not install the client package on any machine that has a PostgreSQL server.

Download the Postgres Clients RPM distribution from VMware Tanzu Network. The download filename has the format: vmware-postgres-clients-<postgres-version>.<vmware-version>.<platform>.<architecture>.zip, for example vmware-postgres-clients-10.18.0.el7.x86_64.zip.
Log in to your system as root, or use sudo in the next step to acquire root privileges.

Navigate to the downloaded RPM file and install it using the yum utility. For example:

$ cd ~/Downloads/vmware-postgres-10
$ yum install ./vmware-postgres10-clients-10.18-0.el7.x86_64.rpm

Installing the VMware Postgres JDBC Driver

Download the VMware Postgres Postgres Clients for RHEL7 from VMware Tanzu Network. The JDBC driver file name inside the zip file has the format: vmware-postgres-jdbc-<jdbc-version>.jar.
Unzip the downloaded file to obtain the driver JAR file. For example:
```
$ unzip vmware-postgres-clients-<version>-0.el7.x86_64.zip
```
The JDBC driver JAR is not installed at the system level; simply include it in your Java CLASSPATH as necessary to connect using the driver. See Setting up the Class Path in the JDBC Driver documentation for more information.

Installing the VMware Postgres Extensions

The VMware Postgres Server and Extensions download from VMware Tanzu Network includes RPM packages for VMware Postgres PL/Java, VMware Postgres PL/R, Orafce, and PostGIS. Follow these steps to download and install these extensions.

On the Postgres server, extract the RPM packages from the Extensions for VMware Postgres Server download file.

$ unzip vmware-postgres-extensions-<postgres-version>.<platform>.zip

To install the extensions see PL/Java, PL/R, and PostGIS.

PL/Java

Install the PL/Java extension RPM.

$ sudo yum install -y vmware-postgres-pljava-extension-<postgres-version>.<platform>.rpm

Install a JDK. The VMware PL/Java extension is tested with Java OpenJDK 1.8.0. The following steps presume you are using this JDK.
```
$ sudo yum install java-1.8.0-openjdk-devel
```

Run the PL/Java jar file to install libraries and extension files.

# JAVA_HOME=/usr/lib/jvm/java
# java -Dpgconfig.sharedir=/usr/share/postgresql \
    -Dpgconfig.pkglibdir=/usr/lib64/postgresql \
    -jar /usr/share/postgresql/extension/pljava-*.jar

The following commands create the PL/JAVA extension in a Postgres database.

# su postgres
$ psql test
#= set pljava.libjvm_location TO '/lib/jvm/jre/lib/amd64/server/libjvm.so';
#= CREATE EXTENSION pljava;

See the PL/Java project information site for information about using PL/Java.

PL/R

Install R from the EPEL repo before you install the VMware Postgres PL/R RPM.
```
$ sudo yum install epel-release
$ sudo yum install R
```

Install the PL/R extension.

$ sudo yum install vmware-postgres-plr-extension-<postgres-version>.<platform>.rpm

Enable the PL/R extension in each Postgres database where it will be used. For example, these commands register the PL/R language in the database named test.
```
# su postgres
$ psql -d testdb -c 'CREATE EXTENSION plr;'
```

See PL/R Project for more about PL/R. See The R Project for Statistical Computing for information about available R packages.

PostGIS

Follow these steps to install the VMware Postgres PostGIS RPM. The sequence is important. You must install the PostGIS RPM before you install the gdal and gdal-devel packages.

Install the VMware Postgres PostGIS RPM.

$ sudo rpm -ivh --no-deps vmware-postgres-postgis-extension-<postgres-version>.<platform>.rpm

Install the epel-releasepackage.
```
$ sudo yum install -y epel-release
```
Create the PostGIS extension in each database where you want to use PostGIS. Note: Do not install the extension in the postgres database.
```
# su postgres
$ createdb testdb
$ psql -d testdb -c 'CREATE EXTENSION postgis;'
```

See the PostGIS web site for information about using PostGIS.

Upgrading a VMware Postgres Installation

This topic covers upgrading from a VMware Postgres minor 10.x version to 10.18.0.

VMware Postgres 10.18 introduces some RPM packaging changes that effect upgrades from previous 10.x versions.

The location of the installed files on disk has changed from /usr to /opt/vmware/postgres/10, and the names of the installed packages have changed from vmware-postgres to vmware-postgres10 or vmware-postgres<version>. Follow these steps to upgrade from a previous 10.x release:

Install the new RPM:

 $ cd ~/Downloads/vmware-postgres-10
 $ sudo yum install ./vmware-postgres10-10.18-0.el7.x86_64.rpm

Update any absolute paths to the Postgres executables that reference the old installation path of /usr. These files might be Postgres server configuration files, or any custom scripts you may have written. For example, if you have an archive_cleanup_command set up in postgresql.conf with an absolute path:
```
archive_cleanup_command = '/usr/bin/pg_archivecleanup ... %r'
```
then remove the absolute path or update it to the new location:
```
archive_cleanup_command = 'pg_archivecleanup ... %r'
```
or
```
archive_cleanup_command = '/opt/vmware/postgres/10/bin/pg_archivecleanup ... %r'
```
When ready to upgrade, update the Postgres user’s PATH to point to the new installation. In the ~/postgres/.bash_profile add the following line:
```
export PATH=/opt/vmware/postgres/10/bin:$PATH
```
You can check that it has taken effect:
```
$ su --login postgres
$ which pg_ctl
/opt/vmware/postgres/10/bin/pg_ctl
```

Switch to the new installation by stopping and starting the server. Do not use the restart shortcut, because it will reuse the old installation:

$ su --login postgres
$ pg_ctl stop -l data/logfile
$ pg_ctl start -l data/logfile

Verify that the correct versions are in use, both on the client machine and the server:

$ su --login postgres
$ psql
psql (10.18 (VMware Postgres 10.18.0))
Type "help" for help.

postgres=# show server_version;
           server_version         
---------------------------------
10.18 (VMware Postgres 10.18.0) 
(1 row)

Confirm the new server is working correctly, and remove the old server package:
```
$ sudo yum remove vmware-postgres  
```