VMware Data Services Manager supports provisioning databases running PostgreSQL versions 10.x, 11.x, 12.x, and 13.x.
When you create a PostgreSQL database with VMware Data Services Manager, 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 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
The following PostgreSQL
|shared_buffers||The amount of memory the database server can use for shared memory buffers.3||128 MB||No|
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
3 Be sure to consult the PostgreSQL documentation before you set the
To access a PostgreSQL database:
To provide access to a PostgresSQL database through a command line interface(CLI): 1. 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
To connect to the database, create a
.db-client-env file in the
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 VMware Data Services Manager when a database is provisioned. Environment variables that you can set in this file are as follows:
|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.|
.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-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:
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.
VMware Data Services Manager 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.|
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.
|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.
VMware Data Services Manager 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.|
VMware Data Services Manager 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 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.
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. 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.
VMware Data Services Manager 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 );
$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.
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>