How to access PostgreSQL database by function calculation

Function Compute: Function calculation It is an event driven all tuoguan computing service. With function computing, you don't need to purchase and manage infrastructure such as servers, just write and upload code. Function calculation provides you with computing resources, runs tasks flexibly and reliably, and provides functions such as log query, performance monitoring and alarm. With function computing, you can quickly build any type of application and service, and only pay for the resources actually consumed by the task.

Access to PostgreSQL database refers to the insertion, query and other operations of database by writing code to call database driver database through TCP protocol in function calculation. In general, different function instances running in function calculation do not share state. Structured data can be persisted in the form of database to realize state sharing. Because the user function runs in the VPC of function calculation, and the user's database runs in the VPC to which the user belongs, accessing the database in the function calculation platform will involve cross VPC access scenarios. Let's first introduce its working mechanism.

Working mechanism

The principle and working mechanism of accessing PostgreSQL are exactly the same as accessing Mysql database. This article will not repeat it. For more details, please refer to Accessing Mysql database Working mechanism section in.

Configuration and function writing

Public configuration

Create VPC

  1. Sign in VPC console.
  2. See VPC Build a proprietary network Create VPC s and switches.

Create security group

stay Security group console Create a new security group, click create security group, set security group name, select VPC as network type, and select the VPC just created.

Note: when setting security group policy, you need to release the port of PostgreSQL instance and the configured VPC intranet IP segment in the exit direction.

Create and configure PostgreSQL instance

  1. To create an instance of PostgreSQL version of cloud database that is suitable for business needs, please refer to Apsaradb PostgreSQL.
    Note: to create an instance of PostgreSQL version of the cloud database, you need to select the same VPC instance configured with the function calculation. You can configure switches in different zones with the function calculation, because the intranets of switches in different zones under the same VPC instance are interconnected.
  1. After the creation is successful, click data security in the navigation bar on the left side of the instance information page.
  2. Click add white list group.
  3. In the pop-up dialog box, configure the network segment address of the VPC network where the function calculation is located in the white list input box.

    1. Sign in VPC console , find the VPC of the application in the VPC list, and click the VPC name to enter the VPC details page.
    2. Copy the IPv4 network segment of the VPC where the application is located.
    3. Paste the IPv4 network segment address of the VPC in the white list setting box in the group, and then click OK.
  4. Finally, visit the PostgreSQL database host as the intranet address of the instance. You can log in to Alibaba cloud console to view:

Function calculation configuration VPC

Note: the region of the function calculation service is the same as the region of the resource created in the public configuration.

  1. stay Function calculation console Create a service.
  2. In the VPC configuration option, select the VPC network, switch and security group you created in step 1.
  3. In permission configuration, select new role and click authorize. In the role quick creation page, click approve authorization.
  4. Click OK to finish the new service.

Function writing and debugging

The following is a demonstration of Python 3 development language access to PostgreSQL database.

Use Fun The tool installs dependencies and project deployment in the directory where the stored code and dependency modules are established.

Python3

  1. Create a directory locally to store code and dependent modules. Create a new template.yml file in the directory, such as / tmp/code/template.yml, as follows.

    ROSTemplateFormatVersion: '2015-09-01'
    Transform: 'Aliyun::Serverless-2018-04-03'
    Resources:
      PostgreSQL-test:
        Type: 'Aliyun::Serverless::Service'
        Properties:
          Description: This is PostgreSQL service
          Role: 'acs:ram::1986114430***:role/fc-public-test'
          SimpleRequest
          VpcConfig:
            VpcId: vpc-****
            VSwitchIds:
              - vsw-***
            SecurityGroupId: sg-***
          InternetAccess: true
        python-test:
          Type: 'Aliyun::Serverless::Function'
          Properties:
            Handler: 'index.handler'
            Initializer: 'index.initializer'
            Runtime: python3
            Timeout: 10
            MemorySize: 128
            CodeUri: './'
            EnvironmentVariables:
              HOST: pgm-bp1yawvyyu***.pg.rds.aliyuncs.com
              PASSWORD: Txd123**
              PORT: 1433
              DATABASE: test_123
              USER: ***
    
  2. The contents of creating the Funfile file in this directory are as follows.
    RUNTIME python3
    RUN fun-install pip install psycopg2
  3. To execute the full install command, install depends on:
    $ fun install
    using template: template.yml
    start installing function dependencies without docker
    //Installation process....
    Install Success
  4. Create a new code file in the function root directory, such as / tmp/code/index.py, as follows.

    # -*- coding: utf-8 -*-
    import logging
    import psycopg2
    import os,sys
    logger = logging.getLogger()
    
    def getConnection():
      try:
        conn = psycopg2.connect(
          database = os.environ['DATABASE'],
          user = os.environ['USER'],
          password = os.environ['PASSWORD'],
          host = os.environ['HOST'],
          port = os.environ['PORT'],
          )
        return conn
      except Exception as e:
        logger.error(e)
        logger.error("ERROR: Unexpected error: Could not connect to PostgreSQL instance.")
        sys.exit()
    
    def conditionallyCreateUsersTable():
        conn = getConnection()
        cur = conn.cursor()
        cur.execute('''CREATE TABLE COMPANY
            (ID INT PRIMARY KEY     NOT NULL,
            NAME           TEXT    NOT NULL,
            AGE            INT     NOT NULL,
            ADDRESS        CHAR(50),
            SALARY         REAL);''')
        conn.commit()
        conn.close()
    
    def initializer(context):
      conditionallyCreateUsersTable()
    
    def handler(event, context):
      try:
        conn = getConnection()
        cur = conn.cursor()
        cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
          VALUES (1, 'Paul', 32, 'California', 20000.00 )");
        conn.commit()
        return 'successfully'
      finally:
        conn.close()
  5. Execute the following command to deploy the function.

    $ fun deploy -y
    using template: template.yml
    using region: cn-hangzhou
    using accountId: ***********3743
    using accessKeyId: ***********Ptgk
    using timeout: 60
    
    //Deployment process...
            function python-test deploy success
    service PostgreSQL-test deploy success

    Log in to the console, and you can see that the related services and functions have been created successfully, and the trigger execution can return the correct results.

summary

Through this article, we can quickly implement function calculation to access PostgreSQL database.

Advantages of using function calculation:

  1. No need to purchase and manage infrastructure such as servers, only focus on the development of business logic, which can greatly shorten the project delivery time and labor cost;
  2. Provide log query, performance monitoring, alarm and other functions for quick troubleshooting;
  3. Operation and maintenance free, millisecond level elastic expansion, rapid bottom expansion to cope with peak pressure, excellent performance;
  4. Cost competitive;

"Alibaba cloud native Pay attention to microservices, Serverless, containers, Service Mesh and other technology fields, focus on cloud native popular technology trends, cloud native large-scale landing practice, and become the technology circle that most understands cloud native developers. "

Tags: Linux VPC Database PostgreSQL network

Posted on Sun, 26 Apr 2020 06:41:16 -0700 by cunoodle2