PostgreSQL Description

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.

How It Works

To create a cluster, you need to select:

• name;
• pool;
• 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 a Database

After creating a cluster, you must first create a user, and then create a database. You must select an owner user when creating a database.

To create a database, from the Control panel:

1. Go to the Databases tab.
2. Click Create.
3. Enter the database name, select the user owner, 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, learn more in the Managing users instructions.

Database Extensions

You can connect extensions to PostgreSQL databases.

Connecting Extensions to the Database

Extensions are connected to each database separately.

To connect an extension, from the Control panel:

1. Go to the Cloud platform ⟶ Managed Databases section.
2. Select the desired cluster and open the Databases tab on its page.
3. In the Extensions block of the desired database card, click Add.
4. Select an extension from the list and save your changes.

Dependent Extensions

Some extensions depend on others (are dependent) — they will not work unless you connect the extensions on which they depend. When connecting a dependent extension to the database, the one it depends on will automatically be connected.

A dependent extension can be deleted separately. To delete an extension that another extension depends on, you must first delete the dependent extension.

Dependent extensions are listed below.

A List of Extensions

Available extensions, including dependent extensions:

• address_standardizer;
• address_standardizer_data_us;
• autoinc;
• bloom;
• btree_gin;
• btree_gist;
• citext;
• cube;
• dict_int;
• dict_xsyn;
• earthdistance, depends on cube;
• fuzzystrmatch;
• hstore;
• intarray;
• isn;
• jsquery;
• lo;
• ltree;
• moddatetime;
• pg_partman;
• pg_repack;
• pg_stat_kcache, depends on pg_stat_statements;
• pg_stat_statements;
• pg_trgm;
• pgcrypto;
• pgrouting, depends on postgis;
• pgrowlocks;
• postgis;
• postgis_tiger_geocoder, depends on postgis;
• postgis_topology, depends on postgis;
• postgres_fdw;
• seg;
• tablefunc;
• unaccent;
• uuid-ossp;
• xml2;
• ip4r;
• pgTAP;
• prefix;
• rum.

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.