PostgreSQL: Preparar una base de datos remota

De PostgreSQL ya hemos hablado en varias entradas en la web, en el día de hoy veremos como preparar una base de datos remota. Para ello nos serviremos de la consola de comandos en un servidor con Centos 7.

Como seguramente ya sabrás, por defecto, para empezar a trabajar con este motor de base de datos, debemos utilizar la primera vez un script, «engine-setup«, que configura las bases de datos básicas, para el buen funcionamiento de este. En muchas casos necesitaremos una configuración diferente de la standard para empezar a trabajar.

Preparar el acceso remoto a una base de datos con PostgreSQL

Partimos de la base que acabamos de instalar este motor de base de datos, como por ejemplo en Centos o en Debian. Una vez hecho, utilizando «systemd«, tendremos que arrancar el ‘daemon‘, pero antes de eso, debemo inicilizarlo. Si queremos, por ejemplo, que no utilice la configuración local anglosajona, esto es, «en_US.UTF8«, por otra, como la española «es_ES.UTF8«, debemos escribir:

Podemos consultar las configuraciones a utilizar en nuestro sistema, utilizando el comando locale -a

También podemos indicar donde se almacenarán las bases de datos, añadiendo --pgdata=/var/lib/pgsql/data/

su -l postgres -c "/usr/bin/initdb --locale=es_ES.UTF8 --auth='ident' --pgdata=/var/lib/pgsql/data"

Una vez hecho esto, ya podemos encender el servicio o daemon, y añadirlo al arranque:

systemctl start postgresql.service
systemctl enable postgresql.service

Accedemos a la consola de administración:

# su - postgres
$ psql

Si todo ha ido bien, veremos las bases de datos creadas, como a nosotros nos interesa:

postgres=# \l
                                 List OF DATABASES
   Name    |  Owner   | Encoding |  COLLATE   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | es_ES.UTF8 | es_ES.UTF8 |
 template0 | postgres | UTF8     | es_ES.UTF8 | es_ES.UTF8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | es_ES.UTF8 | es_ES.UTF8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres

Crear una usuario por defecto

Otra parte importante es crear un usuario por defecto. El usuario por defecto es ‘engine‘, y del almacén de datos (Data Warehouse) es ‘ovirt_engine_history’

postgres=# CREATE ROLE nombre_usuario WITH login encrypted password 'password';

Crear una base de datos

En cambio, para crear una base de datos:

CREATE DATABASE nombre_base_de_datos owner nombre_usuario template template0

Para conectarnos a la nueva base de datos:

\c nombre_base_de_datos

Habilitar la  conexión remota

Para poder acceder remotamente, nos tenemos que asegurar que está habilitado el cifrado MD5, consultando el fichero de configuración /var/lib/pgsql/data/pg_hba.conf, añadiendo la siguiente línea, justo después de la línea que comienza con local.

host    nombre_base_de_datos    nombre_usuario    X.X.X.X/32   md5
host    nombre_base_de_datos    nombre_usuario    ::0/0      md5

También habilitamos la IP remota que necesitará acceder, editando el fichero /var/lib/pgsql/data/postgresql.conf, modificando la siguiente línea:

listen_addresses='*'

En este ejemplo se habilita el acceso desde todas las interfaces. Aquí podemos especificar una IP concreta,

Habilitar el puerto en el cortafuegos

Si queremos habilitar el puerto, en el cortafuegos, podemo hacerlo de la siguiente manera:

yum install iptables-services
iptables -I INPUT 5 -p tcp --dport 5432 -j ACCEPT
service iptables save

Configuración avanzada

Podemos ir más al detalle en la confiugración del motor, modificando el fichero /var/lib/pgsql/data/postgresql.conf, por ejemplo:

autovacuum_vacuum_scale_factor='0.01'
autovacuum_analyze_scale_factor='0.075'
autovacuum_max_workers='6'
maintenance_work_mem='65536'
max_connections='150'

Una vez hechos los cambios, para que se apliquen, debemos reiniciar:

systemctl restart postgresql.service

Y con esto ya lo tendrmeos listo. Espero que esta entrada os sea de utilidad en algún momento.