2. administración de postgresql

5/27/2018 2. Administracin de PostgreSQL

1/42

Administracin de PostgreSQLLic. Jos

Agustn

Torres

Bez

.


2/42

Servidor y Objetos del PostgreSQLUna vez instalado PostgreSQL y abiertopgAdmin uno se encuentra con lo siguiente:


3/42

Servidor y Objetos del PostgreSQLEn Ingles:


4/42

Hay una gran variedad de objetos del PostgreSQL.

Hablaremos de los objetos ms utilizados:

Servidor (Server): Es el servidor de PostgreSQL odemonio. Usted puede tener ms de un servidor

fsico, siempre y cuando la conexin se realice en

diferentes puertos o direcciones IP.

Base de datos (Database): Cada servidorPostgreSQL puede almacenar muchas bases dedatos.

Servidor y Objetos del PostgreSQL


5/42

Tabla (table): Las tablas son los caballosde batalla de cualquier base de datos. Lo

que es nico acerca de las tablas de

PostgreSQL es el soporte de herencia. Esquema (schema): Los esquemas son

los contenedores lgicos de tablas y otros

objetos. Cada base de datos puede tenervarios esquemas. Los esquemas son

parte de las normas ANSI-SQL, por lo se

vern en otras bases de datos.



6/42

Tablespace: es el lugar fsico donde sealmacenan los datos. PostgreSQL permite

tablespaces que se gestionan de forma

independiente, lo que significa que se puedemover fcilmente las bases de datos a las

diferentes unidades de almacenamiento

Vista (view): La mayora de las bases dedatos relacionales tienen vistas para abstraer

consultas. En PostgreSQL las vistas se

pueden actualizar.



7/42

Funcin (function): Las funciones enPostgreSQL pueden devolver un valor o un

conjuntos de registros. Los agregados son

funciones que se utilizan con las

construcciones SQL como GROUP BY.

Cast: Permite convertir de un tipo de datos aotro. Ejemplo: de Integer a Varchar.



8/42

Secuencia (sequence): La secuencia es loque controla el auto-incremento en lasdefiniciones de tabla. Por lo general, se

crean automticamente cuando se defineuna columna de tipo serial.

Disparadores (trigger): Se encuentra enmuchas bases de datos, los disparadores

detectan los eventos de cambio en los datosy puede reaccionar antes o despus decambiar los datos actuales (registros).



9/42

Administracin de PostgreSQLTres son los archivos de configuracin

principales que controlan las operaciones

bsicas de una instancia del servidor

PostgreSQL.

Estos archivos estn ubicados en la carpeta

de datos PostgreSQL por defecto.

Puede editarlos utilizando el editor de texto

de su eleccin, o utilice el paquete de

administracin que viene con pgAdmin.


10/42

Administracin de PostgreSQLSi alguna vez no est seguro en dnde seencuentran estos archivos, ejecute la

siguiente consulta SQL como superusuario

(postgres).

SELECT name, setting

FROM pg_settings

WHERE category = 'File Locations';


11/42

Archivo postgresql.confControla los ajustes

principales de la

instancia del servidor

PostgreSQL, as como

ajustes por defecto paranuevas bases de datos.

Muchos ajustes, "como

la ordenacin de la

memoria", pueden sersobreescritos en la

base de datos, el

usuario, la sesin, etc.


12/42

Archivo postgresql.conf max_connections: Nmero mximo de clientes

conectados a la vez a nuestras bases de datos.Deberamos de incrementar este valor en proporcinal numero de clientes concurrentes en nuestrainstancia de PostgreSQL. Un buen valor paraempezar es el 100

shared_buffers: Este parmetro es importantsimo ydefine el tamao del buffer de memoria utilizado porPostgreSQL. No por aumentar este valor muchotendremos mejor respuesta. En un servidor dedicadopodemos empezar con un 25% del total de nuestramemoria. Nunca mas de 1/3 (33%) del total. Porejemplo, en un servidor con 4Gbytes de memoria,podemos usar 1024MB como valor inicial.


13/42

Archivo postgresql.conf work_mem: Usada en operaciones que contengan

ORDER BY, DISTINCT, joins, .... En un servidor dedicado

podemos usar un 2-4% del total de nuestra memoria si

tenemos solamente unas pocas sesiones (clientes)

grandes. nunca mas de RAM/num.conexiones. Como valor

inicial podemos usar 8 Mbytes, para aplicaciones web, yhasta 128MB para una aplicacin de datawarehouse.

maintenance_work_mem: Usada en operaciones del tipoVACUUM, ANALYZE, CREATE INDEX, ALTER TABLE,

ADD FOREIGN KEY. Su valor depender mucho deltamao de nuestras bases de datos. Por ejemplo, en un

servidor con 4Gbytes de memoria, podemos usar 256MB

como valor inicial. La frmula es 1/16 de nuestra memoria

RAM.


14/42

