共计 15122 个字符,预计需要花费 38 分钟才能阅读完成。
在今年的云栖大会杭州分会上相关专家做了一个关于 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
