Installing VMware Postgres

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 for Linux based platforms, and in a single Debian package for Ubuntu platforms:

For Linux based platforms:

Postgres Server RPM – installs or upgrades all server and client components. This includes the PostgreSQL database, pgBackRest, pg_auto_failover, psql, the ODBC driver, and more.
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 the optional extensions not already included in the Postgres Server rpm.

For Ubuntu platforms:

Postgres and Extensions DEB – installs the Debian package. This includes server, clients, and extensions.

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/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<version>-<vmware-version>.<platform>.<architecture>.zip, for example vmware-postgres15-15.1.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-15.*
yum install ./vmware-postgres15-15.1-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. If the .bash_profile exists prior to installation, it will not be modified. 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 initialize the Postgres instances as part of the pg_auto_failover, or Patroni 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-15.1.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-14*
yum install ./vmware-postgres-clients14-15.1-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 all the VMware Postgres supported extensions. 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/R, and PostGIS.

advanced_password_check

Follow these steps to install the VMWare Postgres advanced_password_check extension.

Load the extension via postgresql.conf.
```
su --login postgres
```
First, check if there are any preloaded shared libraries by editing $PGDATA/postgresql.conf.
```
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
shared_preload_libraries = 'some_lib'
```
Edit the field shared_preload_libraries in the postgresql.conf file to enable the advanced_password_check module, along with any other shared libraries.
```
shared_preload_libraries = 'some_lib,advanced_password_check'
```
Restart the database to apply the changes.
```
pg_ctl -l logfile restart
```

Install the extension in your database:

CREATE EXTENSION advanced_password_check;

See the Greenplum documentation for more details.

login_hook

Follow these steps to install the VMware Postgres login_hook extension. Note that login_hook is not supported for Ubuntu platforms.

Login_hook extension is a tar instead of a RPM. Extract the file.

tar xzf vmware-postgres<version>-login-hook-<login-hook version>.<platform>.tar.gz -C /

