In this article we’ll install PostgreSQL 11 on Linux CentOS 7, perform the basic configuration, consider the main configuration file parameters and performance tuning methods. PostgreSQL is a popular free object-relational database management system. Although it is less popular than MySQL/MariaDB, it is the most professional one.
PostgreSQL strengths:
- Full compliance with SQL standards;
- High performance due to multiversion concurrency control (MVCC);
- Scalability (widely used in high-load environments);
- Support of multiple programming languages;
- Resilient transaction and replication mechanisms;
- Support of JSON data.
How to Install PostgreSQL on CentOS/RHEL?
Although PostgreSQL can be installed from the base CentOS repository, we will install the developer repository since you can always find the current package version there.
First of all, add the PosgreSQL repo:
# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
This repository contains both the latest and earlier versions of PosgreSQL. The information about the repository looks like this:
Let’s install the PostrgeSQL 11 using yum.
# yum install postgresql11-server -y
PostgreSQL server and the required libraries will be installed:
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
After the packages are installed, you will have to initialize the database:
# /usr/pgsql-11/bin/postgresql-11-setup initdb
Also, enable the PostgreSQL daemon and add it to auto-start using systemctl:
# systemctl enable postgresql-11
# systemctl start postgresql-11
Check the service status:
# 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...
If you want to access to PostgreSQL from the outside, open the TCP Port 5432 in the default CentOS firewalld:
# firewall-cmd --get-active-zones
public interfaces: eth0
# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload
Or with iptables:
# iptables-A INPUT -m state --state NEW -m tcp -p tcp --dport 5432 -j ACCEPT
# service iptables restart
If SELinux is enabled, run the following command:
# setsebool -P httpd_can_network_connect_db 1
Using PSQL to Create Database, User, Grant Permissions in PostgreSQL
By default, when you install PostgreSQL there is the only user in the system — postgres. I don’trecommend using this account for daily work with databases. It is better to create separate user for each database.
To connect to a postgres server, run this command:
# sudo -u postgres psql
psql (11.5) Type "help" for help.
postgres=#
The PostgreSQL console appears. Then we’ll show some simple examples of PostgreSQL management from psql console.
Change the default postgres user password:
ALTER ROLE postgres WITH PASSWORD 's3tPa$$w0rd!';
Create a new database and user, and grant the user full access to the new database:
postgres=# CREATE DATABASE newdbtest;
postgres=# CREATE USER mydbuser WITH password '!123456789';
postgres=# GRANT ALL PRIVILEGES ON DATABASE newdbtest TO mydbuser;
To connect to the DB:
postgres=# \c databasename
To display the list of tables:
postgres=# \dt
To display the list of database connections:
postgres=# select * from pg_stat_activity where datname='dbname'
To reset all connections to the database:
postgres=# select pg_terminate_backend(pid) from pg_stat_activity where datname = 'dbname'
To get the information about the current session:
postgres=# \conninfo
To exit psql console, run this command:
postgres=# \q
As you have noticed, the syntax is similar to MariaDB or MySQL.
We’ll note that to manage PostgreSQL databases from a web interface more conveniently, it is recommended to use pgAdmin4 (written on Python and Javascript/jQuery). It is similar to PhpMyAdmin that many web developers are familiar with.
Setting Main PostgreSQL Configuration Parameters
Postgresql configuration files are located in /var/lib/pgsql/11/data:
- postgresql.conf — postgresql configuration file;
- pg_hba.conf — a file containing access settings. In this file, you can set different restrictions for your users or set a database connection policy;
- pg_ident.conf — this file is used to identify clients over the ident protocol.
To prevent local users from logging into postgres without authorization, specify the following in your pg_hba.conf:
local all all md5 host all all 127.0.0.1/32 md5
Let’s consider the most important parameters in postgresql.conf:
listen_addresses— sets the IP addresses a server will accept client connections to. The default value is localhost, it means that only a local connection is possible. To listen to all IPv4 interfaces, specify 0.0.0.0 here;max_connections–the maximum number of simultaneous connections to a DB server;temp_buffers– the maximum size of temporary buffers;shared_buffers— the size of shared memory used by a database server. Typically, a value of 25% of the total server RAM is set;effective_cache_size– a parameter that enables postgres scheduler to determine the amount of available memory for caching on the local drive. Usually, it is set to 50-75% of the total RAM on the server;work_mem– the size of memory to be used by the internal sorting operations of the database management system — ORDER BY, DISTINCT and merging;maintenance_work_mem– the size of memory to be used by the internal operations — VACUUM, CREATE INDEX and ALTER TABLE ADD FOREIGN KEY;fsync– if this parameter is enabled, the DBMS will wait for the physical writing of data on a hard disk. If fsync is enabled, it will be easier for you to recover your database after a system or hardware failure. Obviously, if this parameter is enabled, the database management system will have lower performance, but higher storage reliability. If you disable it, it is worth disabling full_page_writes as well;max_stack_depth— the maximum stack size (2MB by default);max_fsm_pages— using this parameter, you can manage free disk space on the server. For example, after deleting some data from a table, the space occupied earlier doesn’t become free, but is marked as free on the free space map and used for new entries further. If you often write/remove data in the tables on your server, the performance will increase if you set a larger value of this parameter;wal_buffers– the shared memory size (shared_buffers) used to keep WAL data;wal_writer_delay– time between the consecutive periods of writing WAL on a disk;commit_delay— the delay between writing a transaction to the WAL buffer and its write-through to a disk;synchronous_commit— the parameter sets that the result of the successful transaction will be sent after WAL data have been physically written on a disk.
Backup and Restore PostgreSQL Database
You can backup PostgreSQL database in several ways. Let’s consider the easiest one.
First of all, check, which databases are running on your server:
postgres=# \list
We have 4 databases, 3 of them are system ones (postgres and template).
Earlier we created a DB with the name mydbtest and now we will back it up.
You can backup your PostgreSQL database using the pg_dump tool:
# sudo -u postgres pg_dump mydbtest > /root/dupm.sql —
Run this command under postgres user, specify a database and a path to the file you will save a database dump to. Your backup system may take the database dump, or you can send it to your connected cloud storage account in case of using a web server.
To restore the dump to the database, use the psql:
# sudo -u postgres psql mydbtest < /root/dupm.sql
You can also create a backup in a special dump format and compress it using gzip:
# sudo -u postgres pg_dump -Fc mydbtest > /root/dumptest.sql
The dump is then recovered using the pg_restore tool:
# sudo -u postgres pg_restore -d mydbtest /root/dumptest.sql
PostgreSQL Performance Tuning and Optimization
In the previous article related to MariaDB, we showed how to optimize my.cnf configuration file parameters using tuners. PostgreSQL had PgTun for that, but unfortunately it had not been updated for a long time. At the same time, there are a lot of online services you can use to optimize your PostgreSQL configuration. I like PGTune (pgtune.leopard.in.ua).
The interface is very simple. You just need to specify your server parameters (profile, processors, memory, disk type) and click “Generate.” You will be offered a variant of postgresql.conf containing the recommended values of the main PostgreSQL parameters.
For example, the following postgresql.conf settings are recommended for a VPS SSD server with 4xGB RAM and 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
Actually, it is not the only resource by the time the article had been written. Similar services are also available:
- Cybertec PostgreSQL Configurator
- PostgreSQL Configuration Tool
Using these services, you can quickly configure basic PostgreSQL parameters for your hardware and tasks. Later you will be able not only to consider your server resources, but also analyze the operation of your database, its size, the number of connections and fine-tune your PostgreSQL parameters based on these info.