PostgreSQL
Entre las bases de datos de tipo SQL, PostgreSQL cada vez está liderando más y más el mercado. Su motor es más que suficiente para la gran mayoría de usos y además es gratis. Debido a ello la he elegido para ser mi base de datos principal.
Instalación
Cuando se actualiza un PostgreSQL, también se deben actualizar todas las bases de datos que contiene, debido a ello y para evitar errores al actualizar el sistema operativo u otro servicio, después de instalarlo se bloquea para que no sea actualizado a no ser que se desbloquee previamente.
sudo apt update
sudo apt install postgresql postgresql-contrib -y
sudo apt-mark hold postgresql postgresql-contrib
Acceso remoto
Por defecto únicamente se puede acceder desde localhost, pero el uso que yo le daré requiere acceso externo, por ello habilito el acceso a cualquier IP y a cualquier base de datos.
sudo vi /etc/postgresql/14/main/postgresql.conf
El path puede variar según la versión de PostgreSQL, en este caso es la 14.
Se habilita el uso del puerto a cualquier IP
listen_addresses = '*'
Lo siguiente es habilitar para las bases de datos
sudo vi /etc/postgresql/14/main/pg_hba.conf
Permitiendo el acceso a cualquiera y utilizando la contraseña.
host all all 0.0.0.0/0 md5
host all all ::0/0 md5
Tras reiniciar ya será accesible
sudo systemctl restart postgresql
Crear bases de datos
El acceso a la consola de PostgreSQL se hace desde su usuario.
sudo -i -u postgres
psql
Desde ella se crea la base de datos, usuario con contraseña, y se asigna los permisos.
Este es un ejemplo dando todos los permisos a un usuario sobre una base de datos.
CREATE DATABASE database_name;
CREATE USER user_name WITH PASSWORD 'my_password';
GRANT ALL PRIVILEGES ON DATABASE database_name TO user_name;
Acceso al esquema public
Desde la versión 15, ningún usuario tiene acceso al esquema public, siendo este el de por defecto.
Algunas aplicaciones permiten crear su propio esquema, pero no siempre es el caso. En estos casos se requieren permisos extras al anteriormente indicado.
\c database_name
GRANT ALL ON SCHEMA public TO user_name;
Copias de seguridad
Todas las bases de datos requieren realizar copias de seguridad, para ello utilizo el proceso con notificaciones con Nagios ya preparado en la instalación de Ubuntu.
Para este hay que descargar el script para PostgreSQL que utiliza pg_dump para crear las copias de seguridad.
cd /opt/scripts
sudo wget https://gitlab.com/ReiIzumi/scripts-backup/-/raw/master/backup_PostgreSQL.sh
sudo chmod 744 backup_PostgreSQL.sh
Este script tiene como dependencia nc para confirmar que el puerto está habilitado.
sudo apt install netcat-traditional -y
Finalmente editamos el fichero
sudo vi backup_PostgreSQL.sh
En condiciones normales solo hay que indicar el nombre de las bases de datos.
POSTGRESQL_DATABASES="dbName1 dbName2"
Es recomendable ejecutar una copia para confirmar que todo funciona.
sudo ./backup_PostgreSQL.sh
Cron
Las copias de seguridad se deben ejecutar automáticamente, para ello necesitaremos cron.
sudo apt install cron -y
sudo crontab -e
Este ejemplo ejecutará la copia de seguridad cada día a las 10 de la mañana.
00 10 * * * /opt/scripts/backup_PostgreSQL.sh
Nagios
Para monitorizar el sistema, habrá que añadir las siguientes definiciones al servidor previamente creado en Nagios, teniendo en cuenta actualizar la hora de inicio acorde a la hora indicada en Cron en el segundo script, y actualizando los tiempos (en minutos) para que ejecute el aviso de warning o error tras no encontrar la nueva copia de seguridad.
define service {
use local-service
host_name Ubuntu1
service_description PostgreSQL
check_command check_tcp!5432
servicegroups DB
}
define service {
use local-service
host_name Ubuntu1
service_description Backup PostgreSQL
check_command check_nrpe_bkp!PostgreSQL 10:00:00 5 10
servicegroups Backup
max_check_attempts 1
}