Archivo postgresql.conf effective_cache_size: Parmetro usado por el 'query

planner' de nuestro motor de bases de datos paraoptimizar la lectura de datos. En un servidor dedicadopodemos empezar con un 50% del total de nuestramemoria. Como mximo unos 2/3 (66%) del total. Porejemplo, en un servidor con 4Gbytes de memoria,podemos usar 2048MB como valor inicial.

checkpoint_segments: Este parmetro es muyimportante en bases de datos con numerosasoperaciones de escritura (insert,update,delete). Para

empezar podemos empezar con un valor de 64. Engrandes bases de datos con muchos Gbytes de datosescritos podemos aumentar este valor hasta 128-256.


15/42

Archivo pg_hba.confEl archivopg_hba.conf controla

que y cmo los

usuarios pueden

conectarse a bases

de datos PostgreSQL.

Los cambios en el

pg_hba.confrequieren una recarga

o reiniciar el servidor

para tener efecto.


16/42

Archivo pg_hba.confEs importante poder definir desde qu equipos

se pueden conectar a nuestra base de datos,

as como poder definir qu usuarios y a qu

bases de datos se pueden conectar.

Se trata de editar una serie de reglas que se

irn procesando de arriba abajo, cuando seencuentre una regla que cumpla la conexin,

se ejecutar lo que ponga en el mtodo.


17/42

Archivo pg_hba.confHay cuatro formas generales de definir un

acceso autorizado:

TIPO BASE DATOS USUARIOS DIRECCIN MTODOLOCAL base_datos usuario mtodo-autenticacin

HOST base_datos usuario direccinCIDR mtodo-autenticacin

HOSTSSL base_datos usuario direccinCIDR mtodo-autenticacin

HOSTNOSSL base_datos usuario direccinCIDR mtodo-autenticacin


18/42

pg_hba.conf Columna Base de Datos

ALL: se permite la conexin a cualquier base de datos

SAMEUSER: solo a bases de datos que su nombre sea elmismo que el usuario que se conecta SAMEROLE: solo a bases de datos que su nombre sea el

mismo que el role que se conecta

nombd1, nombd2,: se permite la conexin a cualquierade las bases de datos de la lista. @fichero: se permite laconexin a las bases de datos incluidas en el fichero, quedebe estar en el mismo directorio que pg_hba.conf

TIPO BASE DATOS USUARIOS DIRECCIN MTODOHOST base_datos usuario direccinCIDR mtodo-autenticacin


19/42

pg_hba.conf Columna Usuario

ALL: se permite la conexin de cualquier role role1, [+]role2,: se permite la conexin de los

roles de la lista y adems se permite la conexin decualquier role que sea miembro de role2

@fichero: se permite la conexin de los roles

incluidos en el fichero, que debe estar en el mismodirectorio que pg_hba.conf



20/42

pg_hba.conf Columna Direccin

192.168.200.0/24 192.168.200.0255.255.255.0: se pueden conectar todas lasIPs de la red 192.168.200

192.168.0.0/16 192.168.0.0 255.255.0.0:todos las IPs de la red 192.168

192.168.200.85/32: solo esa IP 0.0.0.0/0 0.0.0.0 0.0.0.0.: cualquier IP



21/42

pg_hba.conf Columna Mtodo

TRUST: conexin aceptada sin condiciones

REJECT: conexin rechazada sin condiciones

PASSWORD: se solicita palabra de paso sin encriptar, laspalabras de paso se almacenan en la tablapg_authidy pueden

estar cifradas o no segn como se creara el role.

CRYPT: palabra de paso encriptada (versiones previas a la 7.2)

MD5: palabra de paso con el mtodo de encriptacin md5, y sealmacena tambin con este mtodo. Es el mtodo recomendadopor PostgreSQL.

KRB5: se usa Kerberos v5 para autenticar el cliente, se ha dehabilitar en la instalacin del servidor.



22/42

pg_hba.conf Ejemplo


23/42

Archivo pg_ident.confArchivo accesorio al pg_hba.conf, determinacomo se realiza la autenticacin identque

contiene la correspondencia entre usuarios

del Sistema Operativo y de PostgreSQL.A partir del usuario de la conexin cliente (se

fa de la autenticacin del cliente) y de la

correspondencia indicada en la opcin. Lascorrespondencias se obtienen del fichero

pg_ident.conf.


24/42

Los roles engloba el concepto de usuarios (rolesde login) y grupos de permisos (roles de grupo),

estos ltimos son lo mismo que los roles de

Oracle. Hasta la versin 8 de Postgres no se

podan anidar roles, ahora si. Por defecto, si al

crear una instancia de servidor no se ha indicado

otro usuario, se crea el usuario postgres como

superusuario.En PostgreSQL los usuarios son tipos de roles,

el role es el concepto general.

Configuracin de Grupos y Usuarios


25/42

Desde elsistemaoperativo:



26/42

Desdeuncliente

SQL:



27/42

