PDF de programación - Introducción a Postgresql

Introducción a Postgresql



History!

1977-1985 Michael Stonebraker Inicia postgreSQL como Ingres en la

Universidad de Berkeley, California.

1986, comprado por Computer Associates.
1989, Liberada la primera versión como Postgres.
1994-95 dos estudiantes graduados de Berkeley Jolly Chen y Andrew

Yu añaden SQL a Postgres y lo llaman Postgres95.

1996, (1000 colaboradores) Se decidió quitar el 95 al nombre para

liberarse de la cronología y nace PostgreSQL.
Ultima version al momento 8.3.7, 8.4 en test y 8.5 en desarrollo.



Presentación

Es el SGBD Open Source mas potente del mercado.
Posee casi 30 años de desarrollo.
Licencia BSD de Berkeley.
Esta en la vanguardia de la investigacion en al tecnología transaccional.
Es ACID.
Es segura.
Tiene alternativas comerciales de muchísima calidad. Ej: EnterpriseDB,
CyberTech(Alemania), etc.
Tiene otras alernativas (forks) FastDB, Bizgres, etc.



Presentación

Es un motor Objeto-Relacional [*].
Cliente/servidor
Extensible
Multiples conectores desde lenguajes de
programación.
Lenguaje procedimental propio (PL/pgsql) y
extendido (PL/PERL, PL/PYTHONu, PL/JAVA,
PL/RUBY, PL/R, C, C++...)



Presentación

Puede conectarse por OBDC, OLE DB y JBDC,
además de los conectores propios de cada
lenguaje.

¡ Cocomo de papel !



[*]

El estandar SQL:2003 define las siguientes características

que se peuden implementar en las bases Objeto
relacionales:
Rowtype
Tipos definidos y rutinas por usuario.
Poliformismo
Herencia
Tipos de referencia e identidad de objetos (el OID es uno de ellos)
Tipos de colección (ARRAY, MULTISET, SET, LIST)
Amplaición del SQL para hacerlo computacionalmente completo.
Soporte para objetos de gran tamaño (BLOB y CLOB)
Recursión.



[*] El punto de vista de

Stonebraker

SGBD
relacional

Mysql

Capacidades
De búsqueda
/soporte
multiusuario

Sistemas

De

archivos

Postgresql

SGBD

O-R

SGBD

OO

d

a

cid

e lo

o r v

y

a

M

Derby

Complejidad de los datos y ampliabilidad

DB4O

e lo

o r v

n

e

M

d

a

cid



Arquitecturas comunes

Backend

Cliente

Cliente

...

Proceso

Backend

Servidor /

Cluster de servidores

Archivo/s

Servidor

Datos

Derby –
BerkeleyDB



Mysql – Postgresql -
Firebird


Oracle – DB2 - Greenplum

Características Ppales.

El modelo es de cliente/servidor (hoy en día el más común, pero
no el único).
Su lenguaje procedimental es muy similar al PL de Oracle,
logrando una migración mas amena.
Se adapta a los standares SQL:2003.
Posee MVCC (Multi-Version Concurrency Control). Fue una de las
pioneras (la primera fue InterBase)
Posee WAL (Write Ahead LOG).
Herencia de tablas.
Puntos de recuperacion avanzados (savepoints, replicacion
asincronica)
Optimizador de consultas.



Caracteristicas Ppales.

Juegos de caracteres internacionales UNICODE
e internacionalización.
Tipo de tablas BerkeleyDB (las puede usar
Mysql).



Adicionales

Tipos de datos accesorios:
Números de presición arbitraria (creando numeros más
complejos)
Text de largo ilimitado.
Figuras geometricas, con funciones asociadas.
IpV4 y 6.
Mac Address.
Arrays.
Postgis (necesita un capítulo aparte).



Límites

Maximo de la BD: ilimitado.
De Tablas: 32 TB.
De tupla: 1.6 TB.
De campo: 1 GB
Tuplas x tabla: ilimitado.
Índices por tabla: ilimitado.



Rango de tamaño frecuente en
comparación con otras BD
(Terabytes)

Tamaño aproximado
Tamaño aproximado

Mysql

Postgresql

Oracle

Greenplum



70

60

50

40

30

20

10

0



Indices

Pueden ser definidos por el usuario:
Binary Tree, hash, GIN y Gist.
Basados en expresiones.
parciales
bitmaps.



Avanzadas

Restricciones Referential Integrity Constraints.
Evita dropeos accidentales.
Transacciones BEGIN – END- SAVEPOINTS.
Anidacion de consultas avanzada.
Conexiones encriptadas via SSL.
Dominios, clustering, tablaspaces.
TOAST (atributos comprimidos largos)



Estructura



Arquitectura de servicios

Aplicacion

Postgres

postmaster

psql

Postgres



Ámbitos y Esquemas de Conexión

postmaster

Inicio de Conexion

backend

backend

Auth

Query's

T
i
e
m
p
o

frontend

frontend

frontend

Servidor

Cliente



PostMaster

Proceso principal.
Se maneja como un servicio de sistema.
Levanta la memoria compartida.
Vigila solicitudes y esta al tanto de los movimientos.
Realiza el enlazado a los archivos de datos.
puede manejar varias bases de datos y usuarios.
Uno x CLUSTER.



Conexiones

La comunicacion entre Back y front se realiza
mediante sockets a traves del puerto 5432 (por
defecto).
Generalmente el archivo es /tmp/s.PGSQL.5432.



Esquema Logico del cluster

Repositorio que engloba varias bases de datos.
Las bases de datos engloban un conjunto de esquemas, con
1 usuario minimo propietario.
La sesion se abre solo contra la Base de datos, no contra
Cluster, ni esquemas, ni Tablas.
Por defecto se crean Template 0, ...1 y postgres.
Tablespaces no se crean por defecto.
Roles. usuarios. Son independientes del sistema.



