Home

Instalación de PostgreSQL en CentOS 5.0

inombela's picture

Instalamos el servidor de Bases de datos PostgresQL en nuestro Server CentOs 5.0 para acceder desde el mismo a un Ubuntu Server 12.04  con otra Bases de datos PostgresQL. Véa como lo hicimos,

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.p 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 Authentication8 de la guía de Administración de PostgreSQL8.

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

Conexión a PostgreSQL desde PHP

Para conectarnos a PostgreSQL desde PHP necesitamos instalar el paquete php-pgsql, que propociona las extensiones pdo_pgsql.so y pgsql.so:

# yum install php-pgsql
# /etc/init.d/httpd restart

En una instalación de PostgreSQL sobre CentOS, el método de autenticación configurado por defecto es "ident" (servicio que no está habilitado por defecto), por lo que al conectarnos desde PHP obtendremos el siguiente error:

LOG:  could not connect to Ident server at address "127.0.0.1", port 113: Connection refused
FATAL:  Ident authentication failed for user "recover"

La solución pasa por editar el fichero /var/lib/pgsql/data/pg_hba.conf y cambiar el método de autenticación:

# vim /var/lib/pgsql/data/pg_hba.conf
(..)
#host    all         all         127.0.0.1/32          ident sameuser
host     all         all         127.0.0.1/32          md5

Actualizar el formato/esquema de las bases de datos PostgreSQL

Cuando actualizamos y/o cambiamos de versión un servidor PostgreSQL tenemos que actualizar también el formato/esquema de las bases de datos. En PostgreSQL no existe una herramienta similar al mysql_upgrade de MySQL que nos automatizaría la tarea, por lo que tenemos que realizar una serie de pasos:

1) Backup
2) Borrar/mover el contenido de $PGDATA y volver a inicializar con initdb el nuevo formato de las bases de datos PostgreSQL
3) Restore

# /etc/init.d/postgresql stop
# su - postgres
$ pg_dump woop_pruebas > /tmp/woop_pruebas.pg
$ rm -rf $PGDATA
$ initdb
$ psql -d woop_pruebas -f /tmp/woop_pruebas.pg
# /etc/init.d/postgresql start

Si tras actualizar una instacia de PostgreSQL no actualizamos el formato de las bases de datos al arrancar nos encontraríamos con este error:

An old version of the database format was found.
You need to upgrade the data format before using PostgreSQL.
See /usr/share/doc/postgresql-8.4.7/README.rpm-dist for more information.

Nota: Si utilizas RHEL/CentOS puedes utilizar el repositorio PowerStack8 para ejecutar la última versión estable de PostgreSQL.

 

Fuente: 

Centro Práctico - LEMUR SOLUTION

Contacto: 
inombela