I-postgreSQL: (0.07) Instalando y usando PostgreSQL Cambios: 0.01 21/1/01 primera versión 0.02 29/1/01 LATIN1 para las Ñ 0.03 1/2/01 wrapper + variables de ambiente y estilo de fecha 0.04 3/2/01 indice html de doc, SQL,EUROPEAN y setup, backup con contraseñas 0.05 4/2/01 pruebas y fuente fija para pgaccess con Ñ y acentos 0.06 6/2/01 +solución fecha de Paco Brufal (gracias ;-) y fuente variable 0.07 20.11.03 +script de mantenimiento diario (hace un VACUUM) +script pgdump *** POR PROBAR *** pgbench, test de regresión ANTES DE EMPEZAR: 1) Instalar los paquetes - postgresql # Servidor de base de datos - postgresql-client # Cliente (psql) - pgaccess # Cliente gráfico X (Tk/Tcl) - postgresql-contrib # varios... (pgbench, ¿odbc?) - postgresql-pl # Lenguaje PL/pgSQL basado en SQL - postgresql-doc - ? ecpg # SQL embebido (en C) para postgresql Codificación de caracteres: - Elegir LATIN1 para crear las bases de datos (para evitar problemas ocasionales con las Ñ) - ATENCION: El valor por defecto es: UNICODE Estilo de fecha: NOTA: *** SQL,EUROPEAN no funciona en el setup así que hay que poner SQL y luego modificar /etc/postgresql/postmaster.init a mano *** - SQL,EUROPEAN (date=20/10/1999, datetime=20/10/1999 00:00:00) - GERMAN (date=20.10.1999) - POSTGRES,EUROPEAN (date=20-10-1999, datetime=Fri 23 Apr 00:00:00 1999)* - ATENCION: El valor por defecto es: ISO (date=1999-10-20) *Gracias a Paco Brufal por su solución que me inspiró SQL,EUROPEAN 2) Revisar configuración en /etc/postgresql/postmaster.init - Si no activamos la escucha TCP/IP en el puerto 5432 no se puede acceder con pgaccess de forma remota, pero si de forma local - Sin acceso remoto (RECOMENDADO): +---- | PGDATESTYLE=SQL,EUROPEAN | LANG=es_ES +---- - Con acceso remoto: +---- | PGDATESTYLE=SQL,EUROPEAN | PGALLOWTCPIP=yes | PGPORT=5432 | LANG=es_ES +---- - Hacer efectivos los cambios /etc/init.d/posgresql restart 3) Configurar pgaccess - Menú Databases->Preferences - Preferred languaje = spanish - Fuente fija = -misc-fixed-medium-r-normal-*-*-120-*-*-*-*-* # ATENCION: En -*-clean-medium-r-normal-... no salen Ñ ni acentos # (al menos cuando el primer * se toma como "shumacher") - Fuentes variables = -b&h-*-medium-... (resto igual) # ATENCION: En -adobe-helvetica-medium-r-normal-*-*-120-... el simbolo { se muestra como ( - Pulsar [Save] - Salir y volver a entrar para hacer efectivo el cambio de idioma Para conectar con pgaccess de forma local (con TCPIP desactivada), en la ventana "Abrir Base de datos" (Menú Base de Datos->Abrir): - Dejar "Servidor" en blanco - Poner en "Base de Datos" una base de datos existente (template1) - Poner en "Usuario" postgres 4) (OPCIONAL) Añadir otros usuarios administradores (a pg_shadow) por defecto solo viene "postgres" - Usar pgaccess, pestaña "Usuarios" (entrar como usuario "postgres") - ALTERNATIVA: Desde consola hay que hacer su a usuario postgres: +---- | su | su - postgres | createuser manel | Shall the new user be allowed to create databases? (y/n) y | Shall the new user be allowed to create more new users? (y/n) y +---- 5) Poner contraseña a usuarios administradores (postgres y/o otros) - Las cuentas que no tengan contraseña no serán accesible desde pgaccess al forzar el uso de contraseñas - Usar pgaccess, pestaña "Usuarios", elegir usuario y pulsar [Diseño] 6) Forzar acceso local con contraseña cifrada en /etc/postgresql/pg_hba.conf - Reemplazar en las dos lineas siguientes "trust" por "crypt": +---- | # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP | local all crypt | host all 127.0.0.1 255.255.255.255 crypt +---- - Ahora pgaccess nos exige la contraseña para conectar - El acceso local psql tambien es afectado por contraseña - Los cambios son efectivos inmediatamente 7) Si precisas acceso remoto añade los hosts a /etc/postgresql/pg_hba.conf - Ejemplo: +---- | # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP | host all 192.168.0.0 255.255.255.0 crypt +---- - Esto permite el acceso desde nuestra red local con contraseñas cifradas - Los cambios son efectivos inmediatamente 8) Crear bases de datos Hay varias opciones: - Usar el front-end gráfico "pgaccess", Menú Base de Datos->Nuevo(a) - Emplear el comando "createdb base-de-datos" - Usar el front-end texto psql y comandos SQL: (CREATE DATABASE) 9) Crear tablas - Usar pgaccess, pestaña "Tablas" 10) Crear clientes y asignar privilegios - Usar pgaccess, pestaña "Usuarios" - Los privilegios se pueden asignar por tablas y usuarios ESTO NO ES NECESARIO EN DEBIAN: Establecer variables de ambiente para los usuarios Añadir ". /etc/postgresql/postgresql.env" a: - Usuarios que va a usar la base de datos en ~/.profile - O, para todos los usuarios, en /etc/environment No es necesario porque en Debian se utiliza un wrapper para llamar a psql, ya que la Debian policy no permite el uso de variables de ambiente COMANDOS DE POSTGRESQL psql front-end de modo texto createuser crear usuario createdb crear base de datos dropuser elimina usuario dropdb elimina base de datos pg_dump vuelca base de datos en un script SQL (utilizable por psql) COMANDOS EN POSTGRESQL-CONTRIB pgbench test benchmark ... COPIA DE SEGURIDAD (BASE DE DATOS CON CONTRASEÑA) Desde /var/lib/postgres/data (como root) - pg_dump -u > archivo.sql - # -u = pide usuario y contraseña (a ciegas) - borrar las dos primeras lineas del archivo.sql, es decir: "user: password:" ---8<--- #!/bin/sh # pgdump-basededatos # # Este script hace un volcado comprimido de la base de datos y # luego verifica el archivo comprimido DATABASE="base-de-datos" DESTINO=/home/usuario/dir echo -n "Volcando y comprimiendo contenido de base de datos $DATABASE... " pg_dump $DATABASE | bzip2 > $DESTINO/pg_dump_$DATABASE.bz2 [ $? ] && echo "OK" echo -n "Verificando archivo comprimido... " bunzip2 -t $DESTINO/pg_dump_$DATABASE.bz2 && echo "OK" --->8--- RESTAURAR COPIA SE SEGURIDAD Desde /var/lib/postgres/data (como root) - Debes de crear todos los usuarios (¿solo los administradores?) que tenias en la base de datos para evitar errores al cargar los datos - createdb -U manel nombre-basedatos # crear base de datos vacia - # -U manel = conectar como usuario (y contraseña) - # El usuario postgresql manel debe de existir y tener permisos - psql -U manel -d nombre-basedatos -f archivo.sql # restaurar - # -U manel = conectar como usuario (y contraseña) Si aún no tienes activas las contraseñas puedes hacer - createdb -U postgres nombre-basedatos # crear base de datos vacia - psql -U postgres -d nombre-basedatos -f archivo.sql # restaurar SEGURIDAD: - postmaster corre como usuario "postgres" (BIEN) - No hay ningún suid root (BIEN) - Se puede usar la base de datos localmente sin activar TCP 5432 (BIEN) - No se puede restringir el puerto 5432 solo al interface local (MAL) (se puede establecer conexión con p.ej.:"telnet tu-IP 5432") aunque hay control de acceso - Las contraseñas de la base de datos se almacenan como texto plano (MAL) (aunque solo es leible por root y el usuario postgres) Para el uso seguro de la base de datos de forma remota pero restringido a la red local: - PostgreSQL no debe estar instalada en la máquina que conecta la red local a Internet, sino en otra (RECOMENDADO) - Utiliza un cortafuegos que impida el acceso al puerto 5432 desde IP no locales PRUEBAS: psql -d base-de-datos # Conectamos a base-de-datos =# select * from tabla; # Muestra contenido de tabla =# insert into tabla (campo1,campo2) values ('Texto',NULL) # Insertar \q ó CTRL+D # Para salir MANTENIMIENTO: Una vez al dia lanzar (p.ej. desde el cron) un script con: ---8<--- #!/bin/sh # pgvacuum # # Esto hace un VACUUM a la base de datos permitiendo la reutilización del # espacio liberado por los SQL DELETE y UPDATE, recomendable una vez al dia echo "vacuum;" | psql base-de-datos --->8--- SOLUCION DE PROBLEMAS: POR DENTRO: Postgresql arranca con /usr/lib/postgresql/bin/postgresql-startup UN POCO DE TEORIA: ARCHIVOS: /etc/postgresql/postmaster.init # Configuración /etc/postgresql/pg_hba.conf # Configuración de acceso remoto /var/lib/postgres/data # Bases de datos DEFICIENCIAS: BUGS: A PROBAR MAS: MAS AYUDA: - /usr/share/doc/postgresql/README.Debian.gz # Diferencias en Debian - /usr/share/doc/postgresql-doc/* # Cantidad información - /usr/share/doc/postgresql-doc/html/index.html # Indice de doc html - PostgreSQL-HOWTO - man pg_dump