¿Hay vida más allá de Oracle? Características de Postgres SQL para un DBA de Oracle

Esta entrada en el blog contiene información para ayudar a un DBA de Oracle a entender algunos términos y la administración de una base de datos PostgreSQL. Pretendo dar una introducción a PostgreSQL, no un tutorial o una definición completa de cómo administrar una base de datos PostgreSQL. Para obtener la documentación completa, consulte los manuales de PostgreSQL.

¿Que es una base de datos Oracle?

Un servidor de base de datos Oracle consiste en una instancia de Oracle y una base de datos Oracle.
Una instancia de Oracle consiste en los procesos en segundo plano de Oracle y la memoria asignada dentro del área global compartida (SGA) y el área global del programa (PGA).
Los procesos en segundo plano de Oracle constan de lo siguiente:

• Proceso del escritor de la base de datos (DBWn)
• Proceso de registro de registro (LGWR)
• Proceso de punto de control (CKPT)
• Proceso de supervisión del sistema (SMON)
• Process Monitor Process (PMON)
• Proceso de Recuperación (RECO)
• Archivar procesos (ARCn)
• Una base de datos Oracle consiste en los archivos de datos de la base de datos, los archivos de control, los archivos de registro de rehacer, los archivos de registro de archivos y el archivo de parámetros. Para acceder de forma remota a una base de datos Oracle, existe un proceso separado denominado Listener Oracle.
• En la configuración del Servidor Dedicado (frente a la configuración del Servidor Compartido), cada sesión de base de datos establecida tiene su propio proceso ejecutándose en el servidor.

Para mantener las cosas simples, cualquier comparación con una base de datos Oracle siempre se referirá a una sola instancia que administre una sola base de datos, RAC y Data Guard no serán mencionados. 

Nota: PostgreSQL también tiene el concepto de un modo de espera en caliente (desde 8.2) con el envío de registros de archivo (introducido en 8.0).

PostgreSQL

Procesos de servidor de base de datos

El programa de servidor de base de datos postgres son todos los procesos del servidor. No hay procesos nombrados por separado como en Oracle para las diferentes tareas dentro del entorno de la base de datos. Si mirara la lista de procesos (ps), el nombre de los procesos sería postgres. Sin embargo, en la mayoría de las plataformas, PostgreSQL modifica su título de comando para que los procesos individuales del servidor puedan identificarse fácilmente. Es posible que deba ajustar los parámetros utilizados para comandos como ps y top para mostrar estos títulos actualizados en lugar del nombre del proceso ("postgres").

Los procesos vistos en una lista de procesos pueden ser algunos de los siguientes:

• Proceso maestro: inicia los otros procesos, procesos de fondo y sesión.
• Proceso de Writer: proceso en segundo plano que coordina las escrituras de la base de datos, las escrituras de registro y los puntos de control.
• Proceso de recopilación de estadísticas: proceso en segundo plano que recopila información sobre la actividad del servidor.
• Procesos de sesión de usuario.

Los procesos del servidor se comunican entre sí utilizando semáforos y memoria compartida para garantizar la integridad de los datos a través del acceso simultáneo a los datos.

Clúster de base de datos PostgreSQL

Dentro de un servidor, se pueden construir una o más instancias de Oracle. Las bases de datos están separadas una de otra, por lo general compartiendo solo el proceso de escucha de Oracle. PostgreSQL tiene el concepto de un cluster de base de datos. Un clúster de base de datos es una colección de bases de datos que se almacena en una ubicación común del sistema de archivos (el "área de datos"). Es posible tener varios clústeres de bases de datos, siempre que utilicen diferentes áreas de datos y diferentes puertos de comunicación.
Los procesos junto con los componentes del sistema de archivos se comparten en el clúster de la base de datos. Todos los datos necesarios para un clúster de base de datos se almacenan dentro del directorio de datos del clúster, comúnmente denominado PGDATA (después del nombre de la variable de entorno que se puede usar para definirlo). El directorio PGDATA contiene varios subdirectorios y archivos de configuración.
Los siguientes son algunos de los archivos de configuración del clúster:

