Windows OS Hub
  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2022
    • Windows Server 2019
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Active Directory Domain Services (AD DS)
    • Group Policies
  • Windows Clients
    • Windows 11
    • Windows 10
    • Windows 8
    • Windows 7
    • Windows XP
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
    • KVM
  • PowerShell
  • Exchange
  • Cloud
    • Azure
    • Microsoft 365
    • Office 365
  • Linux
    • CentOS
    • RHEL
    • Ubuntu

 Windows OS Hub / Linux / CentOS / Install and Configure PostgreSQL on CentOS/RHEL

October 19, 2020 CentOSLinuxQuestions and AnswersRHEL

Install and Configure PostgreSQL on CentOS/RHEL

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.

Contents:
  • How to Install PostgreSQL on CentOS/RHEL?
  • Using PSQL to Create Database, User, Grant Permissions in PostgreSQL
  • Setting Main PostgreSQL Configuration Parameters
  • Backup and Restore PostgreSQL Database
  • PostgreSQL Performance Tuning and Optimization

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:

postgresql repositories info

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.

pgAdmin4 - postgresql web management interface

Setting Main PostgreSQL Configuration Parameters

Postgresql configuration files are located in /var/lib/pgsql/11/data:

  1. postgresql.conf — postgresql configuration file;
  2. 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;
  3. 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

postgres list databases

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

psql restore postgresql database

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

pgtune - generate postgresql.conf file for your task

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.

0 comment
1
Facebook Twitter Google + Pinterest
previous post
How to Reduce Windows.edb Huge File Size?
next post
Checking SSL/TLS Certificate Expiration Date with PowerShell

Related Reading

Disable Built-in PDF Viewer in Microsoft Edge

February 3, 2023

Join a Windows Computer to an Active Directory...

February 2, 2023

Enable Internet Explorer (IE) Compatibility Mode in Microsoft...

January 27, 2023

How to Disable or Uninstall Internet Explorer (IE)...

January 26, 2023

How to Stop Automatic Upgrade to Windows 11?

January 18, 2023

Leave a Comment Cancel Reply

Categories

  • Active Directory
  • Group Policies
  • Exchange Server
  • Microsoft 365
  • Azure
  • Windows 11
  • Windows 10
  • Windows Server 2022
  • Windows Server 2019
  • Windows Server 2016
  • PowerShell
  • VMWare
  • Hyper-V
  • Linux
  • MS Office

Recent Posts

  • Configure User’s Folder Redirection with Group Policy

    February 3, 2023
  • Using Previous Command History in PowerShell Console

    January 31, 2023
  • How to Install the PowerShell Active Directory Module and Manage AD?

    January 31, 2023
  • Finding Duplicate E-mail (SMTP) Addresses in Exchange

    January 27, 2023
  • How to Delete Old User Profiles in Windows?

    January 25, 2023
  • How to Install Free VMware Hypervisor (ESXi)?

    January 24, 2023
  • How to Enable TLS 1.2 on Windows?

    January 18, 2023
  • Allow or Prevent Non-Admin Users from Reboot/Shutdown Windows

    January 17, 2023
  • Fix: Can’t Extend Volume in Windows

    January 12, 2023
  • Wi-Fi (Internet) Disconnects After Sleep or Hibernation on Windows 10/11

    January 11, 2023

Follow us

woshub.com
  • Facebook
  • Twitter
  • RSS
Popular Posts
  • How to Configure MariaDB Master-Master/Slave Replication?
  • How to Mount Google Drive or OneDrive in Linux?
  • KVM: How to Expand or Shrink a Virtual Machine Disk Size?
  • Hyper-V Boot Error: The Image’s Hash and Certificate Are not Allowed
  • Adding VLAN Interface in CentOS/Fedora/RHEL
  • Configuring High Performance NGINX and PHP-FPM Web Server
  • Install and Configure SNMP on RHEL/CentOS/Fedor
Footer Logo

@2014 - 2023 - Windows OS Hub. All about operating systems for sysadmins


Back To Top