CONFERENCIA 1 INTRO ARQUITECTURA INSTALACION-U1.pdf ...

Viewer
Transcript

UNIDAD 1. PostgreSQL CONFERENCIA 1. Introducción, Arquitectura, Instalación y Configuración. http://www.postgresql.org Ing. Diego Palacios C. – [email protected]

INTRODUCCION

Ing. Diego Palacios C. – [email protected]

INTRODUCCIÓN A POSTGRESQL •PostgreSQL es un sistema de gestión de bases de datos objeto-relacional, distribuido bajo licencia BSD (Berkeley Software Distribution ) y con su código fuente disponible libremente. •Es el sistema de gestión de bases de datos de código abierto más potente del mercado y en sus últimas versiones no tiene nada que envidiarle a otras bases de datos comerciales. •PostgreSQL utiliza un modelo cliente/servidor •Usa multiprocesos en vez de multihilos para garantizar la estabilidad del sistema.

3

Ing. Diego Palacios C. - PostgreSql Administration Specialist

UN POCO DE HISTORIA  1977-1985 Michael Stonebraker inicia postgreSQL como Ingres en la  

 

4

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.

Ing. Diego Palacios C. - PostgreSql Administration Specialist

UN POCO DE HISTORIA

5

Ing. Diego Palacios C. - PostgreSql Administration Specialist

CARACTERISTICAS  PORTABLE  Escrito en ANSI C.  Compatible con Windows, Linux, Mac OS / X y las principales

plataformas UNIX.  CONFIABLE:  ACID(Atomicidad, Concurrencia, Aislamiento, Durabilidad)  Soporta transacciones.  Soporta Savepoints (Puntos de recuperación)  Usa Write Ahead Logging  ESCALABLE  Usa el Control de Concurrencia Multiversión.  Soporta Tablespaces 6

Ing. Diego Palacios C. - PostgreSql Administration Specialist

CARACTERISTICAS  SEGURO  Emplea Host-Based Access Control  Provee Object-Level Permissions  SSL

 DISPONIBLE  Replicación de apoyo  Soporte para alta disponibilidad  AVANZADO  Full Text Search

 Soporta Triggers y Funciones

7

Ing. Diego Palacios C. - PostgreSql Administration Specialist

CARACTERISTICAS  Soporta lenguajes Procedimentales  PL/pgSQL  PL/Perl  PL/TCL

 PL/PHP

8

Ing. Diego Palacios C. - PostgreSql Administration Specialist

MULTI-VERSION CONCURRENCY CONTROL (MVCC)= ALTA CONCURRENCIA Mediante un sistema denominado MVCC (Acceso concurrente multiversión) PostgreSQL permite que mientras un proceso escribe en una tabla, otros accedan a la misma tabla sin necesidad de bloqueos. Cada usuario obtiene una visión consistente de lo último a lo que se le hizo commit. Esta estrategia es superior al uso de bloqueos por tabla o por filas común en otras bases, eliminando la necesidad del uso de bloqueos explícitos.

9

Ing. Diego Palacios C. - PostgreSql Administration Specialist

WRITE AHEAD LOGS (WAL)  Registra cada una de las transacciones como es el caso de insertar /

actualizar / eliminar antes de que realmente se lleva a cabo el proceso.  El sistema no con considera un dato guardado hasta que éste se escriba en el disco.  Proporciona la recuperación en caso de accidente o falla del sistema

10

Ing. Diego Palacios C. - PostgreSql Administration Specialist

LIMITACIONES Límite Máximo tamaño base de dato Máximo tamaño de tabla Máximo tamaño de fila Máximo tamaño de campo Máximo numero de filas por tabla Máximo numero de columnas por tabla Máximo numero de indices por tabla 11

Valor Ilimitado (Depende de tu sistema de almacenamiento) 32 TB 1.6 TB 1 GB Ilimitado 250 - 1600 (dependiendo del tipo) Ilimitado

Ing. Diego Palacios C. - PostgreSql Administration Specialist

ARQUITECTURA

Ing. Diego Palacios C. – [email protected]

ARQUITECTURA

13

Ing. Diego Palacios C. - PostgreSql Administration Specialist

ARQUITECTURA  Aplicación cliente: Esta es la aplicación cliente que utiliza PostgreSQL

como administrador de bases de datos (PgAdmin III).  Proceso postmaster: Escucha por un puerto/socket las conexiones entrantes de clientes. Tambien es el encargado de crear los procesos hijos que se encargaran de autentificar estas peticiones, gestionar las consultas y mandar los resultados a las aplicaciones clientes.  Archivos de configuracion: Los 3 archivos principales de configuración utilizados por PostgreSQL, postgresql.conf, pg_hba.conf y pg_ident.conf.

14