Los roles de grupo son generalmente roles notienen derecho sesin (LOGIN), pero tienen

otras funciones como miembros. Ejemplo:

CREATE ROLE "3anhoing" NOSUPERUSER INHERITNOCREATEDB NOCREATEROLE NOREPLICATION;

CREATE ROLE jtorres LOGIN

ENCRYPTED PASSWORD'md531fdcdffbb341ac6775a2556dbc20321'SUPERUSER INHERIT NOCREATEDB

NOCREATEROLE NOREPLICATION;

GRANT "3anhoing" TO jtorres;



28/42

Creacin de tablespaces:

Si queremos especificar ubicacionesalternativas para determinadas bases de

datos o tablas, por ejemplo, queremos queciertas tablas estn en otros discos distintos

a los que se encuentran por defecto,debemos crear tablespaces.

Gestin de Base de Datos


29/42

Creacin de tablespaces:CREATE TABLESPACE tablespacename [ OWNERusername ] LOCATION 'directory'

tablespacename: no puede empezar en pg_porque est reservado a los tablespaces desistema.

username: debe ser un superusuario del cluster.

directory: se deben indicar trayectorias absolutas yel propietario del directorio debe ser el usuario conel que se ha instalado PostgreSQL.



30/42

Creacin de bases de datos:

Una base de datos se puede crear desde

la lnea de comandos del sistema

operativo (con el usuario de sistema) o

desde una conexin a una base de datos.

(con un usuario que tenga privilegiospara crear bases de datos).



31/42

Creacin de bases de datos desde elsistema operativo:



32/42

Creacin de bases de datos desde un clienteSQL:

connlimitmarca el nmero de conexionesconcurrentes que pueden haber a la base de datos,tiene por defecto el valor -1, que quiere decir queno hay lmite de conexiones.



33/42

Creacin de Esquemas:Son contenedores lgicos de objetos de basede datos (tablas, vistas, procedimientos, etc.),bsicamente son un espacio de nombres. Es

caracterstico de los esquemas:

Tienen un propietario.

Permiten el uso de la base de datos por

mltiples usuarios sin interferencias. Permiten que se puedan instalar aplicaciones

realizadas por terceros sin que existancolisiones en los nombres de los objetos.



34/42

Creacin de Esquemas:

Para poder ver objetos de un esquema,

debe usarse la notacin esquema.objeto

ya que en PostgreSQL no existe el

concepto de sinnimos como en Oracle.



35/42

Copias de seguridad:Las copias de seguridad son esenciales para las

recuperaciones frente a fallos, se deben determinarlas frecuencia conveniente, que ser funcin del

tamao de los datos y de la frecuencia con que sonmodificados, lo cual marcar la estrategia de copias

de seguridad.

Los volcados SQL son muy flexibles y de granutilidad, nos permitirn hacer copias de seguridad de

toda la base de datos o de partes de ella, y luego,nos permitirn restaurar lo que queramos.



36/42

Copias de seguridad: Las herramientas son: pg_dump: vuelca una base de datos o parte de ella a

un fichero, bien en texto plano o en un formato propiode PostgreSQL. Se puede recuperar cualquier objeto

que est en el fichero aisladamente. El servidor debeestar en marcha.

pg_dumpall: vuelca el servidor completo. pg_restore: recupera los objetos volcados en una

copia de seguridad que no se realizarn en texto planosino en un formato propio de PostgreSQL.

psql: se usa para recuperar los volcados en textoplano.



37/42

pg_dump:Con este comando, podemos volcar una basede datos o parte de ella a un fichero script entexto plano o en un formato propio de

PostgreSQL.

Si lo hacemos en un fichero de texto plano:

tendremos un fichero de texto coninstrucciones SQL

podemos usarpsqlpara restaurar

es portable a servidores SQL



38/42

pg_dump:

Si lo hacemos en un formato propio dePostgreSQL:

debemos usarpg_restore para restaurar es ms flexible

slo es portable entre servidores PostgreSQL.

El pg_dump se debe realizar con el servidor enmarcha, pero no bloquea la base de datos. Es unsistema anlogo al exportde Oracle.



39/42

pg_dump:


_

_ _

_


40/42

pg_dumpall:Conpg_dumpallse realizan volcados del

servidor completo, incluyendo roles de

grupo y roles de login.



41/42

Recuperacin con psql:

La recuperacin con psql se puede hacer

cuando los ficheros de volcado son texto planocon sentencias SQL. Hacemos uso de la

posibilidad que tiene psql de ejecutar cdigo quele viene redireccionado de entrada desde un

fichero.

psql [bd_destino] < fichero.sql



42/42

Recuperacin con pg_restore:

Con pg_restore se pueden restaurar a partir de

ficheros en formato distinto a texto plano (tar ouno propio de PostgreSQL comprimido).

pg_restore --dbname=postgres --create --jobs=4 --verbose mydb.backup


2. administración de postgresql

Documents