Writing load balancer logs to PostgreSQL

1. In the management console, select Virtual Private Cloud.
2. Click Create network.
3. Specify the Name of the network: alb-logging-network.
4. In the Advanced field, select Create subnets.
5. Click Create network.

1. In the management console, select the appropriate folder.
2. At the top of the screen, go to the Service accounts tab.
3. Click Create service account.
4. In the Name field, enter alb-logging-service-account.
5. Add the functions.functionInvoker and editor roles.
6. Click Create.

1. In the management console, select Virtual Private Cloud.

2. Open the Security groups tab.

3. Create a security group for the load balancer:

   1. Click Create security group.

   2. Specify the Name of the group: alb-logging-sg-balancer.

   3. Select Network alb-logging-network.

   4. Under Rules, create the following rules using the instructions below the table:

      Traffic direction	Description	Port range	Protocol	Source / target	CIDR blocks
      Outgoing	any	All	Any	CIDR	0.0.0.0/0
      Incoming	ext-http	80	TCP	CIDR	0.0.0.0/0
      Incoming	ext-https	443	TCP	CIDR	0.0.0.0/0
      Incoming	healthchecks	30080	TCP	Load balancer healthchecks	—

      1. Select the Egress or Ingress tab.

      2. Click Add rule.

      3. In the Port range field of the window that opens, specify a single port or a range of ports that traffic will come to or from.

      4. In the Protocol field, specify the appropriate protocol or leave Any to allow traffic transmission over any protocol.

      5. In the Destination name or Source field, select the purpose of the rule:

         • CIDR: Rule will apply to the range of IP addresses. In the CIDR blocks field, specify the CIDR and masks of subnets that traffic will come to or from. To add multiple CIDRs, click Add CIDR.
         • Security group: Rule will apply to the VMs from the current group or the selected security group.
         • Load balancer healthchecks: Rule allowing a load balancer to health check VMs.

      6. Click Save. Repeat the steps to create all the rules from the table.

   5. Click Save.

4. Similarly, create a security group for the VM named alb-logging-sg-vms with the same alb-logging-network and the following rules:

   Traffic direction	Description	Port range	Protocol	Source / target	CIDR blocks
   Incoming	balancer	80	TCP	Security group	alb-logging-sg-balancer
   Incoming	ssh	22	TCP	CIDR	0.0.0.0/0

5. Similarly, create a security group for the PostgreSQL cluster named alb-logging-sg-cluster with the same alb-logging-network and the following rules:

   Traffic direction	Description	Port range	Protocol	Source / target	CIDR blocks
   Incoming	db	6432	TCP	CIDR	0.0.0.0/0

1. In the management console, select Managed Service for PostgreSQL.

2. Click Create cluster.

3. In the Cluster name field, enter alb-logging-cluster.

4. In the Environment field, select PRODUCTION.

5. In the Version field, select 12.

6. Under Host class:

   • Specify the Intel Cascade Lake platform.
   • Select the burstable type.
   • Specify the b2.medium class.

   Warning

   The b2.medium class was only selected as part of testing. In real projects, we do not recommend using hosts with a guaranteed vCPU share under 100%.

7. Under Size of storage:

   • Select network-ssd.
   • Set the size to 10 GB.

   Note

   The type and size of the disk should be selected according to the projects to be implemented. The above values are used for testing.

8. Under Database, specify the DB attributes:

   • Database name. It must be unique within the folder and contain only Latin letters, numbers, and underscores.
   • DB owner username. It may only contain Latin letters, numbers, and underscores.
   • User password. It must be from 8 to 128 characters long.

   For the database created with the cluster, the character set and collate settings are specified as LC_CTYPE=C and LC_COLLATE=C. You cannot change these settings after the database is created, but you can create a new database with the right settings.

9. Under Network settings:

   • Select alb-logging-network.
   • Select the alb-logging-sg-cluster security group.

10. Under Hosts, add a host to be accessible from outside Yandex Cloud. To do this, enable Public access.

11. Under Advanced settings, enable Access from the management console and Serverless access.

