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
Create database and user with access to database.
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:
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