PostgreSQL on VMware Data Services Manager

Supported PostgreSQL Versions

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

VMware Data Services Manager 2.0 does not support PostgreSQL version 10.x and 11.x.

Configuration

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

Database Basic Information

The Basic Information properties identify the database version, the name of the default database, the replica mode, and the database administrator user credentials.

Property Name	Description
Database Version	Use the dropdown menu to select from the available template versions of the database.
Instance Name	Enter a unique name for the database cluster.
Replica Mode	Configure the number of nodes to create for this cluster. Select one of the following: Single Server - One node with no replicas. You can use vSphere HA. Single vSphere Cluster - Multiple nodes co-located on a single vSphere cluster providing non-disruptive upgrades. You can select a three-node or five-node topology.
Topology	Specify configuration for the database nodes depending on the replica mode you selected.	Database Name	The name of the default database.
Database Name	Nane of the database engine to be created.
Admin Username	The database administrator user name.

Database Advanced Settings

The Advanced Settings properties control certain runtime characteristics of the PostgreSQL database. The following table lists several of the settings you can configure, but there can be more.

Property Name	Description	Default Value	Restart Required on Update?
TimeZone	The time zone for the PostgreSQL server.¹	UTC	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 cannot change the default parameter.	C.UTF-8	N/A
shared_buffers	The amount of memory the database server can use for shared memory buffers.³	25% of total RAM	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 shared_buffers option.

Connecting to a PostgreSQL Database

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.

Install psql or other tool, such as DBeaver, pgAdmin4, Toad, and so on, on your system.
In DSM console, navigate to Databases > Postgres tab, and click the database you want to access.
On the Summary tab, click COPY CONNECTION STRING.
To connect to the database, provide the connection string to the tool you are using.

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.

About the Permissions Assigned to the Default Database User

When you create a database, the default PostgreSQL database user (role) name is pgadmin, and it has a SUPERUSER privileges.

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.

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.

For more information about extensions, see the VMware SQL with Postgres for Kubernetes documentation.