PostgreSQL

postgres és el servidor de bases de dades de Sora: una VM amb PostgreSQL del repositori PGDG i PgBouncer al davant, el seu exporter de Prometheus, un bolcat nocturn de totes les bases de dades a NFS i un script de restauració manual.

Un únic servidor per entorn guarda les bases de dades de totes les aplicacions, cadascuna amb el seu propi usuari i base de dades.

PostgreSQL	Del repositori PGDG. postgres_version fixa una versió major; buit instal·la l'última
Connexions	Aplicacions per PgBouncer :6432 en mode transacció; administració per :5432; scram-sha-256 a tot arreu
Ajustos	Calculats segons la memòria i els nuclis de la màquina, sobreescrivibles un a un
Mètriques	Node Exporter a 9100, postgres_exporter a 9187
Còpies	Totes les bases de dades a NFS segons backup_cron, set còpies
Maquinari	4 vCPU, 8 GiB, disc arrel de 32 GiB i disc de dades de 100 GiB per defecte

Desplegament

La plantilla PostgreSQL Server es desplega en tres fases:

Fase	Treball	Què instal·la
1 infra	vm	La màquina amb el seu disc de dades
2 database	postgres	PostgreSQL a /data, pg_hba.conf, els ajustos i la contrasenya del superusuari
3 services	pooler, exporter, backup	PgBouncer, l'exporter i les còpies de seguretat, en paral·lel

Abans de crear l'stack, a Inventari › Carpetes compartides cal donar accés d'escriptura a l'adreça de la VM a la carpeta NFS de còpies de seguretat. Horizon només ofereix les carpetes NFS on la màquina pot escriure. El mateix servidor NFS també li ha de permetre l'accés.

Després, crear l'stack amb el seu registre DNS, clau SSH i servidor Proxmox, i respondre:

Secció	Què decidir
PostgreSQL	La versió major (buit per a l'última) i la contrasenya del superusuari (buit per generar-la)
Tuning	storage_type (què és realment el disc de dades) i qualsevol ajust extra a settings
Pooler	Els valors per defecte, llevat que una aplicació necessiti el mode session
Backup	El servidor i la carpeta NFS, i l'hora del bolcat

La contrasenya del superusuari queda a la sortida pgadmin_password del treball postgres.

A més dels checks base, Nagios vigila PostgreSQL (5432), PgBouncer (6432), la còpia de seguretat i l'exporter.

Crear un usuari

Cada aplicació necessita el seu propi usuari i base de dades. Es creen amb l'acció Create User de l'stack: les dades de connexió arriben ja emplenades des del treball postgres, així que només es pregunta el nom. Si la contrasenya es deixa buida, es genera i es retorna a dbuser_password.

L'usuari es pot connectar per PgBouncer al moment: PgBouncer consulta les credencials directament a PostgreSQL, així que no cal registrar-hi res. Destruir una instància de l'acció esborra l'usuari i la base de dades; els bolcats de l'NFS es mantenen.

PgBouncer en mode transacció

Una connexió al servidor pertany a un client només durant una transacció. No sobreviuen: els SET de sessió (fer servir SET LOCAL o ALTER ROLE app SET ...), els advisory locks entre transaccions, LISTEN/NOTIFY, els cursors WITH HOLD i les taules temporals. Els prepared statements dels drivers sí que funcionen. Si una aplicació necessita alguna cosa de les anteriors, es connecta al 5432 o l'stack fa servir pool_mode = session.

Canviar ajustos

Cada ajust és una variable, i editar-la torna a executar únicament el seu treball:

• Els ajustos de tuning reescriuen la configuració i recarreguen; només reinicien si el paràmetre ho necessita (shared_buffers, max_connections, ...). Si PostgreSQL rebutja un valor, es restaura la configuració anterior.
• Els ajustos del pooler reinicien PgBouncer.
• L'horari de la còpia reescriu el cron i el check de Nagios.

Per ampliar la màquina, canviar la CPU o la memòria al treball vm i després fer Run again a postgres, que recalcula els ajustos. Canviar postgres_version en un servidor desplegat es rebutja: una versió major s'actualitza a mà amb pg_upgradecluster.

Còpies de seguretat

El bolcat s'executa cada nit segons backup_cron i una vegada durant el desplegament, de manera que un servidor nou no queda mai sense còpia. Rota les carpetes numerades a /mnt/backup/<vm>/ (01 és la més recent, se'n mantenen set) i escriu un pg_dump per cada base de dades. Una base de dades nova entra a la còpia sense fer res.

El check de Nagios es posa en warning si la còpia no ha acabat bé passats backup_check_warning minuts des de la seva hora, i en critical passats backup_check_critical.

Restaurar

La restauració és sempre manual, amb l'script que s'instal·la al costat dels de còpia:

ssh [email protected]
ls /mnt/backup/mioakiyama/01/
sudo /opt/scripts/restore_PostgreSQL.sh keycloak /mnt/backup/mioakiyama/01/keycloak_2026-09-04_06-00-01.sql

Documentació

• architecture.md: components, ports, credencials i mida.
• tuning.md: cada ajust, el seu motiu i les mesures que el sustenten.
• operations.md: desplegament, usuaris, el pooler, canvis i actualitzacions.
• backup.md: la còpia, la seva monitorització i la restauració.