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
Leave a Reply