PostgreSQL on VMware Data Services Manager

Supported PostgreSQL Versions

VMware Data Services Manager supports provisioning databases running PostgreSQL versions 11.x, 12.x, 13.x, 14.x, and 15.x.

From Release 1.5.0, VMware Data Services Manager does not support PostgreSQL version 10.x.

Migrating PostgreSQL 10.x Databases to Later Versions of PostgreSQL

You can choose to migrate databases that you have created using PostgreSQL 10.x before the release of VMware Data Services Manager 1.5.0. To migrate PostgreSQL 10.x database VMs, you can run the pg_backup_restore.sh file after you meet the Prerequisites.

Prerequisites

Before you run the pg_backup_restore.sh script to migrate the PostgreSQL 10.x databases to a later version of PostgreSQL (11.x or later), ensure that:

The sshpass package is installed on all the PostgreSQL 10.x database VMs that should be migrated to a later version of PostgreSQL 10.x.
Docker is installed both on the earlier PostgreSQL 10.x database VM, and on the later version of the PostgreSQL database VM to which the PostgreSQL 10.x database VM will be migrated.
The later version of the PostgreSQL database VM to which the PostgreSQL 10.x database VM is going to be migrated must be accessible from the PostgreSQL 10.x database VM.
The later version of the PostgreSQL database VM to which the PostgreSQL 10.x database VM is going to be migrated and the PostgreSQL 10.x database VM must have the same name and must be created by the same user.
The disk size of the later version of the PostgreSQL database VM to which the PostgreSQL 10.x database VM will be migrated must be equal to or greater than the disk size of the existing PostgreSQL 10.x database.

Procedure

After you have ensured that you meet all the conditions of the prerequisites, use the following steps to run the script and migrate PostgreSQL 10.x database VMs to a later version of the PostgreSQL database VM:

Copy the contents of the following script to a new file named pg_backup_restore.sh on the later version of the PostgreSQL database VM that will host the migrated database.

#!/bin/bash

# PostgreSQL 10 server hostname or IP address
PG10_HOST="172.1.47.54"

# PostgreSQL 10 server SSH port (default is 22)
PG10_SSH_PORT=22

# PostgreSQL 10 server SSH username
PG10_SSH_USER="root"

# Path to store the backups on the local machine
BACKUP_DIR="/tmp/backups"

echo "Connecting to PostgreSQL 10 server at ${PG10_HOST} via SSH..."

PG10_SSH_PASSWORD='8Wxq6L$_e'

# Test SSH connection to PostgreSQL 10 server
if sshpass -p "$PG10_SSH_PASSWORD" ssh -p $PG10_SSH_PORT $PG10_SSH_USER@$PG10_HOST "exit 0" ; then
 echo "SSH connection successful."
else
    echo "Failed to connect to PostgreSQL 10 server via SSH."
    exit 1
fi

echo "Taking backup of database on PostgreSQL 10 server..."

# Generate timestamp for backup filename
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR

sshpass -p $PG10_SSH_PASSWORD ssh  root@$PG10_HOST "docker exec postgres pg_dump -U postgres  -d dbaas" | gzip > $BACKUP_DIR/pg10_all_${TIMESTAMP}.sql.gz


echo "Backup complete."

echo "Starting to import"
gunzip $BACKUP_DIR/pg10_all_${TIMESTAMP}.sql.gz

mkdir /opt/vmware/dbaas/data-mount/dump

cp $BACKUP_DIR/pg10_all_${TIMESTAMP}.sql /opt/vmware/dbaas/data-mount/dump

docker exec postgres  psql -U postgres -d dbaas -f /bitnami/postgresql/dump/pg10_all_${TIMESTAMP}.sql

echo "Import completed"

Open the pg_backup_restore.sh file in a text editor.

Modify the values of variables in the file as follows:

Variables	Values
PG10_HOST	Hostname or IP address of the PostgreSQL 10 server that is goint to be migrated
PG10_SSH_USER	The username that is used to SSH to the PostgreSQL 10.x database VM
PG10_SSH_PASSWORD	The password that is used to SSH to the PostgreSQL 10.x database VM
PG10_SSH_PORT	Use the recommended value, 22
BACKUP_DIR	Any directory within the data mount directory in the later version of the PostgreSQL database VM. For example, if the data mount directory is [/opt/vmware/dbaas/data-mount], then the backup directory can be [/opt/vmware/dbaas/data-mount/dump]

