Creating a PostgreSQL database

If you have a PostgreSQL cluster in your project, you can create a database in it. Use a PostgresqlDatabase resource to define the database. Set the database owner using the spec.owner field. It must match an existing PostgreSQL role. For details, see Creating a PostgreSQL user.

Using the CLIUsing the CLI

1. Create the PostgresqlDatabase resource file, e.g., using the touch postgresqldatabase.yaml command.

2. Open the file and paste the configuration below into it:

   Minimum configuration

   libc locale

   ICU locale

   builtin locale

   Schemas

   apiVersion: postgresql.stackland.yandex.cloud/v1alpha1
   kind: PostgresqlDatabase
   metadata:
     name: test-cluster-test-database
   spec:
     cluster: test-cluster # Name of PostgresqlCluster resource
     name: test-database # Name of the PostgreSQL database
     owner: test-user # Database owner role
   

   The manifest defines only the required fields, such as the cluster name, database name, and owner role.

   Configuration with the libc locale provider and the locale, localeCollate, and localeCType parameters.

   apiVersion: postgresql.stackland.yandex.cloud/v1alpha1
   kind: PostgresqlDatabase
   metadata:
     name: test-cluster-test-database
   spec:
     cluster: test-cluster
     name: test-database
     owner: test-user
     template: template1 # Template for the database
     encoding: UTF8 # Character encoding
     localeProvider: libc # Locale provider
     locale: en_US.UTF-8 # Default locale
     localeCollate: en_US.UTF-8 # String sorting rule
     localeCType: en_US.UTF-8 # Character classification rule
   

   Configuration with the icu locale provider and the icuLocale and icuRules parameters. Available in PostgreSQL and later.

   apiVersion: postgresql.stackland.yandex.cloud/v1alpha1
   kind: PostgresqlDatabase
   metadata:
     name: test-cluster-test-database
   spec:
     cluster: test-cluster
     name: test-database
     owner: test-user
     template: template1
     encoding: UTF8
     localeProvider: icu # Locale provider
     icuLocale: en-US # ICU locale name
     icuRules: "" # Additional ICU collation rules
   

   Configuration with the builtin locale provider and the builtinLocale parameter. Available in PostgreSQL and later.

   apiVersion: postgresql.stackland.yandex.cloud/v1alpha1
   kind: PostgresqlDatabase
   metadata:
     name: test-cluster-test-database
   spec:
     cluster: test-cluster
     name: test-database
     owner: test-user
     template: template1
     encoding: UTF8
     localeProvider: builtin # locale provider; available in PostgreSQL 17 and later
     builtinLocale: C.UTF-8 # built-in locale name
   

   Configuration with managed PostgreSQL schemas.

   apiVersion: postgresql.stackland.yandex.cloud/v1alpha1
   kind: PostgresqlDatabase
   metadata:
     name: test-cluster-test-database
   spec:
     cluster: test-cluster
     name: test-database
     owner: test-user
     allowConnections: true # Enabling connections to the database
     connectionLimit: -1 # Connection limit; -1 for no limit
     isTemplate: false # Using the database as a template
     tablespace: pg_default # Default tablespace
     schemas: # Managed schemas
       - name: test-schema
         owner: test-user
         state: present # present to create or support; absent to delete
   

   In the spec.schemas section, describe the database schemas by specifying the name, owner (optional), and state (present to create or absent to delete).

3. Apply the manifest: kubectl apply -f postgresqldatabase.yaml -n <project_name>. Optionally, you can specify the project name in the metadata.namespace resource property and skip it in the command.

Once the database is created, you cannot change the name, template, encoding, localeProvider, locale, localeCollate, localeCType, icuLocale, icuRules, builtinLocale, or collationVersion field.