Load the extension via postgresql.conf.
```
su --login postgres
```
First, check if there are any preloaded shared libraries by editing $PGDATA/postgresql.conf`.
```
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
shared_preload_libraries = 'some_lib'
```
Edit the field shared_preload_libraries in the postgresql.conf file to enable the login_hook module, along with any other shared libraries.
```
shared_preload_libraries = 'some_lib,login_hook'
```
Restart the database to apply the changes.
```
pg_ctl -l logfile restart
```
Create the login_hook extension.
```
psql -c 'CREATE EXTENSION login_hook';
```

See the login_hook documentation for more details.

Orafce

Follow these steps to install the VMware Postgres Orafce RPM.

Install the Orafce extension RPM.

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

Create the Orafce extension in your database.

su --login postgres
psql -c 'CREATE EXTENSION orafce';

See the Orafce documentation for more details.

Patroni

Follow these steps to install the VMware Postgres Patroni RPM.

Install the VMware Postgres Patroni RPM.

yum install vmware-postgres<version>-patroni-<patroni-version>.<platform>.rpm

Use the Patroni setup page for guidance on configuration

pg_auto_failover

See the pg_auto_failover documentation for details on how to install the extension.

pg_cron

Follow these steps to install the pg_cron extension.

Load the extension via postgresql.conf.
```
su --login postgres
```
First, check if there are any preloaded shared libraries by editing $PGDATA/postgresql.conf.
```
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
shared_preload_libraries = 'some_lib'
```
Edit the field shared_preload_libraries in the postgresql.conf file to enable the pg_cron module, along with any other shared libraries.
```
shared_preload_libraries = 'some_lib,pg_cron'
```
Restart the database to apply the changes.
```
pg_ctl -l logfile restart
```
Install the extension in your database:
```
CREATE EXTENSION pg_cron;
```

For more information about the extension, see the pg_cron repository on GitHub.

pg_partman

Follow these steps to install the VMWare Postgres pg_partman extension RPM.

Install the pg_partman extension RPM.

sudo yum install vmware-postgres<version>-pg_partman-extension-<pg partman version>.<platform>.rpm

Load the extension via postgresql.conf.
```
su --login postgres
```
First, check if there are any preloaded shared libraries by editing $PGDATA/postgresql.conf.
```
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
shared_preload_libraries = 'some_lib'
```
Edit the field shared_preload_libraries in the postgresql.conf file to enable the pg_partman module, along with any other shared libraries.
```
shared_preload_libraries = 'some_lib,pg_partman_bgw'
```
Restart the database to apply the changes.
```
pg_ctl -l logfile restart
```
Create a schema to use with the extension. This step is optional, but recommended. You may choose any schema of your choice, but you cannot change it once the installation is complete. If you use a background worker (BGW) process, you can safely start the database cluster without creating the extension in the configured database(s). You can create the extension at any time and the BGW will automatically pick up that it exists without restarting the cluster (as long as you previously set shared_preload_libraries), and begin running maintenance as configured.
```
psql -c 'CREATE SCHEMA partman';
psql -c 'CREATE EXTENSION pg_partman SCHEMA partman';
```

See the pg_partman documentation for more details.

pg_stat_monitor

Follow these steps to install pg_stat_monitor.

Load the extension via postgresql.conf.
```
su --login postgres
```
First, check if there are any preloaded shared libraries by editing $PGDATA/postgresql.conf.
```
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
shared_preload_libraries = 'some_lib'
```
Edit the field shared_preload_libraries in the postgresql.conf file to enable the pg_stat_monitor module, along with any other shared libraries.
```
shared_preload_libraries = 'some_lib,pg_stat_monitor'
```
Restart the database to apply the changes.
```
pg_ctl -l logfile restart
```
Install the extension in your database:
```
CREATE EXTENSION pg_stat_monitor;
```

For more information about the extension, see the pg_stat_monitor documentation.

pgaudit

Follow these steps to install the VMware Postgres pgaudit RPM.

Install the pgaudit extension RPM.

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

Load the extension via postgresql.conf.
```
su --login postgres
```
First, check if there are any preloaded shared libraries by editing $PGDATA/postgresql.conf.
```
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
shared_preload_libraries = 'some_lib'
```
Edit the field shared_preload_libraries in the postgresql.conf file to enable the pgaudit module, along with any other shared libraries.
```
shared_preload_libraries = 'some_lib,pgaudit'
```
Restart the database to apply the changes.
```
pg_ctl -l logfile restart
```
Create the pgaudit extension.
```
psql -c 'CREATE EXTENSION pgaudit';
```

See the pgaudit documentation for more details.

pgBackRest

See the pgBackRest documentation for details on how to install the extension.

pgbouncer

Install the pgbouncer extension RPM.

vmware-postgres<version>-pgbouncer-<pgbouncer extension version>.<platform>.rpm

See the pgbouncer documentation for more details.

pgvector

Create the pgvector extension in your database.

su --login postgres
psql -c 'CREATE EXTENSION vector';

See the pgvector documentation for more details.

PL/R

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

Install the PL/R extension.

sudo yum install vmware-postgres<version>-plr-extension-<plr-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.

Install the VMware Postgres PostGIS RPM.

yum install vmware-postgres<version>-postgis-extension-<postgres-version>.<platform>.rpm

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.

PostgresML

Follow these steps to install the PostgresML extension RPM.

Install the PostgresML extension RPM.

sudo yum install vmware-postgres<version>-postgresml-extension-<pgml extension version>.<platform>.rpm

Load the extension via postgresql.conf.
```
su --login postgres
```
First, check if there are any preloaded shared libraries by editing $PGDATA/postgresql.conf.
```
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
shared_preload_libraries = 'some_lib'
```
Edit the field shared_preload_libraries in the postgresql.conf file to enable the PostgresML module, along with any other shared libraries.
```
shared_preload_libraries = 'some_lib,pgml'
```

If you are running VMware Postgres 11.x or 12.x, run the following command:

sed -i '/trusted = true/d' /opt/vmware/postgres/<version>/share/extension/pgml.control

Restart the database to apply the changes.

pg_ctl -l /var/lib/pgsql/data/logfile restart

Install the Python dependencies manually. Use the virtualenv tool to install the necessary Python packages into a virtual environment. Ensure that you have python3.9, pip and git installed.

# Install virtualenv
python3.9 -m pip install virtualenv --user
# Create a virtualenv
python3.9 -m virtualenv /var/lib/pgsql/venv_pgml
# Install necessary python dependencies to the virtualenv for linux based platforms
/var/lib/pgsql/venv_pgml/bin/pip install -r /opt/vmware/postgres/<version>/share/pgml-extension/requirements.linux.txt

Create the PostgresML extension.

--- Set the venv to the installation path of virtualenv
SET pgml.venv='/var/lib/pgsql/venv_pgml';
--- Create pgml extension
CREATE EXTENSION pgml;
--- Activate the virtualenv
SELECT pgml.activate_venv('/var/lib/pgsql/venv_pgml');
--- Verify the python dependencies
SELECT pgml.validate_python_dependencies();

See the PostgresML documentation for more details.

Installing the Postgres DEB

Perform this procedure on each host that will run VMware Postgres.

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

Navigate to the downloaded DEB file and install it using the apt utility. For example:

cd ~/Downloads/vmware-postgres-14.*
apt install ./vmware-postgres-14.11.0.ubuntu2204-amd64.deb