Getting started with Managed Service for Sharded PostgreSQL

With Managed Service for Sharded PostgreSQL, you can create and maintain sharded PostgreSQL (SPQR) clusters in the Yandex Cloud infrastructure.

To get started:

1. Create a Sharded PostgreSQL cluster.
2. Create a shard in the Sharded PostgreSQL cluster.
3. Connect to the DB.
4. Send your requests to the database.

Getting startedGetting started

1. Navigate to the management console and log in to Yandex Cloud or sign up if not signed up yet.

2. If you do not have a folder yet, create one:

   1. In the management console, select the appropriate cloud from the list on the left.

   2. At the top right, click Create folder.

   3. Give your folder a name. The naming requirements are as follows:

      • It must be from 2 to 63 characters long.
      • It can only contain lowercase Latin letters, numbers, and hyphens.
      • It must start with a letter and cannot end with a hyphen.

   4. Optionally, specify the description for your folder.

   5. Select Create a default network. This will create a network with subnets in each availability zone. Within this network, you will also have a default security group, within which all network traffic will be allowed.

   6. Click Create.

3. Assign the vpc.user and managed-spqr.editor roles for the folder to your Yandex Cloud account to be able to create a cluster.

   Note

   If you are unable to manage roles, contact your cloud or organization administrator.

4. You can connect to DB clusters from both inside and outside Yandex Cloud:

   • To connect from inside Yandex Cloud, create a Linux VM in the same network as the DB cluster.

   • To connect to the cluster from the internet, enable public access to hosts when creating the cluster.

   Note

   The next step implies connecting to the cluster from a VM. If your plan is to connect to the cluster from the internet, proceed to creating a cluster.

5. Connect to the VM over SSH.

Create a clusterCreate a cluster

1. In the management console, select the folder where you want to create a Sharded PostgreSQL cluster.

2. Select Yandex Managed Service for Sharded PostgreSQL.

3. Click Create cluster.

4. In the Cluster name field, enter a name for the cluster.

5. Select the PRODUCTION environment.

6. Select the sharding type:

   • Standard: Cluster will consist only of infrastructure hosts.
   • Advanced: Cluster will consist only of router hosts and, optionally, coordinator hosts.

7. Under Network settings:

   • Select the cloud network you created before you started out.
   • Select the default security group or create a new one.

8. Specify the computing resource configuration:

   • For standard sharding, specify the infrastructure host configuration under Infrastructure.
   • For advanced sharding, specify the router host configuration under Router.

   To specify your computing resource configuration:

   1. Select the host class. Host class determines the technical specifications of the VMs the cluster hosts will be deployed on.

   2. Under Storage:

      1. Select the disk type.
      2. Set the storage size.

   3. Under Hosts, specify the availability zones and subnets for the hosts that will be created together with the cluster.

      If you plan to connect to the cluster from the internet, enable Public access for the hosts.

9. Optionally, under Coordinator, enable Coordinator and specify the coordinator host configuration.

10. Under Database, specify:

    • Database name. It must be unique within the folder.
    • Database owner username.
    • Password.

11. Under User, specify the password for the Sharded PostgreSQL console.

12. Click Create cluster.

13. Wait until the cluster is ready to work: its status will change to Running, and its state to Alive. To check its state, hover over the cluster status in the Availability column.

Create a shard in the clusterCreate a shard in the cluster

1. In the management console, select the folder where you created the Sharded PostgreSQL cluster.

2. Create a Managed Service for PostgreSQL cluster in the same cloud network as the Sharded PostgreSQL cluster.

3. Open your Sharded PostgreSQL cluster and go to the  Shards tab.

4. Click Create shard in the top-right corner of the page.

5. In the window that opens:

   1. Specify the shard Name.

   2. In the Managed Service for PostgreSQL cluster field, select the PostgreSQL cluster you previously created.

      The Managed Service for PostgreSQL cluster must be in the same folder and cloud network as the Managed Service for Sharded PostgreSQL cluster.

Connect to the DBConnect to the DB

To connect to the Sharded PostgreSQL cluster database:

Send your requests to the databaseSend your requests to the database

1. Create a table named customers in the cluster database:

   CREATE TABLE IF NOT EXISTS customers (
       name VARCHAR,
       phone VARCHAR,
       acctbal NUMERIC
   );
   

2. Populate the table with data:

   INSERT INTO customers (name, phone, acctbal) VALUES
       ('John Doe', '123-45-67', 1500.50),
       ('Mary Johnson', '222-33-44', 3250.00),
       ('David Smith', '555-66-77', -50.75),
       ('Anna Davis', '111-22-33', 0.00),
       ('Paul Brown', '444-55-66', 780.30);
   

3. Get the number of rows in the table:

   SELECT COUNT(*) FROM customers;
   

   Result:

   count
   -------
       5
   (1 row)
   

What's nextWhat's next

• Read about the service concepts.
• Learn more about creating a cluster and connecting to a database.