DBLink con parametros en PostgreSQL

Introducción
Hace unos días una persona me dejó una duda a modo de comentario en la entrada de DBLinks en PostgreSQL. Esta duda es sencilla, y, a la vez interesante, por eso, he decidido crear una entrada explicando la solución.

La duda
¿Es posible crear una vista en PostgreSQL utilizando un DBLink con parámetros dinámicamente?

Es decir, lo que queremos es hacer los siguiente:

SELECT device_name FROM remote_database_v(param1, param2);

La solución
Sobre si es posible crear una Vista con un DBLink parametrizado, lo cierto es que no se puede, es decir, no podemos crear una vista pasando un argumento.

Sin embargo, podemos crear un procedimiento que nos devuelva un tipo "record" y pasarle como parámetros la cadena de conexión, por ejemplo:

CREATE OR REPLACE FUNCTION test_dblink_with_parameter(dbname character varying, dbhost character varying, dbuser character varying, dbuserpass character varying)
  RETURNS SETOF record AS
$BODY$
    SELECT t.device_name
    FROM dblink('dbname=' || $1 || '  port=5432 host=' || $2 || ' user=' || $3 ||' password=' || $4 , 'SELECT device_name FROM devices.as_device') as
     t(device_name character varying);
$BODY$
  LANGUAGE sql VOLATILE;

Y ahora podemos llamar a nuestro procedimiento como si fuese una tabla o vista.

Recordar que al ser de tipo "record" tenemos que decirle cómo es el formato del registro <AS (field1 type, field2 type, fieldn type)>, en nuestor caso devuelve el campo "as_device" que es de type "character varying".

SELECT * FROM test_dblink_with_parameter('dbname','localhost','user','password') AS (device_name character varying);

Algunas Mejoras
En función de los datos que estamos obteniendo, podemos, por ejemplo, optimizar el coste <COST> y el número de rows <ROWS> del procedimiento. En el ejemplo, hemos dejado el coste y el número de rows "por defecto",  COST 100 y  ROWS 100.

Referencias

• Instalación de PostgreSQL 9.0 en OpenIndiana
• Instalación de PostgreSQL en Solaris 10
• Copias de Seguridad en PostgreSQL 
• PostgreSQL 9.0.3 x86 64bit para OpenIndiana con UUID
• Instalación de OSSP UUID en OpenIndiana

Cómo clonar una tabla en PostgreSQL

Introducción
Alguna vez hemos necesitado clonar una tabla, tanto estructura como contenido desde el propio SQL sin tener que recurrir a <pg_dump>.
Tal vez alguno esté pensando en un CREATE TABLE AS SELECT ... pero lo cierto es que esta solución no nos crea los modificadores, índices, etc.

Sin embargo, no os preocupéis, que en PostgreSQL se puede hacer todo, o casi, y este caso sí que está muy resuelto.

Pasos Ejemplo de Clonado "completo"
A continuación os muestro -de una forma sencilla- lo que vamos a hacer, paso a paso para que no resulte complicado:

1. Crearemos una tabla llamada test con los campos company y homepage
2. Añadiremos un índice único en company
3. Añadiremos un par de compañías
4. Crearemos una tabla nueva copiando el contenido de la tabla con un CREATE TABLE  AS SELECT para comprobar cómo no clona los índices, defaults, etc.
5. Utilizaremos un clonado completo

Una vez explicado, vamos a ponernos manos a la obra

search=> CREATE TABLE test(company VARCHAR(20) NOT NULL, homepage VARCHAR(40) NOT NULL);
CREATE TABLE
search=> CREATE UNIQUE INDEX test_company_uq ON test(company);
CREATE INDEX
search=> \d test
             Table "public.test"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 company  | character varying(20) | not null
 homepage | character varying(40) | not null
Indexes:
    "test_company_uq" UNIQUE, btree (company)

Ahora, añadimos un par de registros

search=> INSERT INTO test(company,homepage) VALUES('SFChildren', 'http://www.sfchildren.com');
INSERT 0 1
search=> INSERT INTO test(company,homepage) VALUES('HavocTec', 'http://www.havoctec.com');
INSERT 0 1
search=> SELECT * FROM test;
  company   |         homepage         
------------+---------------------------
 SFChildren | http://www.sfchildren.com
 HavocTec   | http://www.havoctec.com
(2 rows)

Copiamos su contenido utilizando CREATE TABLE newTable AS SELECT * FROM sourceTable y comprobaremos su resultado

search=> CREATE TABLE test2 AS SELECT * FROM test;
SELECT
search=> \d test2
             Table "public.test2"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 company  | character varying(20) |
 homepage | character varying(40) |

Como podemos observar, lo primero que nos falta es el índice que hemos creado <test_company_uq> y los modificadores <NOT NULL> en ambos campos.

Es decir, utilizando esta forma copiamos el contenido y estructura de campos pero no sus modificadores, pero, que no cunda el pánico, vamos a ver cómo lo podemos hacer de una forma sencilla.

Borramos la tabla test2 que hemos creado para poder volver a clonarla, esta vez de forma completa.

search=> DROP TABLE test2;
DROP TABLE

Clonar "todo", para ello vamos a utilizar los modificadores LIKE tabla INCLUIDING [DEFAULTS | CONSTRAINTS | INDEXES]. En nuestro ejemplo, queremos "clonar" toda la estructura  así que utilizamos todas.

