March 2024
M T W T F S S
 123
45678910
11121314151617
18192021222324
25262728293031

Categories

March 2024
M T W T F S S
 123
45678910
11121314151617
18192021222324
25262728293031

Install and Configure PostgreSQL 10 on Fedora 27

add software source
rmohan.com@fedora1 ~ $ sudo dnf install https://download.postgresql.org/pub/repos/yum/10/fedora/fedora-27-x86_64/pgdg-fedora10-10-3.noarch.rpm
Last metadata expiration check: 7:30:40 ago on Tue 02 Jan 2018 10:32:40 AM CST.
pgdg-fedora10-10-3.noarch.rpm 6.9 kB/s | 8.8 kB 00:01
Dependencies resolved.
==============================================================================================================================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================================================================================================================
Installing:
pgdg-fedora10 noarch 10-3 @commandline 8.8 k

Transaction Summary
==============================================================================================================================================================================================================================================
Install 1 Package

Total size: 8.8 k
Installed size: 3.2 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-fedora10-10-3.noarch 1/1
Verifying : pgdg-fedora10-10-3.noarch 1/1

Installed:
pgdg-fedora10.noarch 10-3

Complete!
1.2, install the server and client
rmohan.com@fedora1 ~ $ sudo dnf install postgresql10-server postgresql10
PostgreSQL 10 27 – x86_64 76 kB/s | 164 kB 00:02
Last metadata expiration check: 0:00:00 ago on Tue 02 Jan 2018 06:03:33 PM CST.
Dependencies resolved.
==============================================================================================================================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================================================================================================================
Installing:
postgresql10 x86_64 10.1-1PGDG.f27 pgdg10 1.5 M
postgresql10-server x86_64 10.1-1PGDG.f27 pgdg10 4.4 M
Installing dependencies:
libicu x86_64 57.1-9.fc27 updates 8.4 M
postgresql10-libs x86_64 10.1-1PGDG.f27 pgdg10 354 k

Transaction Summary
==============================================================================================================================================================================================================================================
Install 4 Packages

Total download size: 15 M
Installed size: 54 M
Is this ok [y/N]: y
Downloading Packages:
(1/4): postgresql10-10.1-1PGDG.f27.x86_64.rpm 203 kB/s | 1.5 MB 00:07
(2/4): libicu-57.1-9.fc27.x86_64.rpm 3.8 MB/s | 8.4 MB 00:02
(3/4): postgresql10-libs-10.1-1PGDG.f27.x86_64.rpm 36 kB/s | 354 kB 00:09
(4/4): postgresql10-server-10.1-1PGDG.f27.x86_64.rpm 138 kB/s | 4.4 MB 00:32
———————————————————————————————————————————————————————————————————————————————-
Total 460 kB/s | 15 MB 00:32
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : libicu-57.1-9.fc27.x86_64 1/4
Running scriptlet: libicu-57.1-9.fc27.x86_64 1/4
Installing : postgresql10-libs-10.1-1PGDG.f27.x86_64 2/4
Running scriptlet: postgresql10-libs-10.1-1PGDG.f27.x86_64 2/4
Installing : postgresql10-10.1-1PGDG.f27.x86_64 3/4
Running scriptlet: postgresql10-10.1-1PGDG.f27.x86_64 3/4
Running scriptlet: postgresql10-server-10.1-1PGDG.f27.x86_64 4/4
Installing : postgresql10-server-10.1-1PGDG.f27.x86_64 4/4
Running scriptlet: postgresql10-server-10.1-1PGDG.f27.x86_64 4/4
Verifying : postgresql10-server-10.1-1PGDG.f27.x86_64 1/4
Verifying : postgresql10-10.1-1PGDG.f27.x86_64 2/4
Verifying : postgresql10-libs-10.1-1PGDG.f27.x86_64 3/4
Verifying : libicu-57.1-9.fc27.x86_64 4/4

Installed:
postgresql10.x86_64 10.1-1PGDG.f27 postgresql10-server.x86_64 10.1-1PGDG.f27 libicu.x86_64 57.1-9.fc27 postgresql10-libs.x86_64 10.1-1PGDG.f27

Complete!
What we have to say here is that dnf and yum are similar, and they are more efficient than yum in performance. This is also the mainstream package management tool for
RedHat ‘s distribution suites.

1.3, initialization
rmohan.com@fedora1 ~ $ sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
Initializing database … OK

rmohan.com@fedora1 ~ $ sudo systemctl enable postgresql-10.service
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-10.service ? /usr/lib/systemd/system/postgresql-10.service.
rmohan.com@fedora1 ~ $ sudo systemctl start postgresql-10.service
rmohan.com@fedora1 ~ $ sudo systemctl status postgresql-10.service
? postgresql-10.service – PostgreSQL 10 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-10.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2018-01-02 18:07:03 CST; 12s ago
Docs: https://www.postgresql.org/docs/10/static/
Process: 4654 ExecStartPre=/usr/pgsql-10/bin/postgresql-10-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 4659 (postmaster)
Tasks: 8 (limit: 4915)
CGroup: /system.slice/postgresql-10.service
??4659 /usr/pgsql-10/bin/postmaster -D /var/lib/pgsql/10/data/
??4660 postgres: logger process
??4662 postgres: checkpointer process
??4663 postgres: writer process
??4664 postgres: wal writer process
??4665 postgres: autovacuum launcher process
??4666 postgres: stats collector process
??4667 postgres: bgworker: logical replication launcher

