PostgreSQL: 使用 psql 命令远程连接到 Postgres 实例

我想远程连接到 Postgres 实例。我知道我们可以使用传递主机名的 psql 命令来实现这一点

我尝试了以下方法:

psql -U postgres -p 5432 -h hostname

我修改了目标机器上的/etc/postgreql/9.3/main/pg _ hba. conf 文件，默认情况下允许远程连接

我在文件中添加了以下行

host all all source_ip/32 trust

我重新启动了集群

pg_ctlcluster 9.2 mycluster stop
pg_ctlcluster 9.2 mycluster start

但是，当我尝试从 source _ ip 进行连接时，仍然会出现错误吗

服务器是否在主机上运行并在端口5432上接受 TCP/IP 连接？

我做错了什么？

164747 次浏览
小开
最佳答案

I figured it out.

Had to set listen_addresses='*' in postgresql.conf to allow for incoming connections from any ip / all ip

小开

I resolved this issue using below options:

  1. Whitelist your DB host from your network team to make sure you have access to remote host
  2. Install postgreSQL version 4 or above
  3. Run below command: 
    psql -h <REMOTE HOST> -p <REMOTE PORT> -U <DB_USER> <DB_NAME>
小开 
psql -h <IP_Address> -p <port_no> -d <database_name> -U <DB_username> -W

-W option will prompt for password. For example:

psql -h 192.168.1.50 -p 5432 -d testdb -U testuser -W
小开

Step Wise below

  1. Opening the Port - Make sure the PSQL Port is open to all remote connections or connections from a specific set of IPs as per your requirement. PSQL, in general, runs at port 5432, and it is configurable, so expose relevant Port accordingly.
  2. Update Remote Server PSQL Configuration - Set listen_addresses = '*' in postgresql.conf file, path in general is /etc/postgresql/psql_version/main/postgresql.conf
  3. Connect remotely - psql -U <db_username> -h <IP_address> - in case psql is running on a port other than 5432 on the remote server, specify port by adding -p <port_number>

A little plus below - In case the IP has been mapped to a domain name, you can connect by replacing <IP_address> with <host_name>. To do this, add a new connection rule in pg_hba.conf file

Note - All above explained can cause security issues - best practice always is to either keep your psql port closed, or only allow a list of IPs to connect through the port.

小开

Note that "ident" in pg_hba.conf requires a "ident server" to be running on the client.


提交答案