Curso Postgres Administracion Basica

ADMINISTRACIÓN DE BASES DE
DATOS
PostgreSQL
Fernando Zaragoza Hernández
[email protected]
Agenda
 Introducción
 Instalación
 Preparación de la Base de Datos
 Inicio del servidor
 Apagado del servidor
 Usuarios y privilegios
 Manejo de Bases de Datos
Módulo 6
Administración de Bases de Datos
Introducción
 Características principales de PostgreSQL
 Open Source
 Licencia BSD (modificar, copiar y distribuir para cualquier propósito)
 Ultima versión 8.4.5 (9.0.0 – 9.0.1 – 9.4.4)
 Sitio oficial: http://www.postgresql.org/
 Límites de las bases de datos

 Tamaño para una BD = ilimitado (existen BD de 32 TB)
 Tamaño de una tabla = 32 TB
 Tamaño para un registro = 400 GB
 Tamaño para un campo = 1GB
 Numero de registros en una tabla = ilimitado
 Máximo de columnas en una tabla = 250 - 1600 (dependiendo de los
tipos de columna)
Módulo 6
 Número de índices en una tabla = ilimitado Administración de Bases de Datos
Introducción
 Base de datos de Posgrado (Agosto 2015)

 Postgres 9.1.15
 Código de caracteres SQL ASCII
 220 000 registros de alumnos
 2 831 000 registros de historias académicas
Módulo 6
Instalación
1. Descargar de: http://www.postgresql.org

2. Ejecutar los siguientes comandos:
# groupadd postgres
# useradd -g postgres postgres
# tar -zxvf /home/instalacion/postgresql-x.x.x.tar.gz
# ln -s postgresql-8.x.x postgresql
Módulo 6
Instalación (cont.)
# cd postgresql
# ./configure --prefix=/usr/local/pgsql
# make
# make install
# mkdir /usr/local/pgsql/data
# chown postgres /usr/local/pgsql/data
Módulo 6
Instalación (cont.)
# su - postgres
# PATH=$PATH:/usr/local/pgsql/bin
# export POSTGRES_HOME=/usr/local/pgsql
# export PGDATA=/usr/local/pgsql/data
# export PGLIB=/usr/local/pgsql/lib
# export LD_LIBRARY_PATH=/usr/local/pgsql/lib
Módulo 6
Preparación de la Base de datos
# /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
Opciones de initdb
 [-D, --pgdata=]DATADIR location for this database cluster
 -E, --encoding=ENCODING set default encoding for new
databases
 --locale=LOCALE initialize database cluster with
given locale
 --lc-collate, --lc-ctype, --lc-messages=LOCALE
 --lc-monetary, --lc-numeric, --lc-time=LOCALE
Módulo 6
Inicio del servidor
# /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
# /usr/local/pgsql/bin/pg_ctl -l /usr/local/pgsql/data/errors.log
-D /usr/local/pgsql/data/ -o –i start
# /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
Módulo 6
Inicio del servidor (cont.)
 Para verificar que el servidor se encuentra en operación,

tenemos las siguientes opciones:
 Verificando el procesos en ejecución de postmaster/postgres

 Verificando el puerto abierto
 Verificando las bases de datos del servidor:
#psql -l
Módulo 6
Apagado del servidor
 Existen diferentes formas para detener el servidor.
 Utilizando el comando pg_ctl con la opción stop

 Matando el proceso de postmaster/postgres (no recomendado)
Módulo 6
Práctica 1
Manos a la máquina!!!
Módulo 6
Usuarios y privilegios
 Creación de usuarios
Para crear un usuario se utiliza el comando SQL CREATE

USER nombre_ usuario
template1=# \h create user

CREATE USER user_name
[WITH PASSWORD password]
[CREATEDB | NOCREATEDB]
[CREATEUSER | NOCREATEUSER]
[IN GROUP group1, ...groupN]
[VALID UNTIL 'abstime'];
Módulo 6
Usuarios y privilegios (cont.)
 También es posible crear un usuario desde la linea de comandos
