elasticsearch/docs/reference/connector/docs/connectors-postgresql.asciidoc

[#es-connectors-postgresql]
=== Elastic PostgreSQL connector reference
++++
<titleabbrev>PostgreSQL</titleabbrev>
++++
// Attributes used in this file
:service-name: PostgreSQL
:service-name-stub: postgresql

The _Elastic PostgreSQL connector_ is a connector for https://www.postgresql.org[PostgreSQL^].
This connector is written in Python using the {connectors-python}[Elastic connector framework^].

This connector uses the https://github.com/elastic/connectors/blob/{branch}/connectors/sources/generic_database.py[generic database connector source code^] (branch _{connectors-branch}_, compatible with Elastic _{minor-version}_).
View the specific {connectors-python}/connectors/sources/{service-name-stub}.py[*source code* for this connector^] (branch _{connectors-branch}_, compatible with Elastic _{minor-version}_).


.Choose your connector reference
*******************************
Are you using an Elastic managed connector on Elastic Cloud or a self-managed connector? Expand the documentation based on your deployment method.
*******************************

// //////// //// //// //// //// //// //// ////////
// ////////   NATIVE CONNECTOR REFERENCE   ///////
// //////// //// //// //// //// //// //// ////////

[discrete#connectors-postgresql-native-connector-reference]
=== *Elastic managed connector (Elastic Cloud)*

.View *Elastic managed connector* reference

[%collapsible]
===============

[discrete#connectors-postgresql-availability-prerequisites]
==== Availability and prerequisites

This connector is available as an *Elastic managed connector* in Elastic versions *8.8.0 and later*.
To use this connector natively in Elastic Cloud, satisfy all <<es-native-connectors,Elastic managed connector requirements>>.

[discrete#connectors-postgresql-create-native-connector]
==== Create a {service-name} connector
include::_connectors-create-native.asciidoc[]

[discrete#connectors-postgresql-usage]
==== Usage

To use this connector as an *Elastic managed connector*, use the *Connector* workflow.
See <<es-native-connectors>>.

[TIP]
====
Users must set `track_commit_timestamp` to `on`.
To do this, run `ALTER SYSTEM SET track_commit_timestamp = on;` in PostgreSQL server.
====

For additional operations, see <<-esconnectors-usage>>.

[NOTE]
====
For an end-to-end example of the connector client workflow, see <<es-postgresql-connector-client-tutorial>>.
====

[discrete#connectors-postgresql-compatibility]
==== Compatibility

PostgreSQL versions 11 to 15 are compatible with the Elastic connector.

[discrete#connectors-postgresql-configuration]
==== Configuration

Set the following configuration fields:

Host::
The server host address where the PostgreSQL instance is hosted.
Examples:
+
* `192.158.1.38`
* `demo.instance.demo-region.demo.service.com`

Port::
The port where the PostgreSQL instance is hosted.
Examples:
+
* `5432` (default)

Username::
The username of the PostgreSQL account.

Password::
The password of the PostgreSQL account.

Database::
Name of the PostgreSQL database.
Examples:
+
* `employee_database`
* `customer_database`

Schema::
The schema of the PostgreSQL database.

Comma-separated List of Tables::
A list of tables separated by commas.
The PostgreSQL connector will fetch data from all tables present in the configured database, if the value is `*` .
Default value is `*`.
Examples:
+
* `table_1, table_2`
* `*`
+
[WARNING]
====
This field can be bypassed when using advanced sync rules.
====

Enable SSL::
Toggle to enable SSL verification.
Disabled by default.

SSL Certificate::
Content of SSL certificate.
If SSL is disabled, the `ssl_ca` value will be ignored.
+
.*Expand* to see an example certificate
[%collapsible]
====
```
-----BEGIN CERTIFICATE-----
MIID+jCCAuKgAwIBAgIGAJJMzlxLMA0GCSqGSIb3DQEBCwUAMHoxCzAJBgNVBAYT
AlVTMQwwCgYDVQQKEwNJQk0xFjAUBgNVBAsTDURlZmF1bHROb2RlMDExFjAUBgNV
BAsTDURlZmF1bHRDZWxsMDExGTAXBgNVBAsTEFJvb3QgQ2VydGlmaWNhdGUxEjAQ
BgNVBAMTCWxvY2FsaG9zdDAeFw0yMTEyMTQyMjA3MTZaFw0yMjEyMTQyMjA3MTZa
MF8xCzAJBgNVBAYTAlVTMQwwCgYDVQQKEwNJQk0xFjAUBgNVBAsTDURlZmF1bHRO
b2RlMDExFjAUBgNVBAsTDURlZmF1bHRDZWxsMDExEjAQBgNVBAMTCWxvY2FsaG9z
dDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMv5HCsJZIpI5zCy+jXV
z6lmzNc9UcVSEEHn86h6zT6pxuY90TYeAhlZ9hZ+SCKn4OQ4GoDRZhLPTkYDt+wW
CV3NTIy9uCGUSJ6xjCKoxClJmgSQdg5m4HzwfY4ofoEZ5iZQ0Zmt62jGRWc0zuxj
hegnM+eO2reBJYu6Ypa9RPJdYJsmn1RNnC74IDY8Y95qn+WZj//UALCpYfX41hko
i7TWD9GKQO8SBmAxhjCDifOxVBokoxYrNdzESl0LXvnzEadeZTd9BfUtTaBHhx6t
njqqCPrbTY+3jAbZFd4RiERPnhLVKMytw5ot506BhPrUtpr2lusbN5svNXjuLeea
MMUCAwEAAaOBoDCBnTATBgNVHSMEDDAKgAhOatpLwvJFqjAdBgNVHSUEFjAUBggr
BgEFBQcDAQYIKwYBBQUHAwIwVAYDVR0RBE0wS4E+UHJvZmlsZVVVSUQ6QXBwU3J2
MDEtQkFTRS05MDkzMzJjMC1iNmFiLTQ2OTMtYWI5NC01Mjc1ZDI1MmFmNDiCCWxv
Y2FsaG9zdDARBgNVHQ4ECgQITzqhA5sO8O4wDQYJKoZIhvcNAQELBQADggEBAKR0
gY/BM69S6BDyWp5dxcpmZ9FS783FBbdUXjVtTkQno+oYURDrhCdsfTLYtqUlP4J4
CHoskP+MwJjRIoKhPVQMv14Q4VC2J9coYXnePhFjE+6MaZbTjq9WaekGrpKkMaQA
iQt5b67jo7y63CZKIo9yBvs7sxODQzDn3wZwyux2vPegXSaTHR/rop/s/mPk3YTS
hQprs/IVtPoWU4/TsDN3gIlrAYGbcs29CAt5q9MfzkMmKsuDkTZD0ry42VjxjAmk
xw23l/k8RoD1wRWaDVbgpjwSzt+kl+vJE/ip2w3h69eEZ9wbo6scRO5lCO2JM4Pr
7RhLQyWn2u00L7/9Omw=
-----END CERTIFICATE-----
```
====

[discrete#connectors-postgresql-documents-syncs]
==== Documents and syncs

* Tables must be owned by a PostgreSQL user.
* Tables with no primary key defined are skipped.
* To fetch the last updated time in PostgreSQL, `track_commit_timestamp` must be set to `on`.
Otherwise, all data will be indexed in every sync.

[NOTE]
====
* Files bigger than 10 MB won't be extracted.
* Permissions are not synced.
**All documents** indexed to an Elastic deployment will be visible to **all users with access** to that Elastic Deployment.
====

[discrete#connectors-postgresql-sync-rules]
==== Sync rules

<<es-sync-rules-basic,Basic sync rules>> are identical for all connectors and are available by default.

[discrete#connectors-postgresql-sync-rules-advanced]
===== Advanced sync rules

[NOTE]
====
A <<es-connectors-sync-types-full, full sync>> is required for advanced sync rules to take effect.
====

Advanced sync rules are defined through a source-specific DSL JSON snippet.

[discrete#connectors-postgresql-sync-rules-advanced-example-data]
====== Example data

Here is some example data that will be used in the following examples.

[discrete#connectors-postgresql-sync-rules-advanced-example-data-1]
======= `employee` table

[cols="3*", options="header"]
|===
| emp_id | name  | age
| 3      | John  | 28
| 10     | Jane  | 35
| 14     | Alex  | 22
|===

[discrete#connectors-postgresql-sync-rules-advanced-example-2]
======= `customer` table

[cols="3*", options="header"]
|===
| c_id | name | age
| 2    | Elm  | 24
| 6    | Pine | 30
| 9    | Oak  | 34
|===

[discrete#connectors-postgresql-sync-rules-advanced-examples]
====== Advanced sync rules examples

[discrete#connectors-postgresql-sync-rules-advanced-examples-1]
======= Multiple table queries

[source,js]
----
[
  {
    "tables": [
      "employee"
    ],
    "query": "SELECT * FROM employee"
  },
  {
    "tables": [
      "customer"
    ],
    "query": "SELECT * FROM customer"
  }
]
----
// NOTCONSOLE

[discrete#connectors-postgresql-sync-rules-advanced-examples-1-id-columns]
======= Multiple table queries with `id_columns`

In 8.15.0, we added a new optional `id_columns` field in our advanced sync rules for the PostgreSQL connector.
Use the `id_columns` field to ingest tables which do not have a primary key. Include the names of unique fields so that the connector can use them to generate unique IDs for documents.

[source,js]
----
[
  {
    "tables": [
      "employee"
    ],
    "query": "SELECT * FROM employee",
    "id_columns": ["emp_id"]
  },
  {
    "tables": [
      "customer"
    ],
    "query": "SELECT * FROM customer",
    "id_columns": ["c_id"]
  }
]
----
// NOTCONSOLE

This example uses the `id_columns` field to specify the unique fields `emp_id` and `c_id` for the `employee` and `customer` tables, respectively.

[discrete#connectors-postgresql-sync-rules-advanced-examples-2]
======= Filtering data with `WHERE` clause

[source,js]
----
[
  {
    "tables": ["employee"],
    "query": "SELECT * FROM employee WHERE emp_id > 5"
  }
]
----
// NOTCONSOLE

[discrete#connectors-postgresql-sync-rules-advanced-examples-3]
======= `JOIN` operations

[source,js]
----
[
  {
    "tables": ["employee", "customer"],
    "query": "SELECT * FROM employee INNER JOIN customer ON employee.emp_id = customer.c_id"
  }
]
----
// NOTCONSOLE

[WARNING]
====
When using advanced rules, a query can bypass the configuration field `tables`.
This will happen if the query specifies a table that doesn't appear in the configuration.
This can also happen if the configuration specifies `*` to fetch all tables while the advanced sync rule requests for only a subset of tables.
====

[discrete#connectors-postgresql-known-issues]
==== Known issues

There are no known issues for this connector.
Refer to <<es-connectors-known-issues>> for a list of known issues for all connectors.

[discrete#connectors-postgresql-troubleshooting]
==== Troubleshooting

See <<es-connectors-troubleshooting>>.

[discrete#connectors-postgresql-security]
==== Security

See <<es-connectors-security>>.

// Closing the collapsible section
===============

[discrete#es-connectors-postgresql-connector-client-reference]
=== *Self-managed connector*

.View *self-managed connector* reference

[%collapsible]
===============

[discrete#es-connectors-postgresql-client-availability-prerequisites]
==== Availability and prerequisites

This connector is available as a self-managed *self-managed connector*.
To use this connector, satisfy all <<es-build-connector,self-managed connector requirements>>.

[discrete#es-connectors-postgresql-create-connector-client]
==== Create a {service-name} connector
include::_connectors-create-client.asciidoc[]

[discrete#es-connectors-postgresql-client-usage]
==== Usage

To use this connector as a *self-managed connector*, see <<es-build-connector>>.
[TIP]
====
Users must set `track_commit_timestamp` to `on`.
To do this, run `ALTER SYSTEM SET track_commit_timestamp = on;` in PostgreSQL server.
====

For additional operations, see.

[NOTE]
====
For an end-to-end example of the self-managed connector workflow, see <<es-postgresql-connector-client-tutorial>>.
====

[discrete#es-connectors-postgresql-client-compatibility]
==== Compatibility

PostgreSQL versions 11 to 15 are compatible with Elastic connector frameworks.

[discrete#es-connectors-postgresql-client-configuration]
==== Configuration

[TIP]
====
When using the <<es-build-connector, self-managed connector workflow>>, initially these fields will use the default configuration set in the https://github.com/elastic/connectors-python/blob/{branch}/connectors/sources/postgresql.py[connector source code^].

These configurable fields will be rendered with their respective *labels* in the Kibana UI.
Once connected, users will be able to update these values in Kibana.
====

Set the following configuration fields:

`host`::
The server host address where the PostgreSQL instance is hosted.
Examples:
+
* `192.158.1.38`
* `demo.instance.demo-region.demo.service.com`

`port`::
The port where the PostgreSQL instance is hosted.
Examples:
+
* `5432`
* `9090`

`username`::
The username of the PostgreSQL account.

`password`::
The password of the PostgreSQL account.

`database`::
Name of the PostgreSQL database.
Examples:
+
* `employee_database`
* `customer_database`

`schema`::
The schema of the PostgreSQL database.

`tables`::
A list of tables separated by commas.
The PostgreSQL connector will fetch data from all tables present in the configured database, if the value is `*` .
Default value is `*`.
Examples:
+
* `table_1, table_2`
* `*`
+
[WARNING]
====
This field can be bypassed when using advanced sync rules.
====

`ssl_enabled`::
Whether SSL verification will be enabled.
Default value is `True`.

`ssl_ca`::
Content of SSL certificate (if SSL is enabled).
If SSL is disabled, the `ssl_ca` value will be ignored.
+
.*Expand* to see an example certificate
[%collapsible]
====
```
-----BEGIN CERTIFICATE-----
MIID+jCCAuKgAwIBAgIGAJJMzlxLMA0GCSqGSIb3DQEBCwUAMHoxCzAJBgNVBAYT
AlVTMQwwCgYDVQQKEwNJQk0xFjAUBgNVBAsTDURlZmF1bHROb2RlMDExFjAUBgNV
BAsTDURlZmF1bHRDZWxsMDExGTAXBgNVBAsTEFJvb3QgQ2VydGlmaWNhdGUxEjAQ
BgNVBAMTCWxvY2FsaG9zdDAeFw0yMTEyMTQyMjA3MTZaFw0yMjEyMTQyMjA3MTZa
MF8xCzAJBgNVBAYTAlVTMQwwCgYDVQQKEwNJQk0xFjAUBgNVBAsTDURlZmF1bHRO
b2RlMDExFjAUBgNVBAsTDURlZmF1bHRDZWxsMDExEjAQBgNVBAMTCWxvY2FsaG9z
dDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMv5HCsJZIpI5zCy+jXV
z6lmzNc9UcVSEEHn86h6zT6pxuY90TYeAhlZ9hZ+SCKn4OQ4GoDRZhLPTkYDt+wW
CV3NTIy9uCGUSJ6xjCKoxClJmgSQdg5m4HzwfY4ofoEZ5iZQ0Zmt62jGRWc0zuxj
hegnM+eO2reBJYu6Ypa9RPJdYJsmn1RNnC74IDY8Y95qn+WZj//UALCpYfX41hko
i7TWD9GKQO8SBmAxhjCDifOxVBokoxYrNdzESl0LXvnzEadeZTd9BfUtTaBHhx6t
njqqCPrbTY+3jAbZFd4RiERPnhLVKMytw5ot506BhPrUtpr2lusbN5svNXjuLeea
MMUCAwEAAaOBoDCBnTATBgNVHSMEDDAKgAhOatpLwvJFqjAdBgNVHSUEFjAUBggr
BgEFBQcDAQYIKwYBBQUHAwIwVAYDVR0RBE0wS4E+UHJvZmlsZVVVSUQ6QXBwU3J2
MDEtQkFTRS05MDkzMzJjMC1iNmFiLTQ2OTMtYWI5NC01Mjc1ZDI1MmFmNDiCCWxv
Y2FsaG9zdDARBgNVHQ4ECgQITzqhA5sO8O4wDQYJKoZIhvcNAQELBQADggEBAKR0
gY/BM69S6BDyWp5dxcpmZ9FS783FBbdUXjVtTkQno+oYURDrhCdsfTLYtqUlP4J4
CHoskP+MwJjRIoKhPVQMv14Q4VC2J9coYXnePhFjE+6MaZbTjq9WaekGrpKkMaQA
iQt5b67jo7y63CZKIo9yBvs7sxODQzDn3wZwyux2vPegXSaTHR/rop/s/mPk3YTS
hQprs/IVtPoWU4/TsDN3gIlrAYGbcs29CAt5q9MfzkMmKsuDkTZD0ry42VjxjAmk
xw23l/k8RoD1wRWaDVbgpjwSzt+kl+vJE/ip2w3h69eEZ9wbo6scRO5lCO2JM4Pr
7RhLQyWn2u00L7/9Omw=
-----END CERTIFICATE-----
```
====

[discrete#es-connectors-postgresql-client-docker]
==== Deployment using Docker

include::_connectors-docker-instructions.asciidoc[]

[discrete#es-connectors-postgresql-client-documents-syncs]
==== Documents and syncs

* Tables must be owned by a PostgreSQL user.
* Tables with no primary key defined are skipped.
* To fetch the last updated time in PostgreSQL, `track_commit_timestamp` must be set to `on`.
Otherwise, all data will be indexed in every sync.

[NOTE]
====
* Files bigger than 10 MB won't be extracted.
* Permissions are not synced.
**All documents** indexed to an Elastic deployment will be visible to **all users with access** to that Elastic Deployment.
====

[discrete#es-connectors-postgresql-client-sync-rules]
==== Sync rules

//sync-rules-basic,Basic sync rules are identical for all connectors and are available by default.

[discrete#es-connectors-postgresql-client-sync-rules-advanced]
===== Advanced sync rules

[NOTE]
====
A //connectors-sync-types-full, full sync is required for advanced sync rules to take effect.
====

Advanced sync rules are defined through a source-specific DSL JSON snippet.

[discrete#es-connectors-postgresql-client-sync-rules-advanced-example-data]
====== Example data

Here is some example data that will be used in the following examples.

[discrete#es-connectors-postgresql-client-sync-rules-advanced-example-data-1]
======= `employee` table

[cols="3*", options="header"]
|===
| emp_id | name  | age
| 3      | John  | 28
| 10     | Jane  | 35
| 14     | Alex  | 22
|===

[discrete#es-connectors-postgresql-client-sync-rules-advanced-example-2]
======= `customer` table

[cols="3*", options="header"]
|===
| c_id | name | age
| 2    | Elm  | 24
| 6    | Pine | 30
| 9    | Oak  | 34
|===

[discrete#es-connectors-postgresql-client-sync-rules-advanced-examples]
====== Advanced sync rules examples

[discrete#es-connectors-postgresql-client-sync-rules-advanced-examples-1]
======== Multiple table queries

[source,js]
----
[
  {
    "tables": [
      "employee"
    ],
    "query": "SELECT * FROM employee"
  },
  {
    "tables": [
      "customer"
    ],
    "query": "SELECT * FROM customer"
  }
]
----
// NOTCONSOLE

[discrete#es-connectors-postgresql-client-sync-rules-advanced-examples-1-id-columns]
======== Multiple table queries with `id_columns`

In 8.15.0, we added a new optional `id_columns` field in our advanced sync rules for the PostgreSQL connector.
Use the `id_columns` field to ingest tables which do not have a primary key. Include the names of unique fields so that the connector can use them to generate unique IDs for documents.

[source,js]
----
[
  {
    "tables": [
      "employee"
    ],
    "query": "SELECT * FROM employee",
    "id_columns": ["emp_id"]
  },
  {
    "tables": [
      "customer"
    ],
    "query": "SELECT * FROM customer",
    "id_columns": ["c_id"]
  }
]
----
// NOTCONSOLE

This example uses the `id_columns` field to specify the unique fields `emp_id` and `c_id` for the `employee` and `customer` tables, respectively.

[discrete#es-connectors-postgresql-client-sync-rules-advanced-examples-2]
======== Filtering data with `WHERE` clause

[source,js]
----
[
  {
    "tables": ["employee"],
    "query": "SELECT * FROM employee WHERE emp_id > 5"
  }
]
----
// NOTCONSOLE

[discrete#es-connectors-postgresql-client-sync-rules-advanced-examples-3]
======== `JOIN` operations

[source,js]
----
[
  {
    "tables": ["employee", "customer"],
    "query": "SELECT * FROM employee INNER JOIN customer ON employee.emp_id = customer.c_id"
  }
]
----
// NOTCONSOLE

[WARNING]
====
When using advanced rules, a query can bypass the configuration field `tables`.
This will happen if the query specifies a table that doesn't appear in the configuration.
This can also happen if the configuration specifies `*` to fetch all tables while the advanced sync rule requests for only a subset of tables.
====

[discrete#es-connectors-postgresql-client-client-operations-testing]
==== End-to-end testing

The connector framework enables operators to run functional tests against a real data source.
Refer to <<es-build-connector-testing>> for more details.

To perform E2E testing for the PostgreSQL connector, run the following command:

[source,shell]
----
$ make ftest NAME=postgresql
----

For faster tests, add the `DATA_SIZE=small` flag:

[source,shell]
----
make ftest NAME=postgresql DATA_SIZE=small
----

[discrete#es-connectors-postgresql-client-known-issues]
==== Known issues

There are no known issues for this connector.
Refer to <<es-connectors-known-issues>> for a list of known issues for all connectors.

[discrete#es-connectors-postgresql-client-troubleshooting]
==== Troubleshooting

See <<es-connectors-troubleshooting>>.

[discrete#es-connectors-postgresql-client-security]
==== Security

See <<es-connectors-security>>.

// Closing the collapsible section
===============