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

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

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

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

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 the Serverless Containers container regardless of the public access settings to cluster hosts.

Prepare files for a Docker imagePrepare files for a Docker image

1. In the local directory, create an Ubuntu-based Dockerfile. The container environment configuration depends on whether there is public access to the cluster hosts:

   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. Put 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 there is public access to the cluster hosts:

   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)"
   

   Specify the following in the script:

   • FQDN of your cluster host.
   • Username for connection.
   • User 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 command in the working directory with the Docker file:

   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 in 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 Serverless Containers containerCreate and set up a Serverless Containers container

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

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

3. In the container revision settings, specify:

   • Reference to the previously created image in the registry, in the Image URL field.
   • In the Service account docker-puller field, specify Service account.
   • Network in which the Managed Service for PostgreSQL cluster is located, in the Network field. You do not need to specify the network if public access is enabled for the cluster.

4. Click Create revision.

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

6. Invoke the container by running the command:

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

7. Go to the Logssection 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 the command:

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