createuser
Opciones:
-h, --host host
Especifica el nombre del host de la máquina sobre la que el
postmaster corre.
-p, --puerto puerto
Especifica el puerto TCP/IP o el socket local Unix sobre el que el
postmaster atiende a las conexiones.
-e, --echo
Muestra las consultas que createuser genera y envía al backend.
-q, --quiet
No muestra respuesta alguna.
-d, --createdb
Permite al nuevo usuario crear bases de datos.
Módulo 6
-D, --no-createdb
Impide al nuevo usuario crear bases de datos.
-a, --adduser
Permite al nuevo usuario crear otros usuarios.
-A, --no-adduser
Impide al nuevo usuario crear otros usuarios.
-P, --pwprompt
Si se especifica este parámetro, createuser mostrará un
mensaje preguntando por el password del nuevo usuario. Esto
no es necesario si no planea usar autentificación por
password.
-i, --sysid id_usuario
Le permite elegir otro id de usuario que no sea el que se da por
defecto. Esto no es necesario, pero algunos lo prefieren.
Módulo 6
 Modificación de usuarios
Para cambiar la contraseña y los privilegios de un usuario se

utiliza el comando ALTER USER
Descripción
Se usa para cambiar los atributos de la cuenta de un usuario de
PostgreSQL. Sólo un superusuario de una base de datos puede
cambiar privilegios y fechas de caducidad de passwords con
esta orden. Ordinariamente los usuarios sólo pueden cambiar
su propia palabra clave.
ALTER USER user_name
[ WITH PASSWORD 'password' ]
[ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ]
[ VALID UNTIL 'abstime' ]
Módulo 6
 Eliminación de usuarios
DROP USER borra de la base de datos el usuario creado. Cabe

aclarar que esta cláusula no borra tablas, vistas u otros objetos
que pertenezcan al usuario. Si el usuario es dueño de una base
de datos, se producirá un error.
DROP USER user_name;
Módulo 6
 Creación de grupos
CREATE GROUP permite crear un grupo nuevo en la base de

datos. Este comando solamente podrá ser ejecutado por un
usuario administrativo.
CREATE GROUP name

[ WITH
[ SYSID gid ]
[ USER username [, ...] ] ]
Módulo 6
 Modificación de grupos
ALTER GROUP se usa para añadir usuarios a un grupo o

eliminarlos de un grupo. Sólo los administradores de bases de
datos pueden usar este comando. Añadir un usuario a un
grupo no crea ese usuario. Igualmente, eliminar a un usuario
de un grupo no significa que se elimine al usuario en si mismo.
ALTER GROUP name ADD USER nombre de usuario1, ... ;

ALTER GROUP name DROP USER nombre de usuario1, ... ;
Módulo 6
 Eliminación de grupos
DROP GROUP elimina el grupo especificado de la base de datos.

Los usuarios del grupo no se eliminan.
DROP GROUP nombre_grupo
Módulo 6
 Privilegios
Cuando se crea un objeto en la base de datos, se le asigna un dueño.

El dueño es el usuario que ejecutó la declaración de la creación. Para
cambiar al dueño de una tabla, índice, secuencia, vista, se utiliza el
comando ALTER TABLE. Por default, solo el dueño (o superusuario)
puede hacer cualquier cosa con el objeto. Para permitir que otros
usuarios lo utilicen, los privilegios deben ser concedidos (GRANT).
Hay varios privilegios : SELECT, INSERT, UPDATE, DELETE, RULE,

REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE,
USAGE, y ALL PRIVILEGES. El hecho de modificar o destruir un
objeto es siempre el privilegio del dueño solamente, para ello se utiliza
el comando GRANT.
Módulo 6
 Otorgamiento de privilegios
