Data Management for VMware Tanzu supports provisioning databases running PostgreSQL versions 10.21.0, 11.16.0, 12.11.0, and 13.7.0.
When you create a PostgreSQL database with Data Management for VMware Tanzu, you configure certain PostgreSQL properties.
The Database Configuration properties identify the name of the default database, the database server port number, and the database administrator user credentials.
|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 adminstrator.|
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.1||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||Enable or disable 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.2
Note: You can set the
The following PostgreSQL
1 Run the following PostgreSQL query to obtain the possible time zone values:
SELECT name FROM pg_timezone_names;
2 Be sure to consult the PostgreSQL documentation before you set the
locale or related
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, Data Management for VMware Tanzu 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.
Data Management for VMware Tanzu assigns the following PostgreSQL privileges to the default user:
|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.|
Data Management for VMware Tanzu grants the default user membership in the following PostgreSQL default roles. Granting role membership conveys the privileges assigned to the role.
|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
|pg_write_server_files||Allows the user to write files to any accessible location on the database file system using
pg_write_server_filesdefault roles are not available to the default user in PostgreSQL version 10.x.
Data Management for VMware Tanzu grants the default user the permission to execute the PostgreSQL Server Signaling Functions:
|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.|
Data Management for VMware Tanzu grants the default user the permissions required to execute extension management functions as described in Working with PostgreSQL Extensions.
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, 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 Data Management for VMware Tanzu console as described in Locating the Database Connection Parameters.
Refer to SSL Support in the PostgreSQL documentation for more information on using SSL/TLS with PostgreSQL.
verify-fullTLS configuration for client applications connecting to a database.
You can configure a PostgreSQL database to require SSL/TLS client connections during creation or after provisioning as follows:
PostgreSQL extensions are built-in modules that provide additional functionality to a database. The extensions that are available to a Data Management for VMware Tanzu 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.
postgisextension 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. Data Management for VMware Tanzu 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.
Data Management for VMware Tanzu 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.
Data Management for VMware Tanzu provides the following UDFs to manage database extensions:
|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.
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>;
<user> with the PostgreSQL user name to which you want to grant access.
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;
\dx meta-command displays a list of the extensions created in the current database. You can run the command from any
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 );
Enabling SSH access to a database provides the
db-admin OS user the capability to use the native PostgreSQL tools psql, pg_dump, and pg_restore to import or export data directly to or from the database.
$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.
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.
transfer/directory when a database is recovered.
$HOME directory contains a file named
.db-client-env, the PostgreSQL tools can use the environment variable settings in the file to establish a database connection. This enables you to run the tools without the need to provide the PostgreSQL connection options to the command.
Environment variables that you can set in this file include:
|PGHOST||The host on which the PostgreSQL server is running.|
|PGDATABASE||The name of the PostgreSQL database to which to connect.|
|PGUSER||The PostgreSQL user name.|
|PGPASSWORD||The password for $PGUSER.|
.db-client-env file contents specified below set the PostgreSQL connection parameters to the default values set by Data Management for VMware Tanzu when a database is provisioned:
PGHOST=localhost PGDATABASE=dbaas PGUSER=dbaas PGPASSWORD=<default-password>
.db-client-env settings in place, running the
psql command with no other options attempts to connect to the
dbaas database on the PostgreSQL server running on the local host as the user named
COPYcommand is not available to the default user in PostgreSQL version 10.x, the
db-adminuser can import data using only the
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.
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>'"
cat and the
\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"
pg_restore command to load a dump file into a database:
db-admin@<db-vm>$ pg_restore -d <database-name> transfer/<dump-file-name>
COPYcommand is not available to the default user in PostgreSQL version 10.x, the
db-adminuser can export data using only the
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.
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>'"
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>