Jan 02 18:07:03 fedora1 systemd[1]: Starting PostgreSQL 10 database server…
Jan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.166 CST [4659] LOG: listening on IPv6 address “::1”, port 5432
Jan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.166 CST [4659] LOG: listening on IPv4 address “127.0.0.1”, port 5432
Jan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.168 CST [4659] LOG: listening on Unix socket “/var/run/postgresql/.s.PGSQL.5432”
Jan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.170 CST [4659] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432”
Jan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.176 CST [4659] LOG: redirecting log output to logging collector process
Jan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.176 CST [4659] HINT: Future log output will appear in directory “log”.
Jan 02 18:07:03 fedora1 systemd[1]: Started PostgreSQL 10 database server.
1.4, local access
postgres@fedora1 ~ $ psql
psql (10.1)
Type “help” for help.

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=#
2, configuration
We know that under normal circumstances, you need to access the postgresql service on a host other than the host. However, by default, postgresql only provides local access. To allow other hosts to access, you need to configure the following.

2.1. Open support for non-local visits.
Postgresql configuration files in fedora’s distribution suite are mainly in the data directory, ie /var/lib/pgsql/10/data/

postgres@fedora1 ~ $ psql
psql (10.1)
Type “help” for help.

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=# \q
postgres@fedora1 ~ $ ll /var/lib/pgsql/10/data/
total 136
drwx——. 20 postgres postgres 4096 Jan 2 18:07 .
drwx——. 4 postgres postgres 4096 Jan 2 18:06 ..
drwx——. 5 postgres postgres 4096 Jan 2 18:06 base
-rw——-. 1 postgres postgres 30 Jan 2 18:07 current_logfiles
drwx——. 2 postgres postgres 4096 Jan 2 18:08 global
drwx——. 2 postgres postgres 4096 Jan 2 18:07 log
drwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_commit_ts
drwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_dynshmem
-rw——-. 1 postgres postgres 4269 Jan 2 18:06 pg_hba.conf
-rw——-. 1 postgres postgres 1636 Jan 2 18:06 pg_ident.conf
drwx——. 4 postgres postgres 4096 Jan 2 18:12 pg_logical
drwx——. 4 postgres postgres 4096 Jan 2 18:06 pg_multixact
drwx——. 2 postgres postgres 4096 Jan 2 18:07 pg_notify
drwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_replslot
drwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_serial
drwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_snapshots
drwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_stat
drwx——. 2 postgres postgres 4096 Jan 2 18:21 pg_stat_tmp
drwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_subtrans
drwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_tblspc
drwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_twophase
-rw——-. 1 postgres postgres 3 Jan 2 18:06 PG_VERSION
drwx——. 3 postgres postgres 4096 Jan 2 18:06 pg_wal
drwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_xact
-rw——-. 1 postgres postgres 88 Jan 2 18:06 postgresql.auto.conf
-rw——-. 1 postgres postgres 22761 Jan 2 18:06 postgresql.conf
-rw——-. 1 postgres postgres 58 Jan 2 18:07 postmaster.opts
-rw——-. 1 postgres postgres 103 Jan 2 18:07 postmaster.pid
postgres@fedora1 ~ $
We first modify the configuration postgresql.conf, open the restrictions on non-host access, open the file with vim,

59 #listen_addresses = ‘localhost’ # what IP address(es) to listen on;
???
59 #listen_addresses = ‘*’ # what IP address(es) to listen on;

Then use vim to open the file pg_hba.conf, find 82 lines

82 host all all 127.0.0.1/32 ident
Add later

83 host all all 192.168.1.0/24 trust
At this point, restart the postgresql database.

postgres@fedora1 ~ $ vim /var/lib/pgsql/10/data/postgresql.conf
postgres@fedora1 ~ $ vim /var/lib/pgsql/10/data/pg_hba.conf
postgres@fedora1 ~ $ exit
logout
rmohan.com@fedora1 ~ $ sudo systemctl start postgresql-10.service
[sudo] password for rmohan.com:
lwk@fedora1 ~ $ sudo systemctl status postgresql-10.service
? postgresql-10.service – PostgreSQL 10 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-10.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2018-01-02 18:07:03 CST; 29min ago
Docs: https://www.postgresql.org/docs/10/static/
Process: 4654 ExecStartPre=/usr/pgsql-10/bin/postgresql-10-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 4659 (postmaster)
Tasks: 8 (limit: 4915)
CGroup: /system.slice/postgresql-10.service
??4659 /usr/pgsql-10/bin/postmaster -D /var/lib/pgsql/10/data/
??4660 postgres: logger process
??4662 postgres: checkpointer process
??4663 postgres: writer process
??4664 postgres: wal writer process
??4665 postgres: autovacuum launcher process
??4666 postgres: stats collector process
??4667 postgres: bgworker: logical replication launcher