GRANT  otorga privilegios de acceso a un usuario, un grupo o

a todos los usuarios.
GRANT privilege [, ...] ON object [, ...]

TO { PUBLIC | GROUP group | username }
Nota:
psql \z permite ver la información de los privilegios
Módulo 6
 Revocación de privilegios
REVOKE  Revoca el privilegio de acceso a un usuario, a un

grupo o a todos los usuarios
REVOKE privilegio [, ...]

ON objeto [, ...]
FROM { PUBLIC | GROUP | nombre_usuario }
Módulo 6
Práctica 2
Módulo 6
Manejo de Bases de Datos
 Creación de la base de datos
CREATE DATABASE crea una nueva base de datos

PostgreSQL. El creador pasa a ser el propietario de la nueva
base de datos.
EJEMPLO:
Para crear una nueva base de datos:
template1=> create database mibase;
El comando createdb es un script shell construido y basado en el
comando CREATE DATABASE.
createdb [ options ] dbname [ descripcion ]
Módulo 6
Manejo de Bases de Datos (cont.)
El comando createdb es un script shell construido y basado en el comando
CREATE DATABASE.
createdb [ options ] dbname [ descripcion ]
Opciones:
-h, --host host
-p, --port port
-U, --username username
-W, --password
Forza a que se teclee password.
-e, --echo
Muestra la consulta que createdb genera y envía al motor de la base de datos
(backend)
-q, --quiet
No muestra ninguna respuesta.
-D, --location datadir
-O – Owner (dueño)
-E, --encoding encoding
Módulo 6
 Eliminación de la base de datos
DROP DATABASE elimina las entradas de catálogo de una base

de datos existente y borra el directorio que contiene los datos.
Solamente puede ser ejecutado por el propietario de la base de
datos (normalmente quien la creó).
DROP DATABASE name
Módulo 6
 Plantillas
CREATE DATABASE realmente trabaja copiando una base de

datos existente. Por default, copia la base de datos estándar
del sistema llamada template1. Así esta base de datos es el
"template" para crear nuevas bases de datos. Si nosotros
agregamos objetos a la base de datos template1, estos objetos
serán copiados en bases de datos creadas posteriormente por
el usuario. Este comportamiento permite modificaciones al
sistema estándar de objetos en bases de datos (site-local).
Módulo 6
 Hay una segunda base de datos estándar del sistema llamada

template0. template0 sin que nunca se cambie después initdb.
Para dar la instrucción CREATE DATABASE para copiar
template0 en ves de template1, podemos crear un usuario
“virgen” en la base de datos que no contiene ningún site-local
adicionado en template1. Esto es particularmente práctico al
restaurar con pg_dump dump: el script dump debe restaurar
una base de datos virgen para asegurarse de que reconstruya
el contenido correcto de la base de datos descargada, sin
ningún conflicto con las adiciones que pueden estar presentes
en template1.
Módulo 6
Para crear una base de datos copiando template0 debemos usar:
Del ambiente SQL,

CREATE DATABASE dbname TEMPLATE template0;
o del shell:
#createdb -T template0 dbname
Módulo 6
 Configuración de la base de datos
Existen muchos parámetros de configuración que afectan el

comportamiento del sistema de base de datos.
Todos los nombres de los parámetros son sensibles a

mayúsculas y minúsculas. Cada parámetro toma el valor de uno
de estos cuatro tipos: boolean, integer, float y string. Los valores
booleanos son ON, OFF, TRUE, FALSE, YES, NO, 1, 0.
Módulo 6
 Un forma de cambiar estos parámetros es modificar el archivo

postgresql.conf en el directorio data. Un ejemplo de cómo
luciría este archivo es:
# Comentario
log_connections = yes
syslog = 2
search_path = '$user, public'
Módulo 6
 Una segunda forma de establecer estos parámetros de

configuración es pasarlos como una opción de postmaster a
través de la línea de comandos:
postmaster -c log_connections=yes -c syslog=2

