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. Sharded PostgreSQL uses a PostgreSQL protocol, enabling you to configure sharding rules and run database queries using the psql client. Sharding rules are configured through the admin console (SPQR router admin console). When connecting to the administrator console, put spqr-console for user name and spqr-console for database name.

To get started:

1. Create a Sharded PostgreSQL cluster.
2. Create shards in the Sharded PostgreSQL cluster.
3. Set up your environment.
4. Set up sharding rules.
5. 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, in the top panel, click and select the cloud.

   2. To the right of the cloud name, click .

   3. Select Create folder .

      

   4. 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.

   5. Optionally, specify the description for your folder.

   6. 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.

   7. Click Create.

      

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

   Note

   If you cannot 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 Advanced settings, 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 shards in the clusterCreate shards in the cluster

Create two shards in the Managed Service for Sharded PostgreSQL cluster. To create a shard:

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.

Set up your environmentSet up your environment

Set up sharding rulesSet up sharding rules

1. Connect to the administrator console:

   Linux (Bash)/macOS (Zsh)

   Windows (PowerShell)

   psql "host=<host_FQDN> \
        port=6432 \
        sslmode=verify-full \
        sslrootcert=~/.postgresql/root.crt \
        dbname=spqr-console \
        user=spqr-console"
   

   Learn more about connecting to a Managed Service for Sharded PostgreSQL cluster.

   & "C:\Program Files\PostgreSQL\<PostgreSQL_major_version>\bin\psql.exe" `
       --host=<host_FQDN> `
       --port=6432 `
       --username=spqr-console `
       --dbname=spqr-console `
       --set=sslmode=verify-full `
       --set=sslrootcert=$HOME\.postgresql\root.crt
   

   Learn more about connecting to a Managed Service for Sharded PostgreSQL cluster.

2. Create a sharding rule:

   CREATE DISTRIBUTION ds1 COLUMN TYPES int;
   

   A sharding rule named ds1 will be created for int type columns.

3. Link the table to the rule and specify the sharding key:

   ALTER DISTRIBUTION ds1 ATTACH RELATION customers DISTRIBUTION KEY id;
   

   The customers table will be linked to the ds1 rule. The id column is the sharding key for the customers table.

4. Create key value ranges to distribute data across shards:

   CREATE KEY RANGE krid2 FROM 1000 ROUTE TO <shard_2_name> FOR DISTRIBUTION ds1;
   CREATE KEY RANGE krid1 FROM 1 ROUTE TO <shard_1_name> FOR DISTRIBUTION ds1;
   

   Warning

   Create key value ranges ​starting with the largest value. Breaking this order will result in the key range krid2 intersects with key range krid1 in QDB error. For the range with the largest key value, the right boundary will equal infinity.

5. To exit the administrator console, run the \q command.

Send your requests to the databaseSend your requests to the database

1. Connect to the database:

   Linux (Bash)/macOS (Zsh)

   Windows (PowerShell)

   psql "host=<host_FQDN> \
        port=6432 \
        sslmode=verify-full \
        sslrootcert=~/.postgresql/root.crt \
        dbname=<DB_name> \
        user=<username> \
        target_session_attrs=read-write"
   

   Learn more about connecting to a Managed Service for Sharded PostgreSQL cluster.

   & "C:\Program Files\PostgreSQL\<PostgreSQL_major_version>\bin\psql.exe" `
       --host=<host_FQDN> `
       --port=6432 `
       --username=<username> `
       --dbname=<DB_name> `
       --set=target_session_attrs=read-write `
       --set=sslmode=verify-full `
       --set=sslrootcert=$HOME\.postgresql\root.crt
   

   Learn more about connecting to a Managed Service for Sharded PostgreSQL cluster.

2. Create a table named customers:

   CREATE TABLE customers (
       id INT,
       name VARCHAR,
       phone VARCHAR,
       acctbal NUMERIC
   );
   

3. Start the transaction:

   BEGIN;
   

4. Add rows:

   1. Add a row to the shard with a value range krid1:

      INSERT INTO customers (id, name, phone, acctbal) VALUES (28, 'John Doe', '123-45-67', 1500.50)
      

   2. Add a row to the shard with a value range krid2:

      INSERT INTO customers (id, name, phone, acctbal) VALUES (3200, 'David Smith', '555-66-77', -50.75)
      

5. Complete the transaction:

   COMMIT;
   

6. View rows by specifying the key value in the query, e.g.:

   SELECT * FROM customers WHERE id = 28;
   

   The SELECT * FROM customers; query will result in an error because the rows are distributed across different shards. You can only get rows from one shard at a time. To check the distribution of rows across shards, connect to each shard and view the added entries.

7. To exit the database, run the \q command.

What's nextWhat's next

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