Woop!

Administración y manual 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




Activar el modo de compatibilidad con versiones anteriores de PostgreSQL


Para habilitar la compatibilidad con versiones anteriores de PostgreSQL tenemos que editar el fichero /var/lib/pgsql/data/postgresql.conf y añadir las siguientes variables (mas información en el capítulo Version and Platform Compatibility∞):

add_missing_from = on
array_nulls = on
backslash_quote = safe_encoding
default_with_oids = on
escape_string_warning = on
standard_conforming_strings = off
regex_flavor = advanced
sql_inheritance = on











Podemos comprobar que el cambio de dichas variables de configuración se ha realizado correctamente con la siguiente consulta:

# su - postgres
-bash-3.2$ psql -d template1 -U postgres
template1=# SELECT name, setting FROM pg_settings ;


Si al añadir y/o modificar cualquier variable PostgreSQL no arranca tendremos que revisar el fichero de log /var/lib/pgsql/pgstartup.log para encontrar cualquier warning/error del tipo:

FATAL:  unrecognized configuration parameter "array_nulls"
FATAL:  parameter "standard_conforming_strings" cannot be changed




Instalación de PostgreSQL en un servidor con Plesk


NOTA: Para poder gestionar bases de datos PostgreSQL desde Plesk se necesita comprar el addon "Power Pack"∞!!

Una vez comprado el addon, el panel de control Plesk permite la gestión de bases de datos PostgreSQL.

Podemos instalar PostgreSQL en Plesk de dos formas: 1) desde el interfaz web (Home -> Updates -> PostgreSQL) y 2) desde una shell con el "autoinstaller":

# autoinstaller --select-release-current --install-component postgresql


El siguiente paso es activarlo en el inicio del servidor, arrancar el servicio y asignar una contraseña al usuario administrador "postgres"∞:

# chkconfig postgresql on
# /etc/init.d/postgresql start
# su - postgres
$ psql -d template1 -U postgres
template1=# ALTER USER postgres WITH PASSWORD 'password';
ALTER ROLE


Depués tenemos que entrar al interfaz web (Home -> Database Servers) y configurar el usuario administrador "postgres" con su contraseña, a partir de aquí podremos gestionar PostgreSQL desde el Plesk. Además, se instala la herramienta de adminstración web phpPgAdmin para los clientes junto con una utilidad muy básica pg_manage que permite parar, arrancar e reinciar el servicio PostgreSQL.



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.


Tip! Si utilizas RHEL/CentOS puedes utilizar el repositorio PowerStack∞ para ejecutar la última versión estable de PostgreSQL.



Referencias sobre PostgreSQL


Actualizado: 2011-05-10 17:28:28
Autor: SantiSaez

Buscar:

Powered by Woop!