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 ~]#
[root@www ~]# postgres -V postgres (PostgreSQL) 12.1 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 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) |
Sponsored Link |
|