2017年3月18日 星期六

[研究] PostgreSQL 9.2.18 安裝 (yum) (CentOS 7.3 x64)

[研究] PostgreSQL 9.2.18 安裝 (yum) (CentOS 7.3 x64)

2017-03-18

Linux downloads (Red Hat family)
https://www.postgresql.org/download/linux/redhat/

PostgreSQL RPM Building Project - Yum Repository
https://yum.postgresql.org/

PostgreSQL RPM Building Project - Repository Packages
https://yum.postgresql.org/repopackages.php#pg96
目前最新的 9.6 系列支援 CentOS 7 x64

18.3. Connections and Authentication
https://www.postgresql.org/docs/9.1/static/runtime-config-connection.html
說 port 為 5432

教學

Installing PostgreSQL on Red Hat Enterprise Linux / Fedora Core
Last Update: Mon Sep 03, 2007
https://yum.postgresql.org/files/PostgreSQL-RPM-Installation-PGDG.pdf

檢查,發現 CentOS 7.3 x64 目前 yum 提供 PostgreSQL Database 9.2.18

[root@localhost include]# yum list | grep postgre
postgresql.x86_64                          9.2.18-1.el7                @base
postgresql-libs.x86_64                     9.2.18-1.el7                @base
freeradius-postgresql.x86_64               3.0.4-7.el7_3               updates
libreoffice-postgresql.x86_64              1:5.0.6.2-3.el7             base
pcp-pmda-postgresql.x86_64                 3.11.3-4.el7                base
postgresql.i686                            9.2.18-1.el7                base
postgresql-contrib.x86_64                  9.2.18-1.el7                base
postgresql-devel.i686                      9.2.18-1.el7                base
postgresql-devel.x86_64                    9.2.18-1.el7                base
postgresql-docs.x86_64                     9.2.18-1.el7                base
postgresql-jdbc.noarch                     9.2.1002-5.el7              base
postgresql-jdbc-javadoc.noarch             9.2.1002-5.el7              base
postgresql-libs.i686                       9.2.18-1.el7                base
postgresql-odbc.x86_64                     09.03.0100-2.el7            base
postgresql-plperl.x86_64                   9.2.18-1.el7                base
postgresql-plpython.x86_64                 9.2.18-1.el7                base
postgresql-pltcl.x86_64                    9.2.18-1.el7                base
postgresql-server.x86_64                   9.2.18-1.el7                base
postgresql-test.x86_64                     9.2.18-1.el7                base
postgresql-upgrade.x86_64                  9.2.18-1.el7                base
qt-postgresql.i686                         1:4.8.5-13.el7              base
qt-postgresql.x86_64                       1:4.8.5-13.el7              base
qt5-qtbase-postgresql.i686                 5.6.1-10.el7                base
qt5-qtbase-postgresql.x86_64               5.6.1-10.el7                base
[root@localhost include]# yum list | grep postgresql
postgresql.x86_64                          9.2.18-1.el7                @base
postgresql-libs.x86_64                     9.2.18-1.el7                @base
freeradius-postgresql.x86_64               3.0.4-7.el7_3               updates
libreoffice-postgresql.x86_64              1:5.0.6.2-3.el7             base
pcp-pmda-postgresql.x86_64                 3.11.3-4.el7                base
postgresql.i686                            9.2.18-1.el7                base
postgresql-contrib.x86_64                  9.2.18-1.el7                base
postgresql-devel.i686                      9.2.18-1.el7                base
postgresql-devel.x86_64                    9.2.18-1.el7                base
postgresql-docs.x86_64                     9.2.18-1.el7                base
postgresql-jdbc.noarch                     9.2.1002-5.el7              base
postgresql-jdbc-javadoc.noarch             9.2.1002-5.el7              base
postgresql-libs.i686                       9.2.18-1.el7                base
postgresql-odbc.x86_64                     09.03.0100-2.el7            base
postgresql-plperl.x86_64                   9.2.18-1.el7                base
postgresql-plpython.x86_64                 9.2.18-1.el7                base
postgresql-pltcl.x86_64                    9.2.18-1.el7                base
postgresql-server.x86_64                   9.2.18-1.el7                base
postgresql-test.x86_64                     9.2.18-1.el7                base
postgresql-upgrade.x86_64                  9.2.18-1.el7                base
qt-postgresql.i686                         1:4.8.5-13.el7              base
qt-postgresql.x86_64                       1:4.8.5-13.el7              base
qt5-qtbase-postgresql.i686                 5.6.1-10.el7                base
qt5-qtbase-postgresql.x86_64               5.6.1-10.el7                base
[root@localhost include]#

安裝
yum  -y  install  postgresql-server

初始化資料庫

[root@localhost include]# service   postgresql   initdb
Hint: the preferred way to do this is now "postgresql-setup initdb"
Initializing database ... OK

立刻啟動 (但是重新啟動後,不會自動啟動)

[root@localhost include]# service   postgresql   start
Redirecting to /bin/systemctl start  postgresql.service
[root@localhost include]#

確認啟動