Las opciones de la línea de comandos anulan cualquier ajuste
existente en el archivo.
Módulo 6
 Opciones de conexión
 tcpip_socket (booleano)
 listen_addresses = 'localhost'
 max_connections (integer)
 superuser_reserved_connections (integer)
 port (integer)
 unix_socket_directory (string)
 unix_socket_group (string)
 unix_socket_permissions (integer)
 virtual_host (string)
Módulo 6
 Seguridad y autenticación
 authentication_timeout (integer)
 ssl (boolean)
 krb_server_keyfile (string)
 db_user_namespace (boolean)
Módulo 6
 Memoria
 shared_buffers (integer)
 sort_mem (integer)
 vacuum_mem (integer)
Módulo 6
 Eliminación de una base de datos
Se usa drop database para eliminar la base de datos.
DROP DATABASE  Elimina una base de datos existente
DROP DATABASE name
Módulo 6
 Conjunto de Caracteres
 El soporte local de caracteres se refiere al uso de preferencias

culturales con respecto a los alfabetos, ordenamientos,
formato de números, etc. PostgreSQL utiliza los estándares
ISO C y POSIX proporcionadas por el sistema operativo del
servidor.
Módulo 6
 El soporte local de caracteres se inicializa automáticamente

cuando se crea un cluster de la base de datos usando initdb. Si
se desea utilizar un soporte local de caracteres diferente, se
puede indicar a initdb exactamente que soporte se desea con
la opción -- locale. Por ejemplo:
initdb -- locale=sv_SE
 Este ejemplo fija el soporte de caracteres local al sueco (sv).

 Si más de un juego de caracteres locales es válido entonces
las especificaciones podrían verse de la siguiente manera:
cs_CZ.ISO8859-2.
Módulo 6
 De vez en cuando es útil mezclar reglas de varios juegos de

caracteres, por ejemplo, utilizar las reglas inglesas de colación para
los mensajes en español. Para soportar esto, existe un juego de
subcategorías que controlan ciertos aspectos de las reglas del
soporte de caracteres.
Módulo 6
 Por ejemplo, para fijar el juego de caracteres local al francés

canadiense, pero usando las reglas de EUA para el formato de
las cantidades monetarias initdb quedaría de la siguiente
manera:
initdb -- locale=fr_CA -- lc-monetary=en_US
 Si usted quisiera que el sistema se comportara como si no

tuviera soporte de caracteres local, es necesario utilizar el
juego de caracteres especial C o POSIX.
Módulo 6
 Soporte de juego de caracteres
 El soporte de juego de caracteres en PostgreSQL permite

almacenar texto en diferentes juegos de caracteres, incluyendo
juegos de caracteres single-byte tales como la ISO 8859 y del
multiple-byte tales como EUC (Extended Unix Code), Unicode.
Todos los juegos de caracteres se pueden utilizar
transparentemente a través del servidor. El juego de caracteres
por default es seleccionado mientras se inicializa el cluster de
la base de datos PostgreSQL.
 La siguiente tabla muestra los juegos de caracteres
soportados.
Módulo 6
Módulo 6
 Fijar el juego de caracteres por default:
initdb -E EUC_JP
 También se puede crear una base de datos con un juego de

caracteres diferente, por ejemplo:
createdb -E EUC_KR koreano
o
CREATE DATABASE koreano WITH ENCODING 'EUC_KR';
Módulo 6
Práctica 3
Módulo 6
Mantenimiento de la Base de Datos
 Existen algunas tareas de mantenimiento que se deben realizar

periódicamente para asegurar que el servidor PostgreSQL se
ejecute sin problemas. Las tareas discutidas aquí son
repetitivas y pueden fácilmente automatizarse usando las
herramientas estándares de Unix, tal es el caso de los scripts
de cron, sin embargo, es responsabilidad del administrador de
la base de datos habilitar los scripts apropiados y comprobar
que se ejecuten satisfactoriamente.
 Una tarea obvia del mantenimiento es la creación de respaldos
