Data Management for VMware Tanzu supports provisioning databases running PostgreSQL versions 10.x, 11.x, 12.x, and 13.x.
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.
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 *. |
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 | 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.2
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 Data Management for VMware Tanzu: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 |
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 lc_xxx
options.
To access a PostgreSQL database:
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
The .db-client-env file must contain the PostgreSQL connection parameters to the default values set by Data Management for VMware Tanzu 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.
Refer to SSL Support in the PostgreSQL documentation for more information on using SSL/TLS with PostgreSQL.
verify-full
TLS 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:
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:
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. |
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.
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. |
pg_read_server_files
and
pg_write_server_files
default 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:
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. |
Data Management for VMware Tanzu grants the default user the permissions required to execute extension management functions as described in Working with PostgreSQL Extensions.
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.
postgis
extension by default.
An extension must be explicitly enabled in each database in which it is to be used. Only PostgreSQL SUPERUSER
s 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:
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.
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.
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
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 );
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.
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.
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.
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>
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.
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>