Install PostgreSQL 13 on CentOS 7.5

environment

PostgreSQL 13.0 was released on September 24, 2020, so I will install it.

The environment is as follows.

--CentOS 7.5 (firewalld is disabled)

The installation procedure is almost the same as for PostgreSQL 12 below.

-Install PostgreSQL 12 on CentOS 7.5

Installation of LLVM related libraries

Starting with 12.3, LLVM related libraries are required to install postgresXX-devel. postgresXX-devel is used when compiling the extension. If you just want to run PostgreSQL, you don't have to install it.

# yum -y install epel-release centos-release-scl

Install PostgreSQL yum repository

Install the PostgreSQL repository package for CentOS. The URL list of the repository package for each OS can be found at the following site.

https://yum.postgresql.org/repopackages.php

# yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
~ Omitted ~
Running transaction
  Installing : pgdg-redhat-repo-42.0-13.noarch                                                                                                     1/1 
  Verifying  : pgdg-redhat-repo-42.0-13.noarch                                                                                                     1/1 

Installed:
  pgdg-redhat-repo.noarch 0:42.0-13                                                                                                                    

Complete!

Install PostgreSQL

Install PostgreSQL.

# yum -y install postgresql13-contrib postgresql13-devel

~ Omitted ~
Installed:
  postgresql13-contrib.x86_64 0:13.0-1PGDG.rhel7                              postgresql13-devel.x86_64 0:13.0-1PGDG.rhel7                             

Dependency Installed:
  audit-libs-python.x86_64 0:2.8.5-4.el7           checkpolicy.x86_64 0:2.5-8.el7                devtoolset-7-binutils.x86_64 0:2.28-11.el7           
  devtoolset-7-gcc.x86_64 0:7.3.1-5.16.el7         devtoolset-7-gcc-c++.x86_64 0:7.3.1-5.16.el7  devtoolset-7-libstdc++-devel.x86_64 0:7.3.1-5.16.el7 
  devtoolset-7-runtime.x86_64 0:7.1-4.el7          libcgroup.x86_64 0:0.41-21.el7                libedit-devel.x86_64 0:3.0-12.20121213cvs.el7        
  libicu.x86_64 0:50.2-4.el7_7                     libicu-devel.x86_64 0:50.2-4.el7_7            libsemanage-python.x86_64 0:2.5-14.el7               
  libxslt.x86_64 0:1.1.28-5.el7                    llvm-toolset-7-clang.x86_64 0:5.0.1-4.el7     llvm-toolset-7-clang-libs.x86_64 0:5.0.1-4.el7       
  llvm-toolset-7-compiler-rt.x86_64 0:5.0.1-2.el7  llvm-toolset-7-libomp.x86_64 0:5.0.1-2.el7    llvm-toolset-7-llvm-libs.x86_64 0:5.0.1-8.el7        
  llvm-toolset-7-runtime.x86_64 0:5.0.1-4.el7      llvm5.0.x86_64 0:5.0.1-7.el7                  llvm5.0-devel.x86_64 0:5.0.1-7.el7                   
  llvm5.0-libs.x86_64 0:5.0.1-7.el7                ncurses-devel.x86_64 0:5.9-14.20130511.el7_4  policycoreutils-python.x86_64 0:2.5-34.el7           
  postgresql13.x86_64 0:13.0-1PGDG.rhel7           postgresql13-libs.x86_64 0:13.0-1PGDG.rhel7   postgresql13-server.x86_64 0:13.0-1PGDG.rhel7        
  python-IPy.noarch 0:0.75-6.el7                   scl-utils.x86_64 0:20130529-19.el7            setools-libs.x86_64 0:3.3.8-4.el7                    

Dependency Updated:
  audit.x86_64 0:2.8.5-4.el7              audit-libs.x86_64 0:2.8.5-4.el7    libselinux.x86_64 0:2.5-15.el7    libselinux-python.x86_64 0:2.5-15.el7   
  libselinux-utils.x86_64 0:2.5-15.el7    libsemanage.x86_64 0:2.5-14.el7    libsepol.x86_64 0:2.5-10.el7      policycoreutils.x86_64 0:2.5-34.el7     

Complete!

PostgreSQL is installed under "/ usr / pgsql-13 /".

# ls -l /usr/pgsql-13/
total 20
drwxr-xr-x. 2 root root 4096 Sep 25 23:11 bin
drwxr-xr-x. 3 root root   23 Sep 25 23:11 doc
drwxr-xr-x. 6 root root 4096 Sep 25 23:11 include
drwxr-xr-x. 5 root root 4096 Sep 25 23:11 lib
drwxr-xr-x. 8 root root 4096 Sep 25 23:11 share

PostgreSQL autostart