Ing. Diego Palacios C. - PostgreSql Administration Specialist

ARQUITECTURA  Procesos hijos postgres: Procesos hijos que se encargan de

   

15

autentificar a los clientes, de gestionar las consultas y mandar los resultados a las aplicaciones clientes. PostgreSQL share buffer cache: Memoria compartida usada por PostgreSQL para almacenar datos en caché. Write-Ahead Log (WAL): Componente del sistema encargado de asegurar la integridad de los datos (recuperación de tipo REDO) Kernel disk buffer cache: Caché de disco del sistema operativo. Disco: Disco físico donde se almacenan los datos y toda la información necesaria para que PostgreSQL funcione.

Ing. Diego Palacios C. - PostgreSql Administration Specialist

ARQUITECTURA CLUSTER  Un cluster de base de datos es un conjunto de base de datos que son

administradas en una sola instancia de servidor y residiran en un directorio de datos (/opt/PostgreSQL/8.4/data).  Un proceso Postmaster corresponde a un puerto de un Cluster.

16

Ing. Diego Palacios C. - PostgreSql Administration Specialist

DIRECTORIO DE INSTALACION

Directorio de Instalación (/opt/PostgreSQL/8.4/) bin (servicios, dll)

17

doc (documentacion)

include (librerías de PostgreSQL para esteblecer conexión libpq)

Ing. Diego Palacios C. - PostgreSql Administration Specialist

lib

share

DIRECTORIO DE DATOS DATA

GLOBAL (parametros de configuració n)

18

BASE (bases de datos)

Pg_tblspc (Contiene links a los directorios de los tablespace)

Pg_XLOG

Ing. Diego Palacios C. - PostgreSql Administration Specialist

Pg_CLOG PostgreSQL.CONF, POSTMASTER.PID, POSTMASTER.OPT S, pg_HBA..CONF, pg_IDENT.CONF

ESTRUCTURA DE OID  Cada base de datos es un directorio que contiene uno o varios archivos

por cada tabla o indice creado, estos archivos llegan a un maximo de 1GB si sobrepasan estre valors seran fragmentados.  Cada tabla o inidce se almacena en un Tablespace.  PostgreSQL administra links a otros directorios donde se encunetren los Tablespace.  Por defecto, los indices son almacenados en el mismo tablespace.

19

Ing. Diego Palacios C. - PostgreSql Administration Specialist

ESTRUTURA DE UNA BASE DE DATOS

20

Ing. Diego Palacios C. - PostgreSql Administration Specialist

INSTALACION

Ing. Diego Palacios C. – [email protected]

INSTALACION USUARIOS Y PERMISOS DEL SISTEMA OPERATIVO  PostgreSQL se ejecuta como un demonio (Unix / Linux) o

servicio(Windows).  Tanto los procesos y archivos de datos de PostgreSQL deben pertenecer aun usuario en el sistema operativo.  Por esta razón el usuario del SO no debe ser administrador.

22

Ing. Diego Palacios C. - PostgreSql Administration Specialist

INSTALACION EN LINUX En este link podemos encontrar los instaladores de PostgreSQL: http://www.enterprisedb.com/products-services-training/pgdownload

23

Ing. Diego Palacios C. - PostgreSql Administration Specialist

INSTALACION PASO 1 Copiar el instalador en la carpeta /opt y lo ejecutamos

24

Ing. Diego Palacios C. - PostgreSql Administration Specialist

INSTALACION PASO 2 Empezamos la instalación.

25

Ing. Diego Palacios C. - PostgreSql Administration Specialist

INSTALACION PASO 3 Directorio de Instalación

26

Ing. Diego Palacios C. - PostgreSql Administration Specialist

INSTALACION PASO 4 Directorio de Datos

27

Ing. Diego Palacios C. - PostgreSql Administration Specialist

INSTALACION PASO 5 Contraseña del superusuario.

28

Ing. Diego Palacios C. - PostgreSql Administration Specialist

INSTALACION PASO 6 Configuración del Puerto.

29

Ing. Diego Palacios C. - PostgreSql Administration Specialist

INSTALACION PASO 7 Configuración Regional

30

Ing. Diego Palacios C. - PostgreSql Administration Specialist

INSTALACION PASO 8 Ingresar a PGAdmin III

31

Ing. Diego Palacios C. - PostgreSql Administration Specialist

INSTALACION PASO 9  Configurando las variables de entorno, para esto abrimos un terminal dentro

de un usuario que no sea administrador y escribimos el siguiente comando: vi ~/.bash_profile  Y aparece algo como esto, y agregamos lo que se encuentra marcado:

32

Ing. Diego Palacios C. - PostgreSql Administration Specialist

