add software source
\nrmohan.com@fedora1 ~ $ sudo dnf install https:\/\/download.postgresql.org\/pub\/repos\/yum\/10\/fedora\/fedora-27-x86_64\/pgdg-fedora10-10-3.noarch.rpm
\nLast metadata expiration check: 7:30:40 ago on Tue 02 Jan 2018 10:32:40 AM CST.
\npgdg-fedora10-10-3.noarch.rpm 6.9 kB\/s | 8.8 kB 00:01
\nDependencies resolved.
\n==============================================================================================================================================================================================================================================
\nPackage Arch Version Repository Size
\n==============================================================================================================================================================================================================================================
\nInstalling:
\npgdg-fedora10 noarch 10-3 @commandline 8.8 k<\/p>\n
Transaction Summary
\n==============================================================================================================================================================================================================================================
\nInstall 1 Package<\/p>\n
Total size: 8.8 k
\nInstalled size: 3.2 k
\nIs this ok [y\/N]: y
\nDownloading Packages:
\nRunning transaction check
\nTransaction check succeeded.
\nRunning transaction test
\nTransaction test succeeded.
\nRunning transaction
\nPreparing : 1\/1
\nInstalling : pgdg-fedora10-10-3.noarch 1\/1
\nVerifying : pgdg-fedora10-10-3.noarch 1\/1<\/p>\n
Installed:
\npgdg-fedora10.noarch 10-3<\/p>\n
Complete!
\n1.2, install the server and client
\nrmohan.com@fedora1 ~ $ sudo dnf install postgresql10-server postgresql10
\nPostgreSQL 10 27 – x86_64 76 kB\/s | 164 kB 00:02
\nLast metadata expiration check: 0:00:00 ago on Tue 02 Jan 2018 06:03:33 PM CST.
\nDependencies resolved.
\n==============================================================================================================================================================================================================================================
\nPackage Arch Version Repository Size
\n==============================================================================================================================================================================================================================================
\nInstalling:
\npostgresql10 x86_64 10.1-1PGDG.f27 pgdg10 1.5 M
\npostgresql10-server x86_64 10.1-1PGDG.f27 pgdg10 4.4 M
\nInstalling dependencies:
\nlibicu x86_64 57.1-9.fc27 updates 8.4 M
\npostgresql10-libs x86_64 10.1-1PGDG.f27 pgdg10 354 k<\/p>\n
Transaction Summary
\n==============================================================================================================================================================================================================================================
\nInstall 4 Packages<\/p>\n
Total download size: 15 M
\nInstalled size: 54 M
\nIs this ok [y\/N]: y
\nDownloading Packages:
\n(1\/4): postgresql10-10.1-1PGDG.f27.x86_64.rpm 203 kB\/s | 1.5 MB 00:07
\n(2\/4): libicu-57.1-9.fc27.x86_64.rpm 3.8 MB\/s | 8.4 MB 00:02
\n(3\/4): postgresql10-libs-10.1-1PGDG.f27.x86_64.rpm 36 kB\/s | 354 kB 00:09
\n(4\/4): postgresql10-server-10.1-1PGDG.f27.x86_64.rpm 138 kB\/s | 4.4 MB 00:32
\n———————————————————————————————————————————————————————————————————————————————-
\nTotal 460 kB\/s | 15 MB 00:32
\nRunning transaction check
\nTransaction check succeeded.
\nRunning transaction test
\nTransaction test succeeded.
\nRunning transaction
\nPreparing : 1\/1
\nInstalling : libicu-57.1-9.fc27.x86_64 1\/4
\nRunning scriptlet: libicu-57.1-9.fc27.x86_64 1\/4
\nInstalling : postgresql10-libs-10.1-1PGDG.f27.x86_64 2\/4
\nRunning scriptlet: postgresql10-libs-10.1-1PGDG.f27.x86_64 2\/4
\nInstalling : postgresql10-10.1-1PGDG.f27.x86_64 3\/4
\nRunning scriptlet: postgresql10-10.1-1PGDG.f27.x86_64 3\/4
\nRunning scriptlet: postgresql10-server-10.1-1PGDG.f27.x86_64 4\/4
\nInstalling : postgresql10-server-10.1-1PGDG.f27.x86_64 4\/4
\nRunning scriptlet: postgresql10-server-10.1-1PGDG.f27.x86_64 4\/4
\nVerifying : postgresql10-server-10.1-1PGDG.f27.x86_64 1\/4
\nVerifying : postgresql10-10.1-1PGDG.f27.x86_64 2\/4
\nVerifying : postgresql10-libs-10.1-1PGDG.f27.x86_64 3\/4
\nVerifying : libicu-57.1-9.fc27.x86_64 4\/4<\/p>\n
Installed:
\npostgresql10.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<\/p>\n
Complete!
\nWhat 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
\nRedHat ‘s distribution suites.<\/p>\n
1.3, initialization
\nrmohan.com@fedora1 ~ $ sudo \/usr\/pgsql-10\/bin\/postgresql-10-setup initdb
\nInitializing database … OK<\/p>\n
rmohan.com@fedora1 ~ $ sudo systemctl enable postgresql-10.service
\nCreated symlink \/etc\/systemd\/system\/multi-user.target.wants\/postgresql-10.service ? \/usr\/lib\/systemd\/system\/postgresql-10.service.
\nrmohan.com@fedora1 ~ $ sudo systemctl start postgresql-10.service
\nrmohan.com@fedora1 ~ $ sudo systemctl status postgresql-10.service
\n? postgresql-10.service – PostgreSQL 10 database server
\nLoaded: loaded (\/usr\/lib\/systemd\/system\/postgresql-10.service; enabled; vendor preset: disabled)
\nActive: active (running) since Tue 2018-01-02 18:07:03 CST; 12s ago
\nDocs: https:\/\/www.postgresql.org\/docs\/10\/static\/
\nProcess: 4654 ExecStartPre=\/usr\/pgsql-10\/bin\/postgresql-10-check-db-dir ${PGDATA} (code=exited, status=0\/SUCCESS)
\nMain PID: 4659 (postmaster)
\nTasks: 8 (limit: 4915)
\nCGroup: \/system.slice\/postgresql-10.service
\n??4659 \/usr\/pgsql-10\/bin\/postmaster -D \/var\/lib\/pgsql\/10\/data\/
\n??4660 postgres: logger process
\n??4662 postgres: checkpointer process
\n??4663 postgres: writer process
\n??4664 postgres: wal writer process
\n??4665 postgres: autovacuum launcher process
\n??4666 postgres: stats collector process
\n??4667 postgres: bgworker: logical replication launcher<\/p>\n
Jan 02 18:07:03 fedora1 systemd[1]: Starting PostgreSQL 10 database server…
\nJan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.166 CST [4659] LOG: listening on IPv6 address “::1”, port 5432
\nJan 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
\nJan 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”
\nJan 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”
\nJan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.176 CST [4659] LOG: redirecting log output to logging collector process
\nJan 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”.
\nJan 02 18:07:03 fedora1 systemd[1]: Started PostgreSQL 10 database server.
\n1.4, local access
\npostgres@fedora1 ~ $ psql
\npsql (10.1)
\nType “help” for help.<\/p>\n
postgres=# \\l
\nList of databases
\nName | Owner | Encoding | Collate | Ctype | Access privileges
\n———–+———-+———-+————-+————-+———————–
\npostgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
\ntemplate0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres +
\n| | | | | postgres=CTc\/postgres
\ntemplate1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres +
\n| | | | | postgres=CTc\/postgres
\n(3 rows)<\/p>\n
postgres=#
\n2, configuration
\nWe 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.<\/p>\n
2.1. Open support for non-local visits.
\nPostgresql configuration files in fedora’s distribution suite are mainly in the data directory, ie \/var\/lib\/pgsql\/10\/data\/<\/p>\n
postgres@fedora1 ~ $ psql
\npsql (10.1)
\nType “help” for help.<\/p>\n
postgres=# \\l
\nList of databases
\nName | Owner | Encoding | Collate | Ctype | Access privileges
\n———–+———-+———-+————-+————-+———————–
\npostgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
\ntemplate0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres +
\n| | | | | postgres=CTc\/postgres
\ntemplate1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c\/postgres +
\n| | | | | postgres=CTc\/postgres
\n(3 rows)<\/p>\n
postgres=# \\q
\npostgres@fedora1 ~ $ ll \/var\/lib\/pgsql\/10\/data\/
\ntotal 136
\ndrwx——. 20 postgres postgres 4096 Jan 2 18:07 .
\ndrwx——. 4 postgres postgres 4096 Jan 2 18:06 ..
\ndrwx——. 5 postgres postgres 4096 Jan 2 18:06 base
\n-rw——-. 1 postgres postgres 30 Jan 2 18:07 current_logfiles
\ndrwx——. 2 postgres postgres 4096 Jan 2 18:08 global
\ndrwx——. 2 postgres postgres 4096 Jan 2 18:07 log
\ndrwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_commit_ts
\ndrwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_dynshmem
\n-rw——-. 1 postgres postgres 4269 Jan 2 18:06 pg_hba.conf
\n-rw——-. 1 postgres postgres 1636 Jan 2 18:06 pg_ident.conf
\ndrwx——. 4 postgres postgres 4096 Jan 2 18:12 pg_logical
\ndrwx——. 4 postgres postgres 4096 Jan 2 18:06 pg_multixact
\ndrwx——. 2 postgres postgres 4096 Jan 2 18:07 pg_notify
\ndrwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_replslot
\ndrwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_serial
\ndrwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_snapshots
\ndrwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_stat
\ndrwx——. 2 postgres postgres 4096 Jan 2 18:21 pg_stat_tmp
\ndrwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_subtrans
\ndrwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_tblspc
\ndrwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_twophase
\n-rw——-. 1 postgres postgres 3 Jan 2 18:06 PG_VERSION
\ndrwx——. 3 postgres postgres 4096 Jan 2 18:06 pg_wal
\ndrwx——. 2 postgres postgres 4096 Jan 2 18:06 pg_xact
\n-rw——-. 1 postgres postgres 88 Jan 2 18:06 postgresql.auto.conf
\n-rw——-. 1 postgres postgres 22761 Jan 2 18:06 postgresql.conf
\n-rw——-. 1 postgres postgres 58 Jan 2 18:07 postmaster.opts
\n-rw——-. 1 postgres postgres 103 Jan 2 18:07 postmaster.pid
\npostgres@fedora1 ~ $
\nWe first modify the configuration postgresql.conf, open the restrictions on non-host access, open the file with vim,<\/p>\n
59 #listen_addresses = ‘localhost’ # what IP address(es) to listen on;
\n???
\n59 #listen_addresses = ‘*’ # what IP address(es) to listen on;<\/p>\n
Then use vim to open the file pg_hba.conf, find 82 lines<\/p>\n
82 host all all 127.0.0.1\/32 ident
\nAdd later<\/p>\n
83 host all all 192.168.1.0\/24 trust
\nAt this point, restart the postgresql database.<\/p>\n
postgres@fedora1 ~ $ vim \/var\/lib\/pgsql\/10\/data\/postgresql.conf
\npostgres@fedora1 ~ $ vim \/var\/lib\/pgsql\/10\/data\/pg_hba.conf
\npostgres@fedora1 ~ $ exit
\nlogout
\nrmohan.com@fedora1 ~ $ sudo systemctl start postgresql-10.service
\n[sudo] password for rmohan.com:
\nlwk@fedora1 ~ $ sudo systemctl status postgresql-10.service
\n? postgresql-10.service – PostgreSQL 10 database server
\nLoaded: loaded (\/usr\/lib\/systemd\/system\/postgresql-10.service; enabled; vendor preset: disabled)
\nActive: active (running) since Tue 2018-01-02 18:07:03 CST; 29min ago
\nDocs: https:\/\/www.postgresql.org\/docs\/10\/static\/
\nProcess: 4654 ExecStartPre=\/usr\/pgsql-10\/bin\/postgresql-10-check-db-dir ${PGDATA} (code=exited, status=0\/SUCCESS)
\nMain PID: 4659 (postmaster)
\nTasks: 8 (limit: 4915)
\nCGroup: \/system.slice\/postgresql-10.service
\n??4659 \/usr\/pgsql-10\/bin\/postmaster -D \/var\/lib\/pgsql\/10\/data\/
\n??4660 postgres: logger process
\n??4662 postgres: checkpointer process
\n??4663 postgres: writer process
\n??4664 postgres: wal writer process
\n??4665 postgres: autovacuum launcher process
\n??4666 postgres: stats collector process
\n??4667 postgres: bgworker: logical replication launcher<\/p>\n
Jan 02 18:07:03 fedora1 systemd[1]: Starting PostgreSQL 10 database server…
\nJan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.166 CST [4659] LOG: listening on IPv6 address “::1”, port 5432
\nJan 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
\nJan 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”
\nJan 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”
\nJan 02 18:07:03 fedora1 postmaster[4659]: 2018-01-02 18:07:03.176 CST [4659] LOG: redirecting log output to logging collector process
\nJan 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”.
\nJan 02 18:07:03 fedora1 systemd[1]: Started PostgreSQL 10 database server.
\nrmohan.com@fedora1 ~ $
\n2.1. Modify the firewall configuration.
\nModifying the firewall configuration will add port number 5432 to the firewall whitelist. There are many ways to use ufw<\/p>\n
rmohan.com@fedora1 ~ $ dnf list ufw
\nLast metadata expiration check: 0:00:36 ago on Tue 02 Jan 2018 06:41:47 PM CST.
\nAvailable Packages
\nufw.noarch 0.35-9.fc27 fedora
\nrmohan.com@fedora1 ~ $ sudo dnf install ufw
\nLast metadata expiration check: 0:39:13 ago on Tue 02 Jan 2018 06:03:33 PM CST.
\nDependencies resolved.
\n==============================================================================================================================================================================================================================================
\nPackage Arch Version Repository Size
\n==============================================================================================================================================================================================================================================
\nInstalling:
\nufw noarch 0.35-9.fc27 fedora 222 k<\/p>\n
Transaction Summary
\n==============================================================================================================================================================================================================================================
\nInstall 1 Package<\/p>\n
Total download size: 222 k
\nInstalled size: 978 k
\nIs this ok [y\/N]: y
\nDownloading Packages:
\nufw-0.35-9.fc27.noarch.rpm 99 kB\/s | 222 kB 00:02
\n———————————————————————————————————————————————————————————————————————————————-
\nTotal 98 kB\/s | 222 kB 00:02
\nRunning transaction check
\nTransaction check succeeded.
\nRunning transaction test
\nTransaction test succeeded.
\nRunning transaction
\nPreparing : 1\/1
\nInstalling : ufw-0.35-9.fc27.noarch 1\/1
\nRunning scriptlet: ufw-0.35-9.fc27.noarch 1\/1
\nRunning as unit: run-rcf2b3a65bf7d43b78a6d1e515b174178.service
\nVerifying : ufw-0.35-9.fc27.noarch 1\/1<\/p>\n
Installed:
\nufw.noarch 0.35-9.fc27<\/p>\n
Complete!
\nrmohan.com@fedora1 ~ $
\nrmohan.com@fedora1 ~ $ sudo ufw status
\nStatus: inactive
\nrmohan.com@fedora1 ~ $ sudo ufw enable
\nCommand may disrupt existing ssh connections. Proceed with operation (y|n)? y
\nFirewall is active and enabled on system startup
\nrmohan.com@fedora1 ~ $ sudo ufw status
\nStatus: active<\/p>\n
To Action From
\n— —— —-
\nSSH ALLOW Anywhere
\n224.0.0.251 mDNS ALLOW Anywhere
\nSSH (v6) ALLOW Anywhere (v6)
\nff02::fb mDNS ALLOW Anywhere (v6)<\/p>\n
rmohan.com@fedora1 ~ $ sudo ufw allow 5432
\nRule added
\nRule added (v6)
\nrmohan.com@fedora1 ~ $ sudo ufw default deny
\nDefault incoming policy changed to ‘deny’
\n(be sure to update your rules accordingly)
\nrmohan.com@fedora1 ~ $ sudo systemctl enable ufw.service
\nCreated symlink \/etc\/systemd\/system\/basic.target.wants\/ufw.service ? \/usr\/lib\/systemd\/system\/ufw.service.
\nrmohan.com@fedora1 ~ $ sudo systemctl restart ufw.service
\nrmohan.com@fedora1 ~ $ sudo systemctl status ufw.service
\n? ufw.service – Uncomplicated firewall
\nLoaded: loaded (\/usr\/lib\/systemd\/system\/ufw.service; enabled; vendor preset: disabled)
\nActive: active (exited) since Tue 2018-01-02 18:47:35 CST; 13s ago
\nDocs: man:ufw(8)
\nman:ufw-framework(8)
\nfile:\/\/usr\/share\/doc\/ufw\/README
\nProcess: 6171 ExecStart=\/usr\/libexec\/ufw\/ufw-init start (code=exited, status=0\/SUCCESS)
\nMain PID: 6171 (code=exited, status=0\/SUCCESS)<\/p>\n
Jan 02 18:47:34 fedora1 systemd[1]: Starting Uncomplicated firewall…
\nJan 02 18:47:35 fedora1 systemd[1]: Started Uncomplicated firewall.
\nrmohan.com@fedora1 ~ $ sudo ufw status
\nStatus: active<\/p>\n
To Action From
\n— —— —-
\nSSH ALLOW Anywhere
\n224.0.0.251 mDNS ALLOW Anywhere
\n5432 ALLOW Anywhere
\nSSH (v6) ALLOW Anywhere (v6)
\nff02::fb mDNS ALLOW Anywhere (v6)
\n5432 (v6) ALLOW Anywhere (v6)<\/p>\n
rmohan.com@fedora1 ~ $<\/p>\n","protected":false},"excerpt":{"rendered":"
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<\/p>\n
Transaction Summary ============================================================================================================================================================================================================================================== Install 1 Package<\/p>\n
Total size: […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[93],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7367"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7367"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7367\/revisions"}],"predecessor-version":[{"id":7368,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7367\/revisions\/7368"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7367"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7367"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7367"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}