PostgreSQL

Jiyoung Joung, niko d, Héctor H. Louzao P Versión F37 and newer Last review: 2022-12-19

Los Usuarios de Fedora Server Edition encuentran información adicional en Configuración del Servidor de Base de Datos PostgreSQL

Instalación

La instalación e inicialización del servidor postgresql es un poco diferente en comparación con otros paquetes y distribuciones Linux. Este documento tiene como objetivo resumir los pasos básicos de instalación relevantes para las versiones recientes de Fedora Linux.

$ sudo dnf install postgresql-server postgresql-contrib

El servidor postgresql no se está ejecutando y está deshabilitado de forma predeterminada. Para configurarlo para que se inicie en el arranque, ejecute:

$ sudo systemctl enable postgresql

La base de datos debe completarse con datos iniciales después de la instalación. La inicialización de la base de datos podría ser hecha utilizando el siguiente comando. Crea los archivos de configuración postgresql.conf y pg_hba.conf

$ sudo postgresql-setup --initdb --unit postgresql

Para iniciar el servidor postgresql manualmente, ejecute

$ sudo systemctl start postgresql

Creación de Usuario y Creación de Base de Datos

Ahora usted necesita crear un usuario y una base de datos para el usuario. Esto necesita ser ejecutado desde una cuenta de usuario postgres en su sistema.

$ sudo -u postgres psql

Desde aquí usted puede crear un usuario postgres y una base de datos. Aquí, asumiremos que su cuenta de usuario en su ordenador se llama lenny. Nota: usted también puede ejecutar estos desde la shell con createuser lenny y createdb --owner=lenny carl.

postgres=# CREATE USER lenny WITH PASSWORD 'leonard';
postgres=# CREATE DATABASE my_project OWNER lenny;

Puede ser una buena idea añadir una contraseña para el usuario postgres mientras está en ello:

postgres=# \password postgres

Pulse Ctrl + D o \q para dejar la sesión psql corriendo como usuario postgres. Ahora puede acceder a su nueva base de datos desde su cuenta de usuario (lenny) y empezar a usarla.

$ psql my_project

Configuración Inicial

El servidor postgresql está usando dos archivos de configuración principales

  • /var/lib/pgsql/data/postgresql.conf

  • /var/lib/pgsql/data/pg_hba.conf

If you’re getting ident errors from your app you’ll probably need to perform the accepted solution described at https://serverfault.com/questions/406606/postgres-error-message-fatal-ident-authentication-failed-for-user?newreg=a4fdc3e21349449985cc65b82399c5b4

$ sudo gedit /var/lib/pgsql/data/pg_hba.conf

y edite host all all 127.0.0.1/32 ident a host all all 127.0.0.1/32 md5 y host all all ::1/128 ident a host all all ::1/128 md5. Una vez que haya hecho este cambio, asegúrese que reiniciar el servicio postgresql para los cambios tomen efecto.

$ sudo systemctl restart postgresql

Estos cambios concederían que la mayoría de las aplicaciones se conecten con nombre-usuario/contraseña.

Actualizar

Como puede ver por el mensaje de error en mi ejemplo, no es una instalación nueva, sino una mejora.

Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir[2108]: An old version of the database format was found.
Nov 14 11:45:56 mlich-lenovo.usersys.redhat.com postgresql-check-db-dir[2108]: Use "postgresql-setup upgrade" to upgrade to version 9.3.

Con la versión 9 usted puede usar la herramienta upgrade. Está empaquetada como postgresql-upgrade:

$ sudo postgresql-setup upgrade

Redirecting to /bin/systemctl stop  postgresql.service
Upgrading database: OK

The configuration files was replaced by default configuration.
The previous configuration and data are stored in folder /var/lib/pgsql/data-old.

See /var/lib/pgsql/pgupgrade.log for details.

Los datos se ubican en

  • /var/lib/pgsql/data

  • /var/lib/pgsql/data-old

