Search docs.molo17.com

    PostgreSQL logo PostgreSQL agent

    Target PostgreSQL

    Prerequisites

    To have Gluesync working on your PostgreSQL instance you will need to have:

    • Valid user credentials with permission to read, and write to the target tables and respective database

    Setup via Web UI

    • Hostname / IP Address: DNS record or IP Address of your server;

    • Port: Optional, defaults to 5432;

    • Database name: Name of your target database;

    • Username: Username with read/write to database tables;

    • Password: Password belonging to the given username.

    Specific configuration

    This agent has no specific configuration properties.

    Updates with bulk operations during CDC

    When Use bulk operations during CDC (useBulkOperationsDuringCDC) is on for an entity, the agent does not apply an update as an UPDATE statement. It loads the change into a staging table, deletes the existing row from the target table and inserts the new version of it. With the option off, updates are applied as UPDATE statements.

    For the target table this has the following effects:

    • UPDATE triggers on the target table do not fire, while DELETE and INSERT triggers do;

    • Foreign keys that reference the table with ON DELETE CASCADE delete their child rows on every update, and ON DELETE SET NULL clears the child columns. A foreign key with RESTRICT or NO ACTION makes the write fail while child rows exist;

    • Logical replication, CDC tools and anything else that reads the change log of the target database see a DELETE and an INSERT instead of an UPDATE;

    • The new row gets a new physical location (ctid), and columns that Gluesync does not write, such as a column with a default value, are set again as for a new row.

    Entities whose target table has an identity (auto-increment) column ignore the option: their updates are always applied as UPDATE statements.

    Setup via Rest APIs

    Here following an example of calling the Core Hub’s Rest API via curl to setup the connection for this Agent.

    Connect the agent

    curl --location --request PUT 'http://core-hub-ip-address/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
    --header 'Content-Type: application/json' \
    --header 'Authorization: ••••••' \
    --data '{
            "hostCredentials": {
            "connectionName": "myAgentNickName",
            "host": "host-address",
            "port": 5432,
            "databaseName": "db_name",
            "username": "",
            "password": "",
            "maxConnectionsCount": 100,
            "enableTls": true,
            "certificatePath": "/myPath/cert.pem"
          }
    }'