SERVICIOSDE POSTGRESQL  pg_ctl start [] – Start the server  pg_ctl stop [] – Stop the server

 pg_ctl restart [] – Stop then start the server  pg_ctl status [] – Display server status  pg_ctl reload [] – Reload the configuration file

33

Ing. Diego Palacios C. - PostgreSql Administration Specialist

PARAMETROS DE PG_CTL  -D - Database cluster directory  -l - Write log output to the specified file  -m smart – Wait for clients to disconnect (default)

 -m fast – Active tx rolled back and clients Disconnected  -m immediate – Abort processes without clean shutdown

34

Ing. Diego Palacios C. - PostgreSql Administration Specialist

CONFIGURACION SETEO DE PARAMETROS  En el archivo de configuración postgresql.conf podemos setear los valores de cada parametro que pueden ser de los siguientes tipos de datos: boolean, integer, floating point, o string. Algunos parametros requieren el reincio o reload del servicio.  Existe un solo archivo postgresql.conf por cada cluster.

35

Ing. Diego Palacios C. - PostgreSql Administration Specialist

PARAMETROS DE CONEXION  listen_addresses (string)  Specifies the TCP/IP address(es) on which the server is to listen for

connections  port (integer)  The TCP port the server listens on; 5432 by default.  max_connections (integer)  Determines the maximum number of concurrent connections to the database server

36

Ing. Diego Palacios C. - PostgreSql Administration Specialist

PARAMETROS DE SEGURIDAD Y AUTENTICACIÓN  authentication_timeout (integer)  Maximum time to complete client authentication, in seconds  ssl (boolean)  Enables SSL connections.

37

Ing. Diego Palacios C. - PostgreSql Administration Specialist

PARAMETROS DE MEMORIA  shared_buffers (integer)  Sets the amount of memory the database server uses for shared

memory buffers.  temp_buffers (integer)  Sets the maximum number of temporary buffers used by each database session.  work_mem (integer)  Specifies the amount of memory to be used by internal sort operations and hash tables before switching to temporary disk files.  maintenance_work_mem (integer)  Specifies the maximum amount of memory to be used in maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. 38

Ing. Diego Palacios C. - PostgreSql Administration Specialist

LOGGING La palabra log es un término anglosajón equivalente a la palabra bitácora en la lengua castellana.  log_destination = Por defecto toda la salida de log de Postgres es

llevada a stderr. Desde aquí es llevada hacia los archivos de log. En este parámetro puede ser cambiarse a syslog en linux y eventlog en windows, entre otros y se puede fijar varios separados por comas. Entre syslog y stderr ES PREFERIBLE el último ya que brinda información más detallada que la que es llevada a syslog.

39

Ing. Diego Palacios C. - PostgreSql Administration Specialist

LOGGING  redirect_stderr = off – Permite la captura de stderr y su dirección a

los archivos de log. Esto es útil para poder capturar los errores de extensiones de terceros a postgres integrados con el DBMS como bibliotecas enlazadas dinámicamente.  log_directory = ‘pg_log’ – Cuando esta habilitado redirect_stderr, esta

opción indica el directorio que contendrá los archivos de log. Puede ser especificado absoluto o relativo al directorio de trabajo.

40

Ing. Diego Palacios C. - PostgreSql Administration Specialist

LOGGING  log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log’

– Cuando esta habilitado redirect_stderr, esta opción indica el formato de nombre de archive utilizado para logear.

 log_rotation_size = 10MB – Setea el tamaño máximo en kilobytes

que puede tener un archive de log. Alcanzada esta cantidad, se forza la creación de un nuevo archivo de log. Estando en cero, se asume ilimitado. Es poco recomendable dejar elarchivo sin límite de espacio puesto que en teoría podría generar un archivo más grande que lo que permite el sistema de archivo, haciendo colapsar el módulo de log.

41

Ing. Diego Palacios C. - PostgreSql Administration Specialist

¿CUANDO LOGEAR?  DEBUG5 – DEBUG4 – DEBUG3 – DEBUG2 – DEBUG1







 

42

Mensajes de debug utilizados por los desarrolladores de PostgreSQL. Habilitar esta opción no tiene mucho sentido para usuarios comunes y bajará desempeño del motor considerablemente dada la cantidad de información generada. NOTICE Provocado por un componente de software de Postgres, un script o cualquier otro componente. Se asume como un aviso, sin carácter nocivo. WARNING Algún evento ocurrido ha realizado una operación que no esta del todo bien. Suele darse en sentencias SQL cuya sintaxis no es perfecta, pero puede ser corregida. ERROR Evento irreparable que provoca que el comando ejecutado y la actividad que se este realizando se lleve a cabo. Dentro de los scripts se conoce como “Exception”. FATAL Error en una sesión del DBMS que provoca la terminación inmediata de esta. PANIC Error de la mayor gravedad que provoca la terminación inmediata de todo elDBMS, matando todas las sesiones en todas las BdD.