search=> CREATE TABLE test2 (LIKE test INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
CREATE TABLE
search=> \d test2
             Table "public.test2"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 company  | character varying(20) | not null
 homepage | character varying(40) | not null
Indexes:
    "test2_company_key" UNIQUE, btree (company)

Conclusiones
Aunque el ejemplo es sencillo, la idea era mostrar la potencia de la opción LIKE ... INCLUIDING sin tener que mostrar una estructura muy compleja.

Además, para aquellos que no os guste el SQL, siempre podéis utilizar el comando <pg_dump> para exportar una tabla.


Referencias

• Copias de Seguridad en PostgreSQL - Parte 1
• Instalación de PostgreSQL en OpenIndiana - Parte 1

Instalar PostgreSQL 9.0.3 64bit en OpenIndiana con soporte UUID

Introducción
Continuando con algunas de las características de los módulos "contrib" que tenemos en PostgreSQL, y de algunas dependencias de OpenBravo, vamos a ver cómo podemos Instalar PostgreSQL 9.0.3 en 64bits sobre OpenIndiana con soporte para UUID.

Requisitos Previos
Antes de poder continuar, es necesario Instalar OSSP-UUID en OpenIndiana, en nuestro caso en 64bits -como vimos anteriormente-

Además, os recomiendo -si no lo sabéis ya- leer los post sobre Instalar PostgreSQL 9.x en OpenIndiana y Requisitos de Instalación de PostgreSQL 9.x en OpenIndiana

Instalación desde el Binario
Para aquellos que no tengáis tiempo -o ganas- os dejo una versión de PostgreSQL 9.0.3 64bits con soporte para OSSP UUID que simplemente hay que descargar e Instalar el módulo en PostgreSQL -ver más abajo-

root@openzooey:/# cd /
root@openzooey:/# wget http://blog.sfchildren.com/blogger/postgres/9.0.3/openindiana/postgresql-9.0.3-x86-64bit-openindiana-with-uuid.tar.gz
root@openzooey:/# gzip -dc postgresql-9.0.3-x86-64bit-openindiana-with-uuid.tar.gz | tar xpf -
root@openzooey:/# chown -R postgres:postgres /u01
root@openzooey:/# rm postgresql-9.0.3-x86-64bit-openindiana-with-uuid.tar.gz 

Compilación de PostgreSQL
La compilación de PostgreSQL no supone mayor problema que añadir en el script de configuración la opción <--with-ossp-uuid> para que nos permita utilizar nuestro creador de UUID.

Sin embargo, tendremos que exportar nuestro <LD_LIBRARY_PATH> para que el script <configure> pueda "testear" correctamente el acceso a la biblioteca "uuid" que hemos compilado.

Una vez hecho esto, la compilación es, igual que siempre. Vamos a verlo:

$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/ossp-uuid/lib/64
$ export CC=cc
$ export CFLAGS="-xO3  -xspace -Xa  -xildoff  -m64  -xc99=none -xCC -fast -native"
$ export LDFLAGS="-R/usr/sfw/lib/64 -R/usr/lib/64 -R/opt/ossp-uuid/lib/64"
$ ./configure \
    --prefix=/u01/app/postgres/9.0/db \
    --exec-prefix=/u01/app/postgres/9.0/db \
    --bindir=/u01/app/postgres/9.0/db/bin/64 \
    --libexecdir=/u01/app/postgres/9.0/db/bin/64 \
    --sbindir=/u01/app/postgres/9.0/db/bin/64 \
    --datadir=/u01/app/postgres/9.0/db/share \
    --sysconfdir=/u01/app/postgres/9.0/db/etc \
    --mandir=/u01/app/postgres/9.0/db/man \
    --libdir=/u01/app/postgres/9.0/db/lib/64 \
    --includedir=/u01/app/postgres/9.0/db/include \
    --sharedstatedir=/var/postgres/9.0 \
    --localstatedir=/var/postgres/9.0 \
    --enable-nls \
    --docdir=/u01/app/postgres/9.0/db/doc \
    --with-system-tzdata=/usr/share/lib/zoneinfo \
    --with-python \
    --with-pam \
    --with-openssl \
    --with-libedit-preferred \
    --with-libxml \
    --with-libxslt \
    --with-gssapi \
    --enable-thread-safety \
    --enable-dtrace \
    --disable-integer-datetimes \
    --with-includes=/usr/include:/usr/sfw/include:/opt/ossp-uuid/include \
    --with-libs=/lib/64:/usr/lib/64:/usr/sfw/lib/64:/opt/ossp-uuid/lib/64 \
    --with-ossp-uuid

Compilación e Instalación
Como siempre, ejecutaremos los comando <make> y <pfexec make install>

$ make
$ pfexec make install

NOTA: El proceso de instalación completo se encuentra detallado en Cómo Instalar PostgreSQL 9 64bits en OpenIndiana con SMF

Compilación del Módulo UUID
Para compilar el módulo de UUID, será necesario tener instalado PostgreSQL y situarse en el directorio <contrib> del código fuente de PostgreSQL.

Además, tener en cuenta que debemos compilarlo con GNU Make y no con SVR4 Make. Para comprobar "cual tenéis en el path" simplemente se puede utilizar <which make> y ver cuál es, o, simplemente utilizamos <gmake>

itily@openzooey:~/postgresql-9.0.3$ cd contrib
itily@openzooey:~/postgresql-9.0.3/contrib$ cd uuid-ossp
itily@openzooey:~/postgresql-9.0.3/contrib/uuid-ossp$ gmake
itily@openzooey:~/postgresql-9.0.3/contrib/uuid-ossp$ pfexec gmake install

Esto nos creará una nueva biblioteca de enlace dinámico <uuid-ossp.so> en nuestro directorio <lib/64> de PostgreSQL:

itily@openzooey:~$ cd /u01/app/postgres/9.0/db/lib/64
itily@openzooey:/u01/app/postgres/9.0/db/lib/64$ ls -ltrh uuid-ossp.so
-rwxr-xr-x   1 root root   16K mar 30 21:09 uuid-ossp.so
itily@openzooey:/u01/app/postgres/9.0/db/lib/64$ file uuid-ossp.so
uuid-ossp.so:   ELF 64-bit LSB dynamic lib AMD64 Version 1, dynamically linked, not stripped
itily@openzooey:/u01/app/postgres/9.0/db/lib/64$ ldd uuid-ossp.so
        libuuid.so.16 => /opt/ossp-uuid/lib/64/libuuid.so.16
        libc.so.1 =>     /usr/lib/64/libc.so.1
        libm.so.2 =>     /lib/64/libm.so.2

Instalación en PostgreSQL
Por último, sólo nos queda crear las funciones en nuestra DB de PostgreSQL, para ello, utilizaremos el script <uuid-ossp.sql> que encontraremos en $POSTGRES_HOME/share/contrib

itily@openzooey:/$ cd /u01/app/postgres/9.0/db/share/contrib
itily@openzooey:/u01/app/postgres/9.0/db/share/contrib$ ls -ltr
total 7
-rw-r--r-- 1 root mar 30 21:09 uninstall_uuid-ossp.sql
-rw-r--r-- 1 root mar 30 21:09 uuid-ossp.sql

Deberemos añadirlo a las DB que queramos, utilizando el comando <psql> y un usuario con permisos para "crear languages"

postgres@openzooey:~$ /u01/app/postgres/9.0/db/bin/64/psql -U postgres -d testdb -f uuid-ossp.sql
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
postgres@openzooey:~$ /u01/app/postgres/9.0/db/bin/64/psql -U test -d testdb
psql (9.0.3)
Type "help" for help.

testdb=> SELECT uuid_generate_v3(uuid_ns_url(), 'http://www.postgresql.org');
           uuid_generate_v3          
--------------------------------------
 cf16fe52-3365-3a1f-8572-288d8d2aaa46
(1 row)

testdb=> SELECT uuid_generate_v3(uuid_ns_url(), 'http://www.havoctec.com/');
           uuid_generate_v3          
--------------------------------------
 5f2f3ed0-ce36-3968-b065-4bbaf6684d22
(1 row)

testdb=> \q

Desinstalación del módulo
Para poder desinstalarlo, simplemente utilizaremos el script <uninstall_uuid-ossp.sql> de la siguiente forma:

postgres@openzooey:~$ /u01/app/postgres/9.0/db/bin/64/psql -U postgres -d testdb -f uninstall_uuid-ossp.sql
SET
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION
DROP FUNCTION

Algunos Errores
Si no hemos podido instalar correctamente el módulo dentro de PostgreSQL, se nos mostrará el siguiente error al tratar de usar la función:

testdb=> SELECT uuid_generate_v3(uuid_ns_url(), 'http://www.postgresql.org');
ERROR:  function uuid_ns_url() does not exist
LINE 1: SELECT uuid_generate_v3(uuid_ns_url(), 'http://www.postgresq...
                                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Si intentamos crear las funciones con un usuario que no tiene permisos suficientes, tendremos el siguiente error:

postgres@openzooey:~$ /u01/app/postgres/9.0/db/bin/64/psql -U testuser -d testdb -f uuid-ossp.sql
SET
psql:uuid-ossp.sql:9: ERROR:  permission denied for language c
psql:uuid-ossp.sql:14: ERROR:  permission denied for language c
psql:uuid-ossp.sql:19: ERROR:  permission denied for language c
psql:uuid-ossp.sql:24: ERROR:  permission denied for language c
psql:uuid-ossp.sql:29: ERROR:  permission denied for language c
psql:uuid-ossp.sql:34: ERROR:  permission denied for language c
psql:uuid-ossp.sql:39: ERROR:  permission denied for language c
psql:uuid-ossp.sql:44: ERROR:  permission denied for language c
psql:uuid-ossp.sql:49: ERROR:  permission denied for language c
psql:uuid-ossp.sql:54: ERROR:  permission denied for language c

Conclusiones
Parece más complicado de lo que parece, pero al final, resulta bastante sencillo. Además, debemos recordar que a partir de ahora, tenemos que incluir OSSP-UUID como dependencia de nuestro PostgreSQL

Referencias

• Instalación de PostgreSQL 9.0 en OpenIndiana
• Instalación de PostgreSQL en Solaris 10
• Copias de Seguridad en PostgreSQL 
• PostgreSQL 9.0.3 x86 64bit para OpenIndiana con UUID
• Instalación de OSSP UUID en OpenIndiana

Compilar OSSP UUID en OpenIndiana para PostgreSQL

Introducción
Aunque PostgreSQL tiene un tipo de datos UUID nativo, no existe implementación para la generación de los mismos. Por eso, es necesario utilizar una biblioteca que lo implemente, en nuestro caso OSSP-UUID.

Antes de Empezar
Lo cierto es que la Instalación de OSSP-UUID no comienza con buen pie, ya que la página oficial proporciona un servidor ftp para su descarga, pero ... no ha sido posible, así que he tenido que utilizar algún mirror para poder descargarlo.

Después de este pequeño contratiempo, ya podemos comenzar con la compilación, no exenta de problemillas, así que si no tenéis mucho tiempo y/o ganas, podéis utilizar la versión que he compilado tanto en 32bit como 64bits, aunque en la versión de 64bits no he incluido soporte para C++ ya que ha sido imposible.

Instalación desde el binario
Para aquellos que no tengáis tiempo, podéis instalar OSSP-UUID desde el binario descargando y descomprimiendo sobre el directorio </opt>

root@openzooey:/# cd /opt
root@openzooey:/opt# wget http://blog.sfchildren.com/blogger/postgres/ossp-uuid/openindiana/ossp-uuid-1.6.2-OpenIndiana-x86-32bit-64bit.tar.gz
...
root@openzooey:/opt# gzip -dc ossp-uuid-1.6.2-OpenIndiana-x86-32bit-64bit.tar.gz | tar xpf -
root@openzooey:/opt# rm ossp-uuid-1.6.2-OpenIndiana-x86-32bit-64bit.tar.gz
root@openzooey:/opt# cd ossp-uuid
root@openzooey:/opt/ossp-uuid# bin/64/uuid
2645be62-5b18-11e0-8701-8f5adc896ddd
root@openzooey:/opt/ossp-uuid# bin/uuid
2a08e9ca-5b18-11e0-9914-47107445423d

Instalación desde el código fuente
Para la instalación desde el código fuente, deberemos tener en cuenta que la compilación en 64bits no es posible realizarla utilizando el flag <--with-cxx> y debemos separar las bibliotecas de 64bits de las de 32bits utilizando <--bindir=> y <--libdir>.

Además, hay que hacer un ajuste para que nos enlace de forma correcta la biblioteca, utilizando CC="cc -m64" ya que libtool no lo pasa correctamente

Descargamos el Código Fuente

itily@openzooey:~$ wget http://www.mirrorservice.org/sites/ftp.ossp.org/pkg/lib/uuid/uuid-1.6.2.tar.gz
itily@openzooey:~$ gzip -dc uuid-1.6.2.tar.gz | tar xpf -

Compilación en 64bits

itily@openzooey:~$ cd uuid-1.6.2
itily@openzooey:~/uuid-1.6.2$ export CC="cc -m64"
itily@openzooey:~/uuid-1.6.2$ export CXX=cc
itily@openzooey:~/uuid-1.6.2$ export CFLAGS="-xO3 -xspace -Xa -xildoff -m64 -xc99=none -fast -native -xCC"
itily@openzooey:~/uuid-1.6.2$ export CXXFLAGS="-xO3 -xspace -Xa -xildoff -m64 -xc99=none -fast -native -xCC"
itily@openzooey:~/uuid-1.6.2$ ./configure \
     --prefix=/opt/ossp-uuid \
     --bindir=/opt/ossp-uuid/bin/64 \
     --libdir=/opt/ossp-uuid/lib/64
itily@openzooey:~/a/uuid-1.6.2$ pfexec make install

Compilación en 32bits

itily@openzooey:~/uuid-1.6.2$ export CC="cc"
itily@openzooey:~/uuid-1.6.2$ export CFLAGS="-xO3 -xspace -Xa -xildoff -m32 -xc99=none -xCC"
itily@openzooey:~/uuid-1.6.2$ export CXXFLAGS=$CFLAGS
itily@openzooey:~/uuid-1.6.2$ ./configure \
     --prefix=/opt/ossp-uuid \
     --bindir=/opt/ossp-uuid/bin \
     --libdir=/opt/ossp-uuid/lib
itily@openzooey:~/uuid-1.6.2$ pfexec make install

Comprobación de Funcionamiento

itily@openzooey:~$ /opt/ossp-uuid/bin/uuid -v 4 -n 4
2dadf532-fddf-4902-b89b-4354f208b882
441823a3-5f73-4a9e-be28-0ed394091f7f
926c0d16-99b4-4d72-b5c7-e91933a4818a
c3347042-de2b-4407-9ba5-03ea4ef5a984
itily@openzooey:~$ /opt/ossp-uuid/bin/64/uuid -v 4 -n 4
4376f895-750a-4eef-a865-f06a89baed61
0e4bf68c-7386-4329-9aaa-9e3c3cbc0672
cb5134d4-5cbb-401b-aa70-0edcce7f1a04
ccabcd16-3227-4b59-b0de-3bad19d988f9

Instalar PostgreSQL 9.0.3 64bits con Soporte UUID en OpenIndiana >>

Conclusion
Bueno, al principio parece un poco complicado, aunque, al final, no es más que una instalación normal y, como siempre, son los 64bits los que más problemas nos dan.

Ahora sólo nos queda compilar correctamente nuestro PostgreSQL 9 con Soporte par UUID e Instalarlo en OpenIndiana

Refrencias

• Página oficial de OSSP UUID
• UUID para OpenIndiana x86 32bit y 64bit
• Instalar PostgreSQL 9.0.3 64bits en OpenIndiana con soporte UUID
• Instalar PostgreSQL 9.0.x 64bits en OpenIndiana

Instalación de PostgreSQL 9.0.3 en OpenIndiana utilizando SMF y RBAC, Requisitos

Introducción
Ya hemos hablado varias veces de Cómo Instalar PosgreSQL en OpenIndiana y, también Cómo Actualizar PostgreSQL en OpenIndiana.

En esta ocasión, vamos a compilar PostgreSQL 9.0.3 en 64bits y, debido a las peticiones que tengo por parte de vosotros, también en 32bits.

Además, veremos todos los requisitos necesarios para poder gestionar PostgreSQL 9.0.3 en OpenIndiana utilizando Solaris SMF y, utilizar RBAC para su gestión.

Antes de comenzar
He visto que alguno de vosotros tenéis problemas con la forma de instalación de PostgreSQL sobre OpenIndiana, y, al parecer también con el role <postgres> o los pasos de compilación.

Todos los pasos de compilación de estos post se realizan sobre OpenIndiana 64bits utilizando para la instalación el formato de <Text Install> y siguiendo los pasos de Instalar OpenIndiana en VirtualBox Paso a Paso

Así mismo, os recomiendo -si no lo habéis hecho antes- leer el post sobre Actualización de PostgreSQL 9.0.2 64bits en OpenIndiana, donde explico los pasos de una forma más detallada.

Upgrade desde OpenSolaris
Si hemos hecho el <upgrade> desde OpenSolaris, tendremos el role <postgres> y las autorizaciones necesarias, si por el contrario hemos Instalado OpenIndiana directamente, no existirán y las deberemos crear.


Comprobación de las Autorizaciones
Debemos comprobar también que tenemos las autoriaciones necesarias para poder gestionar el servicio <postgresql_9> en nuestro sistema a través de Solaris SMF.

itily@openzooey:~$ pfexec  cat /etc/security/auth_attr|grep postgres
solaris.smf.manage.postgres:::Manage Postgres service states::
solaris.smf.value.postgres:::Change Postgres value properties::

Si no nos devuelve nada, entonces deberemos crearlo, para ello añadiremos las autorizaciones de la siguiente forma

root@openzooey:/# echo "solaris.smf.manage.postgres:::Manage Postgres service states::" >> /etc/security/auth_attr
root@openzooey:/# echo "solaris.smf.value.postgres:::Change Postgres value properties::" >> /etc/security/auth_attr

Verificación del Profile <Postgres Administration>
Por último, debemos comprobar que tenemos el <profile> de Administración de PostgreSQL definido en nuestro sistema, para ello, haremos

itily@openzooey:~$ pfexec cat /etc/security/prof_attr|grep Postgres
Postgres Administration::::auths=solaris.smf.manage.postgres,solaris.smf.value.postgres

Si no nos devuelve nada, deberemos crearlo de la siguiente manera:

root@openzooey:/# echo "Postgres Administration::::auths=solaris.smf.manage.postgres,solaris.smf.value.postgres" >> /etc/security/prof_attr

Comprobación del Role <postgres>
Debemos comprobar que nuestro sistema tiene el role <postgres> creado, sino, lo crearemos nosotros, para ello, simplemente buscaremos en la base de datos </etc/user_attr> y nos fijaremos en el valor de <type=> para comprobar que es un <role>, por ejemplo

itily@openzooey:~$ pfexec cat /etc/user_attr|grep postgres
postgres::::type=role;profiles=Postgres Administration,All

Llegado a este punto, debemos tener en cuenta que si no existe el <role> postgres, comprobaremos si es un usuario, ya que en OpenIndiana viene como Usuario y no como Role, para ello comprobaremos que existe en </etc/passwd>

root@openzooey:/# cat /etc/passwd |grep postgres
postgres:x:90:90:PostgreSQL Reserved UID:/:/usr/bin/pfksh

Como vemos, existe como usuario, pero no dispone del <profile> Postgres Aministration que necesitamos, así que vamos a añadirlo utilizando la opción "-P 'Postgres Administration'".

Si intentamos modificar el <profile> de un usuario utilizando el comando <rolemod> OpenIndiana nos contestará con un mensaje come este:

root@openzooey:/# rolemod -P 'Postgres Administration' postgres
UX: rolemod: ERROR: Users must be modified with ``usermod''.

Así que utilizaremos el comando <usermod> con la misma opción

root@openzooey:/# usermod -P "Postgres Administration" postgres

Comprobamos que tenemos asignado correctamente todo, y que las autorizaciones están definidas. Para ello, utilizaremos el comando <auth postgres> para las autorizaciones y <profiles postgres> para los profiles.

root@openzooey:~# profiles postgres
postgres:
          Postgres Administration
          Basic Solaris User
          All
root@openzooey:~# auths postgres
 solaris.admin.wusb.read,
 solaris.device.cdrw,
 solaris.device.mount.removable,
 solaris.mail.mailq,
 solaris.profmgr.read,
 solaris.smf.manage.postgres,
 solaris.smf.value.postgres

Ahora debemos dedicir si queremos seguir manteniendo el usuario, o por el contrario convertirlo a un Role y así, poder delegar las tareas de administración.

Mi recomendación es convertirlo a un role -si no lo es-, para ello, utilizaremos el comando <usermod> y la opción <-K type=role> para convertirlo, por ejemplo:

root@openzooey:/# usermod -K type=role postgres
root@openzooey:/# cat /etc/user_attr|grep postgres
postgres::::type=role;profiles=Postgres Administration

Configurar base de datos de <pfexec>
Por último, deberemos definir los comandos que el <profile> Postgres Administration puede ejecutar utilizando <pfexec>, para ello, deberemos de dar de alta.

Es muy importante que entendáis que estáis haciendo, para ello, os recomiendo leer la serie de artículos sobre Solaris RBAC y Modelo de Privilegios.

Con esto, ya tenemos la estructura para poder gestionar nuestro PostgreSQL con RBAC

Comprobación de la Arquitectura y Kernel
A continuación, vamos a comprobar que tipo de arquitectura tenemos: SPARC o x86 y sobre esta, qué tipo de kernel tenemos, de 32bit o 64bit.

Para verificar nuestra arquitectura, utilizaremos el comando <uname -a> por ejemplo:

itily@openzooey:~$ pfexec uname -a
SunOS openzooey 5.11 oi_148 i86pc i386 i86pc

Hay que tener en cuenta que el comando <uname> no nos va a decir si es o no una arquitectura de 64bits, ya que nos dirá <i386>. Esto es porque la diferencia de arquitectura es para SPARC o i386.

Para saber si nuestro kernel es de 32bit o 64bits, deberemos utilizar el comando <isainfo> con las opciones <-kv>, por ejemplo:

itily@openzooey:~$ pfexec isainfo -kv
64-bit amd64 kernel modules

Por lo tanto, ahora sabemos que estamos ejecutando OpenIndiana -o Solaris- sobre una plataforma x86 con un kernel de 64bit.
 
Instalación de PostgreSQL 9.0.3 desde Binarios
Ahora que ya sabemos qué arquitectura tenemos, y nuestro tipo de kernel, podemos seleccionar la versión de PostgreSQL 9.0.3 que deseemos.

• PostgreSQL 9.0.3 i86pc 32bits para OpenIndiana
• PostgreSQL 9.0.3 i86pc 64bits para OpenIndiana

Simplemente, descargaremos el binario que queramos (de 32bit o 64bit) y lo descomprimiremos en nuestro sistema, en </>

itily@openzooey:/# cd /
itily@openzooey:/# wget http://blog.sfchildren.com/blogger/postgres/9.0.3/openindiana/postgresql-9.0.3-x86-64bit-openindiana.tar.gz
itily@openzooey:/# gzip -dc postgresql-9.0.3-x86-64bit-openindiana.tar.gz | tar xvpf -
itily@openzooey:/# chown -R postgres:postgres /u01
itily@openzooey:/# chmod 750 /u01

Configuración de PostgreSQL en SMF
A continuación configuraremos nuestro servicio postgres para que sea controlado mediante SMF, para ello, debemos descargar el Manifest para Solaris y OpenIndiana SMF  PostgreSQL 9 y el Method para Solaris y OpenIndiana SMF PostgreSQL 9

# cd /lib/svc/method
# wget http://blog.sfchildren.com/blogger/openindiana/postgresql/9.0/smf/postgres_9
# chown root:bin postgres_9
# chmod 555 postgres_9
# mkdir -p /var/svc/manifest/application/database
# cd /var/svc/manifest/application/database
# wget http://blog.sfchildren.com/blogger/openindiana/postgresql/9.0/smf/postgresql_9.xml
# svccfg
svc:> validate postgresql_9.xml
svc:> import postgresql_9.xml
svc:> quit
# svcs -p postgresql_9
STATE          STIME    FMRI
disabled        9:29:24 svc:/application/database/postgresql_9:default_32bit
disabled       10:24:40 svc:/application/database/postgresql_9:default_64bit

Conclusion
En esta ocasión hemos visto algunos puntos necesarios para la instalación de PostgreSQL en OpenIndiana.

Además, gracias a los comentarios y dudas, creo que en esta ocasión he intentado despejar esas "dudas" que os asaltan,

Espero que os ayude y, sobre todo, aclarar los pasos de instalación.


Referencias

• Actualizar PostgreSQL 9.0.2 en OpenIndiana
• Instalación de PostgreSQL 9.0 en OpenIndiana
• Instalación de PostgreSQL en Solaris 10
• Copias de Seguridad en PostgreSQL
• RBAC, Roles y Privilegios - Parte 1
• RBAC, Roles y Privilegios - Parte 2
• Manifest PostgreSQL 9.0 OpenIndiana
• Method PostgreSQL 9.0 OpenIndiana
• Página principal PostgreSQL

Actualización a PostgreSQL 9.0.2 en OpenIndiana b148

Introducción
En anteriores post hemos hablado de Cómo Instalar PostgreSQL 9.x en OpenIndiana, así que este será un revisión mínima, por ello os animos a revisarlo.

La nueva versión de PostgreSQL 9.0.2 corrige algunos problemas de memory leak y por ello, es muy recomendable su actualización. Podéis encontrar todos los cambios en el ChangeLog de PostgreSQL

En este post veremos cómo actualizar PostgreSQL 9.0 64bits a la versión PostgreSQL 9.0.2 64bits.

Antes de Comenzar
Bueno, la actualización a PostgreSQL 9.0.2 no requiere hacer un "dump/restore" siempre que nos encontremos en la línea PostgreSQL 9.x, sin embargo, yo recomiendo SIEMPRE hacer un backup antes de comenzar.

Si nos encontramos en una versión diferente, deberemos realizar un "dump/restore" obligatoriamente, así como una instalación "limpia" de PostgreSQL 9.x y, por lo tanto, deberemos iniciar la estrucutra de PGDATA.

Lo cierto, es que cuando comencé a escribir el post, hice una pequeña prueba de actualización "ahí a lo bruto" sin copia de seguridad ni nada, Pensé ¿total por qué va a fallar? Pues ... falló! y me quedé sin mi DB, menos mal que teneía copia de seguridad del día anterior y, que siendo sinceros, es una PostgreSQL de pruebas y no supone mayor problema.

Por eso, antes de comenzar, ya sabéis, si tenéis alguna duda de cómo se hacen, aquí os dejo un post de Cómo Realizar Copias de Seguridad en PostgreSQL.


Para versiones anteriores a PostgreSQL 9.x
Para aquellos que estéis en una versión anterior, os recomiendo primero Instalar PostgreSQL 9.0 en OpenIndiana, y que luego continueis con este post. Recordar además, que es necesario realizar un "dump/restore"

Para versiones PostgreSQL 9.x
Para los demás, comenzamos ... podéis realizar la actualización desde el código fuente, o instalar directamente los binarios.

Obtener el Codigo Fuente
Lo primero que debemos hacer es obtener el código fuente de PostgreSQL 9.0.2 que descargaremos de su página oficial, aquí os dejo un enlace a la página de descargas de PosgreSQL.


Instalar desde el Binario
Para aquellos que o bien no tenéis la posibilidad de compilarlo, o no os apetece :D, aquí os dejo la version compilada para x86 en 64bits de PostgreSQL que he realizado.

• PostgreSQL 64 bit x86 OpenIndiana b148

Una vez descargado podéis ir al paso "Instalar PostgreSQL" para completar la actualización.

Preparar el Entorno
Debemos comprobar que tenemos  todos los binarios necesarios para poder compilar, es decir, compilador, make,  etc.

Si activamos la opción <--enable-dtrace> deberemos tener el directorio </usr/sbin> en nuestro PATH.

itily@openzooey:~/postgres/postgresql-9.0.2$ export PATH=$PATH:/usr/sbin:/sbin:/usr/ccs/bin/
itily@openzooey:~/postgres/postgresql-9.0.2$ which dtrace
/usr/sbin/dtrace
itily@openzooey:~/postgres/postgresql-9.0.2$ which cc
/usr/bin/cc
itily@openzooey:~/postgres/postgresql-9.0.2$ which make
/usr/bin/make
itily@openzooey:~/postgres/postgresql-9.0.2$ which ar
/usr/bin/ar
itily@openzooey:~/postgres/postgresql-9.0.2$ which python
/usr/bin/python

Compilar PostgreSQL
Para compilar esta versión de PostgreSQL utilizaremos las mismas opciones que antes, por lo tanto, podemos hacer un <copy and paste>, de nuestro archivo <config.log>. Para aquellos que no tenéis el <config.log>, aquí os dejo los pasos.

Recordar que, yo utilizo como $POSTGRES_HOME siempre el directorio </u01/app/postgres/> al puro estilo OFA, ...

$ export CC=cc  
$ export CFLAGS="-xO3  -xspace -Xa  -xildoff  -m64  -xc99=none -xCC -fast -native"
$ export LDFLAGS="-R/usr/sfw/lib/64 -R/usr/lib/64"
$ ./configure \
--prefix=/u01/app/postgres/9.0/db \
--exec-prefix=/u01/app/postgres/9.0/db \
--bindir=/u01/app/postgres/9.0/db/bin/64 \
--libexecdir=/u01/app/postgres/9.0/db/bin/64 \
--sbindir=/u01/app/postgres/9.0/db/bin/64 \
--datadir=/u01/app/postgres/9.0/db/share \
--sysconfdir=/u01/app/postgres/9.0/db/etc \
--mandir=/u01/app/postgres/9.0/db/man \
--libdir=/u01/app/postgres/9.0/db/lib/64 \
--includedir=/u01/app/postgres/9.0/db/include \
--sharedstatedir=/var/postgres/9.0 \
--localstatedir=/var/postgres/9.0 \
--enable-nls \
--docdir=/u01/app/postgres/9.0/db/doc \
--with-system-tzdata=/usr/share/lib/zoneinfo \
--with-python \
--with-pam \
--with-openssl \
--with-libedit-preferred \
--with-libxml \
--with-libxslt \
--with-gssapi \
--enable-thread-safety \
--enable-dtrace \
--disable-integer-datetimes \
--with-includes=/usr/include:/usr/sfw/include \
--with-libs=/lib/64:/usr/lib/64:/usr/sfw/lib/64 \
$ make

Instalación de PostgreSQL
Como es una actualización, antes de lanzar nuestro <make install> -o descomprimir el <tar.gz>- debemos realizar una copia de seguridad y detener nuestra PostgreSQL de forma correcta, para ello, deberemos asumir el <role> de <postgres> o uno que tengan privilegios para detenerlo e iniciarlo.

En mi caso, utilizaré el <role postgres> para realizar las tareas.

Si es la primera vez que oyes hablar de roles y privilegios, te animo a que leas RBAC, Roles y Privilegios de Solaris antes de continuar.

Comprobamos que nuestro servicio de PostgreSQL está activo, hacemos un backup y lo desactivamos.

postgres@openzooey:~$ pfexec svcs postgresql_9:default_64bit
STATE          STIME    FMRI
online         11:26:07 svc:/application/database/postgresql_9:default_64bit

postgres@openzooey:~$ /u01/app/postgres/9.0/db/bin/64/pg_dump -C -Z9 -f /export/home/postgres/backups-pre-upgrade.dump.gz mydatabase
postgres@openzooey:~$ gzip -dc /export/home/postgres/backups-pre-upgrade.dump.gz |head -3
--
-- PostgreSQL database dump
--
postgres@openzooey:~$ pfexec /usr/sbin/svcadm disable postgresql_9:default_64bit
postgres@openzooey:~$ pfexec svcs postgresql_9:default_64bit
STATE          STIME    FMRI
disabled       11:33:19 svc:/application/database/postgresql_9:default_64bit

Ahora ya estamos en disposición de realizar nuestro <make install> y proceder a la instalación de PostgreSQL en nuestro sistema o, si has optado por actualizar desde el binario, a descomprimirlo.

Lanzar &lt:make install>

itily@openzooey:~/postgres/postgresql-9.0.2$ pfexec make install

O descargarlo y descromprimirlo -hay que hacerlo como <root> o con un usuario con permisos suficientes-

root@openzooey:/# cd /
root@openzooey:/# wget http://blog.sfchildren.com/blogger/postgres/9.0.2/openindiana/postgresql-9.0.2-x86-64bits.tar.gz
root@openzooey:/# gzip -dc postgresql-9.0.2-x86-64bits.tar.gz | tar xpf -
root@openzooey:/# chown -R postgres:postgres /u01
root@openzooey:/# rm postgresql-9.0.2-x86-64bits.tar.gz

Activamos el Servicio
Vamos a utilizar SMF para gestionar nuestro servicio, por lo tanto, sólo nos queda hacer un <enable> del mismo

root@openzooey:/# svcadm enable postgresql_9:default_64bit
root@openzooey:/# svcs postgresql_9:default_64bit
STATE          STIME    FMRI
online         13:05:36 svc:/application/database/postgresql_9:default_64bit

Comprobamos nuestro PostgreSQL

postgres@openzooey:~$ /u01/app/postgres/9.0/db/bin/64/psql -U postgres
psql (9.0.2)
Type "help" for help.

postgres=# select count(1) from pg_index;
 count
-------
   101
(1 row)

postgres=# \q

Conclusiones
Bueno, como hemos visto, la actualización de PostgreSQL no supone mayores problemas, siempre que estemos en el mismo nivel de versión, es decir, 9.x. Hemos dejado en el tintero las diferentes formas de actualización desde otras versiones, ya que eso requiere todo un post para ello.

Recordar que las buenas práticas de SysAdmin nos obligan a realizar copias de seguridad, y, que si por agún momento se nos pasa, entonces aparecerá nuestro querido amigo "Murphy" y nos dará algún susto.


Referencias

• Instalación de PostgreSQL 9.0 en OpenIndiana
• Instalación de PostgreSQL en Solaris 10
• Copias de Seguridad en PostgreSQL

Utilizar yesterday y today en PostgreSQL como Constantes

Introducción
Hace algunos días hablábamos de las opciones de Manejo de Fechas e Intervalos en PostgreSQL, y cómo se incluían unas <palabras reservadas> para obtener el día de ayer <yesterday> o de hoy <today>, por ejemplo:

search=> select 'today'::date;
    date  
------------
 2010-12-06
(1 row)

search=> select 'yesterday'::date;
    date  
------------
 2010-12-05
(1 row)

Partiendo de ello, vamos a ver algunas implicaciones de utilizar estas sustituciones dentro de Vistas, y cómo podemos solucionarlo.

Tabla de Ejemplo

Lo primero que vamos a hacer es crear una tabla llamada <havoc_test> donde pondremos un nombre y una fecha:

search=> create table havoc_test(nombre character varying, fecha date);

CREATE TABLE

Ahora, añadimos unos registros con los valores de fecha de <yesterday>, <today> y <now()>

search=> insert into havoc_test(nombre, fecha) values ('yesterday', 'yesterday'::date);

INSERT 0 1

search=> insert into havoc_test(nombre, fecha) values ('today', 'today'::date);

INSERT 0 1

search=> insert into havoc_test(nombre, fecha) values ('now', 'now()'::date);

INSERT 0 1

search=> select * from havoc_test;

  nombre   |   fecha    

-----------+------------

 yesterday | 2010-12-05

 today     | 2010-12-06

 now       | 2010-12-06

(3 rows)

Ahora, nos creamos una vista para obtener todos los registros de ayer, por ello, utilizaremos <fecha='yesterday'::date> como condición del where

search=> create or replace view yesterday_v as            select * from havoc_test where fecha = 'yesterday'::date;

CREATE VIEW

search=> select * from yesterday_v;

  nombre   |   fecha    

-----------+------------

 yesterday | 2010-12-05

(1 row)

El resultado es correcto, o mejor dicho, a simple vista es correcto, sin embargo, PostgreSQL nos sustituirá los valores de <yesterday> o <today> por los valores constantes, es decir, <2010-12-05> en nuestro caso. 

Si nos fijamos en cómo PostgreSQL ha creado la vista, veremos que ha sustituido el valor de <yesterday> por el día de ayer:

search=> \d yesterday_v;

       View "public.yesterday_v"

 Column |       Type        | Modifiers 

--------+-------------------+-----------

 nombre | character varying | 

 fecha  | date              | 

View definition:

 SELECT havoc_test.nombre, havoc_test.fecha

   FROM havoc_test

  WHERE havoc_test.fecha = '2010-12-05'::date;

Cuando nosotros lo que queríamos era que, en función del día actual, nos mostrase "ayer", pero no de forma constante.

Para solucionar este problema, simplemente pondremos <yesterday> como constante de texto, es decir, construiremos la cadena "al vuelo" y así PostgreSQL no realizará la sustitución hasta su ejecución. Veamos un ejemplo:

search=> create or replace view yesterday_v as
select * from havoc_test where fecha = cast('' || 'yesterday' || '' as date);
CREATE VIEW
search=> \d yesterday_v; 
View "public.yesterday_v"
 Column |       Type        | Modifiers
--------+-------------------+-----------
 nombre | character varying |
 fecha  | date              |
View definition:
 SELECT havoc_test.nombre, havoc_test.fecha
   FROM havoc_test
  WHERE havoc_test.fecha = (((''::text || 'yesterday'::text) || ''::text)::date);

search=> select * from yesterday_v; 
  nombre   |   fecha  
-----------+------------
 yesterday | 2010-12-05
(1 row)

Conclusiones
Como podemos comprobar, ahora hemos conseguido lo que inicialmente queríamos, tratar los valores de <yesterday> como constante.

Referencias

• Uso de Intervalos en PostgreSQL
• Instalación de PostgreSQL en OpenIndiana

Intervalos y Fechas en PostgreSQL

Introducción
Muchas veces nos encontramos con la necesidad de realizar cálculos con fechas, y siempre son problemáticas. Ya hablamos en otra ocasión sobre Gestión de Husos Horarios en PostgreSQL y hoy vamos a ver algunas funcionalidades que nos ofrece PostgreSQL para movernos con fechas.

CURRENT_DATE y now()
La funcione <current_date> nos permite saber la fecha actual, sin embargo, no sabemos la hora ni su desplazamiento -ya que es un tipo date-.

Si utilizamos la función <now()> obtendremos la fecha-hora actual con su desplazamiento horario, por ejemplo

search=> select current_date;
    date   
------------
 2010-11-05
(1 row)

search=> select now();
              now             
-------------------------------
 2010-11-05 09:14:18.450761+01
(1 row)

También podemos utilizar <current_timestamp> que, a efectos prácticos, es lo mismo que <now()>, por ejemplo

search=> select current_timestamp;
              now             
-------------------------------
 2010-11-05 09:18:13.745486+01
(1 row)

search=> select now(), current_timestamp;
              now              |              now             
-------------------------------+-------------------------------
 2010-11-05 09:18:27.408072+01 | 2010-11-05 09:18:27.408072+01
(1 row)

Uso de Intervalos
Hasta aquí nada nuevo, sin embargo, si quiero obtener la fecha de ayer, cómo lo hago? Bueno, existen dos formas de hacerlo: bien y no tan bien. Hoy os explicaré la forma de hacerlo bien ... :D

PostgreSQL nos ofrece la posibilidad de utilizar Intervalos, por ejemplo, <hour>, <day>, <month> -que corresponden con hora, día, mes, etc- por lo tanto, como puedo utilizar funciones de suma y resta a las fechas, puedo hacer:

search=> select now()-'24 hour'::interval;
           ?column?           
-------------------------------
 2010-11-04 09:24:12.504315+01
(1 row)

He restado un intervalo de 24 horas a la fecha actual, por lo tanto a alguien se le puede estar ocurriendo la siguiente solución para saber la fecha de ayer:

search=> select (now()-'24 hour'::interval)::date;
    date   
------------
 2010-11-04
(1 row)

Bien, esta solución es válida pero no es la correcta. Para saber la fecha de ayer PostgreSQL nos ofrece la siguiente opción, utilizar <yesterday>, por ejemplo:

search=> select 'yesterday'::date;
    date   
------------
 2010-11-04
(1 row)

O mejor aún, con TimeZone

search=> select 'yesterday'::timestamp with time zone;
      timestamptz      
------------------------
 2010-11-04 00:00:00+01
(1 row)

También podemos saber la fecha de hoy utilizando

search=> select 'today'::date;
    date   
------------
 2010-11-05
(1 row)

Como veís el uso de intervalos -usando casting- nos puede ahorras muchas horas de sufrimiento, simplemente debemos recordar que para poder sumar o restar a <now()> debemos utilizar <interval> y, que si queremos saber la fecha de hoy o ayer, podemos utilizar las palabras <today> y <yesterday> haciendo un cast a <date> o <timestamp>.

Por ejemplo, en una de mis aplicaciones existe una tabla de eventos que guarda un registro de lo que ha sucedido, si quisiera saber cuántos eventos se han producido desde ayer utilizaría:

search=> select count(1) from audit.events where event_datetime > 'yesterday'::timestamp with time zone;
 count
-------
 38075
(1 row)

Sin embargo, si quiero saber cuántos eventos se han producido en las últimas 24h, haría

search=> select count(1) from audit.events where event_datetime > now()-'24 hour'::interval;
 count
-------
 37493
(1 row)

El resultado es diferente -como tiene que ser- porque si analizamos las fechas vemos que al utilizar <yesterday> siempre comenzamos desde las 00:00h, y con los intervalos dependemos de la hora en la que se ejecute

  search=> select 'yesterday'::timestamp with time zone, now()-'24 hour'::interval;
      timestamptz       |           ?column?           
------------------------+-------------------------------
 2010-11-04 00:00:00+01 | 2010-11-04 09:52:30.401646+01
(1 row)

Para concluir, os contaré un uso de un intervalo que, realmente a mí, me ha sacado de muchos apuros: week, month, year (semana, mes, año).

Fijaros cómo PostgreSQL se encarga de ajustar los cambios horarios. En España hemos realizado recientemente el cambio de hora, y por lo tanto, hace una semana estábamos con GMT+2, pero ahora estamos con GMT+1.

search=> select now()-'1 year'::interval;
           ?column?           
-------------------------------
 2009-11-05 09:58:15.050784+01
(1 row)

search=> select now()-'1 month'::interval;
           ?column?           
-------------------------------
 2010-10-05 09:58:25.412129+02
(1 row)

search=> select now()-'1 week'::interval;
           ?column?          
------------------------------
 2010-10-29 09:58:31.60385+02
(1 row)

Sólo una última cosa, el uso de nombre ne singular o plurar es el mismo, por ejemplo,  <week> y <weeks>

search=> select now()-'2 week'::interval;
           ?column?           
-------------------------------
 2010-10-22 09:59:20.024957+02
(1 row)

search=> select now()-'2 weeks'::interval;
           ?column?           
-------------------------------
 2010-10-22 09:59:22.601622+02
(1 row)

Conclusiones
PostgreSQL nos ofrece un montón de ventajas en el manejo de Fechas -con o sin intervalos- además de proporcionarnos un interface sencillo. Es cierto que muchas veces -por descuido- olvidamos el uso de los desplazamientos horarios, pero son vitales y, si entramos en el mundo de la auditoría de sistemas, no podemos olvidarlos.

Referencias

• Gestión de Husos Horarios en PostgreSQL
• Instalación de PostgreSQL en Solaris 10

Copias de Seguridad y Recuperación en PostgreSQL - Parte 1

Introducción
En esta ocasión vamos a ver cómo realizar un procedimiento de backup utilizando las herramientas que nos proporciona PostgreSQL. En estas primeras partes, veremos cómo podemos utilizar <pg_dump> y las principales opciones.

Posteriormente, veremos cómo utilizar PostgreSQL PITR -Point In-Time Recovery- y qué ventajas nos ofrece este sistema de respaldo.

Tipos de Backups
En PostgreSQL existen diferentes tipos de Copias de Seguridad -Backups-: Completos y  Parciales.

En esta primera parte, veremos los backups completos y cómo podemos realizarlos, dejaremos los backups parciales para las siguientes entregas.

Backup Completo -Full Backup-
Este tipo de copia de seguridad es un volcado completo de nuestra base de datos en un instante "t", y como es natural, contiene la información hasta el instante que comenzamos el proceso de backup.

Para realizar este backup utilizaremos el comando <pg_dump> y, es posible realizar esta operación en caliente, es decir, podemos tener nuestra base de datos abierta.

Ventajas Backup Completo

• Sencillo de realizar
• Se puede realizar en caliente
• Backup "consistente"
• En bases de datos pequeñas en muy rápido

Problemas Backup Completo

• Se restaura "todo" o "nada"
• Consumo de I/O elevado
• En bases de datos grandes puede no ser efectivo

Realizar un Backup
Ya hemos hablado que para realizar un "full backup" utilizaremos el comando <pg_dump> cuyo formato es el siguiente:

pg_dump [OPCIONES] [DATABASE]

Donde las opciones más importantes son

• -f, Nombre del archivo de salida donde vamos a guardar nuestro backup
• -Z, Salida comprimida con GZip. Por defecto el nivel de compresión es normal, pero podemos utilizar la compresión máxima si ponemos -Z 9
• -a, Sólo datos, nada de estructura.
• -b, Incluye objetos grandes en el volcado (blobs)
• -c, Añade comandos de borrado (clean) antes de cada objeto, por ejemplo, "DROP SCHEMA ..."
• -C, Añade los comandos de creación (create) antes de cada objecto, por ejemplo, "CREATE SCHEMA ..."
• -E, Utiliza el tipo de caracteres indicado en el volcado, por ejemplo, "-E UTF8"
• -n, Vuelca sólo el schema indicado, por ejemplo, "-n public"
• -N, Vuelca todos los schemas menos el indicado, por ejemplo, "-N private"
• -o, Incluye los OIDs en el volcado
• -O, No restaura los propietarios a los objetos
• -s, Vuelca sólo estructura nada de datos
• -t, Vuelca sólo la tabla indicada, por ejemplo, "-t havoctec.users"
• -T, Vuelca todas las tablas menos la indicada, por ejemplo "-T havoctec.options"
• -X, No vuelva los privilegios, es decir, los GRANT y REVOKE

Por ejemplo, podemos realizar un backup completo utilizando

$ pg_dump -f /u07/backups/full/havoctec-db-FULL-02.NOV.2010.dump.gz -Z 9 -o -C havoctec

Restaurar un Full Backup
La restauración de un backup realizado con <pg_dump> es realmente sencilla, debemos tener en cuenta que al ser un backup completo vamos a "volcar todos los datos". En función de las opciones que hemos utilizado en el volcado, deberemos realizar algunos pasos diferentes, por ejemplo, si hemos utilizado la opción -C <opción Create>, dentro del propio dump tendremos los comandos SQL para la creación de la base de datos, y por lo tanto no será necesario crearla. Si no hemos utilizado esa opción, deberemos crear la base de datos primero -a no ser que queramos restaurar sobre otra base de datos diferente-

Para realizar la restauración de un volcado completo, utilizaremos el comando <psql> y como entrada nuestro archivo de backup, por ejemplo

$ psql -U postgres < /u07/backups/full/havoctec-db-FULL-02.NOV.2010.dump

Fijaros que no he puesto el archivo gzip (.gz) ya que el comando <psql> cree que lo que viene son instrucciones SQL en texto plano, si no queremos descromprimirlo podemos utilizar

$ gzip -dc /u07/backups/full/havoctec-db-FULL-02.NOV.2010.dump | psql -U postgres

Un Ejemplo Completo
En este ejemplo vamos a crear una base de datos llamada "havoctec" y en ella una tabla donde introduciremos varios datos. Posteriormente realizaremos una copia de seguridad y la restauraremos.

Recordar que si no tenemos asignado el valor de <PGDATA> deberemos exportarlo antes de poder trabajar con PostgreSQL.

$ export PGDATA=/var/postgres/8.4/data

Nos conectamos a PostgreSQL y crearemos la base de datos <havoctec>

$ psql -U postgres
Password for user postgres:
psql (8.4.1)
Type "help" for help.

postgres=# create database havoctec;
CREATE DATABASE
postgres=# \c havoctec;
psql (8.4.1)
You are now connected to database "havoctec".
havoctec=# create table urls(url_id serial, url_name character varying);
NOTICE:  CREATE TABLE will create implicit sequence "urls_url_id_seq" for serial column "urls.url_id"
CREATE TABLE
havoctec=# insert into urls(url_name) values('http://www.havoctec.com/');
INSERT 0 1
havoctec=# insert into urls(url_name) values('http://www.sfchildren.com/');
INSERT 0 1
havoctec=# insert into urls(url_name) values('http://sparcki.blogspot.com/');
INSERT 0 1
havoctec=# select * from urls;
 url_id |           url_name          
--------+------------------------------
      1 | http://www.havoctec.com/
      2 | http://www.sfchildren.com/
      3 | http://sparcki.blogspot.com/
(3 rows)

A continuación, realizaremos una copia de seguridad utilizando la opción <-C> (create) para poder restaurar en la misma base de datos con el mismo nombre.

$ time pg_dump -C -f /var/backups/havoctec-db-full.dump -U postgres havoctec
Password:

real    0m3.490s
user    0m0.029s
sys     0m0.012s

Ahora, borramos la base de datos

$ psql -U postgres
Password for user postgres:
psql (8.4.1)
Type "help" for help.

postgres=# drop database havoctec;
DROP DATABASE
postgres=# \q

Por último, restauraremos la base de datos

$ psql -U postgres < /var/backups/havoctec-db-full.dump
Password for user postgres:
SET
SET
SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "havoctec".
SET
SET
SET
SET
SET
SET
CREATE LANGUAGE
ALTER LANGUAGE
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE SEQUENCE
ALTER TABLE
ALTER SEQUENCE
 setval
--------
      3
(1 row)

ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT

Comprobamos que tenemos los mismos registros, realizando un simple <SELECT>

$ psql -U postgres -d havoctec
Password for user postgres:
psql (8.4.1)
Type "help" for help.

havoctec=# select * from urls;
 url_id |           url_name          
--------+------------------------------
      1 | http://www.havoctec.com/
      2 | http://www.sfchildren.com/
      3 | http://sparcki.blogspot.com/
(3 rows)

havoctec=# \q

Automatizar la Copias usando el <cron>
Por último, podemos utilizar nuestro <crontab> para realizar una copia de seguridad completa diariamente, y así, evitar sorpresas de última hora.

El problema que nos podemos encontrar es, principalmente, que el comando <pg_dump> nos solicite una contraseña y no podamos automatizar el proceso.

No hay problema, para ello está el archivo <pgpass> PostgreSQL Password File, en el cual declararemos los usuarios y contraseñas necesarios para el acceso sin password. El formato del archivo es el siguiente:

maquina:puerto:base de datos:usuario:password

Si utilizamos un "*" en el campo de <base de datos> se podrán acceder a todas la bases de datos, por ejemplo, si queremos que el usuario backups pueda acceder a cualquier base de datos, ponemos

$ echo "db1.havoctec.com:*:backups:password" > $HOME/.pgpass

$ chmod 600 $HOME/.pgpass

$ pg_dump -U backups -Z9 -C -f /var/backups/havoctec-db-full.dump.gz havoctec

Ahora PostgreSQL no nos ha solicitado la contraseña, porque la ha encontrado en el archivo, y por lo tanto, la ha introducido por nosotros, así que ya podemos añadir nuestra entrada al cron para que haga copias de seguridad completas todos los días a las 02:00h, por ejemplo,

$ crontab -e
    00 02 * * * /u01/app/postgres/8.4/bin/pg_dump -U backups -Z9 -C -f /var/backups/havoctec-db-full.dump.gz havoctec

:wq

Conclusiones
Hemos visto un primer acercamiento a las opciones en cuanto a Backup y Restore que nos ofrece PostgreSQL, y además hemos definido una política básica de copias utilizando el cron del sistema, para, al menos, tener una copia diaria de nuestra base de datos.

En las siguientes entregas veremos cómo podemos crear una política de Backup y Restore Eficiente y Segura en PostgreSQL



Referencias

• Cómo Instalar PostgreSQL en OpenIndiana
• Cómo Instalar PostgreSQL en Solaris
• Cómo Editar el Crontab en Solaris

Instalación de PostgreSQL en OpenIndiana - Parte 1

Introducción
Ya hemos visto Cómo Instalar PostgreSQL en Solaris 10 utilizando los binarios que nos proporciona PostgreSQL, sin embargo, en esta ocasión vamos a ver Cómo Instalar PostgreSQL 9.0 en OpenIndiana

Desde la página de PostgreSQL, podemos descargarnos la versión ya compilada para Solaris 10, sin embargo, esta versión no funciona sobre OpenSolaris y OpenIndiana.

Problemas con los binarios para Solaris 10 en OpenIndiana
El principal problema de los binarios compilados para Solaris reside en el uso de la biblioteca <libssl.so.0.9.7> y <libcrypto.so.0.9.7> que en OpenIndiana son </usr/lib/64/libcrypto.so.0.9.8> y </usr/lib/64/libssl.so.0.9.8>

Es decir, en OpenIndiana -OpenSolaris- tenemos una versión superior, y por lo tanto, cuando verificamos las dependencias del inario utilizando <ldd> vemos que no se encuentran:

# ldd postgres
        libxslt.so.1 =>  /usr/lib/64/libxslt.so.1
        libxml2.so.2 =>  /lib/64/libxml2.so.2
        libpam.so.1 =>   /lib/64/libpam.so.1
        libssl.so.0.9.7 =>       (file not found)
        libcrypto.so.0.9.7 =>    (file not found)
        libgss.so.1 =>   /usr/lib/64/libgss.so.1
        libnsl.so.1 =>   /lib/64/libnsl.so.1
        librt.so.1 =>    /lib/64/librt.so.1
        libsocket.so.1 =>        /lib/64/libsocket.so.1
        libm.so.2 =>     /lib/64/libm.so.2
        libc.so.1 =>     /lib/64/libc.so.1
        libz.so.1 =>     /lib/64/libz.so.1
        libpthread.so.1 =>       /lib/64/libpthread.so.1
        libmp.so.2 =>    /lib/64/libmp.so.2
        libmd.so.1 =>    /lib/64/libmd.so.1

Pero ... que no cunda el pánico, que es un problema fácilmente solucionable. Tenemos tres opciones para ello, o instalamos la versión anterior de OpenSSL cosa que NO RECOMIENDO, o compilamos nuestra propia versión de PostgreSQL o ... podeis descargaros la versión compilada por mí, para OpenIndiana x86 en 64bits de PostgreSQL 9.0.

Si os habéis decidido por la última opción -Instalar PostgreSQL desde el binario par a OpenIndiana-, entonces, podéis ignorar el paso de compilación pero debéis tener en cuenta lo siguiente:

• El binario debe ser instalado en </u01/app/postgres/9.0/db> 
• Utilizaremos el role de <postgres> del sistema
• El <PGDATA> esta en </var/postgres/9.0/data>

Preparación del Entorno
Antes de poder compilar PostgreSQL 9.0 en OpenIndiana, debemos tener en cuenta que son necesarios algunos paquetes, para ello, deberemos instalarlos antes.

Aquí os dejo la lista de paquetes necesarios, para instalarlos, simplemente debemos ejecutar <pkg install _nombre_paquete_>

• sunstudio
• SUNWgmake
• SUNWbison
• SUNWgm4
• SUNWflexlex

Podemos comprobar si ya están instalados utilizando el comando <pkginfo>, por ejemplo

# pkginfo |grep flex
system      SUNWflexlex               Flex Lexer
system      SUNWrsync                 rsync - faster, flexible replacement for rcp
# pkginfo |grep bison
system      SUNWbison                 bison - A YACC Replacement
# pkginfo |grep gm4
system      SUNWgm4                   GNU m4
# pkginfo |grep gmake
system      SUNWgmake                 gmake - GNU make
# pkginfo |grep sunstudio
application sunstudio12u1             Sun Studio 12 update 1

Compilar PostgreSQL 9.0
Para la compilación he utilizado los mismos parámetros que Bjorn Munch utiliza en la versión "oficial" que es posible descargar desde PostgreSQL.

Lo primero que haremos, será descargarnos el fuente de PostgreSQL desde su página oficial, en cualquiera de los dos formatos: tar.gz, tar.bz2

# export CC=cc  
# export CFLAGS="-xO3  -xspace -Xa  -xildoff  -m64  -xc99=none -xCC -fast -native"
# export LDFLAGS="-R/usr/sfw/lib/64 -R/usr/lib/64"
# ./configure 
--prefix=/u01/app/postgres/9.0/db
--exec-prefix=/u01/app/postgres/9.0/db
--bindir=/u01/app/postgres/9.0/db/bin/64
--libexecdir=/u01/app/postgres/9.0/db/bin/64
--sbindir=/u01/app/postgres/9.0/db/bin/64
--datadir=/u01/app/postgres/9.0/db/share
--sysconfdir=/u01/app/postgres/9.0/db/etc
--mandir=/u01/app/postgres/9.0/db/man
--libdir=/u01/app/postgres/9.0/db/lib/64
--includedir=/u01/app/postgres/9.0/db/include
--sharedstatedir=/var/postgres/9.0
--localstatedir=/var/postgres/9.0
--enable-nls
--docdir=/u01/app/postgres/9.0/db/doc
--with-system-tzdata=/usr/share/lib/zoneinfo
--with-python
--with-pam
--with-openssl
--with-libedit-preferred
--with-libxml
--with-libxslt
--with-gssapi
--enable-thread-safety
--enable-dtrace
--disable-integer-datetimes
--with-includes=/usr/include:/usr/sfw/include
--with-libs=/lib/64:/usr/lib/64:/usr/sfw/lib/64
# make
# make install

Configuración de PostgreSQL
A diferencia del post sobre Cómo Instalar PostgreSQL 8.x en Solaris 10, en esta ocasión vamos a utilizar roles para ejecutar el servicio.

Ahora si que estamos en disposición de poder hablar de esta forma de instalación, ya que hemos introducido los conceptos de RBAC, Roles y Privilegios en post anteriores.

No es que la instalación de PostgreSQL 8.x en Solaris 10 no estuviese bien, sino que no utilizaba todos los mecanismos que Solaris nos proporciona -a nivel de seguridad-, para que os hagáis una idea, si ejecutamos el comando <ppriv> sobre el proceso de postgres ejecutado con un usuario normal veremos cuáles son sus privilegios

$ svcs -p postgresql_84
STATE          STIME    FMRI
disabled       mar_04   svc:/application/database/postgresql_84:default_32bit
online         jul_06   svc:/application/database/postgresql_84:default_64bit
               jul_06       3082 postgres
               jul_06       3084 postgres
               jul_06       3085 postgres
$ ppriv 3082
3082:   /u01/app/postgres/8.4/db/bin/64/postgres -D /var/postgres/8.4/data
flags =
        E: basic
        I: basic
        P: basic
        L: basic,contract_event,contract_observer,file_chown,file_chown_self,file_dac_execute,file_dac_read,
file_dac_search,file_dac_write,file_owner,file_setid,ipc_dac_read,ipc_dac_write,ipc_owner,
net_bindmlp,net_icmpaccess,net_mac_aware,net_privaddr,net_rawaccess,proc_audit,
proc_chroot,proc_lock_memory,proc_owner,proc_setid,proc_taskid,sys_acct,sys_admin,
sys_audit,sys_ip_config,sys_mount,sys_nfs,sys_resource

Ahora, si ejecutamos el servicio postgres mediante <pg_ctl> y el role <postgres> veremos la diferencia de privilegios:

$ id
uid=90(postgres) gid=90(postgres)
$ pg_ctl start -l /var/log/postgres/server.log
server starting
$ ps -ef|grep postgre
postgres   794     1   1 09:39:22 pts/1       0:00 /u01/app/postgres/9.0/db/bin/64/postgres
postgres   799   794   0 09:39:22 ?           0:00 /u01/app/postgres/9.0/db/bin/64/postgres
$ ppriv 794
794:    /u01/app/postgres/9.0/db/bin/64/postgres
flags = PRIV_PFEXEC
        E: basic
        I: basic
        P: basic
        L: all

Como podéis ver, la diferencia se encuentra en flags que ahora indica PRIV_PFEXEC ya que al ser un role, su shell es <pfexec> y, si es lanzado por el servicio SMF, es root quien lo lanza.

Además, si queríamos que un usuario -persona- pudiese gestionar nuestro PostgreSQL debíamos entregarle la contraseña de root ya que sino, no podría operar con SMF. Si lo intentase, nos mostraría el siguiente error:

$ id
uid=101(postgres) gid=90(postgres)
$ /usr/sbin/svcadm disable svc:/application/database/postgresql_84:default_64bit
svcadm: svc:/application/database/postgresql_84:default_64bit: Permiso denegado.

Configuración de PostgreSQL en SMF
A continuación configuraremos nuestro servicio postgres para que sea controlado mediante SMF, para ello, debemos descargar el Manifest para Solaris SMF 10 PostgreSQL 9 y el Method para Solaris SMF 10 PostgreSQL 9

# cd /lib/svc/method
# wget http://blog.sfchildren.com/blogger/openindiana/postgresql/9.0/smf/postgres_9
# chown root:bin postgres_9
# chmod 555 postgres_9
# mkdir -p /var/svc/manifest/application/database
# cd /var/svc/manifest/application/database
# wget http://blog.sfchildren.com/blogger/openindiana/postgresql/9.0/smf/postgresql_9.xml
# svccfg
svc:> validate postgresql_9.xml
svc:> import postgresql_9.xml
svc:> quit
# svcs -p postgresql_9
STATE          STIME    FMRI
disabled        9:29:24 svc:/application/database/postgresql_9:default_32bit
disabled       10:24:40 svc:/application/database/postgresql_9:default_64bit

Creación Estructura de PGDATA
Antes de poder iniciar postgres debemos crear la estructura de almacenamiento necesaria, para ello, deberemos ejecutar el comando <initdb> con los parámetros oportunos. En nuestro caso, vamos a iniciar la estructura en formato <UTF-8> y lenguage <es_UTF8> y la opción <-W> para que nos solicite la password que vamos a asignar al usuario postgres de la base de datos

# mkdir -p /var/postgres/9.0/data
# chown -R postgres:postgres /var/postgres
# chmod 700 /var/postgres
# su - postgres
$ export PGDATA=/var/postgres/9.0/data
$ /u01/app/postgres/9.0/db/bin/64/initdb -E utf8 --locale=es.UTF-8 -U postgres -W

Project y Directorio de Logs
Al igual que hicimos con la Instalación de PostgreSQL en Solaris, debemos definir un project y un directorio donde queremos escribir nuestro log. En nuestro caso, el project será <group.postgres> y el directorio </var/log/postgres>

# projadd -c "Postgres Database" -G postgres group.postgres
# projmod -sK "process.max-sem-nsems=(priv,256,deny)" group.postgres
# projmod -sK "project.max-sem-ids=(priv,100,deny)" group.postgres
# projmod -sK "project.max-shm-ids=(priv,100,deny)" group.postgres
# projmod -sK "project.max-shm-memory=(priv,8G,deny)" group.postgres
# mkdir -p /var/log/postgres
# chown -R postgres:postgres /var/log/postgres
# chmod 700 /var/log/postgres

Inicio y Parada de PostgreSQL
Como siempre, vamos a utilizar el framework de Solaris SMF para gestionar el inicio y parada de PostgreSQL, sin embargo, en esta ocasión -a parte de poder utilizar root- podemos utilizar el role <postgres> para gestionarlo, por ejemplo:

$ id
uid=90(postgres) gid=90(postgres)
$ svcs -p postgresql_9
STATE          STIME    FMRI
disabled        9:29:24 svc:/application/database/postgresql_9:default_32bit
disabled        9:38:51 svc:/application/database/postgresql_9:default_64bit
$ pfexec /usr/sbin/svcadm enable svc:/application/database/postgresql_9:default_64bit
$ svcs -p postgresql_9
STATE          STIME    FMRI
disabled        9:29:24 svc:/application/database/postgresql_9:default_32bit
online          9:55:13 svc:/application/database/postgresql_9:default_64bit
                9:55:13      817 postgres
                9:55:13      819 postgres
                9:55:13      820 postgres
                9:55:13      821 postgres
                9:55:13      822 postgres
$ ppriv 817
817:    /u01/app/postgres/9.0/db/bin/64/postgres -D /var/postgres/9.0/data
flags =
        E: basic
        I: basic
        P: basic
        L: all

Otorgar privilegios de administración de PostgreSQL a un usuario
Como hemos comenteado, al utilizar roles podemos delegar privilegios de administración a un usuario cualquiera sin que tengamos que proporciona la contraseña de root.

Por ejemplo, vamos a crear un usuario no privilegiado llamado <zooey> al cual, le vamos a otorgar permisos para poder administrar nuestro servicio PostgreSQL asignándole el role<postgres> y las autorizaciones <solaris.smf.value.postgres> y <solaris.smf.manage.postgres>

# useradd -g sysadmin -s /bin/bash -d /export/home/zooey -m -R postgres -A solaris.smf.manage.postgres,solaris.smf.value.postgres zooey
80 blocks
# passwd zooey
New Password:
Re-enter new Password:
passwd: password successfully changed for zooey
# su - zooey
OpenIndiana     SunOS 5.11      oi_147  September 2010
zooey@openzooey:~$ roles
postgres
zooey@openzooey:~$ pfexec svcs -p postgresql_9
STATE          STIME    FMRI
disabled        9:29:24 svc:/application/database/postgresql_9:default_32bit
disabled       10:24:40 svc:/application/database/postgresql_9:default_64bit
zooey@openzooey:~$ pfexec /usr/sbin/svcadm enable svc:/application/database/postgresql_9:default_64bit
zooey@openzooey:~$ pfexec svcs -p postgresql_9
STATE          STIME    FMRI
disabled        9:29:24 svc:/application/database/postgresql_9:default_32bit
online         10:39:25 svc:/application/database/postgresql_9:default_64bit
               10:39:24     1016 postgres
               10:39:24     1018 postgres
               10:39:24     1019 postgres
               10:39:24     1020 postgres
               10:39:24     1021 postgres
zooey@openzooey:~$ pfexec /usr/sbin/svcadm disable svc:/application/database/postgresql_9:default_64bit
zooey@openzooey:~$ pfexec svcs postgresql_9
STATE          STIME    FMRI
disabled        9:29:24 svc:/application/database/postgresql_9:default_32bit
disabled       10:43:51 svc:/application/database/postgresql_9:default_64bit

Conclusiones
En esta ocasión hemos utilizado muchos más mecanismos para gestionar el servicio de PostgreSQL y, hemos aprendido a utilizar los roles como una herramienta muy potente y segura.

Aunque inicialmente la Instalación de PostgreSQL puede ser de la forma tradicional -utilizando un usuario no privilegiado y root para gestionarlo- Solaris nos proporciona un entorno mucho más seguro y rico para ello.

Espero que con este ejemplo -completo- de instalación podáis ver la potencia de los roles, y como nos pueden ayudar en nuestro día a día.

 
Referencias

• Instalar PostgreSQL 8.x en Solaris 10
• Instalar OpenSolaris Paso a Paso
• RBAC, Roles y Privilegios - Parte 1
• RBAC, Roles y Privilegios - Parte 2
• Manifest PostgreSQL 9.0 OpenIndiana
• Method PostgreSQL 9.0 OpenIndiana
• Página principal PostgreSQL