La misma mejora respaldará sus datos existentes y migrará su base de datos. No olvide migrar su configuración (con meld, por ejemplo: meld /var/lib/pgsql/data{,-old}/postgresql.conf).

Puede necesitar cambiar postgresql a modo de confianza antes de la mejora. Esto debería estar corregido ya.

También puede mejorar volcando su base de datos y cargándola otra vez. Para más información, vea la documentación oficial.

Versión mayor omitida

En Fedora 43 el paquete Postgres provienen de 16 a 18. La mejora de la base de datos no puede omitir una versión mayor y el procedimiento mayor no funcionará. A no ser que ya ha mejorados a Postgres 17 en Fedora 42, tendrá que mejorar desde 16 a 18 en 2 pasos en Fedora 43.

Primero tienes que desinstalar Postgres 18 e instalar Postgres 17. Puedes verificar cuales paquetes están instalados con rpm -qa |grep ^postgres. Antes que instales Postgres 17, respalda tu directorio de dato. Incluso aunque un respaldo es parte del procedimiento de mejora, es mejor estar seguro que sintiéndolo. Después de instalar el paquete Postgres 17 puedes utilizar el proceso de mejora de arriba para hacer la mejora actual desde 16 a 17. Entonces tienes que desinstalar Postgres 17 e instalar Postgres 18 otra vez. Ejecute el procedimiento de mejora una vez más y está bien para ir.

Durante estos 2 pasos del procedimiento quizá encuentra unos pocos obstáculos, los cuáles son explicados aquí:

En caso que vea el error old cluster does not use data checksums but the new one does puedes escoger o bien crear el checksums o bien mejorar con checksums inhabilitado. Para crear el checksums, vaya al intérprete de consola del usuario postgres y ejecute el comando siguiente: pg_checksums -e -D <data-dir>. Aun así, cuando vea este error, ya ha actualizado los paquetes a una versión más moderna mientras la base de datos está todavía en la anterior. Por ello sería más fácil una mejora con PGSETUP_INITDB_OPTIONS="--no-data-checksums" postgresql-upgrade <data-dir> y crear el checksums después. A no ser que cambiaste el directorio de datos de Postgres <data-dir> es /var/lib/pgsql/data.

No olvide ajustar y consolidar sus archivos de configuración (*.conf) desde los directorios de datos antiguos y nuevos así como actualizar la intercalación para todas las bases de datos.

Actualice el resumen (como usuario postgres): ALTER DATABASE <dbname> REFRESH COLLATION VERSION; \c <dbname>; REINDEX DATABASE;

Cortafuegos

PostgreSQL operates on port 5432 (or whatever else you set in your postgresql.conf). In firewalld, to make the change persist after reboot:

$ sudo firewall-cmd --permanent --add-port=5432/tcp

To apply the change to the running firewall immediately:

$ sudo firewall-cmd --add-port=5432/tcp

En el caso de las iptables:

$ sudo iptables -A INPUT -p tcp --dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

Tenga en cuenta que usted probablemente no desea abrir su servidor de base de datos a todo el mundo.

SELinux

Si usted tiene SELinux aplicado, puede entrar en problemas cuando intente hacer alguna configuración que no sea estándar. Por ejemplo, si usted quisiera cambiar una ubicación de su base de datos, debe agregar un nuevo contexto de mapeo para la nueva ubicación:

$ sudo semanage fcontext -a -t postgresql_db_t "/my/new/location(/.*)?"

Si el puerto predeterminado no trabaja para usted, es posible que necesite asignar el tipo de puerto de postgre al puerto deseado:

$ sudo semanage port -a -t postgresql_port_t -p tcp 5433

Si usted instala una aplicación web que desea comunicar con PostgreSQL por medio de TCP/IP, le tendrá que decir a SELinux que permita esto en el huésped del servidor web:

$ sudo setsebool -P httpd_can_network_connect_db on

Configuración