Replace db name, dbaas, as required in the following lines of the script:
- sshpass -p $PG10_SSH_PASSWORD ssh root@$PG10_HOST "docker exec postgres pg_dump -U postgres -d dbaas" | gzip > $BACKUP_DIR/pg10_all_$ {TIMESTAMP}.sql.gz
- docker exec postgres psql -U postgres -d dbaas -f /bitnami/postgresql/dump/pg10_all_${TIMESTAMP}.sql
Save the modified ./pg_backup_restore.sh script.
Run the command, chmod 755 pg_backup_restore.sh.
Run the modified script of the modified file, pg_backup_restore.sh in the later version of the PostgreSQL database VM to which the PostgreSQL 10.x database VM is going to be migrated.

Note:

For a PostgreSQL 10.x database, you cannot change the database options of a Replica database in VMware Data Services Manager 1.5.0.

For a PostgreSQL 10.x database, you cannot scale the database configuration of a Primary database or Replica database(s).

If you want to create a High Availability database cluster from a PostgreSQL 10.x database VM in VMware Data Services Manager 1.5.0, you must follow the prerequisites and steps to migrate the PostgreSQL 10.x database VM to a later version of PostgreSQL database VM and then create Replica databases for the later version of the PostgreSQL database VM.

Configuration

When you create a PostgreSQL database with VMware Data Services Manager, you configure certain PostgreSQL properties.

Database Configuration

The Database Configuration properties identify the name of the default database, the database server port number, and the database administrator user credentials.

Property Name	Description
Database Name	The name of the default database.
Database Port No	The port number on which the database server runs. (The default is 5432.) (Read-only)
Username	The database administrator user name.
Password	The password for the database administrator. The special characters allowed are @, #, %, &, and *.

Note: The default values for these properties are provided through the user interface, but you should change them as required. VMware Data Services Manager does not support changing the Password from the console or API. You must change this password using a database client. If you change the password in this manner, the new password is not reflected in VMware Data Services Manager.

DB Options Configuration

The DB Options Configuration properties control certain runtime characteristics of the PostgreSQL database. These are:

Property Name	Description	Default Value	Restart Required on Update?
TimeZone	The time zone for the PostgreSQL server.¹	GMT	Yes
max_connections	The maximum number of concurrent connections allowed to the PostgreSQL server. Choose a suitable value based on CPU, memory, and application requirements.²	100	Yes
log_connections	Activate or deactivate logging of connection attempts and successful completion of authentication.	off	No
locale	The language, sorting, number formatting, date/time, currency, etc. preference for the PostgreSQL server.³ Note: You can set the `locale` only at database creation time; you cannot set this parameter after you create the database.	Unset The following PostgreSQL `locale`s are available in this release of VMware Data Services Manager: C, de_DE.utf8, en_US.utf8, en_US.UTF-8, es_ES.utf8, fr_FR.utf8, ja_JP.utf8, ko_KR.utf8, POSIX, zh_CN.utf8, zh_TW.utf8	N/A
shared_buffers	The amount of memory the database server can use for shared memory buffers.⁴	128 MB	No

Note: If you want to set a parameter to the PostgreSQL server default, you must remove/clear the entry.