• postgresql.conf - Parámetro o archivo de configuración del servidor principal.
• pg_hba.conf - Archivo de configuración de autenticación de cliente.
• pg_ident.conf - Mapa de la cuenta del sistema operativo al archivo de la cuenta PostgreSQL.

Los subdirectorios de clúster:

• base - Subdirectorio que contiene subdirectorios por base de datos
• global - Subdirectorio que contiene tablas de todo el cluster
• pg_auth - Archivo de autorización que contiene definiciones de usuario y rol.
• pg_control - Archivo de control.
• pg_database - Información de bases de datos dentro del clúster.
• pg_clog - Subdirectorio que contiene datos de estado de confirmación de transacción
• pg_multixact - Subdirectorio que contiene datos de estado de múltiples movimientos (usado para bloqueos de filas compartidos)
• pg_subtrans - Subdirectorio que contiene datos de estado de subtransacción
• pg_tblspc - Subdirectorio que contiene enlaces simbólicos a espacios de tabla
• pg_twophase - Subdirectorio que contiene archivos de estado para transacciones preparadas
• pg_xlog - Subdirectorio que contiene archivos WAL (Write Ahead Log)

De forma predeterminada, para cada base de datos en el clúster hay un subdirectorio dentro de PGDATA / base, que recibe el nombre del OID de la base de datos (identificador de objeto) en pg_database. Este subdirectorio es la ubicación predeterminada para los archivos de la base de datos; en particular, sus catálogos de sistema están almacenados allí. Cada tabla e índice se almacenan en un archivo separado, que recibe su nombre del número de archivo de la tabla o índice, que se puede encontrar en pg_class.relfilenode.

Varios componentes que los DBA de Oracle normalmente igualan a una base de datos se comparten entre bases de datos dentro de un cluster de PostgreSQL, incluido el archivo de parámetros, el archivo de control, los registros de rehacer, los espacios de tabla, las cuentas, los roles y los procesos en segundo plano.

Archivos de datos de espacios de tablas y objetos

PostgreSQL introdujo la administración del tablespace en la versión 8.0. La representación física de un espacio de tabla dentro de PostgreSQL es simple: es un directorio en el sistema de archivos, y la asignación se realiza a través de enlaces simbólicos.
Cuando se crea una base de datos, el espacio de tablas predeterminado es donde se almacenan por defecto todos los objetos de la base de datos. En Oracle esto sería similar al Sistema, al Usuario y a los espacios de tablas temporales. Si no se define ningún espacio de tabla predeterminado durante la creación, los archivos de datos entrarán en un subdirectorio de PGDATA / base. Preferiblemente, la ubicación de la información del catálogo del sistema y las estructuras de datos de la aplicación residirían en espacios de tablas gestionados por separado. Esto está disponible.
Al igual que en Oracle, la definición de una tabla PostgreSQL determina qué espacio de tablas reside el objeto. Sin embargo, no existe limitación de tamaño, excepto los límites físicos que el sistema operativo coloca en el dispositivo. Los datos de la tabla individual se almacenan dentro de un archivo dentro del tablespace (o directorio). El software de la base de datos dividirá la tabla entre múltiples archivos de datos en caso de que los datos de la tabla superen 1 GB.

Desde la versión 8.1, es posible dividir una tabla en espacios de tabla separados (o lo mismo). Esto se basa en la característica de herencia de tablas de PostgreSQL, que utiliza una capacidad del planificador de consultas denominada exclusión de restricciones.

No existe capacidad para separar columnas específicas (como LOB) en espacios de tabla definidos por separado. Sin embargo, además de los archivos de datos que representan la tabla (en múltiplos de 1 GB), hay una separación de archivos de datos para columnas dentro de una tabla que están TOAST. El sistema de almacenamiento PostgreSQL llamado TOAST (la técnica de almacenamiento de atributo de gran tamaño) almacena automáticamente valores mayores que una página de base de datos única en un área de almacenamiento secundaria por tabla. La técnica TOAST permite columnas de datos de hasta 1 GB de tamaño.

