En este artículo instalaremos PostgreSQL 11 en Linux CentOS 7, realice la configuración básica, considere los parámetros del archivo de configuración principal y los métodos de ajuste del rendimiento. PostgreSQL es un popular sistema gratuito de administración de bases de datos relacionales de objetos. Aunque es menos popular que MySQL / MariaDB, es el más profesional.
Puntos fuertes de PostgreSQL:
- Cumplimiento total de los estándares SQL;
- Alto rendimiento debido al control de concurrencia multiversion (MVCC);
- Escalabilidad (ampliamente utilizado en entornos de alta carga);
- Soporte de múltiples lenguajes de programación;
- Mecanismos resistentes de transacción y replicación;
- Soporte de datos JSON.
¿Cómo instalar PostgreSQL en CentOS / RHEL?
Aunque PostgreSQL se puede instalar desde el repositorio base de CentOS, instalaremos el repositorio de desarrolladores ya que siempre puede encontrar la versión actual del paquete allí.
En primer lugar, agregue el repositorio de PosgreSQL:
# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Este repositorio contiene las versiones más recientes y anteriores de PosgreSQL. La información sobre el repositorio se ve así:
Instalemos PostrgeSQL 11 usando yum.
# yum install postgresql11-server -y
Se instalarán el servidor PostgreSQL y las bibliotecas necesarias:
Installing : libicu-50.2-3.el7.x86_64 1/4 Installing : postgresql11-libs-11.5-1PGDG.rhel7.x86_64 2/4 Installing : postgresql11-11.5-1PGDG.rhel7.x86_64 3/4 Installing : postgresql11-server-11.5-1PGDG.rhel7.x86_64 4/4
Una vez instalados los paquetes, deberá inicializar la base de datos:
# /usr/pgsql-11/bin/postgresql-11-setup initdb
Además, habilite el demonio de PostgreSQL y agréguelo para que se inicie automáticamente usando systemctl:
# systemctl enable postgresql-11
# systemctl start postgresql-11
Verifique el estado del servicio:
# systemctl status postgresql-11
● postgresql-11.service - PostgreSQL 11 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2020-10-18 16:02:15 +06; 26s ago
Docs: https://www.postgresql.org/docs/11/static/
Process: 8714 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 8719 (postmaster)
CGroup: /system.slice/postgresql-11.service
├─8719 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
├─8721 postgres: logger
├─8723 postgres: checkpointer
├─8724 postgres: background writer
├─8725 postgres: walwriter
├─8726 postgres: autovacuum launcher
├─8727 postgres: stats collector
└─8728 postgres: logical replication launcher
Oct 18 16:02:16 host1.woshub.com systemd[1]: Starting PostgreSQL 11 database server...
Si desea acceder a PostgreSQL desde el exterior, abra el puerto TCP 5432 en el Firewalld predeterminado de CentOS:# firewall-cmd --get-active-zones
public interfaces: eth0
# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload
O con iptables:
# iptables-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
# service iptables restart
Si SELinux está habilitado, ejecute el siguiente comando:
# setsebool -P httpd_can_network_connect_db 1
Uso de PSQL para crear bases de datos, usuarios y otorgar permisos en PostgreSQL
De forma predeterminada, cuando instala PostgreSQL, existe el único usuario en el sistema: postgres. No recomiendo usar esta cuenta para el trabajo diario con bases de datos. Es mejor crear un usuario separado para cada base de datos.
Para conectarse a un servidor de Postgres, ejecute este comando:
# sudo -u postgres psql
psql (11.5) Type "help" for help.
postgres=#
Aparece la consola de PostgreSQL. Luego mostraremos algunos ejemplos simples de administración de PostgreSQL desde la consola psql.
Cambie la contraseña de usuario de postgres predeterminada:
ALTER ROLE postgres WITH PASSWORD 's3tPa$$w0rd!';
Cree una nueva base de datos y un usuario, y otorgue al usuario acceso completo a la nueva base de datos:
postgres=# CREATE DATABASE newdbtest;
postgres=# CREATE USER mydbuser WITH password '!123456789';
postgres=# GRANT ALL PRIVILEGES ON DATABASE newdbtest TO mydbuser;
Para conectarse a la base de datos:
postgres=# c databasename
Para mostrar la lista de tablas:
postgres=# dt
Para mostrar la lista de conexiones a la base de datos:
postgres=# select * from pg_stat_activity where datname="dbname"
Para restablecer todas las conexiones a la base de datos:
postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname="dbname"
Para obtener la información sobre la sesión actual:
postgres=# conninfo
Para salir de la consola psql, ejecute este comando:
postgres=# q
Como habrá notado, la sintaxis es similar a MariaDB o MySQL.
Notaremos que para administrar bases de datos PostgreSQL desde una interfaz web de manera más conveniente, se recomienda usar pgAdmin4 (escrito en Python y Javascript / jQuery). Es similar a PhpMyAdmin con el que muchos desarrolladores web están familiarizados.
Establecimiento de los parámetros de configuración principales de PostgreSQL
Los archivos de configuración de Postgresql se encuentran en /var / lib / pgsql / 11 / data:
- postgresql.conf – archivo de configuración de postgresql;
- pg_hba.conf – un archivo que contiene la configuración de acceso. En este archivo, puede establecer diferentes restricciones para sus usuarios o establecer una política de conexión a la base de datos;
- pg_ident.conf – este archivo se utiliza para identificar clientes sobre el protocolo de identificación.
Para evitar que los usuarios locales inicien sesión en postgres sin autorización, especifique lo siguiente en su pg_hba.conf:
local all all md5 host all all 127.0.0.1/32 md5
Consideremos los parámetros más importantes en postgresql.conf:
listen_addresses: Establece las direcciones IP a las que un servidor aceptará conexiones de cliente. El valor predeterminado es localhost, lo que significa que solo es posible una conexión local. Para escuchar todas las interfaces IPv4, especifique 0.0.0.0 aquí;max_connections–El número máximo de conexiones simultáneas a un servidor de base de datos;temp_buffers– el tamaño máximo de los búferes temporales;shared_buffers– el tamaño de la memoria compartida utilizada por un servidor de base de datos. Normalmente, se establece un valor del 25% de la RAM total del servidor;effective_cache_size– un parámetro que permite al programador de Postgres determinar la cantidad de memoria disponible para almacenar en caché en la unidad local. Por lo general, se establece en el 50-75% de la RAM total en el servidor;work_mem– el tamaño de la memoria que utilizarán las operaciones de clasificación interna del sistema de gestión de la base de datos – ORDER BY, DISTINCT y merging;maintenance_work_mem– el tamaño de la memoria que utilizarán las operaciones internas – VACÍO, CREAR ÍNDICE y ALTERAR TABLA AÑADIR LLAVE EXTRANJERA;fsync– si este parámetro está habilitado, el DBMS esperará la escritura física de datos en un disco duro. Si fsync está habilitado, será más fácil para usted recuperar su base de datos después de una falla del sistema o del hardware. Obviamente, si este parámetro está habilitado, el sistema de administración de la base de datos tendrá un rendimiento menor, pero una mayor confiabilidad de almacenamiento. Si lo deshabilita, también vale la pena deshabilitar full_page_writes;max_stack_depth– el tamaño máximo de pila (2 MB por defecto);max_fsm_pages– con este parámetro, puede administrar el espacio libre en disco en el servidor. Por ejemplo, después de eliminar algunos datos de una tabla, el espacio ocupado anteriormente no se libera, sino que se marca como libre en el mapa de espacio libre y se usa para nuevas entradas. Si escribe / elimina con frecuencia datos en las tablas de su servidor, el rendimiento aumentará si establece un valor mayor de este parámetro;wal_buffers– el tamaño de la memoria compartida (shared_buffers) utilizado para mantener los datos de WAL;wal_writer_delay– tiempo entre los períodos consecutivos de escritura de WAL en un disco;commit_delay– la demora entre la escritura de una transacción en el búfer WAL y su escritura directa en un disco;synchronous_commit– el parámetro establece que el resultado de la transacción exitosa se enviará después de que los datos WAL se hayan escrito físicamente en un disco.
Copia de seguridad y restauración de la base de datos PostgreSQL
Puede hacer una copia de seguridad de la base de datos PostgreSQL de varias formas. Consideremos el más fácil.
En primer lugar, compruebe qué bases de datos se están ejecutando en su servidor:
postgres=# list
Tenemos 4 bases de datos, 3 de ellas son del sistema (postgres y plantilla).
Anteriormente creamos una base de datos con el nombre mydbtest y ahora lo respaldaremos.
Puede hacer una copia de seguridad de su base de datos PostgreSQL usando el pg_dump herramienta:
# sudo -u postgres pg_dump mydbtest > /root/dupm.sql –
Ejecute este comando con el usuario de postgres, especifique una base de datos y una ruta al archivo en el que guardará el volcado de la base de datos. Su sistema de respaldo puede tomar el volcado de la base de datos, o puede enviarlo a su cuenta de almacenamiento en la nube conectada en caso de utilizar un servidor web.
Para restaurar el volcado a la base de datos, use el psql:
# sudo -u postgres psql mydbtest < /root/dupm.sql
También puede crear una copia de seguridad en un formato de volcado especial y comprimirla usando gzip:
# sudo -u postgres pg_dump -Fc mydbtest > /root/dumptest.sql
Luego, el volcado se recupera con la herramienta pg_restore:
# sudo -u postgres pg_restore -d mydbtest /root/dumptest.sql
Optimización y ajuste del rendimiento de PostgreSQL
En el artículo anterior relacionado con MariaDB, mostramos cómo optimizar los parámetros del archivo de configuración my.cnf usando sintonizadores. PostgreSQL tenía PgTun para eso, pero desafortunadamente no se había actualizado durante mucho tiempo. Al mismo tiempo, hay muchos servicios en línea que puede utilizar para optimizar su configuración de PostgreSQL. Me gusta PGTunepgtune.leopard.in.ua).
La interfaz es muy sencilla. Solo necesita especificar los parámetros de su servidor (perfil, procesadores, memoria, tipo de disco) y hacer clic en «Generar». Se le ofrecerá una variante de postgresql.conf que contiene los valores recomendados de los parámetros principales de PostgreSQL.
Por ejemplo, se recomiendan las siguientes configuraciones de postgresql.conf para un servidor VPS SSD con 4xGB RAM y 4xvCPU:
# DB Version: 11 # OS Type: linux # DB Type: web # Total Memory (RAM): 4 GB # CPUs num: 4 # Connections num: 100 # Data Storage: ssd max_connections = 100 shared_buffers = 1GB effective_cache_size = 3GB maintenance_work_mem = 256MB checkpoint_completion_target = 0.7 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1.1 effective_io_concurrency = 200 work_mem = 5242kB min_wal_size = 1GB max_wal_size = 4GB max_worker_processes = 4 max_parallel_workers_per_gather = 2 max_parallel_workers = 4 max_parallel_maintenance_workers = 2
En realidad, no es el único recurso en el momento en que se escribió el artículo. También hay servicios similares disponibles:
- Configurador Cybertec PostgreSQL
- Herramienta de configuración de PostgreSQL
Con estos servicios, puede configurar rápidamente los parámetros básicos de PostgreSQL para su hardware y tareas. Más adelante, no solo podrá considerar los recursos de su servidor, sino también analizar el funcionamiento de su base de datos, su tamaño, la cantidad de conexiones y ajustar sus parámetros de PostgreSQL en función de esta información.