Jan 02 18:07:03 fedora1 systemd[1]: Starting PostgreSQL 10 database server…
Jan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.166 CST [4659] LOG: listening on IPv6 address “::1”, port 5432
Jan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.166 CST [4659] LOG: listening on IPv4 address “127.0.0.1”, port 5432
Jan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.168 CST [4659] LOG: listening on Unix socket “/var/run/postgresql/.s.PGSQL.5432”
Jan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.170 CST [4659] LOG: listening on Unix socket “/tmp/.s.PGSQL.5432”
Jan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.176 CST [4659] LOG: redirecting log output to logging collector process
Jan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.176 CST [4659] HINT: Future log output will appear in directory “log”.
Jan 02 18:07:03 fedora1 systemd[1]: Started PostgreSQL 10 database server.
rmohan.com@fedora1 ~ $
2.1. Modify the firewall configuration.
Modifying the firewall configuration will add port number 5432 to the firewall whitelist. There are many ways to use ufw

rmohan.com@fedora1 ~ $ dnf list ufw
Last metadata expiration check: 0:00:36 ago on Tue 02 Jan 2018 06:41:47 PM CST.
Available Packages
ufw.noarch 0.35-9.fc27 fedora
rmohan.com@fedora1 ~ $ sudo dnf install ufw
Last metadata expiration check: 0:39:13 ago on Tue 02 Jan 2018 06:03:33 PM CST.
Dependencies resolved.
==============================================================================================================================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================================================================================================================
Installing:
ufw noarch 0.35-9.fc27 fedora 222 k

Transaction Summary
==============================================================================================================================================================================================================================================
Install 1 Package

Total download size: 222 k
Installed size: 978 k
Is this ok [y/N]: y
Downloading Packages:
ufw-0.35-9.fc27.noarch.rpm 99 kB/s | 222 kB 00:02
———————————————————————————————————————————————————————————————————————————————-
Total 98 kB/s | 222 kB 00:02
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : ufw-0.35-9.fc27.noarch 1/1
Running scriptlet: ufw-0.35-9.fc27.noarch 1/1
Running as unit: run-rcf2b3a65bf7d43b78a6d1e515b174178.service
Verifying : ufw-0.35-9.fc27.noarch 1/1

Installed:
ufw.noarch 0.35-9.fc27

Complete!
rmohan.com@fedora1 ~ $
rmohan.com@fedora1 ~ $ sudo ufw status
Status: inactive
rmohan.com@fedora1 ~ $ sudo ufw enable
Command may disrupt existing ssh connections. Proceed with operation (y|n)? y
Firewall is active and enabled on system startup
rmohan.com@fedora1 ~ $ sudo ufw status
Status: active

To Action From
— —— —-
SSH ALLOW Anywhere
224.0.0.251 mDNS ALLOW Anywhere
SSH (v6) ALLOW Anywhere (v6)
ff02::fb mDNS ALLOW Anywhere (v6)

rmohan.com@fedora1 ~ $ sudo ufw allow 5432
Rule added
Rule added (v6)
rmohan.com@fedora1 ~ $ sudo ufw default deny
Default incoming policy changed to ‘deny’
(be sure to update your rules accordingly)
rmohan.com@fedora1 ~ $ sudo systemctl enable ufw.service
Created symlink /etc/systemd/system/basic.target.wants/ufw.service ? /usr/lib/systemd/system/ufw.service.
rmohan.com@fedora1 ~ $ sudo systemctl restart ufw.service
rmohan.com@fedora1 ~ $ sudo systemctl status ufw.service
? ufw.service – Uncomplicated firewall
Loaded: loaded (/usr/lib/systemd/system/ufw.service; enabled; vendor preset: disabled)
Active: active (exited) since Tue 2018-01-02 18:47:35 CST; 13s ago
Docs: man:ufw(8)
man:ufw-framework(8)
file://usr/share/doc/ufw/README
Process: 6171 ExecStart=/usr/libexec/ufw/ufw-init start (code=exited, status=0/SUCCESS)
Main PID: 6171 (code=exited, status=0/SUCCESS)

Jan 02 18:47:34 fedora1 systemd[1]: Starting Uncomplicated firewall…
Jan 02 18:47:35 fedora1 systemd[1]: Started Uncomplicated firewall.
rmohan.com@fedora1 ~ $ sudo ufw status
Status: active

To Action From
— —— —-
SSH ALLOW Anywhere
224.0.0.251 mDNS ALLOW Anywhere
5432 ALLOW Anywhere
SSH (v6) ALLOW Anywhere (v6)
ff02::fb mDNS ALLOW Anywhere (v6)
5432 (v6) ALLOW Anywhere (v6)

rmohan.com@fedora1 ~ $

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>