1 PostgreSQL Capacitación Nivel 1 Día 2
2 Agenda Herramientas »PgAdmin, psql Monitoreo Administración general Jerarquia de objetos. Creación de objetos del cluster. Respaldos WAL. Mantenimiento SQL.
3 PgAdmin Vs psql
4 PgAdmin vs psql (2)
5 PgAdmin Query Builder
6 Monitoreo con PgAdmin Tools → Server Status Conexiones clientes abiertas
7 Monitoreando inserciones Esto nos indica Que esta habiendo una Carga importante de datos
8 Herramientas SO: top El top muestra de manera dinámica el uso de nuestro server.
9 Herramientas SO: htop Es más completo. No viene instalado por defecto.
10 Herramientas SO: iostat, mpstat, free Son relativamente estándar.
11 Herramientas Postgres El script de inicio posee una opción status. Solo nos indica el PID del servidor que está levantado contra el cluster.
12 Herramientas SO: Admin. Tareas win. Podemos ver de manera básica el comportamiento del servidor. Desde 'cmd' con el comando tasklist.
13 Herramientas SO: cmd Algunos Windows no tienen estos comandos. W2003 y superior si los tienen.
14 Actividad I/O Consulta sobre el catalogo de sistema. – Nos trae cuantos bloques fueron consultados y cuantos fueron 'tocados'.
15 Setear el script de arranque SysV $ su – root # tar xvfz postgresql-.tar.gz # cd postgresql- # cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql # chmod a+x /etc/rc.d/init.d/postgresql # update-rc.d postgresql defaults (o) # ln -s /etc..sql /etc/rc3.d/S12postgresql # ln -s /etc..sql /etc/rc3.d/K02postgresql update-rc.d es una herramienta característica de las versiones basadas en Debian.
16 Manejar el servidor Con service: » service postgresql [start|stop|restart|reload|status] Con script: » /etc/init.d/postgres [start|stop|restart|reload|status] Con comando: » pg_ctl start [-w] [-t SECS] [-D DATADIR] [-s] [-l FILENAME] [-o "OPTIONS"] » pg_ctl stop [-W] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN- MODE] » pg_ctl restart [-w] [-t SECS] [-D DATADIR] [-s] [-m SHUTDOWN- MODE] [-o "OPTIONS"] » pg_ctl reload [-D DATADIR] [-s] » pg_ctl status [-D DATADIR] » pg_ctl kill SIGNALNAME PID
17 Administrando Nuestro contexto
18 Administrando Usuarios Usuarios SO != usuarios Base de datos Rol = usuario – USER opción LOGIN por defecto. – ROLE opción NO LOGIN por defecto. – GROUP no tiene CONNECTION LIMIT SE-Postgresql – Propone un ambiente más seguro entre usuarios del SO y la base.
19 Ejemplos CREATE GROUP los_resagados WITH NOSUPERUSER NOLOGIN; SELECT (CURRENT_DATE+1)::timestamp; CREATE USER resagado1 WITH PASSWORD '1234' VALID UNTIL '2009-08-06 00:00:00' IN GROUP los_resagados; CREATE USER resagado2 WITH PASSWORD '1234' VALID UNTIL '2009-08- 06 00:00:00' IN GROUP los_resagados; DROP USER resagado1; ALTER USER resagado2 WITH PASSWORD '4321'; 1 2
20 Bases de Datos CREATE DATABASE name [ [ WITH ] [ OWNER [=] dbowner ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ TABLESPACE [=] tablespace ] [ CONNECTION LIMIT [=] connlimit ] ] TEMPLATE: utilizará esa base como plantilla de la nueva. Incluido los datos! TABLESPACE: Podemos almacenar la base en una parte especial del disco. CONNECTION LIMIT: Permite hasta un número determinado de conexiones. OWNER: Dueño de la base. HINT: La base que utilicemos como plantilla no debe estar siendo accedida Por otros usuarios.
21 Tablespaces vs. Schemas Esquemas – Organización lógica de objetos y relaciones de la base. – Por defecto: Public. Tablespaces – Organización física de objetos del cluster.
22 Ejemplo Tablespace emanuel@pgsql:/var/lib/pgsql$ sudo mkdir data/test_ts emanuel@pgsql:/var/lib/pgsql$ sudo chown postgres data/test_ts CREATE TABLESPACE test_ts OWNER resagado2 LOCATION '/var/lib/pgsql/data/test_ts'; CREATE DATABASE test WITH OWNER resagado2 TEMPLATE template1 TABLESPACE test_Ts; Createdb createuse r Comandos desde shell
23 Ejemplo Schema + Tablespace base1=# create database foo with owner = postgres template=template1 encoding='UTF8' tablespace=prueba; CREATE DATABASE base1=# create schema x; CREATE SCHEMA base1=# create table x(); CREATE TABLE base1=# alter table x set schema x; ALTER TABLE root@box:/usr/local/pgsql# mkdir tablespace root@box:/usr/local/pgsql# chown postgres tablespace/ base1=# create tablespace prueba owner postgres location '/usr/local/pgsql/tablespace'; CREATE TABLESPACE base1=# alter table x.x set tablespace prueba; ALTER TABLE Una tabla puede estar en un esquema y en un tablespace al mismo tiempo.
24 Jerarquía de Objetos Servidores – Bases de datos Cast Lenguajes Esquemas... Replicacion – Tablespaces. – Roles Group y User. Tablas Vistas Tipos Funciones Sequences Funciones y Triggers Dominios Agregados y conversiones
25 Respaldos y restauraciones
26 Herramientas estandar pg_dump pg_restore psql (sirve para restaurar)
27 pg_dump -f, --file=FILENAME output file name -F, --format=c|t|p output file format (custom, tar, plain text) -i, --ignore-version proceed even when server version mismatches -Z, --compress=0-9 compression level for compressed formats -a, --data-only dump only the data, not the schema -b, --blobs include large objects in dump -c, --clean clean (drop) schema prior to create -C, --create include commands to create database in dump -d, --inserts dump data as INSERT commands, rather than COPY -D, --column-inserts dump data as INSERT commands with column names -E, --encoding=ENCODING dump the data in encoding ENCODING -n, --schema=SCHEMA dump the named schema(s) only -N, --exclude-schema=SCHEMA do NOT dump the named schema(s) -o, --oids include OIDs in dump -O, --no-owner skip restoration of object ownership in plain text format -s, --schema-only dump only the schema, no data -S, --superuser=NAME specify the superuser user name to use in plain text format -t, --table=TABLE dump the named table(s) only -T, --exclude-table=TABLE do NOT dump the named table(s) -x, --no-privileges do not dump privileges (grant/revoke) --disable-dollar-quoting disable dollar quoting, use SQL standard quoting --disable-triggers disable triggers during data-only restore Generale s Contro l De salida
28 pg_restore -d, --dbname=NAME connect to database name -f, --file=FILENAME output file name -F, --format=c|t specify backup file format -i, --ignore-version proceed even when server version mismatches -l, --list print summarized TOC of the archive -v, --verbose verbose mode -a, --data-only restore only the data, no schema -c, --clean clean (drop) schema prior to create -C, --create create the target database -I, --index=NAME restore named index -L, --use-list=FILENAME use specified table of contents for ordering output from this file -n, --schema=NAME restore only objects in this schema -O, --no-owner skip restoration of object ownership -P, --function=NAME(args) restore named function -s, --schema-only restore only the schema, no data -S, --superuser=NAME specify the superuser user name to use for disabling triggers -t, --table=NAME restore named table -T, --trigger=NAME restore named trigger -x, --no-privileges skip restoration of access privileges (grant/revoke) --disable-triggers disable triggers during data-only restore --no-data-for-failed-tables -1, --single-transaction restore as a single transaction Generale s Contro l De Salida
29 Dump and restore cooktip :) Volcar con compresión y con re-creación de objetos: pg_dump -c -U -Z6 > archivo.sql.gz Restaurar con compresión: gzip -d archivo.sql.gz pg_restore -Uubuntu -dubuntu dump.sql
30 Hot dump & restore WTF?! pg_dump -Fp -C -Uubuntu ubuntu | perl -pe 's/ubuntu/ubuntu2/g' | psql -p5432 -Uubuntu WTF?????!!!!!! La linea de perl es opcional, solamente cambio el nombre de la base a crear. Se utiliza para migraciones teniendo 2 servidores arriba. Como? – Teniendo 2 clusters en puertos distintos
31 Dump con tar De esta forma se dumpea en formato tar. pg_dump -Ft -U database > dump.tar Se puede descomprimir con: tar xvf dump.tar
32 Dump - Restore PgAdmin Click botón derecho sobre la base. – Seleccionar Backup o restore.
33 Tips No hagas respaldos separados (pgdump - s y -a). Esto servirá para mantener los campos serial en buen estado. Nombrá el archivo con la fecha del dump, asi sera mas fácil de ordenar. Al menos tener 3 o más backups históricos.
34 WAL Write Ahead Log. – Permite acelerar inserciones masivas – Es un estadio previo a comprometer los datos en el espacio de datos. – Su configuración en ambientes masivos es crítica. – Permite integridad de transacciones.
35 WAL Configuración #fsync = on # Sincronización con disco (se recomienda utilizar en # ON, aunque diversos experimentos en ZFS dieron # buenos resultados. #synchronous_commit = on # Sincroniza inmediatamnete al comprometer #wal_sync_method = fsync # Verificar cual soporta el SO open_datasync, # fdatasync, fsync, fsync_writethrough, open_sync #full_page_writes = on # Recupera desde escrituras parciales #wal_buffers = 64kB # min 32kB(change requires restart) #wal_writer_delay = 200ms # 1-10000 milliseconds; Junta varias escrituras al # mismo tiempo #commit_delay = 0 # range 0-100000, in microseconds idem anterior #commit_siblings = 5 # range 1-1000 #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 5min # range 30s-1h #checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0 #checkpoint_warning = 30s # 0 is off
36 WAL Ubicación en el cluster: pg_xlog Contenido: archivos de bloque
37 Antiguos ¬¬ problemas de la WAL LOG: next transaction ID: 884736; next OID: 306834> PANIC: could not access status of transaction 884736> DETAIL: could not read from file> "/usr /local/pgsql8b3/data/pg_clog/0000" at offset 221184: Success> LOG: startup process (PID 17774) was terminated by signal 6> LOG: aborting startup due to startup process failure LOG: next transaction ID: 1048576; next OID: 306834> PANIC: could not access status of transaction 1048576> DETAIL: could not open file "/usr/local/ pgsql8b3/data/pg_clog/0001": No> such file or directory pg_resetxlo g ¡NO FUNCIONA! dd bs=8k count=1 /usr/local/pgsql8b3/data/pg_clog/0001
38 Mantenimiento
39 Herramientas indispensables VACUUM ANALYZE REINDEX Clusterdb Mantenimiento Logs MARTILLO + =
40 Vacuum Recobrar espacio de disco ocupado por la actualización y borrado de datos. Actualizar las estadísticas usadas por el planeador. Proteger contra la posible pérdida de datos sobre problemas con el transaction ID en datos viejos Tipos: - FREEZE - ANALYZE - FULL - S/P
41 Vacuum Full vs. vacuum VACUUM FULL puede liberar mayor cantidad de disco pero es más lento. VACUUM FULL requiere bloqueo exclusivo en la tabla que esta trabajando. Desventaja es que no reduce el tamaño proporsional del índice. Puede hacer a los índices más grandes. Reindex database. EL Vacuum estándar puede correr de forma paralela con otras operaciones exepto modificaciones en los objetos (ALTER) Generalmente se recomienda utilizar este tipo de vacuum.
42 Vacuum Desde linea de comandos: – Vacuumdb -a, --all vacuum all databases -d, --dbname=DBNAME database to vacuum -t, --table='TABLE[(COLUMNS)]' vacuum specific table only -f, --full do full vacuuming -z, --analyze update optimizer hints Desde un cliente: VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [,...] ) ] ]
43 Vacuum Freeze ¿Como saber cuando los XID's están al borde? – SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r'; – SELECT datname, age(datfrozenxid) FROM pg_database; El posible mensaje que puede aparecer es: – WARNING: database "ubuntu" must be vacuumed within 177008866 transactions
44 Configurar VACUUM vacuum_cost_limit = 200 vacuum_cost_page_hit = 1 vacuum_cost_page_miss = 10 vacuum_cost_page_dirty = 20 vacuum_cost_delay = 0 Esto se va sumando Hasta alcanzar el límite Es para disminuir el I/O cuando el cálculo de costo toco límite. Dejarlo bajo es una buena práctica, ir subiendo a medida que hagamos las mediciones.
45 Autovacuum Es un daemon que constantemente vigila las tablas con muchas inserciones y actualizaciones. Es altamente recomendado. Se configura desde el postgresql.conf Se lo puede desactivar.
46 Mejoras en 8.4 Vacuum en 8.4 utiliza VSM (Visibility Space Map) – Permite realizar la operación sobre tuplas modificadas unicamente.
47 Otros caminos Reacomodar la tabla con ALTER.
48 ANALYZE Recolecta estadísticas de acuerdo a la configuración de las relaciones. Se puede setear la recolección por columna. – ALTER TABLE tabla ALTER [ COLUMN ] column SET STATISTICS integer ANALYZE [ VERBOSE ] [ table [ ( column [,...] ) ] ] VACUUM ANALYZE ; Muchos DBA consideran que las estadísticas a veces suelen ser tan importantes como los datos.
49 REINDEX Re-indiza los índices. Suele pasar (aunque no debería) que los índices se corrompan o desactualicen. Los índices clusterizados no tienen efecto con este comando. REINDEX { INDEX | TABLE | DATABASE | SYSTEM } name [ FORCE ] Desde linea de comando: reindexdb
50 Clusterdb Reclusteriza los índices que han sido clusterizados. La sentencia es CLUSTER y la linea de comandos es clusterdb.
51 ¿CLUSTER? Obliga a la tabla a guardarse en el mismo orden que el índice. Se utiliza mucho para tablas grandes y de pocos cambios. Inversamente proporsional, a menos cambios, se recomienda aumentar el fillfactor. Crear: CLUSTER employees USING employees_ind; Actualizar: CLUSTER employees; Actualizar todo: CLUSTER; CREATE INDEX nombreind ON tabla USING btree (columna) WITH (FILLFACTOR=80); ALTER TABLE tabla CLUSTER ON nombreind;
52 Rotación y mantenimiento de logs Evitar el crecimiento exponencial de los logs. Especificar rotación. Mostrar datos realmente relevantes. Borrar historicos seguido, a menos que sean valuables. Setear niveles de log de acuerdo a la situación, evitar dejar uno estático. pg_ctl start | rotatelogs /var/log/pgsql_log 86400 Desde el postgresql.conf: log_rotation_size = 10MB
53 Martillo Suele usarse cuando todo falla.
54 SQL
55 Lenguajes DDL DML Control de transacciones Control de permisos
56 Sentencias básicas DML SELECT INSERT UPDATE TRUNCATE
57 SELECT SELECT [ ALL | DISTINCT [ ON ( expression [,...] ) ] ] * | expression [ AS output_name ] [,...] [ FROM from_item [,...] ] [ WHERE condition ] [ GROUP BY expression [,...] ] [ HAVING condition [,...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] select ] [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [,...] ] [ LIMIT { count | ALL } ] [ OFFSET start ] [ FOR { UPDATE | SHARE } [ OF table_name [,...] ] [ NOWAIT ] [...] ] where from_item can be one of: [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [,...] ) ] ] ( select ) [ AS ] alias [ ( column_alias [,...] ) ] function_name ( [ argument [,...] ] ) [ AS ] alias [ ( column_alias [,...] | column_definition [,...] ) ] function_name ( [ argument [,...] ] ) AS ( column_definition [,...] ) from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [,...] ) ] Predicad o Fuente s Agrupació n Conjunció n Ordenació n Filtro Bloqueo s Condició n
58 INSERT INSERT INTO table [ ( column [,...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [,...] ) [,...] | query } [ RETURNING * | output_expression [ AS output_name ] [,...] ]
59 UPDATE UPDATE [ ONLY ] table [ [ AS ] alias ] SET { column = { expression | DEFAULT } | ( column [,...] ) = ( { expression | DEFAULT } [,...] ) } [,...] [ FROM fromlist ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ AS output_name ] [,...] ]
60 DELETE - TRUNCATE DELETE FROM [ ONLY ] table [ [ AS ] alias ] [ USING usinglist ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ AS output_name ] [,...] ] TRUNCATE [ TABLE ] name [,...] [ CASCADE | RESTRICT ]
61 DELETE ejemplo
62 Condicionales de SQL CASE COALESCE NULLIF GREATEST AND LEAST
63 Subquerys - SemiJoins Operadores: NOT IN, IN (list) or (query), EXIST, NOT EXIST, ANY, SOME, ALL
64 Sentencias básicas DDL CREATE ALTER DROP
65 DDL create table varios( state char(2), name varchar(30), children integer, distance float, budget numeric(19,2), born date, checkin time, started timestamp default CURRENT_TIMESTAMP ); comment on table varios IS 'ejemplo de tabla con varios datos y un default'; CREATE TABLE statename ( code CHAR(2) PRIMARY KEY, name CHAR(30) ); COMMENT ON statename IS 'tabla de paises'; create table customer ( customer_id integer, name char(30), telephone char(20), state char(2) references statename ); COMMENT ON customer IS 'ingresar un state que este en statename'; create table employee ( employee_id integer primary key, name varchar(20), weight float ); create table llaves_compuestas( col1 integer, col2 integer, primary key(col1,col2) );
66 ...DDL (cont) create table llaves_foraneas_compuestas( col3 integer, col4 integer, foreign key (col3, col4) references llaves_compuestas ); create table t_con_check( nombre varchar(20) CHECK (length(trim(nombre)) > 1), sexo char(1) CHECK(sexo IN ('M','F')), fecha_pasada date CHECK(fecha_pasada BETWEEN '1990-01-01' AND CURRENT_DATE), CHECK (upper(trim(nombre)) != 'EMA') --check de tabla );
67 ALTER ejemplos prueba=# alter table customer add primary key(customer_id); NOTICE: ALTER TABLE / ADD PRIMARY KEY creará el índice implícito «customer_pkey» para la tabla «customer» ALTER TABLE prueba=# create table coches( customer integer references customer, modelo varchar(4) ); CREATE TABLE prueba=# create index ix_btree_customer on customer USING btree(customer_id) TABLESPACE prueba; Prueba=# create index ix_h_cutomer on customer USING hash(customer_id) TABLESPACE prueba;
68 Vistas simples Con la vista se puede hacer una simulacion del sinonym de Oracle (Permite dar un alias a una tabla). De esta forma: CREATE VIEW tablaAlias AS 'Select * from tabla'; -- No confundir con las vistas materializadas!!! De la misma forma podemos crear una funcion para que retorne un record set... CREATE OR REPLACE FUNCTION simula(integer) IMMUTABLE LANGUAGE SQL AS 'Select * from tabla where columna = $1';
69 Control transaccional BEGIN END SAVEPOINT COMMIT ROLLBACK PREPARE TRANSACTION ROLLBACK PREPARED
70 Ejemplo básico
71 Control permisos GRANT REVOKE
72 Gracias!