Al igual que en Oracle, la definición de índice determina en qué espacio de tabla reside. Por lo tanto, es posible obtener la ventaja de rendimiento de separar los discos de los datos de una tabla frente a su indexación, aliviando la contención de E / S durante la manipulación de datos.
En Oracle existen espacios de tablas temporales donde se utiliza la información de clasificación y el espacio de evaluación temporal necesarios para declaraciones distintas y similares. PostgreSQL no tiene este concepto de un tablespace temporal; sin embargo, requiere almacenamiento para poder realizar estas actividades también. Dentro del espacio de tabla "predeterminado" de la base de datos (definido en la creación de la base de datos), hay un directorio llamado pgsql_tmp. Este directorio contiene el almacenamiento temporal necesario para la evaluación. Los archivos que se crean dentro del directorio existen solo mientras se está ejecutando la instrucción SQL. Crecen muy rápido, y lo más probable es que no estén diseñados para la eficiencia del espacio sino más bien para la velocidad. Tenga en cuenta que la fragmentación del disco podría ser el resultado de esto, y debe haber suficiente espacio en el disco para admitir las consultas del usuario. Con el lanzamiento de 8.3, hay definiciones de espacios de tablas temporales usando el parámetro temp_tablespaces.roles y los procesos en segundo plano.

REDO y Archivado

PostgreSQL usa Write-Ahead Logging (WAL) como su enfoque para el registro de transacciones. El concepto central de WAL es que los cambios en los archivos de datos (donde residen las tablas y los índices) deben escribirse solo después de que esos cambios hayan sido registrados, es decir, cuando los registros de registro que describen los cambios hayan sido descargados al almacenamiento permanente. Si seguimos este procedimiento, no es necesario que limpiemos las páginas de datos en el disco en cada compromiso de transacción, porque sabemos que, en caso de falla, podremos recuperar la base de datos utilizando el registro: cualquier cambio que no se haya aplicado a las páginas de datos se pueden rehacer desde los registros de registro. (Esta es la recuperación de avance, también conocida como REDO).

PostgreSQL mantiene su (WAL) en el subdirectorio pg_xlog del directorio de datos del cluster.

WAL se introdujo en PostgreSQL en la versión 7.1. Para mantener la consistencia de la base de datos en caso de falla, las versiones anteriores forzaron todas las modificaciones de datos en el disco antes de que cada transacción se confirmara. Con WAL, solo debe descargarse un archivo de registro al disco, lo que mejora enormemente el rendimiento al tiempo que agrega capacidades como la Recuperación puntual y el archivo de transacciones.

Un sistema PostgreSQL teóricamente produce una secuencia indefinidamente larga de registros WAL. El sistema divide físicamente esta secuencia en archivos de segmentos WAL, que normalmente son de 16 MB cada uno. El sistema normalmente crea algunos archivos de segmento y luego los "recicla" al cambiar el nombre de los archivos de segmento que ya no se necesitan a números de segmento más altos. Si tuviera que realizar una lista del directorio pg_xlog, siempre habría un puñado de archivos cambiando de nombre a lo largo del tiempo.

Para agregar el archivo de los archivos WAL, existe un parámetro dentro del archivo de parámetros donde se agrega un comando para ejecutar el proceso de archivo. Una vez hecho esto, las copias de seguridad del sistema operativo "en línea" están disponibles ejecutando los comandos pg_start_backup y pg_stop_backup, que suspenden y reanudan la escritura en los archivos de datos mientras continúan escribiendo las transacciones en los archivos WAL y ejecutando el proceso de archivo.
La inclusión del archivo WAL y los comandos de respaldo en línea se agregaron en la versión 8.0.

Rollback o Undo

Es interesante cómo se utiliza la asignación dinámica de espacio en disco para el almacenamiento y procesamiento de registros dentro de las tablas. Los archivos que representan la tabla crecen a medida que la tabla crece. También crece con las transacciones que se realizan en su contra. En Oracle existe un concepto de rollback o deshacer segmentos que contienen la información para revertir una transacción. En PostgreSQL, los datos se almacenan dentro del archivo que representa la tabla. Por lo tanto, cuando se realizan eliminaciones y actualizaciones en una tabla, el archivo que representa el objeto contendrá los datos anteriores. Este espacio se reutiliza pero para forzar la recuperación del espacio usado, se debe ejecutar un proceso de mantenimiento llamado vacío.

