Database management
You can add and remove databases, as well as view information about them.
Getting a list of cluster databases
- Go to the folder page and select Managed Service for PostgreSQL.
- Click on the name of the cluster you need and select the Databases tab.
If you don't have the Yandex.Cloud command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To get a list of databases in a cluster, run the command:
$ yc managed-postgresql database list
--cluster-name=<cluster name>
The cluster name can be requested with a list of clusters in the folder.
To get a list of cluster databases, use the list method.
Creating a database
You can create an unlimited number of databases in each Managed Service for PostgreSQL cluster.
By default, databases are created with the LC_COLLATE=C
and LC_CTYPE=C
encoding settings. This allows PostgreSQL to run queries with string data types more efficiently, but may sometimes work counter-intuitively (for example, with Cyrillic). These settings are covered in more detail in the PostgreSQL documentation.
Alert
The LC_COLLATE and LC_CTYPE settings of a database cannot be changed after its creation.
You can configure character collation and sorting settings for columns when you create and edit
specific tables. Learn more in the PostgreSQL documentation.
To create a database:
-
Go to the folder page and select Managed Service for PostgreSQL.
-
Click on the name of the cluster you need.
-
If the owner of the new database still doesn't exist, add the user.
-
Select the Databases tab.
-
Click Add.
-
Enter the database name, select its owner, and configure the character set.
If you don't have the Yandex.Cloud command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To create a database in a cluster:
-
See the description of the CLI's create database command:
$ yc managed-postgresql database create --help
-
Request a list of cluster users to select the owner of the new database:
$ yc managed-postgresql user list --cluster-name <cluster name>
If the required user is not in the list, create it.
-
Run the create database command. If needed, specify the character collation and sorting settings (default settings are
LC_COLLATE=C
andLC_CTYPE=C
):$ yc managed-postgresql database create <database name> --cluster-name <cluster name> --owner <username of the DB owner> --lc-collate ru_RU.UTF-8 --lc-type ru_RU.UTF-8
Managed Service for PostgreSQL runs the create database operation.
The cluster name can be requested with a list of clusters in the folder.
You can create a new database in a cluster using the create method.
Deleting a database
- Go to the folder page and select Managed Service for PostgreSQL.
- Click on the name of the cluster you need and select the Databases tab.
- Click in the line of the necessary DB and select Delete.
If you don't have the Yandex.Cloud command line interface yet, install and initialize it.
The folder specified in the CLI profile is used by default. You can specify a different folder using the --folder-name
or --folder-id
parameter.
To delete a database, run the command:
$ yc managed-postgresql database delete <database name>
--cluster-name=<cluster name>
The cluster name can be requested with a list of clusters in the folder.
You can delete a database using the delete method.
Warning
Before creating a new database with the same name, wait for the delete operation to complete, otherwise the database being deleted is restored. Operation status can be obtained with a list of cluster operations.