Jerarquia de Objetos

Servidores
Bases de datos
Cast
Lenguajes
Esquemas
...
Replicacion
Tablespaces.
Roles Group y User.

Tablas
Vistas
Tipos
Funciones
Sequences
Funciones y Triggers
Dominios
F. Agregación y conversiones
Operadores



¿Homo paquetus u
Homo compilatus ?

Instalación



Instalación Recomendada

En linux a traves de RPM o APT-GET (Ej: ”rpm -i
postgresql-8.3.rpm” o ”apt-get install postgresql-8.3”).
En Windows tiene un asistente:
 PgInstaller: traducido al español, customizable
(instalación silenciosa), paquete MSI: embebible en otros
instaladores
OneClickInstaller: más simple pero en ingles
Ambos tienen el StackBuilder (apache, php, etc.).
Soporta Win, Linux y MacOS.



Lo que hay que saber por si las...

#export PGDATA='/usr/local/postgresql/data'
#su postgres
/usr/lib/postgresql/<ver>/bin$initdb -l /var/log (...parametros extra)
SE CREA UN CLUSTER NECESARIO PARA ARRANCAR
#postmaster -D $PGDATA
o
#pg_ctl start & (para que quede en segundo plano)



pg_ctl

start
stop (pg_ctl stop -m s[mart] f[ast] i[nmediate])
status
restart
kill
register y unregister
ps -ef |grep postgres .... ps -A |grep postgres



Arranque automatico

copiar el script de postgresql de las contrib a
et/init.d/
chmod a+x postgresql-8.3
ln -s /etc..sql /etc/rc3.d/S12postgresql
idem K02
o en debian con: update-rc.d postgresql defaults
98 02



Configuración y
herramientas
principales



Archivos

postgresql.conf
pg_hba.conf
pg_ident.conf
main.pid (solo contiene el pid del proceso)
(son iguales tambien en windows)
Las rutas dependen mucho del paquete de
instalacion, por defecto compilando desde fuentes
es /usr/local/pgsql



postgresql.conf

Principales:
listen_addresses='*'
port
max_connections
ssl
max_stack_depth=2048 (pila)

log_destination=stderr
log_rotatio_age
log_rotation_size
vacuum
cliente



pg_hba.conf

Controla accesos de
users desde ip's
Tipos de conexiones
TRUST, REJECT, MD5,
PASSWD, CRYPT,
KRB5....

TYPE-DATABASE-USR-ADDRESS-METHOD
local all all 127.0.0.1/32 md5
local all all 0.0.0.0

ident sameuser



Servidores Dedicados

$sysctl -w vm.overcommit_memory=2
es por que el kernel 2.4 y superior mata al
postmaster...
Se recomienda que los procesos tengan prioridad
-10 o mayor.
Backups ! PITR, pg_dump, mirroring



ACID



¿Que es?

[A]tomicidad (transacciones indivisibles)
[C]onsistencia
[I]solation ,aislamiento (no se pueden ver entre
transacciones)
[D]urabilidad: exito de una transaccion que
perdura.



Solucion para [A]

Sentencias de BEGIN, END, ROLLBACK,
COMMIT y SAVEPOINT.



Solucion [C]

A partir de 7.*, gestor de integridad:
not null
check
unique
primary key
fk -match full y partial



Solucion [I]

MVCC



Solucion [D]

WAL (write Ahead Log) /var/pg_xlog
Acelera los tiempos de commit y de insercion y
update.



Monitoreo basico



Monitor

A traves de ps o top, free, vmstat, iostat.
Para poder realizar estadisticas desde el catalogo debe estar
activo el recolector de estadisticas en el postgresql.conf.
tablas de catalogo:
pg_stat_activity
pg_stat_database
pg_stat_all_databases
pg_stat_all_indexes
pg_locks ....



Monitoreando I/O desde psql

SELECT relfilenode, relpages FROM pg_class
WHERE relname='tabla'
SELECT ..... WHERE relname IN
('pg_toast_relfilenode','pg_toast','relfilenode') –
desborde de TOAST.



psql

(herramienta cliente)



El poder de la linea de comando...

El psql permite hacer absolutamente todo lo que
se puede hacer con el Postgresql.
Inclusive, todo aquello que hacemos de forma
grafica con el Pgadmin3.



Ingreso

La autenticacion puede ser configurada a traves
de los archivos pg_hba.conf y pg_ident.conf.
Estos controlan y realizan el nexo con entre los
usuarios del sistema operativo y la base de datos.
postgres$psql -Upostgres -Ddatabase
Se recomienda el ident en sameuser.



desde afuera...

psql -l -Upostgres (lista las bases)
psql template1 postgres -A -t -c ”select * from
pg_database where encoding = 6” | while read D;
do (tira el result set a $D)



[Meta]comandos

\c (conecta con otra base)
\?
\h <comando sql>
\l (lista bases)
\d[t|i|s|S|v]
\o (redirecciona output)
\H (html)
\i (ejecutar comandos desde archivos)



¡Esta vez me

tocaba
a mi !

Referencias:



Bibliografia Recomendada

Practical Postgresql (www.commandprompt.org)
www.postgresql.org/documentation
www.pgfoundry.org
www.planetpostgresql.org
wiki.postgresql.org



Ud. ya conoce Postgresql.

¿Que tal si lo prueba?

Gracias por quedarse
en su asiento SIN arrojar

zapatos al disertante.

file:///media/KINGSTON/PGDAY_JUNIN/INTRO_POSTGRES/unnoba.JPG