如何将 pgAdmin III (Windows) 连接到 postgresql (Linux in virtual box)

How to connect pgAdmin III (Windows) to postgresql (Linux in virtual box)

提问by Tobias

I have a postgresqldemon on my virtual Linux box, and just installed pgAdmin III on the Windows machine. Now I'd like to configure the first connection.

postgresql我的虚拟 Linux 机器上有一个恶魔，刚刚在 Windows 机器上安装了 pgAdmin III。现在我想配置第一个连接。

What I did so far:

到目前为止我做了什么：

• I edited /etc/postgresql/9.1/main/pg_hba.confand added a line to make the demon accept connections from my Windows machine:
  host all all 192.168.123.45/32 md5
• I set a shell passphrase for the system user postgres(sudo passwd postgres)
• Edit: this seems to be irrelevant
  I copied /usr/share/postgresql/9.1/pg_service.conf.sampleto /etc/postgresql-common/pg_service.confand uncommented the sample service, giving me
  [postgres]
  dbname=postgres
  user=postgres
• Edit: this seems to be irrelevant
  I put a line into /etc/environmentto point to the pgservice.conffile:
  PGSERVICEFILE=/etc/postgresql-common/pg_service.conf
  (which works; when I duplicated the PuTTY session, it had this variable)
• I restarted the database server after each change:
  sudo /etc/init.d/postgresql restart
• Finally, I restartet the Linux box,

• 我编辑/etc/postgresql/9.1/main/pg_hba.conf并添加了一行让恶魔接受来自我的 Windows 机器的连接：
  host all all 192.168.123.45/32 md5
• 我为系统用户postgres( sudo passwd postgres)设置了一个 shell 密码
• 编辑：这似乎是无关紧要
  我复制/usr/share/postgresql/9.1/pg_service.conf.sample到/etc/postgresql-common/pg_service.conf和注释掉的示例服务，给我
  [Postgres的]
  DBNAME = Postgres的
  用户的Postgres =
• 编辑：这似乎无关紧要，
  我在其中放了一行/etc/environment指向pgservice.conf文件：（
  PGSERVICEFILE=/etc/postgresql-common/pg_service.conf
  有效；当我复制 PuTTY 会话时，它有这个变量）
• 每次更改后我都重新启动了数据库服务器：
  sudo /etc/init.d/postgresql restart
• 最后，我重新启动了 Linux 机器，

The server seems to have restarted successfully:

服务器似乎已成功重新启动：

[ ok ] Restarting PostgreSQL 9.1 database server: main.

However, when I try to connect to the service, pgAdmin III gives me an error message:
Error connecting to the server: definition of service "postgres" not found.

但是，当我尝试连接到该服务时，pgAdmin III 给了我一条错误消息：
连接到服务器时出错：未找到服务“postgres”的定义。

dmesg | grep postgresgives me a single line:

dmesg | grep postgres给我一行：

[   18.054965] postgres (2242): /proc/2242/oom_adj is deprecated, please use /proc/2242/oom_score_adj instead.

I have no clue whether postgresqluses my pq_service.confat all, or whatever else is the problem; any help is appreciated ...

我不知道是否postgresql使用了我pq_service.conf的，或者还有什么问题；任何帮助表示赞赏...

Edit:My original question, "How can I know whether postgresql uses my pg_service.conf file?", seems to be answered - it simply doesn't. I still can't connect; but now the question doesn't match the error messages anymore.

编辑：我原来的问题“ How can I know whether postgresql uses my pg_service.conf file?”似乎得到了回答——它根本没有。我还是连接不上；但现在问题不再与错误消息匹配。

I removed the "Service" entry from my "New Server Registration" data. Now I get another error - something like "password authentication for user >>postgres<< failed". I'm quite sure the password is correct - I just set it ... and I tested it by commenting out my ssh key from the authorized_keysfile.

我从“新服务器注册”数据中删除了“服务”条目。现在我收到另一个错误 - 类似于“用户 >>postgres<< 的密码验证失败”。我很确定密码是正确的——我只是设置了它……我通过从authorized_keys文件中注释掉我的 ssh 密钥来测试它。

I'd happily connect with my ssh key, but this seems to be difficult as well. With PuTTY, my key is taken from Pageant, and I'm logged in without any problem; pgAdmin talks about "SSH tunnelling", but I normally don't need tunnels for this local machine ...

我很乐意使用我的 ssh 密钥连接，但这似乎也很困难。使用 PuTTY，我的密钥是从 Pageant 中获取的，并且我登录时没有任何问题；pgAdmin 谈论“SSH 隧道”，但我通常不需要这台本地机器的隧道......

I tried to create a tunnel anyway.

无论如何，我试图创建一个隧道。

• PuTTY session:
  • Source port is 5432
  • destination is my-vbox.host.name:5432
• In pgAdmin, "SSH Tunnel":
  • Username postgres
  • using Identity file
  • Tunnel host localhost→ error:
    "SSH error: Could not connect to socket with error code 10051"
  • Tunnel host localhost:5432→ error:
    "SSH error: Unable to resolve host: localhost:5432"
  • Tunnel host 127.0.0.1:5432→ error:
    "SSH error: Unable to resolve host: 127.0.0.1:5432"

• PuTTY 会话：
  • 源端口是 5432
  • 目的地是 my-vbox.host.name:5432
• 在 pgAdmin 中，“SSH 隧道”：
  • 用户名 postgres
  • 使用身份文件
  • 隧道主机localhost→ 错误：
    “ SSH error: Could not connect to socket with error code 10051”
  • 隧道主机localhost:5432→ 错误：
    “ SSH error: Unable to resolve host: localhost:5432”
  • 隧道主机127.0.0.1:5432→ 错误：
    “ SSH error: Unable to resolve host: 127.0.0.1:5432”

How the heck is this supposed to be configured?!

这到底是怎么配置的？！

回答by Tobias

I finally managed to connect as db user tobias(I never had problems to connect locally, with psql).

我终于设法以 db 用户身份连接tobias（我在本地连接时从未遇到过问题，使用psql）。

# sudo -u postgres psql postgres
psql (9.1.9)
Type "help" for help.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication | {}
 tobias    | Superuser                                      | {}

postgres=# ALTER USER tobias WITH PASSWORD 's3cr3t';
ALTER ROLE

After doing this, I could connect as tobias, using the password just set. The "Maintenance DB" happens to be template1; I didn't try to change this.

这样做后，我可以tobias使用刚刚设置的密码连接为。“维护数据库”恰好是template1；我没有试图改变这一点。

For further reference - I activated log_connectionsin /etc/postgresql/9.1/main/postgresql.confand watched the log:

更多参考-我激活log_connections的/etc/postgresql/9.1/main/postgresql.conf，看着日志：

sudo tail -f /var/log/postgresql/postgresql-9.1-main.log