Ing. Diego Palacios C. - PostgreSql Administration Specialist

¿CUANDO LOGEAR?  log_min_duration_statement = -1

De estar seteado en un valor mayor que cero, indica la duración mínima que debe tener una consulta en milisegundos para que sea logeada. Este parámetro funciona en combinación con log_min_error_statement.

43

Ing. Diego Palacios C. - PostgreSql Administration Specialist

¿QUE LOGEAR?  log_connections = off

De estar habilitado, logea cada conexión exitosa realizada al servidor.  log_disconnections = off De estar habilitado, logea cada desconexión del servidor.  log_duration = off De estar habilitado logea la duración de cada consulta SQL realizada.

44

Ing. Diego Palacios C. - PostgreSql Administration Specialist

CREANDO Y ADMINISTRANDO UNA BASE DE DATOS

45

Ing. Diego Palacios C. - PostgreSql Administration Specialist

QUE ES UNA BASE DE DATOS EN POSTGRESQL  Una base de datos es una colección de objetos SQL. Se trata de una

colección de esquemas y los esquemas contienen las tablas, funciones, etc.  Las Bases de datos se crean con la sentencia CREATE DATABASE y se destruyen con elComando DROP DATABASE.  Para determinar el conjunto de Base de datos existentes podemos hacer un: SELECT datname FROM pg_database;

46

Ing. Diego Palacios C. - PostgreSql Administration Specialist

SCHEMA SEARCH PATH  EL esquema por defecto en una base de datos es “public”.  SHOW search_path;  SET search_path TO myschema, public;

47

Ing. Diego Palacios C. - PostgreSql Administration Specialist

OBJECT OWNERSHIP

48

Ing. Diego Palacios C. - PostgreSql Administration Specialist

PGADMIN III

49

Ing. Diego Palacios C. - PostgreSql Administration Specialist

ERRORES FRECUENTES DE CONEXION  Could not connect to Server: Servicio no disponible.  FATAL: no pg_hba.conf entry, listas de acceso no existen o

desconfiguradas.

50

Ing. Diego Palacios C. - PostgreSql Administration Specialist

PROPIEDADES DEL SERVIDOR

51

Ing. Diego Palacios C. - PostgreSql Administration Specialist

ACCEDIENDO A LA INFORMACION

52

Ing. Diego Palacios C. - PostgreSql Administration Specialist

FILTRANDO DATOS

53

Ing. Diego Palacios C. - PostgreSql Administration Specialist

HERRAMIENTA DE EDICION DE DATOS

54

Ing. Diego Palacios C. - PostgreSql Administration Specialist

ORDEN Y FILTRO DE DATOS

55

Ing. Diego Palacios C. - PostgreSql Administration Specialist

HERRAMIENTA DE CONSULTAS

56

Ing. Diego Palacios C. - PostgreSql Administration Specialist

DATA OUTPUT

57

Ing. Diego Palacios C. - PostgreSql Administration Specialist

EXPLAIN

58

Ing. Diego Palacios C. - PostgreSql Administration Specialist

MENSAJES

59

Ing. Diego Palacios C. - PostgreSql Administration Specialist

HISTORY

60

Ing. Diego Palacios C. - PostgreSql Administration Specialist

CREANDO UNA BASE DE DATOS

61

Ing. Diego Palacios C. - PostgreSql Administration Specialist

REPORTES DE BASE DE DATOS

62

Ing. Diego Palacios C. - PostgreSql Administration Specialist

BACKUP Y RESTORE

63

Ing. Diego Palacios C. - PostgreSql Administration Specialist

ESQUEMAS

64

Ing. Diego Palacios C. - PostgreSql Administration Specialist

DOMINIOS

65

Ing. Diego Palacios C. - PostgreSql Administration Specialist

FUNCIONES

66

Ing. Diego Palacios C. - PostgreSql Administration Specialist

SECUENCIAS

67

Ing. Diego Palacios C. - PostgreSql Administration Specialist

TABLAS

68

Ing. Diego Palacios C. - PostgreSql Administration Specialist

TABLAS - COLUMNAS

69

Ing. Diego Palacios C. - PostgreSql Administration Specialist

RESTRICCIONES

70

Ing. Diego Palacios C. - PostgreSql Administration Specialist

TRIGGERS

71

Ing. Diego Palacios C. - PostgreSql Administration Specialist

VISTAS

72

Ing. Diego Palacios C. - PostgreSql Administration Specialist

TABLESPACES

73

Ing. Diego Palacios C. - PostgreSql Administration Specialist

FIN

74

Ing. Diego Palacios C. - PostgreSql Administration Specialist