Como se mencionó anteriormente, el servidor postgresql está usando dos archivos de configuración principales

  • /var/lib/pgsql/data/postgresql.conf

  • /var/lib/pgsql/data/pg_hba.conf

systemd

Algunos parámetros de configuración se pasan al demonio por medio de opciones de la línea de comandos. Este comportamiento puede anular ajustes de postgresql.conf. Por ejemplo, si desea cambiar el número de puerto del servidor a 5433, cree un archivo llamado /etc/systemd/system/postgresql.service que contenga:

.include /lib/systemd/system/postgresql.service
[Service]
Environment=PGPORT=5433

Nota: el cambio en PGPORT o PGDATA requerirá, normalmente, ajustar la configuración de SELinux también; vea la sección selinux.

Siga la documentación de systemd 2 para más detalles.

postgresql.conf

Si desea que postgres acepte conexiones de red, debería cambiar

listen_addresses = 'localhost'

a

listen_addresses = '*'

pg_hba.conf

Una vez que su base de datos está configurada, usted necesita configurar el acceso a su base de datos. Esto se puede hacer editando el archivo /var/lib/pgsql/data/pg_hba.conf. Hay reglas como estas en el archivo:

# TYPE    DATABASE        USER            ADDRESS                 METHOD
  host    all             all             127.0.0.1/32            md5
  host    all             all             ::1/128                 md5
  local   all             postgres                                peer

El primer campo indica el tipo de conexión. Puede tener estos valores:

  • local — Socket de dominio Unix

  • host — Socket TCP/IP plano o encriptado SSL

  • hostssl — Un socket TCP/IP encriptado SSL

  • hostnossl — Socket TCP/IP plano

La última columna indica qué método de autentificación será utilizado.

  • md5 — cliente tiene que poner una contraseña procesada con el algoritmo MD5

  • ident — obtiene el nombre de usuario del cliente que se conecta desde el sistema operativo y lo consulta con el mapa especificado

  • trust — cualquiera que sea capaz de conectarse al servidor PostgreSQL puede actuar como cualquier usuario sin tener que poner contraseña

  • peer — obtiene el nombre de usuario del sistema operativo y verifica si coincide con el nombre de usuario de la base de datos

Cuando el servidor de base de datos está autentificando al cliente, busca un registro con un tipo de conexión, dirección del cliente, base de datos solicitada y nombre de usuario que coincidan. Tan pronto como encuentra estas credenciales, lleva a cabo la autentificación. Si la autentificación falla, no se tienen en cuenta más registros posteriores. Si ningún registro coincide, se deniega el acceso del cliente.

Los ajustes predeterminados están normalmente restringidos a localhost.

Cuando usted instala su servidor de base de datos y al principio intenta "hacerlo funcionar", debería desactivar el cortafuegos, SELinux y hacer la autentificación de postgres permisiva. Tenga en cuenta que esto expondrá en gran medida a su servidor, así que hágalo solo en una red de confianza — preferiblemente sin red:

host    all             all             127.0.0.1/32            trust

Tan pronto como sea capaz de conectar, encienda sus sistemas de seguridad uno a uno verificando que la conexión puede ser establecida.

Para más información vea la documentación oficial para el archivo pg_hba.conf.

Optimización

La configuración predeterminada de postgres está muy debilitada. Puede manejar aplicaciones simples sin acceso constante a la base de datos, pero si usted requiere un rendimiento más alto, debería reconfigurar su instancia. Toda la magia está sucediendo en`/var/lib/pgsql/data/postgresql.conf\``. Tampoco, el mecanismo de registro está configurado de una manera muy intuitiva.

Rendimiento

El número de clientes que pueden ser conectados a PostgreSQL al mismo tiempo:

max_connections = <number>

shared_buffers es el punto de entrada. Este está diciendo a PostgreSQL cuanta memoria se dedica al almacenamiento en caché. Establecer esto al 25% de la memoria total de su sistema es un buen principio. Si esto no funciona para usted, intente ir a algo entre 15% - 40% de la memoria total.