Archivo de registro del servidor

Oracle tiene el archivo de registro de alerta. PostgreSQL tiene el archivo de registro del servidor. Una opción de configuración incluso tendría la información de conexión que normalmente vemos en listener.log de Oracle aparece en el registro del servidor de PostgreSQL. Los parámetros dentro del archivo de configuración del servidor (postgresql.conf) determinan el nivel, la ubicación y el nombre del archivo de registro.
Para ayudar con el mantenimiento del archivo de registro del servidor (crece rápidamente), existe una funcionalidad para girar el archivo de registro del servidor. Los parámetros se pueden configurar para determinar cuándo rotar el archivo según el tamaño o la antigüedad del archivo. La administración de los archivos antiguos se deja al administrador.

Aplicaciones

El comando initdb crea un nuevo clúster de base de datos PostgreSQL.
El comando psql inicia el front-end basado en terminal a la solicitud de comando de PostgreSQL o SQL. Las consultas y los comandos se pueden ejecutar de forma interactiva o mediante archivos. El símbolo del sistema psql tiene varias características atractivas:

• Completa ayuda en línea para los comandos psql y la sintaxis SQL.
• Historial de comandos y edición de línea.
• Los comandos SQL pueden existir en múltiples líneas y se ejecutan solo después del punto y coma (;).
• Se pueden ingresar varios comandos SQL separados por punto y coma en una sola línea.
• Formato de salida flexible.
• Múltiples comandos de descripción de objetos que son superiores a DESCRIBE de Oracle.

Dependiendo de las configuraciones de seguridad de los entornos, las conexiones se pueden establecer local o remotamente a través de TCP / IP. Debido a estas conexiones de seguridad separadas, las contraseñas pueden o no ser necesarias para conectarse.
El comando pg_ctl es una utilidad para mostrar el estado, iniciar, detener o reiniciar el servidor de base de datos PostgreSQL (postgres). Aunque el servidor se puede iniciar a través del ejecutable de postgres, pg_ctl encapsula tareas tales como redirigir la salida del registro, separarla adecuadamente del terminal y del grupo de procesos, y proporcionar opciones para el apagado controlado.

Los comandos pg_dump y pg_restore son utilidades diseñadas para exportar e importar los contenidos de una base de datos PostgreSQL. Los volcados pueden salir en formato de archivo de script o de archivo. El formato de archivo de script crea archivos de texto sin formato que contienen los comandos SQL necesarios para reconstruir la base de datos al estado en el que se encontraba en el momento en que se generó. El formato de archivo de archivo crea un archivo para ser utilizado con pg_restore para reconstruir la base de datos.
Los formatos de archivos de almacenamiento están diseñados para ser portátiles en todas las arquitecturas. Históricamente, cualquier tipo de actualización al software PostgreSQL requeriría un pg_dump de la base de datos antes de la actualización. Luego, un pg_restore después de la actualización. Ahora, para versiones menores (es decir, el tercer decimal - 8.2.x) las actualizaciones se pueden hacer en su lugar. Sin embargo, cambiar las versiones en el primer o segundo decimal aún requiere un pg_dump / pg_restore.

Existe una herramienta gráfica llamada pgAdmin III desarrollada por separado. Se distribuye con las versiones de Linux y Windows de PostgreSQL. La conexión a un servidor de base de datos se puede establecer de forma remota para realizar tareas administrativas. Debido a que la herramienta está diseñada para administrar todos los aspectos del entorno de la base de datos, la conexión a la base de datos debe realizarse a través de una cuenta de súper usuario.
La herramienta pgAdmin III tiene las siguientes características atractivas estándar:

• Diseño intuitivo
• Estructura de árbol para crear y modificar objetos de base de datos 
• Revisión y almacenamiento de SQL al modificar o crear objetos