postgres远程连接方式配置

  连接远端postgres时需要指定IP地址，默认安装的postgres数据库配置只监控本地地址（localhost），其他主机是无法访问的，
这里通过一个简单的例介绍远程主机连接方式。
环境如下：
主机         IP地址
QXY          192.168.40.170

QXY1        192.168.40.171

1、(QXY)主机创建一个新用户scott

[postgres@qxy data]$ cd
[postgres@qxy ~]$ psql
psql.bin (9.6.4)
Type "help" for help.

postgres=# \help create user        
Command:     CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

postgres=# create user scott password 'tiger';
CREATE ROLE

2、(QXY1)直接发起连接是会报如下错误

[postgres@QXY1 bin]$   psql -U soctt -h 192.168.40.170 -d postgres
psql: could not connect to server: Connection refused
        Is the server running on host "192.168.40.170" and accepting
        TCP/IP connections on port 5432?
[postgres@QXY1 bin]$ 

3、配置pg_hba.conf文件

(QXY)主机
[postgres@qxy data]$ pwd
/spark/pgsql/data
[postgres@qxy data]$ cat pg_hba.conf 
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

在# IPv4 local connections:
下面添加一行，内容为  "host  all  all  192.168.40.1/24  md5"，代表192.168.40网段的IP地址的所有用户都可以连接,/24代表网段，如果是/32 需要写完整的IP地址

添加之后的内容如下：

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             192.168.40.1/24         md5

4、修改postgres监听的IP地址，默认是localhost

(QXY)主机
postgres@qxy data]$ cat postgresql.conf

# - Connection Settings -

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'                  # what IP address(es) to listen on;  <=======新增这行，把localhost改成*，监听所有的IP
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)

5、重新启动postgres

(QXY) 主机
[postgres@qxy data]$ pg_ctl stop -D $PGDATA -m fast
waiting for server to shut down....LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
LOG:  database system is shut down
 done
server stopped
[postgres@qxy data]$ pg_ctl start -D $PGDATA
server starting
[postgres@qxy data]$ LOG:  database system was shut down at 2018-06-06 09:31:29 GMT
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

6、远程主机连接

[postgres@QXY1 bin]$   psql -U scott -h 192.168.40.170 -d postgres
Password for user scott: 
psql (10.3, server 9.6.4)
Type "help" for help.

postgres=> 
postgres=>