All Products
Search
Document Center

Simple Log Service:Collect PostgreSQL query results

Last Updated:Jul 11, 2024

This topic describes how to create a Logtail configuration in the Simple Log Service console to collect PostgreSQL query results.

Prerequisites

  • Logtail V0.16.0 or later is installed on your Linux server or Logtail V1.0.0.8 or later is installed on your Windows server. For more information, see Install Logtail on a Linux server or Install Logtail on a Windows server.

    Important

    Make sure that the server on which Logtail is installed can connect to the database from which you want to collect PostgreSQL query results.

  • The IP address of the server on which Logtail is installed is added to a whitelist of the PostgreSQL database from which you want to collect PostgreSQL query results.

    For more information about how to configure an IP address whitelist for an ApsaraDB RDS for PostgreSQL instance, see Configure an IP address whitelist.

Principle

Logtail executes the SELECT statement that is specified in a Logtail configuration on a regular basis, and then uploads the query results to Simple Log Service.

After Logtail obtains query results, Logtail saves the value of the CheckPoint field in the results to the Logtail server. The next time Logtail executes the SELECT statement, Logtail adds the value of the CheckPoint field to the SELECT statement. This way, Logtail can collect incremental data.

Important

If you use checkpoints during data collection, you must sort the values of the CheckPoint field in the SELECT statement. Otherwise, data duplication or other issues may occur.

image

Features

  • You can collect query results from PostgreSQL databases.

  • You can configure paged query settings.

  • You can specify time zones.

  • You can specify timeout periods.

  • You can save the values of the CheckPoint field.

  • You can specify the maximum number of logs that can be collected at a time.

Scenarios

  • Collect incremental data based on marks such as an auto-increment ID or a point in time.

  • Synchronize data based on filter conditions.

Procedure

  1. Log on to the Simple Log Service console.

  2. In the Import Data section, select Custom Data Plug-in.

  3. Select the project and Logstore. Then, click Next.

  4. In the Machine Group Configurations step, configure a machine group.

    1. Configure the Scenario and Installation Environment parameters based on your business requirements.

      Important

      You must configure the Scenario and Installation Environment parameters regardless of whether a machine group is available. The parameter settings affect subsequent configurations.

    2. Make sure that a machine group is displayed in the Applied Server Groups section and click Next.

      Machine group available

      Select a machine group from the Source Machine Group section.

      image

      No machine group available

      Click Create Machine Group. In the Create Machine Group panel, configure the parameters. You can set the Machine Group Identifier parameter to IP Address or Custom Identifier. For more information, see Create a custom identifier-based machine group or Create an IP address-based machine group.

      Important

      If you apply a machine group immediately after you create the machine group, the heartbeat status of the machine group may be FAIL. This issue occurs because the machine group is not connected to Simple Log Service. To resolve this issue, you can click Retry. If the issue persists, see What do I do if no heartbeat connections are detected on Logtail?

  5. In the Configure Data Source step, set the Configuration Name and Plug-in Configuration parameters, and then click Next.

    • inputs is required and is used to configure the data source settings for the Logtail configuration.

      Important

      You can specify only one type of data source in inputs.

    • processors is optional and is used to configure the data processing settings for the Logtail configuration to parse data. You can specify one or more processing methods.

      If your logs cannot be parsed based only on the setting of inputs, you can configure processors in the Plug-in Configuration field to add plug-ins for data processing. For example, you can extract fields, extract log time, mask data, and filter logs. For more information, see Use Logtail plug-ins to process data.

    {
      "inputs": [
        {
          "type": "service_pgsql",
          "detail": {
            "Address": "pgm-****.pg.rds.aliyuncs.com",
            "User": "****",
            "Password": "*******",
            "DataBase": "****",
            "Limit": true,
            "PageSize": 100,
            "StateMent": "select * from specialalarmtest where id > $1 order by id",
            "CheckPoint": true,
            "CheckPointColumn": "id",
            "CheckPointStart": "0",
            "CheckPointSavePerPage": true,
            "CheckPointColumnType": "int",
            "IntervalMs": 1000
          }
        }
      ]
    }

    Parameter

    Type

    Required

    Description

    type

    string

    Yes

    The type of the data source. Set the value to service_pgsql.

    Address

    string

    No

    The address of the PostgreSQL database. Example: pgm-****.pg.rds.aliyuncs.com.

    Default value: 127.0.0.1:5432.

    User

    string

    No

    The username of the account that is used to log on to the PostgreSQL database.

    Default value: root.

    Password

    string

    No

    The password of the account that is used to log on to the PostgreSQL database. This parameter is empty by default.

    If you have high requirements for data security, we recommend that you set the username and password to xxx. After your configurations are synchronized to the Logtail server, find the User and Password parameters in the /usr/local/ilogtail/user_log_config.json file and change the values based on your business requirements. For more information, see Modify the Logtail configuration on the Logtail server.

    Important

    If you modify this parameter in the Simple Log Service console, the parameter setting in the Logtail configuration on the Logtail server is overwritten after the modification is synchronized to the server.

    DataBase

    string

    No

    The name of the PostgreSQL database.

    DialTimeOutMs

    int

    No

    The timeout period for connections to the PostgreSQL database. Unit: milliseconds.

    Default value: 5000.

    ReadTimeOutMs

    int

    No

    The timeout period for reads from the PostgreSQL database. Unit: milliseconds.

    Default value: 5000.

    StateMent

    string

    No

    The SELECT statement.

    If you set the CheckPoint parameter to true, you must include the column specified by the CheckPointColumn parameter in a WHERE clause of the SELECT statement that you specify for the StateMent parameter. You must also set the value for the column to $1. For example, if you set the CheckPointColumn parameter to id, you must specify the value of the StateMent parameter in the SELECT * from ... where id > $1 format.

    Limit

    boolean

    No

    Specifies whether to use a LIMIT clause to paginate query results.

    Default value: false. This value indicates that no LIMIT clause is used.

    We recommend that you set the Limit parameter to true. If you set the Limit parameter to true, a LIMIT clause is automatically added to the SQL statement that you specify for the StateMent parameter when Logtail executes the SQL statement.

    PageSize

    int

    No

    The maximum number of logs that can be returned on each page. If you set the Limit parameter to true, you must configure this parameter.

    MaxSyncSize

    int

    No

    The maximum number of logs that can be synchronized at a time.

    Default value: 0. This value indicates that the number is unlimited.

    CheckPoint

    boolean

    No

    Specifies whether to use checkpoints during data collection.

    Default value: false. This value indicates that no checkpoint is used.

    CheckPointColumn

    string

    No

    The name of the column that stores checkpoints. The column is also referred to as the checkpoint column.

    If you set the CheckPoint parameter to true, you must configure this parameter.

    Warning

    Values in the checkpoint column must be incremental. Otherwise, some data may not be collected. The maximum value in the results of a query operation is used as the input for the next query operation.

    CheckPointColumnType

    string

    No

    The type of the checkpoint column. Valid values: int and time. If you set this parameter to int, the values in the checkpoint column are of the int64 type. If you set this parameter to time, the values in the checkpoint column can be of the time type that is supported by PostgreSQL.

    If you set the CheckPoint parameter to true, you must configure this parameter.

    CheckPointStart

    string

    No

    The initial value of the checkpoint column.

    If you set the CheckPoint parameter to true, you must configure this parameter.

    CheckPointSavePerPage

    boolean

    No

    Specifies whether to save checkpoints each time query results are paginated. Valid values:

    • true: saves checkpoints each time query results are paginated.

    • false: saves checkpoints each time query results are synchronized.

    IntervalMs

    int

    Yes

    The synchronization interval. Unit: milliseconds.

  6. Create indexes and preview data. Then, click Next. By default, full-text indexing is enabled in Simple Log Service. You can also manually create field indexes for the collected logs or click Automatic Index Generation. Then, Simple Log Service generates field indexes. For more information, see Create indexes.

    Important

    If you want to query all fields in logs, we recommend that you use full-text indexes. If you want to query only specific fields, we recommend that you use field indexes. This helps reduce index traffic. If you want to analyze fields, you must create field indexes. You must include a SELECT statement in your query statement for analysis.

  7. Click Log Query. You are redirected to the query and analysis page of your Logstore.

    You must wait approximately 1 minute for the indexes to take effect. Then, you can view the collected logs on the Raw Logs tab. For more information, see Query and analyze logs.

