# Postgres

Kubemq postgres target connector allows services using kubemq server to access postgres database services.

## Prerequisites

The following are required to run the postgres target connector:

* kubemq cluster
* postgres server
* kubemq-targets deployment

## Configuration

Postgres target connector configuration properties:

| Properties Key                     | Required | Description                                 | Example                        |
| ---------------------------------- | -------- | ------------------------------------------- | ------------------------------ |
| max\_idle\_connections             | no       | set max idle connections                    | "10"                           |
| max\_open\_connections             | no       | set max open connections                    | "100"                          |
| connection\_max\_lifetime\_seconds | no       | set max lifetime for connections in seconds | "3600"                         |
| instance\_connection\_name         | yes      | set sql instance name                       | project:us-east1:db-porudction |
| credentials                        | yes      | gcp credentials files                       | "google json credentials"      |
| db\_user                           | yes      | gcp db user name files                      | "google user"                  |
| db\_name                           | yes      | gcp db name                                 | "google instance name"         |
| db\_password                       | yes      | gcp db password                             | "google db password"           |

Example:

```yaml
bindings:
  - name: kubemq-query-gcp-postgres
    source:
      kind: kubemq.query
      name: kubemq-query
      properties:
        address: "kubemq-cluster:50000"
        client_id: "kubemq-query-gcp-postgres-connector"
        auth_token: ""
        channel: "query.postgres"
        group:   ""
        auto_reconnect: "true"
        reconnect_interval_seconds: "1"
        max_reconnects: "0"
    target:
      kind: gcp.stores.postgres
      name: target-gcp-postgres
      properties:
        instance_connection_name: "instanceConnectionName"
        db_user:                  "user"
        db_name:                  "dbName"
        db_password:              "dbPassword"
        max_idle_connections: "10"
        max_open_connections: "100"
        connection_max_lifetime_seconds: "3600"
        credentials: 'json'
```

## Usage

### Query Request

Query request metadata setting:

| Metadata Key | Required | Description         | Possible values |
| ------------ | -------- | ------------------- | --------------- |
| method       | yes      | set type of request | "query"         |

Query request data setting:

| Data Key | Required | Description  | Possible values    |
| -------- | -------- | ------------ | ------------------ |
| data     | yes      | query string | base64 bytes array |

Example:

Query string: `SELECT id,title,content FROM post;`

```javascript
{
  "metadata": {
    "method": "query"
  },
  "data": "U0VMRUNUIGlkLHRpdGxlLGNvbnRlbnQgRlJPTSBwb3N0Ow=="
}
```

### Exec Request

Exec request metadata setting:

| Metadata Key     | Required | Description                            | Possible values     |
| ---------------- | -------- | -------------------------------------- | ------------------- |
| method           | yes      | set type of request                    | "exec"              |
| isolation\_level | no       | set isolation level for exec operation | ""                  |
|                  |          |                                        | "read\_uncommitted" |
|                  |          |                                        | "read\_committed"   |
|                  |          |                                        | "repeatable\_read"  |
|                  |          |                                        | "serializable"      |
|                  |          |                                        |                     |

Exec request data setting:

| Data Key | Required | Description | Possible values    |
| -------- | -------- | ----------- | ------------------ |
| data     | yes      | exec string | base64 bytes array |

Example:

Exec string:

```sql
INSERT INTO post(ID,TITLE,CONTENT) VALUES (1,NULL,'Content One'),(2,'Title Two','Content Two');
```

```javascript
{
  "metadata": {
    "method": "exec",
    "isolation_level": "read_uncommitted"
  },
  "data": "SU5TRVJUIElOVE8gcG9zdChJRCxUSVRMRSxDT05URU5UKSBWQUxVRVMKCSAgICAgICAgICAgICAgICAgICAgICAgKDEsTlVMTCwnQ29udGVudCBPbmUnKSwKCSAgICAgICAgICAgICAgICAgICAgICAgKDIsJ1RpdGxlIFR3bycsJ0NvbnRlbnQgVHdvJyk7" 
}
```

### Transaction Request

Transaction request metadata setting:

| Metadata Key     | Required | Description                            | Possible values     |
| ---------------- | -------- | -------------------------------------- | ------------------- |
| method           | yes      | set type of request                    | "transaction"       |
| isolation\_level | no       | set isolation level for exec operation | ""                  |
|                  |          |                                        | "read\_uncommitted" |
|                  |          |                                        | "read\_committed"   |
|                  |          |                                        | "repeatable\_read"  |
|                  |          |                                        | "serializable"      |

Transaction request data setting:

| Data Key | Required | Description   | Possible values    |
| -------- | -------- | ------------- | ------------------ |
| data     | yes      | string string | base64 bytes array |

Example:

Transaction string:

```sql
DROP TABLE IF EXISTS post;
CREATE TABLE post (
         ID serial,
         TITLE varchar(40),
         CONTENT varchar(255),
         CONSTRAINT pk_post PRIMARY KEY(ID)
       );
INSERT INTO post(ID,TITLE,CONTENT) VALUES
                       (1,NULL,'Content One'),
                       (2,'Title Two','Content Two');
```

```javascript
{
  "metadata": {
    "key": "your-postgres-key",
    "method": "delete"
  },
  "data": "CURST1AgVEFCTEUgSUYgRVhJU1RTIHBvc3Q7CiAgICBDUkVBVEUgVEFCTEUgcG9zdCAoCgkgICAgICAgICBJRCBzZXJpYWwsCgkgICAgICAgICBUSVRMRSB2YXJjaGFyKDQwKSwKCSAgICAgICAgIENPTlRFTlQgdmFyY2hhcigyNTUpLAoJICAgICAgICAgQ09OU1RSQUlOVCBwa19wb3N0IFBSSU1BUlkgS0VZKElEKQoJICAgICAgICk7CiAgICBJTlNFUlQgSU5UTyBwb3N0KElELFRJVExFLENPTlRFTlQpIFZBTFVFUwoJICAgICAgICAgICAgICAgICAgICAgICAoMSxOVUxMLCdDb250ZW50IE9uZScpLAoJICAgICAgICAgICAgICAgICAgICAgICAoMiwnVGl0bGUgVHdvJywnQ29udGVudCBUd28nKTs="
}
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.kubemq.io/configuration/connectors/kubemq-targets/gcp/postgres.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.