Introduction
PostgreSQL is one of the most advanced and popular relational database management systems in the world of free software. Its combination of robustness, extensibility and standard compliance makes it an ideal option for Linux environments, where it benefits from the stability and security of the kernel and the management tools available. In this article you will learn to install, configure and optimize PostgreSQL in the most used distributions, as well as to apply good safety and monitoring practices.
Previous requirements
Before you start, make sure you have root or sweat access on your Linux server. It is recommended to have at least 2 GB of RAM and 20 GB of disk space for a test environment, although in production more resources are required according to the expected load. Check that your system is updated by running the update commands corresponding to your distribution. In addition, have a text editor as nano or vim to modify the configuration files.
Installation in Ubuntu / Debian
In Debian-based distributions, the process is simple thanks to official repositories. First update the package index:
sudo apt updatesudo apt upgrade -y
Then install PostgreSQL and the useful contribution package:
sudo apt install postgresql postgresql-contrib -y
The service is automatically started. You can check their status with:
sudo systemctl status postgresql
To access the PostgreSQL command interpreter, change the postgres user:
sudo -i -u postgrespsql
From here you can create databases and users according to your needs.
Installation in RHEL / CentOS
In the Red Hat family distributions, PostgreSQL repositories are used directly to obtain more recent versions than those included by default. First install the official repository:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
Then install the server:
sudo yum install -y postgresql15-server postgresql15-contrib
Initiates the database:
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
Enable and start the service:
sudo systemctl enable --now postgresql-15
Verify that it is active withsystemctl status postgresql-15. Access the user in a similar way to Debian.
Basic configuration
The main configuration files are found in/etc/postgresql/<versión>/main/(Debian) or/var/lib/pgsql/<versión>/data/(RHEL). The most important are:
postgresql.conf: controls memory parameters, connections and registration.pg_hba.conf: manages authentication and customer access.
For a development environment, you can increaseshared_buffers25% of available RAM and establishmax_connectionsaccording to the expected number of customers. Inpg_hba.conf, change the type lineshostamd5orscram-sha-256to improve safety. After changing, recharge the service:
sudo systemctl reload postgresql
Performance optimization
Good optimization begins with monitoring of slow consultations. Enable the parameterlog_min_duration_statementto 500ms to record those that exceed that threshold. Then useEXPLAIN ANALYZEto review implementation plans and add indices where necessary.
Another key adjustment iswork_mem, which defines the memory available for management and hash operations by consultation. A value between 4 and 16 MB is usually a good starting point, gradually increasing if you see disk use in these operations.
It also considers the use ofeffective_cache_size, which must reflect the amount of memory available for the kernel file system; typically it is set to 50-70% of the total RAM. Finally, program maintenance tasks such asVACUUMandANALYZEbyautovacuumo cron to avoid block of tables.
Security and backups
The security of PostgreSQL is strengthened by limiting network access. Inpg_hba.conf, restricts connections to the required IP addresses and avoids usingtrustin productive environments. Implement encrypted connections using SSL, generating a certificate and activatingssl = oninpostgresql.conf.
For backup, the toolpg_dumpallows logical exports, whilepg_basebackupsupports the physical cluster. A recommended strategy is to combine night braindumps with weekly physical backups and store them in a remote site or in object storage such as S3.
Do not forget to rotate the keys of the replication users and review the authentication logs withgrepor tools likepgbadgerto detect attempts at unauthorized access.
Monitoring and maintenance
Continuous monitoring helps to detect bottlenecks before they affect users. Extensions likepg_stat_statementsprovide time consumption metrics for consultation. Install it with:
CREATE EXTENSION pg_stat_statements;
Then check the viewpg_stat_statementsto identify the most expensive consultations.
External tools such asPrometheuswith the exporterpostgres_exporterorGrafanaallow for real-time display panels. Configure alerts for CPU use thresholds, memory, lag replication and number of failed transactions.
Finally, program regular configuration reviews, backup recovery test and security patch updates following your distribution and PostgreSQL life cycle.
Conclusion
Install and optimize PostgreSQL in Linux is a process that combines the strength of the operating system with the power and flexibility of the database. Following the installation steps, adjusting the key parameters, applying good security practices and establishing a monitoring and maintenance regime, you can get a reliable, efficient and ready data environment to grow with the demands of your application. Remember that optimization is a continuous process: regularly review the logs, adjust settings and keep your backups checked to ensure the availability and integrity of your information.
