CentOS 7
Sponsored Link

PostgreSQL 12 : Install2020/01/31

 
The version of PostgreSQL in CentOS 7 repository is 9.2 but Install 12 with RPM package if you need.
[1] It's possible to install from CentOS SCLo Software Collections.
It's OK to install it even if 9.2 is already installed because 12 is located on another PATH.
# install from SCLo

[root@www ~]#
yum --enablerepo=centos-sclo-rh -y install rh-postgresql12-postgresql-server
[2] Packages from Software Collections are installed uder the [/opt] directory.
To use it, Load environment variables like follows.
# load environment variables

[root@www ~]#
scl enable rh-postgresql12 bash
[root@www ~]#
postgres -V

postgres (PostgreSQL) 12.1
[root@www ~]#
which postgres

/opt/rh/rh-postgresql12/root/usr/bin/postgres
[3] If you'd like to enable PostgreSQL 10 automatically at login time, configure like follows.
[root@www ~]#
vi /etc/profile.d/rh-postgresql12.sh
# create new

source /opt/rh/rh-postgresql12/enable
export X_SCLS="`scl enable rh-postgresql12 'echo $X_SCLS'`"
[4] Run initial setup and start PostgreSQL Server.
[root@www ~]#
postgresql-setup --initdb --unit rh-postgresql12-postgresql

 * Initializing database in '/var/opt/rh/rh-postgresql12/lib/pgsql/data'
 * Initialized, logs are in /var/lib/pgsql/initdb_rh-postgresql12-postgresql.log

[root@www ~]#
systemctl enable --now rh-postgresql12-postgresql

[5] By default setting, it's possible to connect to PostgreSQL Server only from Localhost with [peer] authentication.
Refer to the official site below about details of authentication methods.
⇒ https://www.postgresql.jp/document/10/html/auth-pg-hba-conf.html
# listen only localhost by default

[root@www ~]#
grep listen_addresses /var/opt/rh/rh-postgresql12/lib/pgsql/data/postgresql.conf

#listen_addresses = 'localhost' # what IP address(es) to listen on;
# authentication methods by default

[root@www ~]#
grep -v -E "^#|^$" /var/opt/rh/rh-postgresql12/lib/pgsql/data/pg_hba.conf

local   all             all                                     peer
host    all             all             127.0.0.1/32            ident
host    all             all             ::1/128                 ident
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
[6] On [peer] authentication, it needs OS user and PostgreSQL user whose name are the same to connect to PostgreSQL Server.
# add an OS user

[root@www ~]#
useradd cent
# add an PostgreSQL user and his Database with PostgreSQL admin user

[root@www ~]#
su - postgres

-bash-4.2$
createuser cent

-bash-4.2$
createdb testdb -O cent
# show users and databases

-bash-4.2$
psql -c "select usename from pg_user;"
 usename
----------
 postgres
 cent
(2 rows)

-bash-4.2$
psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres    +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres    +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | cent     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
[7] Try to connect to PostgreSQL Database with a user added above.
# connect to testdb

[cent@www ~]$
psql testdb

psql (12.1)
Type "help" for help.

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

# show databases
testdb=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres    +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres    +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | cent     | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

# create a test table
testdb=> create table test_table (no int, name text); 
CREATE TABLE

# show tables
testdb=> \dt 
          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 public | test_table | table | cent
(1 row)

# insert data to test table
testdb=> insert into test_table (no,name) values (01,'CentOS'); 
INSERT 0 1

# confirm
testdb=> select * from test_table; 
 no |  name
----+--------
  1 | CentOS
(1 row)

# remove test table
testdb=> drop table test_table; 
DROP TABLE

testdb=> \dt 
Did not find any relations.

# exit
testdb=> \q 

# remove testdb

[cent@www ~]$
dropdb testdb

[cent@www ~]$
psql -l

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres    +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres    +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)
Matched Content