de los datos bajo un determinado horario. Sin un respaldo
reciente, no existe ninguna posibilidad de recuperar los datos
después de una catástrofe, por ejemplo fuego, falla del disco,
entre otros.
Módulo 6
 VACUMM
 El comando VACUUM de PostgreSQL se debe correr regularmente por

varias razones:
 Para recuperar espacio en disco ocupado por actualizaciones o registros
borrados.
 Para actualizar la estadística de datos usada por el planificador de
consultas en PostgreSQL.
 La tarea recomendada para la mayoría de los sitios es

programar un VACUUM a la base de datos una vez al día en las
horas de menos trabajo, es recomendable utilizar VACUUM y
no VACUUM completo para la recuperación del espacio.
Módulo 6
 Actualización del planeador estadístico
 El planificador de consultas de PostgreSQL hace uso de la

información estadística sobre el contenido de tablas para
optimizar las consultas. Estas estadísticas son recopiladas por
el comando ANALYZE, que se puede invocar así mismo o como
un paso opcional en VACUUM. Es importante contar con
estadísticas exactas, ya que de lo contrario el rendimiento de
las consultas puede no ser el mejor.
Módulo 6
 Rutina de indexado
 En algunas ocasiones es recomendable reconstruir los índices

con el comando REINDEX. Sin embargo, A partir de la versión
7.4 se ha reducido sustancialmente la necesidad de esta
actividad comparada con versiones anteriores.
Módulo 6
 Respaldos y restauración de la información
En Postgres las bases de datos se deben respaldar

regularmente. El procedimiento es muy simple, es importante
tener un bosquejo básico de las técnicas de respaldo.
Hay dos funciones fundamentales para trabajar con

PostgreSQL:
 Respaldo de SQL
 Respaldo a nivel del sistema de archivos
Módulo 6
 Respaldo a nivel de sistema de archivos
Una alternativa para respaldar, es copiar directamente los

archivos donde PostgreSQL almacena los datos de la base de
datos. Se puede utilizar cualquier método para los respaldos
generalmente del sistema de archivos, se hace de la siguiente
manera:
tar -cf backup.tar /usr/local/pgsql/data
Módulo 6
 Respaldos SQL-dump
La idea básica del método de SQL-dump es generar un archivo

del texto con sentencias SQL ,el objetivo es reconstruir la base
de datos en el mismo estado que era a la hora de la respaldo
PostgreSQL nos proporciona las herramientas pg_dump y

pg_dumpall, las cuales nos ayudan tanto como para respaldar
las tablas, como para copiarlas de un sistema a otro en un
formato transportable.
Módulo 6
 pg_dump se emplea para respaldar una base de datos o una

tabla en particular.
 pg_dumpall respalda todas las bases de datos en el sistema.
El uso básico de este comando es:

pg_dump dbname > outfile
EJEMPLO
La forma más general de emplear pg_dump para respaldar sólo
una tabla, es la siguiente:
pg_dump -t mitabla mibase > respaldo.sql
Módulo 6
 Restauración de un respaldo
Los archivos del texto creados por el pg_dump deben ser leídos
adentro por programa psql. La forma general del comando para
restaurar una respaldo es:
psql dbname -f infile
Una vez que esté restaurado, es necesario ejecutar

ANALYZE en cada base de datos, el optimizador saca
estadísticas. o correr vacuumdb - a - z para analizar todas
las bases de datos.
Módulo 6
 Respaldo y restauración en un solo comando
pg_dump y psql tienen capacidad para leer y escribir en pipe (|)

esto permite respaldar una base de datos directamente a partir
de un servidor a otro; por ejemplo:
pg_dump - h host1 dbname |psql - h host2 dbname
Módulo 6
 Respaldo y compresión
