PostgreSQL PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.
To get the container up and running you must provide your own pg_hba.conf, pg_ident.conf and postgresql.conf. The default config files from version 16 are attached in this project (comments removed) but you should override deployment config with configmaps.
TLS support is enabled by default. To to setup for testing use a self-signed certificate.
Create the certificate authority root key
openssl req -new -nodes -text -out root.csr \
-keyout root.key -subj "/CN=psq.gautier.org"Sign the root certificate signing request to create the root certificate (Note: the extfile path is for macOS)
openssl x509 -req -in root.csr -text -days 3650 \
-extfile /System/Library/OpenSSL/openssl.cnf \
-extensions v3_ca -signkey root.key -out root.crtGenerate the server.key file and the server.csr
openssl req -new -nodes -text -out server.csr \
-keyout server.key -subj "/CN=psql.gautier.org"Finally generate the server.crt
openssl x509 -req -in server.csr -text -days 3650 \
-CA root.crt -CAkey root.key -CAcreateserial \
-out server.crtserver.key and server.crt need to be on the postgres server as defined in the postgresql.conf key ssl_key_file and ssl_cert_file.
root.crt should be stored on the client, so the client can verify that the server’s certificate was signed by the certification authority. Note: this is only for self signed certs.
root.key should be stored offline for use in creating future certificates.
The mode of the key file must be specifically set chmod og-rwx server.key.
# | SSL/TLS
# ╰―――――――――――――――――――――――――
ssl = on
ssl_key_file = '/mnt/volumes/secrets/tls.key'
ssl_cert_file = '/mnt/volumes/secrets/tls.crt'
#ssl_ca_file = ''
#ssl_crl_file = ''
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_min_protocol_version = 'TLSv1.2'
#ssl_max_protocol_version = ''
#ssl_dh_params_file = ''
#ssl_passphrase_command = ''
#ssl_passphrase_command_supports_reload = offThis container is designed to deploy in a kubernetes cluster. The deployment mechanism provides an availability of 99.9% (Downtime Monthly: 4m 21s). Higher availability is not needed as this database backs applications that have a limited user base and has zero public access.
Downtime due to maintenance is mitigated with local development environment based on compose and the CICD process.
Currently disaster recover is manual. The container health mechanism should provide the advanced notice of disaster states that would cause down-time.
hc-disk hc-postgres
/usr/bin/psql -c "SELECT pg_reload_conf();"