[root@localhost ~]# ps aux | grep pg
postgres   4283  0.0  0.2 234344  9332 ?        S    07:12   0:00 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
root       4582  0.0  0.0 112648   960 pts/1    S+   07:28   0:00 grep --color=auto pg
[root@localhost ~]#


[root@localhost ~]# service status postgresql
The service command supports only basic LSB actions (start, stop, restart, try-restart, reload, force-reload, status). For other actions, please try to use systemctl.


[root@localhost ~]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: active (running) since Sat 2017-03-18 07:12:13 CST; 18min ago
 Main PID: 4283 (postgres)
   CGroup: /system.slice/postgresql.service
           ├─4283 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
           ├─4284 postgres: logger process
           ├─4286 postgres: checkpointer process
           ├─4287 postgres: writer process
           ├─4288 postgres: wal writer process
           ├─4289 postgres: autovacuum launcher process
           └─4290 postgres: stats collector process

Mar 18 07:12:12 localhost.localdomain systemd[1]: Starting PostgreSQL databas...
Mar 18 07:12:13 localhost.localdomain systemd[1]: Started PostgreSQL database...
Hint: Some lines were ellipsized, use -l to show in full.
[root@localhost ~]#


設定每次重新開機後,自動啟動 (但不會立刻啟動)

[root@localhost include]# chkconfig   postgresql   on
Note: Forwarding request to 'systemctl enable postgresql.service'.
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service.
[root@localhost include]#

開啟 防火牆 port 5432

[root@localhost include]# firewall-cmd --add-port=5432/tcp --permanent
success
[root@localhost include]#

重新載入防火牆設定,讓剛剛設定生效

[root@localhost include]# firewall-cmd --reload
success
[root@localhost include]#

列出防火牆全部設定

[root@localhost ~]# firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: ens33
  sources:
  services: dhcpv6-client ssh
  ports: 5432/tcp 80/tcp
  protocols:
  masquerade: no
  forward-ports:
  sourceports:
  icmp-blocks:
  rich rules:

[root@localhost ~]#

設定檔案
postgresql.conf 及 pg_hba.conf

如果不知道在哪
locate   postgresql.conf
locate   pg_hba.conf

-----------------------------------------------------------------------------------

測試


[root@localhost ~]# su - postgres
Last login: Sat Mar 18 11:06:06 CST 2017 on pts/1
-bash-4.2$ psql
psql (9.2.18)
Type "help" for help.

postgres=# create database TestDB;
CREATE DATABASE
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileg
es
-----------+----------+----------+-------------+-------------+------------------
-----
 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/post
gres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
    +
           |          |          |             |             | postgres=CTc/post
gres
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

postgres=# \c TestDB
You are now connected to database "TestDB" as user "postgres".
TestDB=# create table table1 (id serial, title text, body text);
CREATE TABLE
TestDB=# insert into table1 (title, body) values ('pgpool', 'test');
INSERT 0 1
TestDB=# select * from table1;
 id | title  | body
----+--------+------
  1 | pgpool | test
(1 row)

TestDB=# \q
-bash-4.1$ exit
logout
[root@centos1 ~]#

停止服務

systemctl  stop  postgresql

********************************************************************************

總結

安裝、啟動、設定防火牆
yum  -y  install  postgresql-server
postgresql-setup   initdb
systemctl   restart   postgresql
systemctl   status   postgresql
firewall-cmd --add-port=5432/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-all

(完)

[研究] PostgreSQL 9.2.18 安裝 (yum) (CentOS 7.3 x64)
http://shaurong.blogspot.com/2017/03/postgresql-9218-yum-centos-73-x64.html

[研究] 雙機 PostgreSQL 9.3.2 同步(Replication)(使用pgpool-II)(CentOS 6.5 x64)
http://shaurong.blogspot.tw/2014/01/postgresql-932-replicationpgpool.html

[研究] PostgreSQL 9.3.2 (yum) + PgAdminIII 1.18.1(yum)安裝 (CentOS 6.5 x64)
http://shaurong.blogspot.com/2014/01/postgresql-932-yum-pgadminiii-1181yum.html

[研究] PostgreSQL 9.3.2 (tgz) + PgAdminIII 1.18.1(tgz)安裝 (CentOS 6.5 x64)
http://shaurong.blogspot.com/2014/01/postgresql-932-tgz-pgadminiii-1181tgz.html

[研究] PostgreSQL 9.2(yum) + PgAdminIII v1.16.0(yum) 安裝 (CentOS 6.5 x64)
http://shaurong.blogspot.com/2014/01/postgresql-92yum-pgadminiii-v1160yum.html

[研究] PostgreSQL 8.4.18(yum) + PgAdminIII v1.16.0(yum) 安裝 (CentOS 6.5 x64)
http://shaurong.blogspot.com/2014/01/postgresql-8.html

[研究] PostgreSQL 9.3.2 安裝(tgz)(CentOS 6.5 x64)
http://shaurong.blogspot.tw/2014/01/postgresql-932-tgzcentos-65-x64.html

沒有留言:

張貼留言