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. In the list of services, select Identity and Access Management.
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 group Name: alb-logging-sg-balancer.

   3. Select the 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.

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

      4. In the Protocol field, specify the appropriate protocol or keep 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 subnet masks that traffic will come to or from. To add multiple CIDRs, click Add.
         • 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 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 selected for testing only. In real projects, we do not recommend using hosts with a guaranteed vCPU share less than 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. Navigate 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 together 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 the 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:

    • Under Login, enter a username.

    • 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 the backend group Name: 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 backend VMs will use to receive incoming traffic from the load balancer at: 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 use 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: alb-logging-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 the 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: 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 these environment variables:

   • VERBOSE_LOG: Parameter displaying detailed information about the function. Type in True.
   • DB_HOSTNAME: Name of the PostgreSQL database host to connect to.
   • DB_PORT: Port for connection.
   • DB_NAME: Name of the database to connect to.
   • DB_USER: Username for the connection.
   • DB_PASSWORD: Password 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. In the line with the database you need, click .
   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 name for the trigger, 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 created earlier.

9. Click Create trigger.