PostgreSQL¶
You can use an external PostgreSQL database instance outside the PMM Server container running on the same or other hosts.
Environment variables¶
PMM predefines certain flags that allow you to use PostgreSQL parameters as environment variables:
Warning
The PERCONA_TEST_* environment variables are experimental and subject to change. It is recommended that you use these variables for testing purposes only and not on production. The minimum supported PostgreSQL server version is 14.
To use PostgreSQL as an external database instance, use the following environment variables:
| Environment variable | Flag | Description |
|---|---|---|
| PERCONA_TEST_POSTGRES_ADDR | postgres-addr | Hostname and port for external PostgreSQL database. |
| PERCONA_TEST_POSTGRES_DBNAME | postgres-name | Database name for external or internal PostgreSQL database. |
| PERCONA_TEST_POSTGRES_USERNAME | postgres-username | PostgreSQL user name to connect as. |
| PERCONA_TEST_POSTGRES_DBPASSWORD | postgres-password | Password to be used for database authentication. |
| PERCONA_TEST_POSTGRES_SSL_MODE | postgres-ssl-mode | This option determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the database. Currently supported: disable, require, verify-ca, verify-full. |
| PERCONA_TEST_POSTGRES_SSL_CA_PATH | postgres-ssl-ca-path | This parameter specifies the name of a file containing SSL certificate authority (CA) certificate(s). |
| PERCONA_TEST_POSTGRES_SSL_KEY_PATH | postgres-ssl-key-path | This parameter specifies the location for the secret key used for the client certificate. |
| PERCONA_TEST_POSTGRES_SSL_CERT_PATH | postgres-ssl-cert-path | This parameter specifies the file name of the client SSL certificate. |
| PERCONA_TEST_PMM_DISABLE_BUILTIN_POSTGRES | Environment variable to disable built-in PMM server database. Note that Grafana depends on built-in PostgreSQL. And if the value of this variable is “true”, then it is necessary to pass all the parameters associated with Grafana to use external PostgreSQL. |
By default, communication between the PMM server and the database is not encrypted. To secure a connection, follow PostgeSQL SSL instructions and provide POSTGRES_SSL_* variables.
To use grafana with external PostgreSQL add GF_DATABASE_* environment variables accordingly.
Example
To use PostgreSQL as an external database:
- Generate all necessary SSL certificates.
-
Deploy PMM Server with certificates under read-only permissions and Grafana user and Grafana group.
```sh /pmm-server-certificates# la -la drwxr-xr-x 1 root root 4096 Apr 5 12:43 . drwxr-xr-x 1 root root 4096 Apr 5 12:43 .. -rw------- 1 grafana grafana 1391 Apr 5 12:38 certificate_authority.crt -rw------- 1 grafana grafana 1257 Apr 5 12:38 pmm_server.crt -rw------- 1 grafana grafana 1708 Apr 5 12:38 pmm_server.key ``` -
Attach
pg_hba.confand certificates to the PostgreSQL image.```sh /external-postgres-configuration# cat pg_hba.conf local all all trust hostnossl all example_user all reject hostssl all example_user all cert /external-postgres-certificates# ls -la drwxr-xr-x 1 root root 4096 Apr 5 12:38 . drwxr-xr-x 1 root root 4096 Apr 5 12:43 .. -rw------- 1 postgres postgres 1391 Apr 5 12:38 certificate_authority.crt -rw------- 1 postgres postgres 1407 Apr 5 12:38 external_postgres.crt -rw------- 1 postgres postgres 1708 Apr 5 12:38 external_postgres.key ``` -
Create
useranddatabasefor pmm-server to use. Set appropriate rights and access. -
Install
pg_stat_statementsin PostgreSQL in order to have all metrics according to this handy document. -
Run PostgreSQL server.
docker run --name external-postgres -e POSTGRES_PASSWORD=secret <image_id> postgres -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.max=10000 -c pg_stat_statements.track=all -c pg_stat_statements.save=off -c ssl=on -c ssl_ca_file=$CA_PATH -c ssl_key_file=$KEY_PATH -c ssl_cert_file=$CERT_PATH -c hba_file=$HBA_PATH -
Run PMM server.
docker run --name pmm-server -e PERCONA_TEST_POSTGRES_ADDR=$ADDRESS:$PORT -e PERCONA_TEST_POSTGRES_DBNAME=$DBNAME -e PERCONA_TEST_POSTGRES_USERNAME=$USER -e PERCONA_TEST_POSTGRES_DBPASSWORD=$PASSWORD -e PERCONA_TEST_POSTGRES_SSL_MODE=$SSL_MODE -e PERCONA_TEST_POSTGRES_SSL_CA_PATH=$CA_PATH -e PERCONA_TEST_POSTGRES_SSL_KEY_PATH=$KEY_PATH -e PERCONA_TEST_POSTGRES_SSL_CERT_PATH=$CERT_PATH -e PERCONA_TEST_PMM_DISABLE_BUILTIN_POSTGRES=true -e GF_DATABASE_URL=$GF_DATABASE_URL -e GF_DATABASE_SSL_MODE=$GF_SSL_MODE -e GF_DATABASE_CA_CERT_PATH=$GF_CA_PATH -e GF_DATABASE_CLIENT_KEY_PATH=$GF_KEY_PATH -e GF_DATABASE_CLIENT_CERT_PATH=$GF_CERT_PATH percona/pmm-server:2
Get expert help¶
If you need assistance, you can find comprehensive and free database knowledge on our community forum or blog posts. For professional support and services, contact our Percona Database Experts.