Managing databases in Yandex MPP Analytics for PostgreSQL

To get a list of cluster databases, run this command:

SELECT * FROM pg_database ORDER BY datname;

To create a database, run this command:

CREATE DATABASE <DB_name> <parameter_name> <parameter_value>;

There can be several parameter name: parameter value pairs.

Available parameters:

• TEMPLATE: Database template. All the DB contents specified in the TEMPLATE parameter will be copied to the new database, including the data tables. The default value is template1.
• CONNECTION LIMIT: Maximum number of connections.

For more information about the available parameters, see the official Greenplum® documentation.

For example, to create a database named db1 that can process a maximum of 55 active connections, run this command:

CREATE DATABASE db1 CONNECTION LIMIT 55;

To create a copy of a database in the same cluster, run this command:

CREATE DATABASE <copy_name> TEMPLATE <name_of_DB_to_copy>;

To learn the size of a database, run this command:

SELECT pg_size_pretty(pg_database_size('<DB_name>'));

To rename a database, run this command:

ALTER DATABASE <DB_name> RENAME TO <new_DB_name>;

To change the maximum number of connections, run this command:

ALTER DATABASE <DB_name> CONNECTION LIMIT <new_number_of_connections>;

Changing a database-level setting:

ALTER DATABASE <DB_name> SET <setting_name> TO <new_setting_value>;

Removing a database-level setting value:

ALTER DATABASE <DB_name> RESET <setting_name>;

Removing all database-level setting values:

ALTER DATABASE <DB_name> RESET ALL;

To delete a database, run this command:

DROP DATABASE <DB_name>;