centos7 安装postgresql12

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