手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

如何在Fedora 27上安装并配置PostgreSQL 10

[日期:2018-02-13] 来源:Linux社区  作者:qwfys200 [字体: ]

在今年的云栖大会杭州分会上相关专家做了一个关于Postgresql的专题分享,其中提到了Postgresql 10的一些新特性,个人感觉倍受鼓舞,接下来希望分别针对Ubuntu 16.04LTS和Fedora27上如何安装并配置Postgresql 10做一个简要介绍。这篇博客主要针对Fedora 27做简要介绍。

1、安装

大家进入Postgresql官网下载页面,选择
数据版本postgreql10,Linux 发行套件Fedora 27,然后选择64位,即x86_64,这个时候就会出现相关的安装脚本。

1.1 、添加软件源

linuxidc@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、安装服务器与客户端

linuxidc@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!

这里要说的是dnf和yum是类似的,在性能上比yum更高效,这也是接下来
RedHat旗下相发行套件的主流包管理工具。

1.3、初始化

linuxidc@fedora1 ~ $ sudo /usr/pgsql-10/bin/postgresql-10-setup initdb
Initializing database ... OK

linuxidc@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.
linuxidc@fedora1 ~ $ sudo systemctl start postgresql-10.service 
linuxidc@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、本地访问

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、配置

我们知道通常情况下,需要在主机以外的主机上访问postgresql服务,但是默认情况下,postgresql仅仅只提供本地访问,要让其他主机也可以访问,需要做如下配置。

2.1、开放对非地访问的支持。

fedora旗下发行套件中的postgresql配置文件主要在数据目录,即/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 ~ $ 

我们先修改配置postgresql.conf,打开对非主机访问的限制,用vim打开该文件,

59 #listen_addresses = 'localhost'         # what IP address(es) to listen on;

修改为
59 #listen_addresses = '*' # what IP address(es) to listen on;

再用vim打开文件pg_hba.conf,找到82行

82 host    all             all             127.0.0.1/32            ident

后面追加

 83 host    all             all             192.168.1.0/24          trust

这时,重新启动postgresql数据库。

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
linuxidc@fedora1 ~ $ sudo systemctl start postgresql-10.service 
[sudo] password for linuxidc: 
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.
linuxidc@fedora1 ~ $ 

2.1、修改防火墙配置。

修改防火墙配置将将端口号5432添加到防火墙白名单中去。方法很多,这里采用ufw

linuxidc@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
linuxidc@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!
linuxidc@fedora1 ~ $ 
linuxidc@fedora1 ~ $ sudo ufw status
Status: inactive
linuxidc@fedora1 ~ $ sudo ufw enable
Command may disrupt existing ssh connections. Proceed with operation (y|n)? y
Firewall is active and enabled on system startup
linuxidc@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)             

linuxidc@fedora1 ~ $ sudo ufw allow 5432
Rule added
Rule added (v6)
linuxidc@fedora1 ~ $ sudo ufw default deny
Default incoming policy changed to 'deny'
(be sure to update your rules accordingly)
linuxidc@fedora1 ~ $ sudo systemctl enable ufw.service 
Created symlink /etc/systemd/system/basic.target.wants/ufw.service → /usr/lib/systemd/system/ufw.service.
linuxidc@fedora1 ~ $ sudo systemctl restart ufw.service 
linuxidc@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.
linuxidc@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)             

linuxidc@fedora1 ~ $ 

3、远程主机连接过来

Ubuntu 16.04 下安装 PostgreSQL 和 phpPgAdmin  http://www.linuxidc.com/Linux/2016-08/134260.htm

Linux下RPM包方式安装PostgreSQL  http://www.linuxidc.com/Linux/2016-03/128906.htm

Linux下安装 PostgreSQL 并设置基本参数  http://www.linuxidc.com/Linux/2016-11/137324.htm

Ubuntu 16.04 下 PostgreSQL 主从复制配置  http://www.linuxidc.com/Linux/2017-08/146190.htm

Fedota 24 将数据库升级到 PostgreSQL 9.5  http://www.linuxidc.com/Linux/2016-11/137374.htm

CentOS7安装配置PostgreSQL9.6  http://www.linuxidc.com/Linux/2017-10/147536.htm

CentOS5.8_x64下离线安装PostgreSQL 9.1  http://www.linuxidc.com/Linux/2017-10/147822.htm

CentOS 6.5下PostgreSQL服务部署  http://www.linuxidc.com/Linux/2017-01/139144.htm

本文永久更新链接地址http://www.linuxidc.com/Linux/2018-02/150922.htm

linux
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款