Se puede utilizar herramientas de Unix para trabajar con

problemas de espacio, es común que se tenga una tabla, o base
de datos que utilice mas espacio de lo permitido por el sistema
operativo. Con pg_dump puede escribir a la salida estándar por
lo tanto, se puede pasar a un programa que comprime datos.
pg_dump dbname | gzip > filename.gz
Módulo 6
Ejercicio Final
Módulo 6

Curso Postgres Administracion Basica

Cargado por

Copyright:

Formatos disponibles

Curso Postgres Administracion Basica

Cargado por

Información del documento

Título original

Derechos de autor

Formatos disponibles

Compartir este documento

Compartir o incrustar documentos

Opciones para compartir

¿Le pareció útil este documento?

¿Este contenido es inapropiado?

Copyright:

Formatos disponibles

Curso Postgres Administracion Basica

Cargado por

Copyright:

Formatos disponibles

ADMINISTRACIÓN DE BASES DE

 Características principales de PostgreSQL

 Límites de las bases de datos

 Base de datos de Posgrado (Agosto 2015)

1. Descargar de: http://www.postgresql.org

# /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

 Para verificar que el servidor se encuentra en operación,

 Verificando el procesos en ejecución de postmaster/postgres

 Existen diferentes formas para detener el servidor.

 Utilizando el comando pg_ctl con la opción stop

Para crear un usuario se utiliza el comando SQL CREATE

template1=# \h create user

Para cambiar la contraseña y los privilegios de un usuario se

DROP USER borra de la base de datos el usuario creado. Cabe

DROP USER user_name;

CREATE GROUP permite crear un grupo nuevo en la base de

CREATE GROUP name

ALTER GROUP se usa para añadir usuarios a un grupo o

ALTER GROUP name ADD USER nombre de usuario1, ... ;

DROP GROUP elimina el grupo especificado de la base de datos.

DROP GROUP nombre_grupo

Cuando se crea un objeto en la base de datos, se le asigna un dueño.

Hay varios privilegios : SELECT, INSERT, UPDATE, DELETE, RULE,

GRANT  otorga privilegios de acceso a un usuario, un grupo o

GRANT privilege [, ...] ON object [, ...]

REVOKE  Revoca el privilegio de acceso a un usuario, a un

REVOKE privilegio [, ...]

 Creación de la base de datos

CREATE DATABASE crea una nueva base de datos

 Eliminación de la base de datos

DROP DATABASE elimina las entradas de catálogo de una base

DROP DATABASE name

CREATE DATABASE realmente trabaja copiando una base de

 Hay una segunda base de datos estándar del sistema llamada

Para crear una base de datos copiando template0 debemos usar:

Del ambiente SQL,

 Configuración de la base de datos

Existen muchos parámetros de configuración que afectan el

Todos los nombres de los parámetros son sensibles a

 Un forma de cambiar estos parámetros es modificar el archivo

 Una segunda forma de establecer estos parámetros de

postmaster -c log_connections=yes -c syslog=2

 Eliminación de una base de datos

Se usa drop database para eliminar la base de datos.

DROP DATABASE  Elimina una base de datos existente

DROP DATABASE name

 El soporte local de caracteres se refiere al uso de preferencias

 El soporte local de caracteres se inicializa automáticamente

 Este ejemplo fija el soporte de caracteres local al sueco (sv).

 De vez en cuando es útil mezclar reglas de varios juegos de

 Por ejemplo, para fijar el juego de caracteres local al francés

 Si usted quisiera que el sistema se comportara como si no

 Soporte de juego de caracteres

 El soporte de juego de caracteres en PostgreSQL permite

 Fijar el juego de caracteres por default:

 También se puede crear una base de datos con un juego de

 Existen algunas tareas de mantenimiento que se deben realizar

 El comando VACUUM de PostgreSQL se debe correr regularmente por

 La tarea recomendada para la mayoría de los sitios es