Execute the following command to start PostgreSQL automatically.

# systemctl enable postgresql-13.service

Creating a database cluster

Create a database cluster. You are running as the root user. The database file is created in "/ var / lib / pgsql / 13 / data /" by default, but this time it is changed to "/ data /" and below. It can be specified by the "PGDATA" environment variable in the postgresql-13.service file.

# vi /usr/lib/systemd/system/postgresql-13.service

Change before)
Environment=PGDATA=/var/lib/pgsql/13/data/
After change)
Environment=PGDATA=/data/

# systemctl daemon-reload

Then run the following command to create the database cluster.

# PGSETUP_INITDB_OPTIONS="-E UTF8 --no-locale" /usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database ... OK

Confirm that it is generated under "/ data /".

# cat /data/PG_VERSION 
13

Start PostgreSQL

Add the PostgreSQL bin directory to your path before booting.

# su - postgres

/var/lib/pgsql/.pgsql_Add the path to your profile.

# vi /var/lib/pgsql/.pgsql_profile
PATH=/usr/pgsql-13/bin:$PATH
export PATH

Modify PGDATA.

# vi /var/lib/pgsql/.bash_profile
#PGDATA=/var/lib/pgsql/12/data
PGDATA=/data

# source ~/.bash_profile

Start PostgreSQL with "pg_ctl start".

$ pg_ctl start
2020-09-25 23:16:08.611 CEST [12910] HINT:  Future log output will appear in directory "log".
 done
server started

Database check

Let's check the created database cluster. Let's display the list of versions and databases.

$ psql -V
psql (PostgreSQL) 13.0
$ psql -l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(3 rows)

Creating users and databases

Create a user ("testuser") and a database ("testdb").

$ createuser --login --pwprompt testuser
Enter password for new role: 
Enter it again:

$ createdb --owner=testuser testdb

Allow external connections

PostgreSQL does not allow remote connection by default, so change the settings.

# vi /data/postgresql.conf
* By default, "/var/lib/pgsql/13/data/postgresql.conf」

Change before)
#listen_addresses = 'localhost'
After change)
listen_addresses = '*'

Then modify pg_hba.conf.

# vi /data/pg_hba.conf
* By default, "/var/lib/pgsql/13/data/pg_hba.conf」

# "local" is for Unix domain socket connections only
local   testdb          testuser                                md5
→ Added one line so that testuser can connect to testdb with md5.
local   all             all                                     peer
# IPv4 local connections:
host    all             all             192.168.10.0/24         md5
→192.168.10.0/Added one line so that you can connect from 24 (this server environment).
host    all             all             127.0.0.1/32            ident

Reload the configuration changes.

$ pg_ctl reload
* Or systemctl reload postgresql-13 should be fine.

Change the postgres user password (although you don't have to)

$ psql
alter role postgres with password 'postgres';

Check the connection remotely (A5: I checked the connection from SQL, but the explanation is omitted)

To check the connection from the local, execute as follows.

$ psql testdb testuser

Password for user testuser: 
psql (13.0)
Type "help" for help.

testdb=> 

Create table

Let's create a table and populate it with data.

testdb=> create table test (id int, value text);
CREATE TABLE
testdb=> insert into test (id, value) values (1, 'test text');
INSERT 0 1
testdb=> select * from test;
 id |   value   
----+-----------
  1 | test text
(1 row)

"\ D" displays a list of tables, and "\ du" displays a list of roles.

testdb=> \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | test | table | testuser
(1 row)

testdb=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  |                                                            | {}

Other setting changes

PostgreSQL seems to have various parameters set to small values as a whole. I changed only the shared buffer from 128MB to 512MB (20% to 40% of the total memory seems to be good). There are many other parameters that should be changed, but since they are system-dependent, including the shared buffer, we will not change them this time.

Here's how to change the shared buffer.

# vi /data/postgresql.conf
* By default, "/var/lib/pgsql/13/data/postgresql.conf」

Change before)
shared_buffers = 128MB
After change)
shared_buffers = 512MB

All you have to do is restart PostgreSQL.

Log settings

I changed the log settings as follows.

$ vi /data/postgresql.conf

Before the change, it is as follows.

log_filename = 'postgresql-%a.log'
log_rotation_size = 0
#log_min_duration_statement = -1
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_lock_waits = off

After the change, it is as follows.

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_size = 1GB
log_min_duration_statement = 500ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0

Run the benchmark with pgbench

Benchmark tool pgbench is included as standard in PostgreSQL. First, create a benchmark table and data with pgbench.