¹ Run the following PostgreSQL query to obtain the possible time zone values: `SELECT name FROM pg_timezone_names;

² In a High Availability cluster of PostgreSQL databases, you cannot configure the max_connections of a Replica database lower than that of the Primary database.

³ Be sure to consult the PostgreSQL documentation before you set the locale or related lc_xxx options.

⁴ Be sure to consult the PostgreSQL documentation before you set the shared_buffers option.

Connecting to a PostgreSQL Database

Prerequisites

To access a PostgreSQL database:

The host on which the client application is running must have connectivity to the Application Network configured for the database. You can use any SQL client application to connect to a database in your PostgreSQL database.
To connect the host, you must be able to identify the FQDN of the PostgreSQL server host, the port on which the server is running, and the database name.
You must also have the PostgreSQL admin credentials on hand. You can obtain this information from the VMware Data Services Manager console as described in Locating the Database Connection Parameters.

Connecting to a PostgreSQL Database Through PSQL Command Line Interface

To provide access to a PostgresSQL database through a command line interface(CLI):

You must enable SSH access. This creates the db-admin OS user with the capability to use the native PostgreSQL tools, such as, psql, pg_dump, and pg_restore; and allow the import or export data directly to or from the database.
Log into the host OS as the db-admin user.
To connect to the database, create a .db-client-env file in the db-admin user's $HOME directory.
```
printf "PGHOST=localhost\nPGDATABASE=dbaas\nPGUSER=dbaas\nPGPASSWORD=< default-password >" > $HOME/.db-client-env
```
Note: Replace < default-password > with password of the PostgreSQL user that is used to connect to the database.
The .db-client-env file must contain the PostgreSQL connection parameters to the default values set by VMware Data Services Manager when a database is provisioned. Environment variables that you can set in this file are as follows:

Environment Variable	Description
PGHOST	The host on which the PostgreSQL server is running.
PGDATABASE	The name of the PostgreSQL database to which you want to connect.
PGUSER	The PostgreSQL user name.
PGPASSWORD	The password of the PostgreSQL user that abides by the PostgreSQL password restrictions.

With these .db-client-env settings in place, running the psql command (without adding any other options) attempts to connect the user dbaas to the dbaas database on the PostgreSQL server.

Using TLS with a PostgreSQL Database

Refer to SSL Support in the PostgreSQL documentation for more information on using SSL/TLS with PostgreSQL.

Note: VMware Data Services Manager does not support verify-full TLS configuration for client applications connecting to a database.

Requiring TLS

Note: The default SSL/TLS configuration for PostgreSQL on VMware Data Services Manager does not require secure client connections.

You can configure a PostgreSQL database to require SSL/TLS client connections during creation or after provisioning as follows:

You specify Require Client TLS when you create the database.
You require TLS by configuring the database after it is provisioned as described in Requiring TLS-Secured Client Connections.

About the Permissions Assigned to the Default Database User

When you create a database, the default PostgreSQL database user (role) name is dbaas. You can change this name, if you choose, before you provision the database.

While the default user is not a PostgreSQL SUPERUSER, VMware Data Services Manager assigns this user the privileges, roles, and function execution permissions that allow them to create other roles and databases, import and export data from the database, execute the PostgreSQL server signalling functions, and manage PostgreSQL extensions.

Privileges

VMware Data Services Manager assigns the following PostgreSQL privileges to the default user:

Privilege Name	Description
CREATEDB	Allows the user to create a database.
CREATEROLE	Allows the user to create, alter, and drop a (non-superuser) role.
REPLICATION	Allows the user to connect in replication mode.
BYPASSRLS	Instructs PostgreSQL to bypass every row-level security policy for the user.

Roles

VMware Data Services Manager grants the default user membership in the following PostgreSQL default roles. Granting role membership conveys the privileges assigned to the role.

Role Name	Description
pg_monitor	Allows the user to read monitoring views, execute monitoring functions.
pg_signal_backend	Allows the user to signal a server process to cancel a query or terminate a session.
pg_read_server_files	Allows the user to read files from any accessible location on the database file system using `COPY` or other file access functions.
pg_write_server_files	Allows the user to write files to any accessible location on the database file system using `COPY` or other file access functions.

Note: The pg_read_server_files and pg_write_server_files default roles are not available to the default user in PostgreSQL version 10.x. Support for PostgreSQL version 10.x is not available from release 1.5.0 of VMware Data Services Manager

Functions

VMware Data Services Manager grants the default user the permission to execute the PostgreSQL Server Signaling Functions:

Function Name	Description
pg_reload_conf()	Sends a signal to reload configuration files.
pg_cancel_backend()	Sends a signal to cancel a query.
pg_rotate_logfile()	Immediately rotates the PostgreSQL server's log file.
pg_terminate_backend()	Sends a signal to terminate a client session.

Extension Management

VMware Data Services Manager grants the default user the permissions required to execute extension management functions as described in Working with PostgreSQL Extensions.

Working with PostgreSQL Extensions

PostgreSQL extensions are built-in modules that provide additional functionality to a database. The extensions that are available to a VMware Data Services Manager database depend on the PostgreSQL version provisioned for the database. Listing the Available and Registered Extensions describes the psql and SQL commands that you can run to identify the installed and available extensions for your PostgreSQL database.

Note: When you create a PostgreSQL database, VMware Data Services Manager enables the postgis extension by default.

An extension must be explicitly enabled in each database in which it is to be used. Only PostgreSQL SUPERUSERs can enable an extension. VMware Data Services Manager provides an alternate method of managing PostgreSQL extensions via user-defined functions (UDFs) because it does not grant SUPERUSER permissions to a database owner by default. These functions allow the database owner to create, drop, and update an extension in a database.

VMware Data Services Manager creates the extension management UDFs in the default PostgreSQL template1 database, which makes them available to any database created from this database template. (The default behaviour of PostgreSQL is to use template1 as the database template when you create a new database.)

By default, the Username that you configure when you create a database is the owner of the database specified in Database Name, and has access to the extension management functions. This user also has the permissions required to grant access to the extension management functions to other PostgreSQL users as described in Granting Access to the Extension Management UDFs.

About the Extension Management UDFs

VMware Data Services Manager provides the following UDFs to manage database extensions:

Function Signature(s)	Description
dms_create_extension( name text ) dms_create_extension( name text, schema text, version text, cascade bool DEFAULT FALSE)	Creates (registers) the named extension in the database.
dms_update_extension( name text, version text )	Updates the version of a previously-created extension in the database. (The extension must support the upgrade operation.)
dms_drop_extension( name text, cascade bool DEFAULT FALSE )	Drops the named extension from the database.

These UDFs are available to any user with access, and can be invoked using SQL from any PostgreSQL client.

Granting Access to the Extension Management UDFs

The database owner can grant other PostgreSQL users access to the extension management UDFs with the following SQL commands:

GRANT EXECUTE ON FUNCTION dms_create_extension(text) TO <user>;
GRANT EXECUTE ON FUNCTION dms_create_extension(text, text, text, bool) TO <user>;
GRANT EXECUTE ON FUNCTION dms_drop_extension(text, bool) TO <user>;
GRANT EXECUTE ON FUNCTION dms_update_extension(text, text) TO <user>;

Replace <user> with the PostgreSQL user name to which you want to grant access.

Listing the Available and Registered Extensions

All extensions installed with PostgreSQL are available to be created (i.e. registered) in any database. You may find it useful to view a list of the available extensions. You may also be interested in a list of the extensions that are already registered in a specific database.

You can query the PostgreSQL pg_available_extensions catalog table directly to view the name, default version, and installed version of all available extensions:
```
db=> SELECT * from pg_available_extensions;
```
The \dx meta-command displays a list of the extensions created in the current database. You can run the command from any psql client:
```
db=> \dx
```

Example Usage

The extension management UDFs allow users with access to create, update, or drop an extension in a database by invoking SQL commands from any PostgreSQL client.

For example, to register the latest version of the hstore extension in the current database (named db), run the following command:

db=> SELECT dms_create_extension( 'hstore' );

To register version 1.6 of the hstore extension in the schema named extschema in the current database:

db=> SELECT dms_create_extension( 'hstore', 'extschema', '1.6' );

To drop this extension, and any dependent objects, from the current database:

db=> SELECT dms_drop_extension( 'hstore', true );

Importing and Exporting Data

The $HOME directory of the db-admin user on the database contains a subdirectory named transfer/. This directory is a symbolic link to the database data disk. You can copy large data files for import operations to the transfer/ directory, or use it as a write target for data export operations.

Note: Any files that reside in the transfer/ directory use storage on the data disk, limiting the amount of disk space available to the database. Ensure that you promptly remove from this directory any files that you no longer require. You may also choose to extend the size of the data disk which is use by the database.

Note: VMware Data Services Manager removes the contents of the transfer/ directory when a database is recovered.

Importing Data

Note: Because the COPY command is not available to the default user in PostgreSQL version 10.x, the db-admin user can import data using only the pg_restore command.

You can copy data files to the database to locally load data into the database, potentially avoiding network latency or connection issues that could arise with a remote operation.

To import data locally, the data file(s) must reside on the database. You can use ssh or other similar command to copy the data file(s) to the transfer/ directory on the database.

For example, the following command copies a local file named datafile.csv to the $HOME/transfer directory for the db-admin OS user on the database:

$ scp datafile.csv db-admin@<db-vm>:transfer

When you reference a file that you have copied to the database in an import operation, you must specify the absolute path to the file.

Examples

Load a data file on the database file system into a PostgreSQL table:

db-admin@<db-vm>$ psql -c "COPY <table-name> (<column-name>[, ...]) FROM '/home/db-admin/transfer/<data-file-name>'"

Use cat and the psql \copy command to stream a file and load it into a PostgreSQL table:

db-admin@<db-vm>$ cat transfer/<data-file-name> | psql -c "\copy <table-name> (<column-name>[, ...]) FROM stdin"

Use the pg_restore command to load a dump file into a database:

db-admin@<db-vm>$ pg_restore -d <database-name> transfer/<dump-file-name>

Exporting Data

Note: Because the COPY command is not available to the default user in PostgreSQL version 10.x, the db-admin user can export data using only the pg_dump command.

You may choose to export data from a database to examine the data or to load it into another database. Using PostgreSQL tools, you can export data from one or more PostgreSQL tables, or back up an entire database to a file.

When you export data, the exported data file will be written to the database. You can use ssh or other similar command to copy the data file(s) from the database to your local host:

$ scp db-admin@<db-vm>:transfer/<export-file-name> ./localdir/

When you reference a file on the database in a PostgreSQL export operation, you must specify the absolute path to the file.

Examples

Export table data to the database file system:

db-admin@<db-vm>$ psql -c "COPY <table-name> TO '/home/db-admin/transfer/<export-file-name>'"

Use the pg_dump command to create a logical backup of a database and write the file to the database file system:

db-admin@<db-vm>$ pg_dump -F -t <database-name> > transfer/<dump-file-name>