Installing PostgreSQL on CentOS 8 #
URL: https://linuxize.com/post/how-to-install-postgresql-on-centos-8/
# update
[root@thermalite ~]# dnf -y update
Last metadata expiration check: 0:06:57 ago on Sun 04 Oct 2020 02:00:47 AM EDT.
Dependencies resolved.
Nothing to do.
Complete!
# To list the available PostgreSQL module streams, type:
[root@thermalite ~]# dnf module list postgresql
Last metadata expiration check: 0:00:04 ago on Sun 04 Oct 2020 08:30:30 PM EDT.
CentOS-8 - AppStream
Name Stream Profiles Summary
postgresql 9.6 client, server [d] PostgreSQL server and client module
postgresql 10 [d][e] client, server [d] [i] PostgreSQL server and client module
postgresql 12 client, server [d] PostgreSQL server and client module
Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
[root@localhost ~]# dnf install @postgresql:10
Last metadata expiration check: 0:08:37 ago on Sun 04 Oct 2020 02:00:47 AM EDT.
Dependencies resolved.
================================================================================
Package Arch Version Repo Size
================================================================================
Installing group/module packages:
postgresql-server x86_64 10.14-1.module_el8.2.0+487+53cc39ce AppStream 5.0 M
Installing dependencies:
libpq x86_64 12.4-1.el8_2 AppStream 195 k
postgresql x86_64 10.14-1.module_el8.2.0+487+53cc39ce AppStream 1.5 M
Installing module profiles:
postgresql/server
Enabling module streams:
postgresql 10
Transaction Summary
================================================================================
Install 3 Packages
Total download size: 6.7 M
Installed size: 26 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): libpq-12.4-1.el8_2.x86_64.rpm 2.9 MB/s | 195 kB 00:00
(2/3): postgresql-10.14-1.module_el8.2.0+487+53 11 MB/s | 1.5 MB 00:00
(3/3): postgresql-server-10.14-1.module_el8.2.0 34 MB/s | 5.0 MB 00:00
--------------------------------------------------------------------------------
Total 18 MB/s | 6.7 MB 00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : libpq-12.4-1.el8_2.x86_64 1/3
Installing : postgresql-10.14-1.module_el8.2.0+487+53cc39ce.x86_6 2/3
Running scriptlet: postgresql-server-10.14-1.module_el8.2.0+487+53cc39c 3/3
Installing : postgresql-server-10.14-1.module_el8.2.0+487+53cc39c 3/3
Running scriptlet: postgresql-server-10.14-1.module_el8.2.0+487+53cc39c 3/3
Verifying : libpq-12.4-1.el8_2.x86_64 1/3
Verifying : postgresql-10.14-1.module_el8.2.0+487+53cc39ce.x86_6 2/3
Verifying : postgresql-server-10.14-1.module_el8.2.0+487+53cc39c 3/3
Installed:
libpq-12.4-1.el8_2.x86_64
postgresql-10.14-1.module_el8.2.0+487+53cc39ce.x86_64
postgresql-server-10.14-1.module_el8.2.0+487+53cc39ce.x86_64
Complete!
[root@localhost ~]# dnf install postgresql-contrib
Last metadata expiration check: 0:09:01 ago on Sun 04 Oct 2020 02:00:47 AM EDT.
Dependencies resolved.
================================================================================
Package Arch Version Repo Size
================================================================================
Installing:
postgresql-contrib x86_64 10.14-1.module_el8.2.0+487+53cc39ce AppStream 804 k
Installing dependencies:
uuid x86_64 1.6.2-42.el8 AppStream 63 k
Transaction Summary
================================================================================
Install 2 Packages
Total download size: 867 k
Installed size: 2.7 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): uuid-1.6.2-42.el8.x86_64.rpm 325 kB/s | 63 kB 00:00
(2/2): postgresql-contrib-10.14-1.module_el8.2. 801 kB/s | 804 kB 00:01
--------------------------------------------------------------------------------
Total 734 kB/s | 867 kB 00:01
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : uuid-1.6.2-42.el8.x86_64 1/2
Running scriptlet: uuid-1.6.2-42.el8.x86_64 1/2
Installing : postgresql-contrib-10.14-1.module_el8.2.0+487+53cc39 2/2
Running scriptlet: postgresql-contrib-10.14-1.module_el8.2.0+487+53cc39 2/2
Verifying : postgresql-contrib-10.14-1.module_el8.2.0+487+53cc39 1/2
Verifying : uuid-1.6.2-42.el8.x86_64 2/2
Installed:
postgresql-contrib-10.14-1.module_el8.2.0+487+53cc39ce.x86_64
uuid-1.6.2-42.el8.x86_64
Complete!
[root@localhost ~]# postgresql-setup initdb
WARNING: using obsoleted argument syntax, try --help
WARNING: arguments transformed to: postgresql-setup --initdb --unit postgresql
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
[root@localhost ~]# systemctl enable --now postgresql
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
[root@localhost ~]# postgres psql -c "SELECT version();"
"root" execution of the PostgreSQL server is not permitted.
The server must be started under an unprivileged user ID to prevent
possible system security compromise. See the documentation for
more information on how to properly start the server.
[root@localhost ~]# exit
logout
[malex@localhost /]$ sudo -u postgres psql -c "SELECT version();"
[sudo] password for malex:
version
--------------------------------------------------------------------------------
-----------------------------
PostgreSQL 10.14 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.3.1 201911
21 (Red Hat 8.3.1-5), 64-bit
(1 row)
# To install the default stream, PostgreSQL server version 10.0 type:
[malex@thermalite ~]$ sudo dnf install @postgresql:10
# You may also want to install the contrib package which provides several
# additional features for the PostgreSQL database system.
[malex@thermalite ~]$ sudo dnf install postgresql-contrib
# Once the installation is complete, initialize the PostgreSQL
# database with the following command:
[malex@thermalite ~]$ sudo postgresql-setup initdb
Initializing database ... OK
# Start the PostgreSQL service and enable it to start on boot:
[malex@thermalite ~] sudo systemctl enable --now postgresql
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql.service → /usr/lib/systemd/system/postgresql.service.
[malex@thermalite ~]$ sudo systemctl start postgresql
[malex@thermalite ~]$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2020-10-04 20:23:44 EDT; 29min ago
Process: 653663 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=0/SUCCESS)
Main PID: 653666 (postmaster)
Tasks: 8 (limit: 49100)
Memory: 19.5M
CGroup: /system.slice/postgresql.service
├─653666 /usr/bin/postmaster -D /var/lib/pgsql/data
├─653667 postgres: logger process
├─653669 postgres: checkpointer process
├─653670 postgres: writer process
├─653671 postgres: wal writer process
├─653672 postgres: autovacuum launcher process
├─653673 postgres: stats collector process
└─653674 postgres: bgworker: logical replication launcher
Oct 04 20:23:44 thermalite.localdomain systemd[1]: Starting PostgreSQL database server...
Oct 04 20:23:44 thermalite.localdomain postmaster[653666]: 2020-10-04 20:23:44.737 EDT [653666] LOG: listening on IPv4 address "0.0.0.0", port 5432
Oct 04 20:23:44 thermalite.localdomain postmaster[653666]: 2020-10-04 20:23:44.737 EDT [653666] LOG: listening on IPv6 address "::", port 5432
Oct 04 20:23:44 thermalite.localdomain postmaster[653666]: 2020-10-04 20:23:44.753 EDT [653666] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Oct 04 20:23:44 thermalite.localdomain postmaster[653666]: 2020-10-04 20:23:44.787 EDT [653666] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
Oct 04 20:23:44 thermalite.localdomain postmaster[653666]: 2020-10-04 20:23:44.809 EDT [653666] LOG: redirecting log output to logging collector process
Oct 04 20:23:44 thermalite.localdomain postmaster[653666]: 2020-10-04 20:23:44.809 EDT [653666] HINT: Future log output will appear in directory "log".
Oct 04 20:23:44 thermalite.localdomain systemd[1]: Started PostgreSQL database server.
[malex@localhost ~]$
# configure the firewall setting
[malex@thermalite ~]$ sudo firewall-cmd --add-service=postgresql --permanent
success
[malex@thermalite ~]$ sudo firewall-cmd --reload
success
# Verify the changes with the ss utility:
[malex@localhost ~]$ ss -nlt | grep 5432
LISTEN 0 128 0.0.0.0:5432 0.0.0.0:*
LISTEN 0 128 [::]:5432 [::]:*
[malex@localhost ~]$
# Use the psql tool to verify the installation by connecting to the
# PostgreSQL database server and print its version :
[malex@thermalite ~] sudo -u postgres psql -c "SELECT version();"
# To log in to the PostgreSQL server as the postgres user, first
# switch to the user and then access the PostgreSQL prompt using the psql utility:
[malex@thermalite ~] sudo su - postgres
# run psql locally
[postgres@thermalite ~]$ psql
psql (10.14)
Type "help" for help.
postgres=#
postgres=# \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)
postgres=# create database thermalitedb;
CREATE DATABASE
postgres=# \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
thermalitedb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
postgres=#
postgres=# \q
[postgres@thermalite ~]$
# creating PostgreSQL Role and Database #
CREATE USER dbuser WITH PASSWORD 'mypass';
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dbuser;
ALTER USER dbuser WITH PASSWORD 'mypass';
DROP DATABASE bedrock;
DROP DATABASE johndb;
DROP DATABASE ofincy;
SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
postgres=# \c thermalitedb
You are now connected to database "thermalitedb" as user "postgres".
thermalitedb=# \conninfo
You are connected to database "thermalitedb" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
thermalitedb=# \dt
Did not find any relations.
thermalitedb=# SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
(0 rows)
thermalitedb=# \q
[postgres@thermalite ~]$ exit
logout
[malex@thermalite ~]$ psql -d thermalitedb -U dbuser
psql (10.14)
Type "help" for help.
thermalitedb=> \conninfo
You are connected to database "thermalitedb" as user "dbuser" via socket in "/var/run/postgresql" at port "5432".
thermalitedb=> \q
[malex@thermalite ~]$ psql -h 192.168.1.80 -d thermalitedb -U dbuser
Password for user dbuser:
psql (10.14)
Type "help" for help.
thermalitedb=> \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
thermalitedb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
thermalitedb=>
Wednesday, August 12, 2020
How to Install PostgreSQL on CentOS 8
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment