PostgreSQL: Instalación de PostgreSQL en Linux

Instalación de PostgreSQL en CentOS

Para utilizar el servidor de bases de datos PostgreSQL en CentOS es necesario instalar los paquetes postgresql y postgresql-server:

yum install postgresql-server postgresql

El paquete postgresql-server contiene los binarios del servidor DBMS, y postgresql las utilidades cliente para conectarnos a un servidor, así como documentación en formato HTML y las páginas "man".

El servicio no queda activado en el arranque, así que lo activaremos con la utilidad chkconfig:

# chkconfig postgresql on
# /etc/init.d/postgresql start
Iniciando la base de datos:                                [  OK  ]
Iniciando servicios postgresql:                            [  OK  ]

Conexión a un servidor PostgreSQL

Para conectarnos al servidor PostgreSQL nos cambiamos al usuario postgres y utilizamos el CLI psql, ejemplo:

# su - postgres
$ psql -d template1 -U postgres
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
    \h for help with SQL commands
    \? for help with psql commands
    \g or terminate with semicolon to execute query
    \q to quit

template1=#

Permitir conexiones remotas a PostgreSQL

Por seguridad, tras una instalación por defecto de PostgreSQL en CentOS, este aceptará únicamente conexiones locales en el puerto 5432/tcp:

# netstat -punta | grep LISTEN
tcp        0      0 127.0.0.1:5432              0.0.0.0:*                   LISTEN      15112/postmaster

Para modificar este comportamiento tenemos que editar el fichero /var/lib/pgsql/data/pg_hba.conf, que contiene la configuración para la autenticación de clientes y añadir el listado de las redes y/o IPs desde las que nos vamos a conectar:

# vim /var/lib/pgsql/data/pg_hba.conf
host all all 192.168.0.0/24 trust
host all all 10.10.0.1/32 trust

Además, tenemos que editar el fichero /var/lib/pgsql/data/postgresql.conf y modificar el parámetro listen_addresses para indicar que escuche en las interfaces necesarias, en este caso lo habilitaremos para todas:

# vim /var/lib/pgsql/data/postgresql.conf
listen_addresses='*'

El último paso es reiniciar el servicio y comprobar que los cambios se han aplicado correctamente:

# /etc/init.d/postgresql restart
Parando el servicio  postgresql:                           [  OK  ]
Iniciando servicios postgresql:                            [  OK  ]

# netstat -punta | grep LISTEN
tcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      15608/postmaster

En este ejemplo, permitimos el acceso a la red 192.168.0.0/24 y a la IP 10.10.0.1, quedando el servicio postmaster escuchando en todas las interfaces . Para mas información consultar el capítulo Client Authentication∞ de la guía de Administración de PostgreSQL∞.

Modificar la contraseña del usuario postgres de PostgreSQL

Para cambiar la contraseña de un usuario de PostgreSQL tenemos que utilizar la sentencia SQL ALTER USER usuario WITH PASSWORD:

# su - postgres
$ psql -d template1 -U postgres

template1=# ALTER USER postgres WITH PASSWORD 'zaQ6RzRhFb';
ALTER ROLE

Comprobar la versión de PostgreSQL instalada

La forma mas cómoda es ejecutando la sentencia SQL SELECT VERSION(), ejemplo:

template1=# SELECT VERSION();
              version                                                  
-----------------------------------------------------------------------------------------------------------
 PostgreSQL 8.1.11 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)
(1 fila)

Backup y restore de bases de datos en PostgreSQL

En PostgreSQL tenemos el comando pg_dump para realizar copias de seguridad de BBDD, su uso es muy similar al mysqldump de MySQL. En este ejemplo obtenemos un listado de todas las BBDD existentes y realizamos el backup de una de ellas:

$ LANG=en_US psql -l
   List of databases
  Name     |  Owner   | Encoding 
--------------+----------+----------
 postgres     | postgres | UTF8
 template0    | postgres | UTF8
 template1    | postgres | UTF8
 woop_pruebas | postgres | UTF8
(4 rows)

$ pg_dump woop_pruebas > /tmp/woop_pruebas.pg

El resultado es un fichero ASCII con todas las sentencias SQL necesarias para restaurar la BBDD.

Para restaurar una BBDD desde un fichero utilizaremos el comando:

$ psql -d woop_pruebas -f /tmp/woop_pruebas.pg
SET
SET
SET
COMMENT
REVOKE
REVOKE
GRANT
GRANT

Para hacer un backup de todas las BBDD podemos utilizar el comando pg_dumpall o realizar un pequeño script en BASH, ejemplo:

#!/bin/bash
DIR=/var/lib/pgsql/backups
LANG=en_US LIST=$(psql -l | awk '{ print $1}' | grep -vE '^-|^List|^Name|template[0|1]|^\(')
for db in $LIST
do
   pg_dump $db | gzip -c >  $DIR/$db.gz
done

Páginas

Instalación de PostgreSQL en Linux