$ pgbench -i -s 10 testdb
dropping old tables...
NOTICE:  table "pgbench_accounts" does not exist, skipping
NOTICE:  table "pgbench_branches" does not exist, skipping
NOTICE:  table "pgbench_history" does not exist, skipping
NOTICE:  table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 9.02 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 12.99 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 9.12 s, vacuum 0.87 s, primary keys 3.00 s).

Once the data is ready, it's time to run the benchmark.

$ pgbench -c 10 -j 10 -t 2000 -N testdb
starting vacuum...end.
transaction type: <builtin: simple update>
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 2000
number of transactions actually processed: 20000/20000
latency average = 4.910 ms
tps = 2036.732154 (including connections establishing)
tps = 2039.124174 (excluding connections establishing)

"-c 10" represents the number of clients "10" and "-t 1000" represents the number of transactions per client.

The cache hit rate after running the benchmark can be seen as follows.

select relname,
   round(heap_blks_hit * 100 / (heap_blks_hit+heap_blks_read), 2)
   as cache_hit_ratio from pg_statio_user_tables
     where heap_blks_read > 0 order by cache_hit_ratio;

     relname      | cache_hit_ratio 
------------------+-----------------
 test             |           50.00
 pgbench_accounts |           63.00
 pgbench_branches |           82.00
 pgbench_tellers  |           95.00
 pgbench_history  |           99.00
(5 rows)

The cache hit rate of the index is as follows.

select relname, indexrelname,
   round(idx_blks_hit * 100 / (idx_blks_hit + idx_blks_read), 2)
   as cache_hit_ratio from pg_statio_user_indexes
     where idx_blks_read > 0 order by cache_hit_ratio;

     relname      |     indexrelname      | cache_hit_ratio 
------------------+-----------------------+-----------------
 pgbench_tellers  | pgbench_tellers_pkey  |           33.00
 pgbench_branches | pgbench_branches_pkey |           50.00
 pgbench_accounts | pgbench_accounts_pkey |           98.00
(3 rows)

reference

Recommended Posts

Install PostgreSQL 12 on Centos8
Install PostgreSQL 13 on CentOS 7.5
Install Golang on CentOS 8
Install Vertica 10.0 on CentOS 6.10
Install nginx on centOS7
Install Python 3 on CentOS 7
Install kuromoji on CentOS7
Install Mattermost on CentOS 7
Install PostGIS 2.5.5 on CentOS7
Install jpndistrict on CentOS 7
Install Redmine 4.1.1 on CentOS 7
Smokeping Install on CentOS7
Install OpenFOAM v2006 on CentOS
Install Apache on CentOS on VirtualBox
Install Ruby 2.7 on CentOS 7 (SCL)
Note: Install PostgreSQL 9.5 on Ubuntu 18.04
Install Java Open JDK 8 on CentOS 7
How to install MariaDB 10.4 on CentOS 8
Install apache 2.4.46 from source on CentOS7
Steps to install MySQL 8 on CentOS 8
Steps to install devtoolset-6 on CentOS 7
Install Java 9 on windows 10 and CentOS 7
[CentOS] Install apache-loggen
OpenVPN on CentOS 8
Install samba4 from source code on CentOS8
Install the webmail client Rainloop on CentOS 8
How to install beta php8.0 on CentOS8
Install NextCloud on CentOS 7 with Alibaba Cloud ECS
Install Corretto 8 on Windows
Maven on CentOS 7 tutorial
Install OpenJDK on macOS
Install Java on Mac
Docker installation on CentOS 6
Use perltidy on CentOS 8
Install pyqt5 on ubuntu
Try OpenLiteSpeed on CentOS8
Tomcat v8 on CentOS7
Run PostgreSQL on Java
Install Docker on Manjaro
Zabbix 5 installation on CentOS 8
Install Ruby on Ubuntu 20.04
Use mod_auth_cas on CentOS 8
Install lombok on SpringToolSuite4
Install GitLab on CentOS 8 with no internet connection
Use bat on Centos.
Install Autoware on Ubuntu 18.04.5
OpenJDK installation on CentOS 7
Install openjdk11 on mac
Install Homebrew on Ubuntu 20.04
Install CMS Made Simple v2.2.2 on LAMP on CentOS 7.3
Install OpenJDK 8 on mac
Install BookStack Documentation Wiki on Elastic Compute Service on CentOS 7
Install the latest hardware drivers from ELRepo on CentOS
Install MySQL 5.6 on CentOS6 [How to specify the version]
Install ag (the silver searcher) [on CentOS / Ubuntu / Mac]
Try RabbitMQ + PHP on CentOS
Install Docker on Raspberry Pi
Install Docker on Windows 10 PRO
Network install CentOS 8 with Kickstart.
Install OpenJDK7 (JAVA) on ubuntu 14.04
Install Cybozu Office 10 on Ubuntu 20.4