Installation of PostgreSQL on Fedora
1. Pre-installation commands
1 2 3 | [root@localhost malex]# sudo dnf install -y vi [root@localhost malex]# sudo dnf update -y [root@localhost malex]# sudo reboot |
2. Install the reporpms for PostgreSQL
[root@localhost malex]# dnf install https://download.postgresql.org/pub/repos/yum/reporpms/F-28-x86_64/pgdg-fedora-repo-latest.noarch.rpm
Last metadata expiration check: 0:05:26 ago on Thu 13 Jun 2019 12:05:07 AM EDT.
pgdg-fedora-repo-latest.noarch.rpm 11 kB/s | 9.6 kB 00:00
Dependencies resolved.
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
pgdg-fedora-repo noarch 42.0-4 @commandline 9.6 k
Transaction Summary
================================================================================
Install 1 Package
Total size: 9.6 k
Installed size: 9.1 k
Is this ok [y/N]: y
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : pgdg-fedora-repo-42.0-4.noarch 1/1
Verifying : pgdg-fedora-repo-42.0-4.noarch 1/1
Installed:
pgdg-fedora-repo.noarch 42.0-4
Complete!
3. Install PostgreSQL client and server
[root@localhost malex]# sudo dnf install postgresql11-server postgresql11
PostgreSQL 11 28 - x86_64 48 kB/s | 233 kB 00:04
PostgreSQL 10 28 - x86_64 71 kB/s | 283 kB 00:03
PostgreSQL 9.6 28 - x86_64 12 kB/s | 276 kB 00:22
PostgreSQL 9.5 28 - x86_64 102 kB/s | 268 kB 00:02
PostgreSQL 9.4 28 - x86_64 47 kB/s | 220 kB 00:04
Last metadata expiration check: 0:00:00 ago on Thu 13 Jun 2019 12:13:49 AM EDT.
Dependencies resolved.
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
postgresql11-server x86_64 11.2-2PGDG.f28 pgdg11 4.9 M
Installing dependencies:
postgresql11 x86_64 11.2-2PGDG.f28 pgdg11 1.7 M
postgresql11-libs x86_64 11.2-2PGDG.f28 pgdg11 378 k
Transaction Summary
================================================================================
Install 3 Packages
Total download size: 6.9 M
Installed size: 29 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): postgresql11-libs-11.2-2PGDG.f28.x86_64. 13 kB/s | 378 kB 00:27
(2/3): postgresql11-11.2-2PGDG.f28.x86_64.rpm 34 kB/s | 1.7 MB 00:50
(3/3): postgresql11-server-11.2-2PGDG.f28.x86_6 77 kB/s | 4.9 MB 01:05
--------------------------------------------------------------------------------
Total 109 kB/s | 6.9 MB 01:05
warning: /var/cache/dnf/pgdg11-e93d07c77da33496/packages/postgresql11-server-11.2-2PGDG.f28.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Importing GPG key 0x442DF0F8:
Userid : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>"
Fingerprint: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]: y
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : postgresql11-libs-11.2-2PGDG.f28.x86_64 1/3
Running scriptlet: postgresql11-libs-11.2-2PGDG.f28.x86_64 1/3
Installing : postgresql11-11.2-2PGDG.f28.x86_64 2/3
Running scriptlet: postgresql11-11.2-2PGDG.f28.x86_64 2/3
Running scriptlet: postgresql11-server-11.2-2PGDG.f28.x86_64 3/3
Installing : postgresql11-server-11.2-2PGDG.f28.x86_64 3/3
Running scriptlet: postgresql11-server-11.2-2PGDG.f28.x86_64 3/3
Verifying : postgresql11-server-11.2-2PGDG.f28.x86_64 1/3
Verifying : postgresql11-11.2-2PGDG.f28.x86_64 2/3
Verifying : postgresql11-libs-11.2-2PGDG.f28.x86_64 3/3
Installed:
postgresql11-server.x86_64 11.2-2PGDG.f28 postgresql11.x86_64 11.2-2PGDG.f28
postgresql11-libs.x86_64 11.2-2PGDG.f28
Complete!
[root@localhost malex]#
4. Verify that PostgreSQL has been installed
[root@localhost malex]# rpm -qi postgresql11-server
Name : postgresql11-server
Version : 11.2
Release : 2PGDG.f28
Architecture: x86_64
Install Date: Thu 13 Jun 2019 12:15:46 AM EDT
Group : Applications/Databases
Size : 19893074
License : PostgreSQL
Signature : DSA/SHA1, Sat 23 Feb 2019 07:44:19 PM EST, Key ID 1f16d2e1442df0f8
Source RPM : postgresql11-11.2-2PGDG.f28.src.rpm
Build Date : Sat 23 Feb 2019 07:43:49 PM EST
Build Host : koji-fedora28-x86-64-pgbuild
Relocations : (not relocatable)
Vendor : PostgreSQL Global Development Group
URL : https://www.postgresql.org/
Summary : The programs needed to create and run a PostgreSQL server
Description :
PostgreSQL is an advanced Object-Relational database management system (DBMS).
The postgresql11-server package contains the programs needed to create
and run a PostgreSQL server, which will in turn allow you to create
and maintain PostgreSQL databases.
5. Initialize PostgreSQL database
[root@localhost malex]# sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
Initializing database ... OK
6. Start and enable the PostgreSQL service
[root@localhost malex]# sudo systemctl start postgresql-11
[root@localhost malex]# sudo systemctl enable postgresql-11
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-11.service → /usr/lib/systemd/system/postgresql-11.service.
7. Verify that the PostgreSQL service is running
[root@localhost malex]# sudo systemctl status postgresql-11
● postgresql-11.service - PostgreSQL 11 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; vend>
Active: active (running) since Thu 2019-06-13 00:19:50 EDT; 23s ago
Docs: https://www.postgresql.org/docs/11/static/
Main PID: 1911 (postmaster)
Tasks: 8 (limit: 4915)
Memory: 17.4M
CGroup: /system.slice/postgresql-11.service
├─1911 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
├─1912 postgres: logger
├─1914 postgres: checkpointer
├─1915 postgres: background writer
├─1916 postgres: walwriter
├─1917 postgres: autovacuum launcher
├─1918 postgres: stats collector
└─1919 postgres: logical replication launcher
Jun 13 00:19:50 localhost.localdomain systemd[1]: Starting PostgreSQL 11 databa>
Jun 13 00:19:50 localhost.localdomain postmaster[1911]: 2019-06-13 00:19:50.491>
Jun 13 00:19:50 localhost.localdomain postmaster[1911]: 2019-06-13 00:19:50.491>
Jun 13 00:19:50 localhost.localdomain postmaster[1911]: 2019-06-13 00:19:50.532>
Jun 13 00:19:50 localhost.localdomain postmaster[1911]: 2019-06-13 00:19:50.548>
Jun 13 00:19:50 localhost.localdomain postmaster[1911]: 2019-06-13 00:19:50.577>
Jun 13 00:19:50 localhost.localdomain postmaster[1911]: 2019-06-13 00:19:50.577>
Jun 13 00:19:50 localhost.localdomain systemd[1]: Started PostgreSQL 11 databas>
8. Configure the firewall setting
[root@localhost malex]# sudo firewall-cmd --add-service=postgresql --permanent
success
[root@localhost malex]# sudo firewall-cmd --reload
success
9. Connect to the PostgreSQL database using the postgres account on psql
[malex@thermalite ~]$ sudo su - postgres
postgres@thermalite$ psql
psql (11.2)
Type "help" for help.
postgres=#
10. Locate the configuration files
postgres=# show hba_file ;
hba_file
------------------------------------
/var/lib/pgsql/11/data/pg_hba.conf
(1 row)
postgres=# show ident_file ;
ident_file
--------------------------------------
/var/lib/pgsql/11/data/pg_ident.conf
(1 row)
postgres=# show config_file ;
config_file
----------------------------------------
/var/lib/pgsql/11/data/postgresql.conf
(1 row)
postgres=#
postgres=#\q
postgres@thermalite$ exit
11. Update PostgreSQL configuration files
[malex@thermalite ~]$ sudo su -
[root@localhost malex]#
11.1. Update /var/lib/pgsql/11/data/postgresql.conf
[root@localhost malex]# vi /var/lib/pgsql/11/data/postgresql.conf
Update the following:
=====================
1. Change 'localhost' to '*'
#listen_addresses = 'localhost' # what IP address(es) to listen on (old setting);
listen_addresses = '*' # what IP address(es) to listen on (new setting);
2. Uncomment the port
#port = 5432 # (change requires restart)
port = 5432 # (change requires restart)
11.2. Update /var/lib/pgsql/11/data/pg_hba.conf
[root@localhost malex]# vi /var/lib/pgsql/11/data/pg_hba.conf
Update the following:
=====================
1. Change 'peer' to 'trust'
# "local" is for Unix domain socket connections only
#local all all peer
local all all trust
2. Change 'peer' to 'trust'
# IPv4 local connections:
#host all all 127.0.0.1/32 ident
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
3. Add this lines
# Accept from anywhere
host all all 0.0.0.0/0 md5
host all all ::/0 md5
# Accept from trusted subnet
host all all 192.168.18.0/24 md5
# Allow users from 192.168.x.x hosts to connect to any database, if
# they pass the ident check. If, for example, ident says the user is
# "malex" and he requests to connect as PostgreSQL user "dbuser", the
# connection is allowed if there is an entry in pg_ident.conf for map
# "thermalite" that says "malex" is allowed to connect as "dbuser".
#
# TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.0.0/16 ident map=thermalite
11.3. Update /var/lib/pgsql/11/data/pg_ident.conf
[root@localhost malex]# vi /var/lib/pgsql/11/data/pg_ident.conf
# Put your actual configuration here
# ----------------------------------
# MAPNAME SYSTEM-USERNAME PG-USERNAME
thermalite malex dbuser
12. Restart the PostgreSQL service
[root@localhost malex]# sudo systemctl restart postgresql-11
[root@localhost malex]# sudo systemctl status postgresql-11
● postgresql-11.service - PostgreSQL 11 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; vendor preset: disabled)
Active: active (running) since Sat 2019-08-03 12:42:04 EDT; 16s ago
Docs: https://www.postgresql.org/docs/11/static/
Process: 2417 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 2422 (postmaster)
Tasks: 8 (limit: 4915)
Memory: 17.6M
CGroup: /system.slice/postgresql-11.service
├─2422 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
├─2423 postgres: logger
├─2425 postgres: checkpointer
├─2426 postgres: background writer
├─2427 postgres: walwriter
├─2428 postgres: autovacuum launcher
├─2429 postgres: stats collector
└─2430 postgres: logical replication launcher
Aug 03 12:42:03 thermalite systemd[1]: Starting PostgreSQL 11 database server...
Aug 03 12:42:03 thermalite postmaster[2422]: 2019-08-03 12:42:03.915 EDT [2422] LOG: listening on IPv4 address "0.0.0.>
Aug 03 12:42:03 thermalite postmaster[2422]: 2019-08-03 12:42:03.915 EDT [2422] LOG: listening on IPv6 address "::", p>
Aug 03 12:42:03 thermalite postmaster[2422]: 2019-08-03 12:42:03.931 EDT [2422] LOG: listening on Unix socket "/var/ru>
Aug 03 12:42:03 thermalite postmaster[2422]: 2019-08-03 12:42:03.947 EDT [2422] LOG: listening on Unix socket "/tmp/.s>
Aug 03 12:42:03 thermalite postmaster[2422]: 2019-08-03 12:42:03.974 EDT [2422] LOG: redirecting log output to logging>
Aug 03 12:42:03 thermalite postmaster[2422]: 2019-08-03 12:42:03.974 EDT [2422] HINT: Future log output will appear in>
Aug 03 12:42:04 thermalite systemd[1]: Started PostgreSQL 11 database server.
[malex@thermalite ~]$
[malex@thermalite ~]$ sudo -u postgres psql
psql (11.2)
Type "help" for help.
postgres=#
postgres=# create database mydatabase;
postgres=# create user dbuser with encrypted password 'dbpassword';
postgres=# grant all privileges on database mydatabase to dbuser;
GRANT
postgres=# grant all privileges on database mydatabase to dbuser;
ALTER USER dbuser WITH PASSWORD 'dbpassword';
postgres@thermalite$ psql
psql (11.2)
Type "help" for help.
postgres=# quit
postgres@thermalite$ psql mydatabase dbuser;
psql (11.2)
Type "help" for help.
mydatabase=>
mydatabase=> \conninfo
You are connected to database "mydatabase" as user "dbuser" via socket in "/var/run/postgresql" at port "5432".
mydatabase=> \dt
List of relations
Schema | Name | Type | Owner
--------+----------------+-------+----------
public | contact | table | dbuser
public | customer | table | dbuser
public | hc_device_user | table | dbuser
public | quote | table | dbuser
public | stock | table | dbuser
(5 rows)
mydatabase=> SELECT
mydatabase-> *
mydatabase-> FROM
mydatabase-> pg_catalog.pg_tables
mydatabase-> WHERE
mydatabase-> schemaname != 'pg_catalog'
mydatabase-> AND schemaname != 'information_schema';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+----------------+------------+------------+------------+----------+-------------+-------------
public | hc_device_user | dbuser | | t | f | f | f
public | quote | dbuser | | t | f | f | f
public | contact | dbuser | | t | f | f | f
public | stock | dbuser | | t | f | f | f
public | customer | dbuser | | t | f | f | f
(5 rows)
mydatabase=> \q
[root@thermalite ~]# psql -d mydatabase -U dbuser
psql (11.2)
Type "help" for help.
mydatabase=>
mydatabase=> \conninfo
You are connected to database "mydatabase" as user "dbuser" via socket in "/var/run/postgresql" at port "5432".
mydatabase=>
mydatabase=> \q
[root@thermalite ~]# psql -h 192.168.1.100 -d mydatabase -U dbuser
Password for user dbuser:
psql (11.2)
Type "help" for help.
mydatabase=>
No comments:
Post a Comment