12. Leave the default values in all other fields.

13. Click Create cluster.

After creating the cluster, you will be automatically redirected to the Clusters page.

1. Wait until alb-logging-cluster changes its status to Alive, then select this cluster.

2. Go to the SQL tab.

3. Select the user that you created together with the cluster and enter their password.

4. Select the database you created along with the cluster and click Connect.

5. In the edit window, enter the following query:

   CREATE TABLE load_balancer_requests (
       type            varchar(24) NOT NULL,
       "time"          timestamptz NOT NULL,
       http_status     varchar(4) NOT NULL,
       backend_ip      varchar(40) NULL,
       request_time    numeric NULL
   );
   

6. Click Execute.

7. Wait until a message confirms that the query is complete.

1. In the management console, select Compute Cloud.

2. Open the Instance groups tab. Click Create group of virtual machines.

3. Enter the instance group name: alb-logging-ig.

4. In the Service account field, select the account you previously created.

5. Under Allocation, select multiple availability zones to ensure fault tolerance of your hosting.

6. Under Instance template, click Define.

7. Under Boot disk image, open the Marketplace tab and click Show all Marketplace products. Select LEMP and click Use.

8. Under Computing resources:

   • Select the VM's platform.
   • Specify the required number of vCPUs and the amount of RAM.

   This minimum configuration is enough for functional website testing:

   • Platform: Intel Cascade Lake.
   • Guaranteed vCPU performance: 5%.
   • vCPU: 2.
   • RAM: 1 GB.

9. Under Network settings, select the Network named alb-logging-network that you created earlier and its subnets.

10. In the Public address field, select Auto.

11. Select the alb-logging-sg-vms security group created earlier.

12. Specify the data required for accessing the VM:

    • Enter the username into the Login field.

    • In the SSH key field, paste the contents of the public key file.

      You need to create a key pair for the SSH connection yourself. See the section on how to connect to VMs via SSH.

    Alert

    Once created, the VM gets an IP address and a host name (FQDN) for connections. If you selected No address in the Public address field, you will not be able to access the VM from the internet.

13. Click Save.

14. Under Scaling, enter the Size of the instance group: 2.

15. Under Integration with Application Load Balancer, select Create target group and enter alb-logging-tg as the group name. You can read more about target groups here.

16. Click Create.

1. In the management console, select the folder where you want to create your log group.
2. Select Cloud Logging.
3. Click Create group.
4. Enter a name, such as alb-logging-group, and description for the log group.
5. Set the log group record retention period.
6. Click Create group.

1. In the management console, select Application Load Balancer.
2. In the left-hand panel, select Backend groups. Click Create backend group.
3. Enter Name of the backend group: alb-logging-bg.
4. Under Backends, click Add.
5. Enter the backend Name: alb-logging-backend.
6. In the Target groups field, select the alb-logging-tg group.
7. Specify Port that backend VMs will use to receive incoming traffic from the load balancer: 80.
8. Click Add health check.
9. Specify Port the backend VMs will use to accept health check connections: 80.
10. Specify Path the load balancer will access for health checks: /.
11. Click Create.

1. In the management console, select Application Load Balancer.
2. In the left-hand panel, select HTTP routers. Click Create HTTP router.
3. Specify the HTTP router Name: alb-logging-router.
4. Click Add virtual host.
5. Specify the virtual host Name: alb-logging-host.
6. Click Add route.
7. Specify the route Name: alb-logging-route.
8. In the Backend group field, select the alb-logging-bg group.
9. Click Create.

1. In the management console, select Application Load Balancer.
2. Click Create L7 load balancer and select Manual.
3. Enter the load balancer Name: test-load-balancer.
4. Under Network settings, select alb-logging-network and thealb-logging-sg-balancer security group that you created earlier.
5. Under Log settings, specify alb-logging-group.
6. Under Listeners, click Add listener.
7. Enter the listener Name: alb-logging-listener.
8. In the Protocol field, select HTTPS.
9. In the HTTP router field, select the alb-logging-router router you created earlier.
10. Click Create.

1. In the management console, select Cloud Functions.

2. Click Create function.

3. Enter a function name: alb-logging-function.

4. Click Create. After creating the function, you will be automatically redirected to the Editor page.

5. Select Python 3.8 as the runtime environment and click Continue.

6. Clear the file editing area and paste the following code into it:

   Function code

   import os
   import logging
   import psycopg2
   import json
   
   logger = logging.getLogger()
   logger.setLevel(logging.INFO)
   verboseLogging = eval(os.environ['VERBOSE_LOG'])
   if verboseLogging:
       logger.info('Loading handler function')
   
   def handler(event, context):
       statusCode = 500
       
       if verboseLogging:
           logger.info(event)
           logger.info(context)
       
       connection_string = (
           "host='{db_hostname}' port='{db_port}' dbname='{db_name}' "
           "user='{db_user}' password='{db_password}' sslmode='require'"
       ).format(
           db_hostname=os.environ['DB_HOSTNAME'],
           db_port=os.environ['DB_PORT'],
           db_name=os.environ['DB_NAME'],
           db_user=os.environ['DB_USER'],
           db_password=os.environ['DB_PASSWORD']
       )
           
       if verboseLogging:
           logger.info(f'Connecting: {connection_string}')
   
       conn = psycopg2.connect(connection_string)
       cursor = conn.cursor()
   
       messages = event['messages'][0]['details']['messages']
   
       for message in messages:
           alb_message = message['json_payload']
           alb_message['table_name'] = 'load_balancer_requests'
           insert_statement = (
               'INSERT INTO {table_name} ' 
               '(type, "time", http_status, backend_ip, request_time) ' 
               'VALUES(\'{type}\', timestamptz \'{time}\', \'{http_status}\', ' 
               '\'{backend_ip}\', {request_processing_times[request_time]});'
           ).format(**alb_message)
   
           if verboseLogging:
               logger.info(f'Exec: {insert_statement}')
           try:
               cursor.execute(insert_statement)
               statusCode = 200
           except Exception as error:
               logger.error(error)
           
           conn.commit()
           
       cursor.close()
       conn.close()
   
       return {
           'statusCode': statusCode,
           'headers': {
               'Content-Type': 'text/plain'
           }
       }
   

7. Specify the following version parameters:

   • Timeout, sec: 10.
   • Memory: 128 MB.

8. Select the alb-logging-service-account service account you created earlier. On behalf of this account, the function will write data to the DB.

9. Add environment variables:

   • VERBOSE_LOG: Parameter that displays detailed information about the function run. Enter the True value.
   • DB_HOSTNAME: Name of the PostgreSQL database host to connect to.
   • DB_PORT: Port to connect to.
   • DB_NAME: Name of the database to connect to.
   • DB_USER: Username for the connection.
   • DB_PASSWORD: Password that you entered when creating your cluster.

   To define the values of connection parameters:

   1. In the management console, select Managed Service for PostgreSQL.
   2. Select the alb-logging-cluster cluster.
   3. Click in the line with the database you need.
   4. Select Connect.
   5. On the Shell tab, find a sample connection string.
   6. Move the values of the host, port, dbname, and user variables to the appropriate Value field for the function environment variables.

10. Click Save changes.

1. In the management console, select the folder where you want to create a trigger.

2. Select Cloud Functions.

3. In the left-hand panel, select Triggers.

4. Click Create trigger.

5. Under Basic settings:

   • Enter a trigger name, e.g., alb-logging-trigger.
   • In the Type field, select Cloud Logging.
   • In the Launched resource field, select Function.

6. Under Cloud Logging settings:

   • In the Log group field, select alb-logging-group.
   • In the Resource types field, specify alb.loadBalancer.

7. Under Batch message settings:

   • In the Waiting time, s field, specify 15.
   • In the Batch size field, specify 10.

8. Under Function settings, select the function you previously created, and specify:

   • Tag of the function version: $latest.
   • Service account you previously created.

9. Click Create trigger.