shared_buffers = <memory unit>

El planificador de consultas utiliza este valor para saber cuanta memoria hay disponible en su sistema. El planificador de consultas utiliza esta información para determinar si el plan cabe en la memoria o no. Ajustar esto al 50% de la memoria total es una práctica común.

effective_cache_size = <memory unit>

Cuando PostgreSQL realiza operaciones de clasificación, planea su estrategia si clasificar la consulta en el disco o en memoria. Tenga en cuenta que esta memoria está disponible para cada instancia de clasificación. En caso de que varios usuarios envíen consultas a su servidor de base de datos, esto puede aumentar bastante. Por lo tanto, esto está estrechamente vinculado a max_connections.

work_mem = <memory unit>

Para más información sobre este punto le aconsejo leer la documentación oficial sobre el ajuste de PostgreSQL.

Registro

De forma predeterminada, los registros se rotan cada semana y es posible que no encuentre mucha información allí. Se podría perder un nivel de registro, fecha, hora, etc. Además, para aplicaciones web simples, algunos prefieren aumentar la verbosidad.

log_destination = 'stderr'

Esto está bien. Si usted quisiera que syslog tome el cuidado de sus registros, cambie 'stderr' a 'syslog' o incluso 'syslog,stderr'. Si se decide por syslog, no olvide configurar el mismo syslog también; para más información, vea la documentación oficial.

logging_collector = on

En el caso de registro en stderr, postgres cogerá todos los registros si usted habilita la opción logging_collector.

Esta es la opción predeterminada:

log_filename = 'postgresql-%a.log'

Un método preferido podría ser nombrar los archivos de registro por la fecha en la que fueron creados:

log_filename = 'postgresql-%G-%m.log

Rotación. Esto realmente dependen de la misma aplicación. En el caso de una sencilla aplicación con pocos datos en la base de datos, todos los registros pueden mantenerse permanentemente en el disco sin rotación.

log_truncate_on_rotation = off
log_rotation_age = 31d

Incrementar el número de entradas en el registro:

client_min_messages = notice      # default notice
log_min_messages = info           # default warning
log_min_error_statement = notice  # default error

Si desea registrar consultas lentas, no dude en utilizar esta opción:

log_min_duration_statement = 1000  # in ms

La entrada de registro predeterminada no contiene mucha información:

FATAL:  Ident authentication failed for user "test"
DETAIL:  Connection matched pg_hba.conf line 84: "host    all             all             ::1/128                 ident"

Mejorémoslo para:

2013-12-30 17:51:36 CET testx@::1(50867):postgres [11213] FATAL:  password authentication failed for user "testx"
2013-12-30 17:51:36 CET testx@::1(50867):postgres [11213] DETAIL:  Connection matched pg_hba.conf line 84: "host   all             all             ::1/128                 md5 "

Solo tiene que modificar la opción log_line_prefix.

# %t -- timestamp
# %u -- user
# %r -- client's host
# %d -- database
# %p -- PID
log_line_prefix = '%t %u@%r:%d [%p] '

Si está ejecutando una única base de datos con un único usuario conectado, tiene más sentido simplificar el prefijo a

log_line_prefix = '%t [%p] '

Receta final

log_destination = 'stderr'
logging_collector = on
log_filename = 'postgresql-%G-%m.log'
log_truncate_on_rotation = off
log_rotation_age = 31d
client_min_messages = notice
log_min_messages = info
log_min_error_statement = notice
log_line_prefix = '%t %u@%r:%d [%p] '

Referencia

Documentación completa de empaquetamiento RPM

Ajuste de rendimiento

Configuración de registro

Mejora PostgreSQL

Archivo pg_hba.conf

¿Ve algún error tipográfico, algo desaparecido u obsoleto, o algo que pueda ser mejorado? Edite este documento en https://forge.fedoraproject.org/docs/quick-docs.

Want to help? Learn how to contribute to Fedora Docs ›