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:

1. Generate all necessary SSL certificates.
2. Deploy PMM Server with certificates under read-only permissions and Grafana user and Grafana group.

   /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
   

3. Attach pg_hba.conf and certificates to the PostgreSQL image.

   /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
   

4. Create user and database for pmm-server to use. Set appropriate rights and access.
5. Install pg_stat_statements in PostgreSQL in order to have all metrics according to this handy document.
6. 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
   

7. 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, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.

Community Forum Get a Percona Expert