zeldor.biz

Linux, programming and more

Copyright © 2023
Log in

CentOS 8 install PostgreSQL 12

January 3, 2020 by Igor Drobot Leave a Comment

CentOS 8 has PostgreSQL within the default repositories but its’ a bit outdated – currently during writing of this post, it’s the version: 10.6.
The currently official version of PostgreSQL is version: 12.1

So this instruction was written to install the current actual version of PostgreSQL 12.1 on CentoOS 8 system.

Let’s get started!

Install the repository RPM for PostgreSQL 12:

$ dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Disable the built-in PostgreSQL module to be able to perform the installation of postgresql12 [12.1-2PGDG.rhel8]:

$ dnf -qy module disable postgresql
# client package
$ dnf install postgresql12
# server package
$ dnf install postgresql12-server

Initialize the database:

$ /usr/pgsql-12/bin/postgresql-12-setup initdb

Enable auto start and start the PostgreSQL service:

$ systemctl enable postgresql-12
$ systemctl start postgresql-12

Get into the PostgreSQL interactive terminal:

$ sudo su postgres
$ psql

PG basic commands

List all PostgreSQL-databases:

\list or \l

Create Dump of a PostgreSQL-database:

pg_dump airflowdb > airflowdb.sql

Restore a database from a easier created dump:

psql airflowdb < airflowdb.sql

Administrative commands

Create database and user with access to database.
Database: db2go
Username: mpl7491
Password: secure123

sudo su postgres -c psql
postgres=# create database db2go;
postgres=# create user mpl7491 with encrypted password 'secure123';
postgres=# grant all privileges on database db2go to mpl7491;

Alternative way to create a new database and a role:

create role rstudio LOGIN PASSWORD 'password1234!';
create database rstudio owner rstudio ENCODING 'UTF8';

To be able to establish a connection with the database, you have to create new entry for you database and username in pg_hba.conf:

host    db2go             mpl7491             0.0.0.0/0            md5

This entry will allow a connection from everywhere – so please make sure you know, what you are doing.
To take this entry in effect you can reload the pg_hba.conf by:

SELECT pg_reload_conf();

Establish a connection with db2go user to do the last test:

CREATE TABLE example (id int, mydata text);

Productive load configuration

Find out the path to your PostgreSQL configuration file:

psql -U postgres -c 'SHOW config_file'

The mostly preferable prod configuration for a system with 256GB of RAM:

# Determines the maximum number of concurrent connections to the database server.
max_connections = 100

# Sets the amount of memory the database server uses for shared memory buffers.
shared_buffers = 16GB

# Sets the planner's assumption about the effective size of the disk cache that is available to a single query.
effective_cache_size = 24GB

# Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.
maintenance_work_mem = 2GB

# Specifies the target of checkpoint completion, as a fraction of total time between checkpoints. 
checkpoint_completion_target = 0.7

# The amount of shared memory used for WAL data that has not yet been written to disk. 
wal_buffers = 16MB

# Sets the default statistics target for table columns without a column-specific target set via ALTER TABLE SET STATISTICS.
default_statistics_target = 100

# Sets the planner's estimate of the cost of a non-sequentially-fetched disk page.
random_page_cost = 4

# Sets the number of concurrent disk I/O operations that PostgreSQL expects can be executed simultaneously.
effective_io_concurrency = 2

# Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files
work_mem = 41MB

min_wal_size = 1GB
# Maximum size to let the WAL grow during automatic checkpoints. This is a soft limit
max_wal_size = 4GB

# Sets the maximum number of background processes that the system can support. 
max_worker_processes = 12

# Sets the maximum number of workers that can be started by a single Gather or Gather Merge node.
max_parallel_workers_per_gather = 4

Filed Under: Linux Tagged With: CentOS, Database, postgresql

Categories

Archives

Tags

apache2 Apple arduino ARM Automation backup bash Cisco Cluster Corosync Database Debian Debian squeeze DIY DNS Fedora FTP Fun Icinga Ipv6 KVM Linux LVM MAC OS X Monitoring MySQL Nagios Nginx openSUSE OpenVPN PHP Proxy Python python3 qemu RAID rsync Samba security ssh Ubuntu virtualization Windows Windows 7 Wordpress

Leave a Reply

Your email address will not be published. Required fields are marked *