Modify the Logtail configuration on the Logtail server

If you do not enter real information for parameters such as Address, User, and Password in Plug-in Config when you create a Logtail configuration, you can modify the parameters after the Logtail configuration is delivered to the Logtail server.

Linux

  1. Log on to the Logtail server.

  2. Find the service_pgsql keyword in the /usr/local/ilogtail/user_log_config.json file and modify parameters such as Address, User, and Password.

  3. Run the following command to restart Logtail:

    sudo /etc/init.d/ilogtaild stop; sudo /etc/init.d/ilogtaild start

Windows

  1. Log on to the Logtail server.

  2. Find the service_pgsql keyword in the C:\Program Files\Alibaba\Logtail\user_log_config.json file, and modify parameters such as Address, User, and Password.

    The file locations differ between Windows 64-bit operating systems and Windows 32-bit operating systems. For more information, see Logtail configuration files and record files.

  3. Restart Logtail.

    1. Choose Start > Control Panel > Administrative Tools > Services.

    • In the Services dialog box, select the corresponding service.

      • For Logtail V0.x.x.x, select LogtailWorker.

      • For Logtail V1.0.0.0 or later, select LogtailDaemon.

    • Right-click and select the desired action, such as Start, Stop, or Restart.

Troubleshooting

If no data is displayed on the preview page or query page after logs are collected by using Logtail, you can troubleshoot the errors based on the instructions that are provided in What do I do if errors occur when I use Logtail to collect logs?

Sample database table and sample logs

This section provides a sample database table of a PostgreSQL database and sample logs that are collected by Logtail.

  • Sample database table

    // Create a table. 
    CREATE TABLE IF NOT EXISTS specialalarmtest (
        id BIGSERIAL NOT NULL,
        time TIMESTAMP NOT NULL,
        alarmtype varchar(64) NOT NULL,
        ip varchar(16) NOT NULL,
        COUNT INT NOT NULL,
        PRIMARY KEY (id)
    );
    
    // Insert data. 
    insert into specialalarmtest (time, alarmtype, ip, count) values(now(), 'NO_ALARM', '10.10.***.***', 0);
    insert into specialalarmtest (time, alarmtype, ip, count) values(now(), 'NO_ALARM', '10.10.***.***', 1);
    insert into specialalarmtest (time, alarmtype, ip, count) values(now(), 'NO_ALARM', '10.10.***.***', 2);
    insert into specialalarmtest (time, alarmtype, ip, count) values(now(), 'NO_ALARM', '10.10.***.***', 3);
                            
  • Sample logs Sample logs