Setting up a Yandex Managed Service for PostgreSQL connection from a container in Serverless Containers

To connect to the Managed Service for PostgreSQL cluster from Serverless Containers:

1. Prepare files for a Docker image.
2. Create a Docker image in the registry.
3. Create and set up a container in Serverless Containers.

If you no longer need the resources you created, delete them.

Required paid resourcesRequired paid resources

• Managed Service for PostgreSQL cluster: computing resources allocated to hosts, storage and backup size (see Managed Service for PostgreSQL pricing).
• Public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).
• Container Registry registry: Storing created Docker images and leveraging the vulnerability scanner (see Container Registry pricing).
• Serverless Containers container: Number of container calls, idle time of provisioned instances, and computing resources allocated to run the container (see Container Registry pricing).

Getting startedGetting started

1. If you do not have Docker yet, install it. Make sure Docker Engine is running.
2. If you do not have a Managed Service for PostgreSQL cluster, create one. You can connect from Serverless Containers regardless of the public access settings for the cluster hosts.

Prepare files for a Docker imagePrepare files for a Docker image

1. In your local directory, create an Ubuntu-based Dockerfile. Container setup depends on whether public access to the cluster hosts is enabled:

   Without public access

   With public access

   FROM ubuntu:latest
   
   RUN apt-get update && \
       apt-get install postgresql-client --yes
   
   COPY pg-version.sh pg-version.sh
   
   RUN chmod +x pg-version.sh
   
   ENTRYPOINT ["/pg-version.sh"]
   

   FROM ubuntu:latest
   
   RUN apt-get update && \
       apt-get install wget postgresql-client --yes && \
       mkdir --parents ~/.postgresql && \
       wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" \
       --output-document ~/.postgresql/root.crt && \
       chmod 0600 ~/.postgresql/root.crt
   
   COPY pg-version.sh pg-version.sh
   
   RUN chmod +x pg-version.sh
   
   ENTRYPOINT ["/pg-version.sh"]
   

2. Place the pg-version.sh script in the same working directory. The script connects to the database and requests the PostgreSQL version. The connection string in the script depends on whether public access to the cluster hosts is enabled:

   Without public access

   With public access

   #!/bin/bash
   
   echo "$0: Start: $(date)"
   
   echo "Viewing the PostgreSQL Server Version"
   
   export PGPASSWORD='<password>'
   psql -h <host_FQDN> -p 6432 -U <username> -d <DB_name> -c 'select version();'
   
   echo "$0: End: $(date)"
   

   #!/bin/bash
   
   echo "$0: Start: $(date)"
   
   echo "Viewing the PostgreSQL Server Version"
   
   export PGPASSWORD='<password>'
   psql -h <host_FQDN> -p 6432 --set=sslmode=require -U <username> -d <DB_name> -c 'select version();'
   
   echo "$0: End: $(date)"
   

   In your script, specify the following:

   • FQDN of your cluster host.
   • Username for connection.
   • Password.
   • Name of the database to connect to.

Create the Docker image in the registryCreate the Docker image in the registry

1. Create a registry in Yandex Container Registry.

2. Build the Docker image by running the following command from the directory containing the Dockerfile:

   docker build . \
       -t cr.yandex/<registry_ID>/ubuntu:pgconnect
   

   Check that the image with the specified name appeared in the local repository:

   docker images
   

3. Authenticate with the registry:

   1. Get an IAM token.

   2. Run this command:

      docker login \
      --username iam \
      --password <IAM_token> \
      cr.yandex
      

   For other methods, see Authentication in Container Registry.

4. Push the Docker image to the registry:

   docker push cr.yandex/<registry_ID>/ubuntu:pgconnect
   

Create and set up a container in Serverless ContainersCreate and set up a container in Serverless Containers

1. Create a service account named docker-puller with the container-registry.images.puller role.

2. Create a container named demo-pg-connect in Serverless Containers.

3. In the container revision settings, specify:

   • Link to the previously created image in the registry, in the Image URL field.
   • The docker-puller service account, in the Service account field.
   • Network hosting the Managed Service for PostgreSQL cluster, in the Network field. If public access is enabled for the cluster, specifying a network is optional.

4. Click Create revision.

5. Copy the container invocation link from the General information section in the management console.

6. Invoke the container by running this command:

   curl --header "Authorization: Bearer $(yc iam create-token)" <invocation_link>
   

7. Go to the Logs section and make sure the container logs contain information about the PostgreSQL version.

Delete the resources you createdDelete the resources you created

To stop paying for the resources you created:

1. Delete the Serverless Containers container.
2. Delete the Docker images from the registry.
3. Delete the registry.
4. Delete the service account.
5. Delete the Managed Service for PostgreSQL cluster.

To delete the created Docker image from the local repository, run this command:

docker rmi cr.yandex/<registry_ID>/ubuntu:pgconnect