PostgreSQL filter

Requirements

Sandbox environment

Setup your sandbox environment with Docker and Docker Compose, and clone the Envoy repository with Git.

curl

Used to make HTTP requests.

In this example, we show how the PostgreSQL filter can be used with the Envoy proxy.

The Envoy proxy configuration includes a PostgreSQL filter that parses queries and collects Postgres-specific metrics.

Step 1: Build the sandbox

Change to the examples/postgres directory.

Build and start the containers.

  1. $ pwd
  2. envoy/examples/postgres
  3. $ docker-compose pull
  4. $ docker-compose up --build -d
  5. $ docker-compose ps
  7.        Name                      Command             State                             Ports
  8. ----------------------------------------------------------------------------------------------------------------------
  9. postgres_postgres_1   docker-entrypoint.sh postgres  Up      5432/tcp
  10. postgres_proxy_1      /docker-entrypoint.sh /usr ... Up      10000/tcp, 0.0.0.0:1999->1999/tcp, 0.0.0.0:8001->8001/tcp

Step 2: Issue commands using psql

This example uses psql client inside a container to issue some commands and verify they are routed via Envoy. Note that we should set the environment variable PGSSLMODE=disable to disable SSL because the current implementation of the filter can’t decode encrypted sessions.

  1. $ docker run --rm -it --network envoymesh -e PGSSLMODE=disable postgres:latest psql -U postgres -h proxy -p 1999
  2. ... snip ...
  4. postgres=# CREATE DATABASE testdb;
  5. CREATE DATABASE
  6. postgres=# \c testdb
  7. You are now connected to database "testdb" as user "postgres".
  8. testdb=# CREATE TABLE tbl ( f SERIAL PRIMARY KEY );
  9. CREATE TABLE
  10. testdb=# INSERT INTO tbl VALUES (DEFAULT);
  11. INSERT 0 1
  12. testdb=# SELECT * FROM tbl;
  13.  f
  14. ---
  15.  1
  16. (1 row)
  18. testdb=# UPDATE tbl SET f = 2 WHERE f = 1;
  19. UPDATE 1
  20. testdb=# INSERT INTO tbl VALUES (DEFAULT);
  21. ERROR:  duplicate key value violates unique constraint "tbl_pkey"
  22. DETAIL:  Key (f)=(2) already exists.
  23. testdb=# DELETE FROM tbl;
  24. DELETE 1
  25. testdb=# INSERT INTO tbl VALUES (DEFAULT);
  26. INSERT 0 1
  27. testdb=# \q

Step 3: Check egress stats

Check egress stats were updated.

  1. $ curl -s http://localhost:8001/stats?filter=egress_postgres
  2. postgres.egress_postgres.errors: 1
  3. postgres.egress_postgres.errors_error: 1
  4. postgres.egress_postgres.errors_fatal: 0
  5. postgres.egress_postgres.errors_panic: 0
  6. postgres.egress_postgres.errors_unknown: 0
  7. postgres.egress_postgres.messages: 42
  8. postgres.egress_postgres.messages_backend: 32
  9. postgres.egress_postgres.messages_frontend: 10
  10. postgres.egress_postgres.messages_unknown: 0
  11. postgres.egress_postgres.notices: 0
  12. postgres.egress_postgres.notices_debug: 0
  13. postgres.egress_postgres.notices_info: 0
  14. postgres.egress_postgres.notices_log: 0
  15. postgres.egress_postgres.notices_notice: 0
  16. postgres.egress_postgres.notices_unknown: 0
  17. postgres.egress_postgres.notices_warning: 0
  18. postgres.egress_postgres.sessions: 1
  19. postgres.egress_postgres.sessions_encrypted: 0
  20. postgres.egress_postgres.sessions_unencrypted: 1
  21. postgres.egress_postgres.statements: 7
  22. postgres.egress_postgres.statements_delete: 1
  23. postgres.egress_postgres.statements_insert: 2
  24. postgres.egress_postgres.statements_other: 2
  25. postgres.egress_postgres.statements_parse_error: 4
  26. postgres.egress_postgres.statements_parsed: 4
  27. postgres.egress_postgres.statements_select: 1
  28. postgres.egress_postgres.statements_update: 1
  29. postgres.egress_postgres.transactions: 7
  30. postgres.egress_postgres.transactions_commit: 7
  31. postgres.egress_postgres.transactions_rollback: 0

Step 4: Check TCP stats

Check TCP stats were updated.

  1. $ curl -s http://localhost:8001/stats?filter=postgres_tcp
  2. tcp.postgres_tcp.downstream_cx_no_route: 0
  3. tcp.postgres_tcp.downstream_cx_rx_bytes_buffered: 0
  4. tcp.postgres_tcp.downstream_cx_rx_bytes_total: 373
  5. tcp.postgres_tcp.downstream_cx_total: 1
  6. tcp.postgres_tcp.downstream_cx_tx_bytes_buffered: 0
  7. tcp.postgres_tcp.downstream_cx_tx_bytes_total: 728
  8. tcp.postgres_tcp.downstream_flow_control_paused_reading_total: 0
  9. tcp.postgres_tcp.downstream_flow_control_resumed_reading_total: 0
  10. tcp.postgres_tcp.idle_timeout: 0
  11. tcp.postgres_tcp.max_downstream_connection_duration: 0
  12. tcp.postgres_tcp.upstream_flush_active: 0
  13. tcp.postgres_tcp.upstream_flush_total: 0

See also

Envoy PostgreSQL filter

Learn more about using the Envoy PostgreSQL filter.

Envoy admin quick start guide

Quick start guide to the Envoy admin interface.

PostgreSQL

The PostgreSQL database.