Description PostgreSQL

Technical Characteristics

Three DBMS versions are available: PostgreSQL 11, 12, and 13.

Fixed and custom node configurations are available for creating a database cluster.

Fixed configurations with a predefined amount of resources:

  • 2 vCPU, 4 GB RAM, 32 GB local disk;
  • 2 vCPU, 8 GB RAM, 64 GB local disk;
  • 4 vCPU, 16 GB RAM, 128 GB local disk;
  • 8 vCPU, 32 GB RAM, 256 GB local disk;
  • 16 vCPU, 64 GB RAM, 512 GB local disk;
  • 32 vCPU, 128 GB RAM, 1024 GB local disk.

In custom configurations, you can select the amount of resources:

  • vCPU — from 1 to 8 cores;
  • RAM — from 4 GB to 64 GB;
  • local disk — from 15 GB to 512 GB.

Please note that about 5 GB is reserved on the local disk for the operating system, service components, and log storage. The rest of the volume is available for hosting databases.

Database clusters can only be created in private and public networks:

  • Private network — you can only connect to the database cluster from the selected private network;
  • Public network — you can connect to the database cluster from the Internet.

DBMS Settings

DBMS settings are selected specifically to ensure high database performance. The settings parameters depend on the selected computing resources configuration for the database cluster.

If the database cluster configuration is changed, the following parameters will be also changed automatically:

shared_buffers 
effective_cache_size 
maintenance_work_mem
max_worker_processes 
max_parallel_workers 
autovacuum_max_workers 
vacuum_cost_limit 
max_parallel_workers_per_gather 
max_maintenance_workers 

To view all DBMS settings, connect to the database and run the show all command.

How It Works

To create a cluster, you need to select:

  • name;
  • region;
  • DBMS version;
  • node configuration;
  • the number of replicas (if the cluster is fault tolerant);
  • the subnet where the cluster will be created.

After the creation process starts, a new cluster will appear in the interface. You can manage your cluster on its page, which will become available after the cluster status changes to Active.

Only the cluster itself is available to users — there is no access to the cluster nodes, since they are on the Selectel side.

All the functionality for managing the cluster is inside it. From the cluster page, you can create databases, users, assign access rights, and scale the cluster.

You cannot change the server addresses or the subnet that the cluster is connected to.

After creating a cluster, you need to create users and databases. Then you need to assign database access rights to users. You can create up to 50 databases and up to 50 users in a cluster. All names must be unique.

When connecting to the cluster, you need to provide the database name, username, password, and cluster port and addresses. Learn more about connecting to a cluster.

All users in the cluster have the same rights.

Creating Users and Databases

After creating a cluster, the user can create databases and users within this cluster.

First, you need to create a user. This is necessary because when creating a database, you must select an owner-user.

Ownership of objects created by remote users will be transferred to the database owner.

Database owner has access to this database only while being its owner. If you want to change the owner, but so that the former owner still has access, you need to grant them access to the database.

Follow these steps to create a user:

  1. Go to the database cluster page.
  2. Go to the Users tab.
  3. Click Create.
  4. Enter the user’s name and set a password. Please note that you need to save your password, as it will not be stored in the Control panel.

Follow these steps to create a database:

  1. Go to the Databases tab.
  2. Click Create.
  3. Enter the database name, select the owner-user, and configure LC_CTYPE and LC_COLLATE.

The character set locale is responsible for the classification of characters and their case differences. The sorting locale determines the settings for comparing strings and characters, and also affects sorting. You need to configure locales so that the data in the databases is processed correctly. For more information about locales, see the PostgreSQL documentation.

After creating databases and users, you can provide users access to specific databases.

Connecting Extensions to the Database

Follow these steps to connect extensions via the interface:

  1. Go to the Databases tab within a cluster.
  2. Expand the accordion with settings in the database card.
  3. Click Add next to Extensions.
  4. Select all required extensions from the list and save the settings.

Extensions are connected to each database separately.

A List of Available Extensions

Available extensions:

Connection Pooler

PostgreSQL clusters use PgBouncer for managing connection poolers. PgBouncer minimizes overhead when configuring new connections to PostgreSQL. You can configure the connection pool on the Settings page when creating a cluster or during its operation.

Pooling mode

Strategy for connecting the application server to the database.

In order not to compromise transaction semantics for connection pooling, pgbouncer supports several types of pooling:

  • Transaction — connection is assigned to a client for the duration of a transaction. This mode allows each client to use the pool for the duration of a single transaction at a time.

    If there are not enough available connections in the pooler to handle the transactions submitted, the additional transactions are queued to run once connections are available.

    Transaction mode is useful when you have a large number of clients that maintain connections.

    This mode is enabled by default.

  • Session — connection is assigned when a client connects. This mode allows clients to continue issuing requests until they disconnect from the database, which maintains the client’s connection the entire time.

    If there are not enough available connections in the pool to handle all the connecting clients, the additional clients are queued up to connect once an existing client disconnects.

    Session mode is useful when your application uses prepared statements, advisory locks, listen/notify, or other features which operate on a session level rather than on a transaction level.

  • Statement — multi-statement transactions are not allowed.

    This mode is the most restrictive one and only allows a single statement at a time before moving on to the next in the queue. This means queries with multiple statements are not allowed and will fail.

Statement mode is useful when you force autocommit for each client, meaning each transaction is limited to a single statement.