DBever连接PG库

古城微笑少年丶 2024-02-18 09:58 15阅读 0赞

## 一、简介 ##

**DBeaver是一种通用数据库管理工具**，适用于需要以专业方式使用数据的每个人；适用于开发人员，数据库管理员，分析师和所有需要使用数据库的人员的 ***免费(DBeaver Community)*** 的多平台数据库工具，支持 Windows、Linux 和 macOS。它有两个版本，企业版和社区版，对于个人开发者来说，社区版的功能已经足够强大。

DBeaver 是由 Java 编写的，默认使用 JDK 11 进行编译。社区版基于 Apache-2.0 License 在 GitHub 上开源，目前已获得 24k+ 的星标。

DBeaver支持80多个数据库。支持所有流行的数据库，包括：**MySQL**、PostgreSQL、MariaDB、SQLite、**Oracle**、Db2、**SQL Server**、Sybase、MS Access、Teradata、Firebird、Derby 等等。商业版本更是可以支持各种 NoSQL 和大数据平台：**MongoDB**、InfluxDB、Apache Cassandra、**Redis**、Apache Hive 等。

## 二、准备 ##

1.开启虚拟机，登录centos7的root用户；

2.打开xshell（其他终端模拟器也行，也可以不连接终端）,在Xshell终端的提示符下（例如`[C:\~]$`），使用以下命令连接到我所需要的192.168.204.128的root用户：

ssh root@192.168.204.128

![0d006db8cb5f41e0b8090b51a9dbcb9c.png][]

根据弹窗提示输入密码登录root用户。

3.下载和安装DBeave

可以通过 DBeaver 官方下载安装包，前往DBeaver官方网站（[Download | DBeaver Community][Download _ DBeaver Community]），选择相应的版本下载即可：我这里选择的是`Windows（installer），找到文件双击安装。`![dd64105946264a2a9e1685abaac0aa59.png][]

## 三、连接 ##

### 1.打开DBeaver ###

安装完成后，打开DBeaver应用程序。

### 2.创建新连接 ###

在DBeaver的主界面上，点击左上角的“新连接”按钮（图标为蓝色加号），或者右键单击“数据库连接”并选择“新建连接”。

![08c84a93bb1e4ad4b255fcf50f78ed8d.png][]

### 3.配置连接参数 ###

在新建连接对话框中，选择“PostgreSQL”作为数据库类型。然后，填写以下信息：

*  主机名/IP地址：输入PostgreSQL服务器的主机名或IP地址。
 *  端口：输入PostgreSQL服务器的端口号，默认为5432。
 *  数据库：输入要连接的数据库名称。
 *  用户名和密码：输入用于连接到数据库的用户名和密码。

![a32afffe975a46e38b37ab0c5e17686f.png][]

> **注意：**
> 
> 在DBeaver上连接PostgreSQL库时，需要提供正确的主机地址来建立连接。该主机地址应与CentOS 7上运行PostgreSQL服务器的主机地址相匹配。确保在 DBeaver 中提供正确的服务器 IP 地址作为主机地址，并使用与 CentOS 7 上 PostgreSQL 设置相匹配的端口号和其他必需的登录凭据，以成功连接到 PostgreSQL 数据库。
> 
> 通常情况下，如果您在CentOS 7上安装了PostgreSQL服务器并希望使用DBeaver连接该服务器，则主机地址将相同。
> 
> 如果PostgreSQL 的 `listen_addresses` 设置为 "\*"（通配符），表示 PostgreSQL 监听所有可用的网络接口（IP地址）上的连接。在这种情况下，您可以在 DBeaver 中使用服务器的 IP 地址作为主机地址来连接 PostgreSQL 数据库。

**如果dbever连接PG库时不使用默认用户，我们可以创建新的用户建立连接：**

在 CentOS 7 的 PostgreSQL 数据库中创建用户和密码步骤：

1.  登录到 CentOS 7 的 PostgreSQL 服务器。
2.  打开终端或命令行界面，并使用超级用户（例如 `postgres`）执行以下命令来连接到 PostgreSQL 数据库：
    
        sudo -u postgres psql
        或
        su - postgres
        psql
3.  在 PostgreSQL 命令提示符下，执行以下命令来创建新用户：
    
        CREATE USER username WITH PASSWORD 'password';
        --例如：
        CREATE USER CSY WITH PASSWORD '123456';
    
    将 `username` 替换为您想要创建的用户名，将 `password` 替换为您想要设置的密码。
4.  授予该用户访问数据库的权限。执行以下命令来授予用户对所有数据库的适当权限：
    
        ALTER USER username CREATEDB;
5.  （可选）如果需要限制用户只能访问特定数据库，请执行以下命令：
    
        GRANT ALL PRIVILEGES ON DATABASE database_name TO username;
        --例如：
        GRANT ALL PRIVILEGES ON DATABASE postgres TO CSY;
    
    将 `database_name` 替换为您希望用户能够访问的数据库名称。
6.  如果要授予在 CentOS 7 的 PostgreSQL 数据库中创建的新用户CSY拥有更多权限，在 PostgreSQL 命令提示符下，执行以下命令：
    
        --只授予对数据库的权限，而不包括对其中的表、视图、函数等的权限：
        GRANT ALL PRIVILEGES ON DATABASE database_name TO username;
        --例如：
        GRANT ALL PRIVILEGES ON DATABASE postgres TO CSY;
        
        --给予用户"CSY"对数据库中的所有位于public模式下的表、视图、函数等的全部权限：
        GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO CSY;
        GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO CSY;
        GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO CSY;
        
        --授权csy建立DB，建立角色的权限：
        --普通用户默认情况下是没有权限创建数据库的。只有超级用户才拥有该权限
        --若授予用户"CSY"创建数据库的权限，需要以超级用户身份登录到PostgreSQL
        ALTER USER CSY CREATEDB;
        
        --为用户"CSY"赋予创建角色的权限
        ALTER USER CSY CREATEROLE;
7.  查看所创建角色/用户及其权限：
    
        \du
    
    ![c83fd4a3256a48ff956c0c3b1800f6dc.png][]
8.  使用以下命令退出 PostgreSQL 命令提示符：
    
        \q

> 假如我们新创建的用户在DBEVER连接PG库测试时有这种提示：
> 
> 比如我的用户CSY
> 
> 致命错误: 用户 "CSY" Password 认证失败；
> 
> 因为在PostgreSQL中创建一个新用户时，默认情况下是没有设置密码的。
> 
> 我们创建时实际时配置有密码，但现在可以为新用户再修改下密码：
> 
>     ALTER USER CSY WITH PASSWORD 'your_password';
> 
> **注意：**
> 
> ①要以具有足够权限的用户登录到PostgreSQL数据库；
> 
> ②确保您在PG库中的`pg_hba.conf`文件中配置了正确的认证规则。您可以通过编辑该文件来控制用户连接数据库时的认证方式。例如，可以使用密码认证或其他认证方法。
> 
> 然后重启PostgreSQL服务，以使更改生效。
> 
> 最后再测试连接即可连接成功。

### 4.测试连接 ###

点击“测试连接”按钮，DBeaver将尝试连接到指定的PostgreSQL数据库。如果一切正常，将收到一个成功连接的消息。

**如果在 DBeaver 中连接 PostgreSQL 数据库时遇到测试连接超时的问题，可以尝试以下解决方法：**

#### **①检查网络连接**： ####

确保您的 CentOS 7 服务器和客户端（运行 DBeaver 的计算机）之间的网络连接正常。检查防火墙设置、路由器配置或其他网络设备以确保允许对 PostgreSQL 端口的访问。

要检查防火墙设置、路由器配置或其他网络设备以确保允许对 PostgreSQL 端口的访问，并确定网络连接是否正常，请按照以下步骤进行操作：

##### Ⅰ、防火墙设置： #####

*  检查 CentOS 7 服务器上的防火墙规则，确保已允许通过 PostgreSQL 使用的端口（通常是默认的 5432 端口）的入站连接。您可以使用以下命令来检查 iptables 防火墙规则：
    
        sudo iptables -L -n
    
    如果在 INPUT 规则中存在允许连接到 PostgreSQL 的端口规则，则表示防火墙设置正常。![037d5ee27c5744fb89bdb71603896d1d.png][]

在输出结果中，找到名为 "INPUT" 的链。该链包含了所有传入连接的规则。检查 "INPUT" 链中的规则，查看是否存在允许连接到 PostgreSQL 端口（**通常是默认的 5432 端口**）的规则。这些规则中通常会使用 `-p tcp --dport 5432` 来指定协议和端口。如果在 "INPUT" 链中存在类似下面的规则，表示防火墙设置正常：

ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpt:5432

该规则表示允许从任意 IP 地址（0.0.0.0/0）连接到 PostgreSQL 端口（5432）的传入 TCP 连接。

> **备注：**
> 
> 我当时在测试PG库连接时就一直出现连接超时的情况，经过多方查找和验证，最后发现时自己的防火墙设置中缺少了INPUT规则，即缺少了允许连接到 PostgreSQL 的端口规则：
> 
> 运行**sudo iptables -L -n**命令后发现只有一个接受22端口的规则！
> 
> ![12c4093338e74aebb13956996f8a6917.png][]
> 
> 即在防火墙设置中没有找到类似以下规则的条目：
> 
>     ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            tcp dpt:5432
> 
> 这意味着当前的防火墙设置可能不允许从任意 IP 地址连接到 PostgreSQL 的默认端口（5432）。
> 
> **注意：**
> 
> 具体的输出结果可能因系统配置而有所不同。确保检查 "INPUT" 链中的所有规则以确认是否存在允许连接到 PostgreSQL 端口的规则。如果未找到相应的规则，还请参考 CentOS 7 防火墙配置文档或联系系统管理员以获取准确的帮助。

**防火墙设置中没有找到规则条目的解决办法：**

1.  使用超级用户权限打开终端或命令行界面。
2.  检查并列出当前正在运行的防火墙服务。在 CentOS 7 上，默认使用的是 firewalld 服务。执行以下命令以查看状态：
    
        sudo firewall-cmd --state
    
    ![1f014b59a3d445519f0b548612cef114.png][]
3.  如果输出显示 "running"，则表示 firewalld 正在运行。执行以下命令启用对 PostgreSQL 端口的访问：
    
        sudo firewall-cmd --add-port=5432/tcp --permanent
    
    ![7bd6f337f7fa4023b130e7b9265caeaf.png][]
4.  重新加载防火墙配置以使更改生效：
    
        sudo firewall-cmd --reload
    
    ![c85c9073058f4d34b7ac91e5674d3ff3.png][]
5.  检查更新的规则条目，执行命令：
    
        sudo iptables -L -n
    
    ![640784cf4fdc46beadcfc5a8f7482c79.png][]

> 现在，防火墙应该已被更新，允许来自任意 IP 地址的连接访问 PostgreSQL 的默认端口（5432）。DBever进行再次测试连接，发现成功连接PG库。至此我的问题就解决啦！

##### Ⅱ、路由器配置： #####

*  检查路由器或网络设备上的端口转发设置，确保将请求从外部网络正确转发到 CentOS 7 服务器的相应端口。请参考您所使用的路由器或网络设备的文档，了解如何设置端口转发。

##### Ⅲ、网络连通性测试： #####

*  在 CentOS 7 服务器上执行以下命令，以确保 PostgreSQL 服务器正在监听所需的端口（默认为 5432）：
    
        netstat -tuln | grep 5432
    
    ![5248edec89824ae8a445f65a57d41c25.png][]

如果结果中显示 "LISTEN"，表示 PostgreSQL 正在监听该端口，这表示网络连接正常。

*  在运行 DBeaver 的计算机上，可以使用 `telnet` 命令测试能否访问 CentOS 7 服务器上的 PostgreSQL 端口。例如，如果 PostgreSQL 端口是 5432，可以执行以下命令：
    
        telnet centos7_server_ip 5432
    
    **如果成功连接，表示客户端可以访问服务器的 PostgreSQL 端口，这表示网络连接正常。操作步骤如下：**

（1）打开命令提示符（Windows）或终端（Mac/Linux）；

（2）在命令提示符或终端中，输入以下命令，并将 `centos7_server_ip` 替换为 CentOS 7 服务器的 IP 地址，将 `5432` 替换为 PostgreSQL 端口号：

telnet 192.168.204.128 5432

（3）按下回车键执行该命令。

（4）成功连接到 CentOS 7 服务器的 PostgreSQL 端口：![466cf22cfe564557bfcb6ef04646d38b.png][]

> 如果连接失败或显示任何错误消息，请检查网络设置、防火墙规则和其他网络设备，确保允许从运行 DBeaver 的计算机访问 CentOS 7 服务器的 PostgreSQL 端口。
> 
> 如果在运行 DBeaver 的计算机上执行 telnet 命令时收到 "'telnet' 不是内部或外部命令" 的错误消息，这意味着 telnet 客户端未安装或未正确配置。
> 
> 在最新的 Windows 操作系统中，默认情况下不包含 telnet 客户端。可以尝试以下方法来解决此问题：
> 
> **安装 telnet 客户端：**
> 
>  *  打开“控制面板”并选择“程序”或“程序和功能”。
>  *  点击“打开或关闭Windows功能”。
>  *  在弹出的窗口中找到并选中“Telnet客户端”，然后点击“确定”。
>  *  等待安装完成后，尝试再次执行 `telnet` 命令。

**以上步骤中，状态正常的表现包括：**

*  **防火墙设置中存在针对 PostgreSQL 端口（通常是 5432）的入站规则。**
 *  **路由器或网络设备已正确配置端口转发，将请求从外部网络转发到 CentOS 7 服务器的 PostgreSQL 端口。**
 *  **在 CentOS 7 服务器上执行 `netstat` 命令时，显示 PostgreSQL 正在监听所需的端口。**
 *  **在运行 DBeaver 的计算机上通过 `telnet` 命令成功连接到 CentOS 7 服务器的 PostgreSQL 端口。**

> **注意：**
> 
> 具体的网络环境和配置可能会有所不同，因此确保根据您自己的设置和需求进行适当的检查和调整。

#### **②检查主机地址和端口号**： ####

确保在 DBeaver 中正确输入了 PostgreSQL 服务器的主机地址和端口号。使用前面提到的方法验证服务器的 IP 地址和端口号，并确保在 DBeaver 中使用相应的值。

#### **③检查 PostgreSQL 配置**： ####

在 CentOS 7 上，打开 PostgreSQL 服务器的配置文件 postgresql.conf（通常位于 /var/lib/pgsql/data 目录下），检查以下设置：

如果您更改了这些配置，请重启 PostgreSQL 服务器使更改生效。

*  `listen_addresses`：确保已启用适当的网络接口监听。
 *  `port`：确保端口号与 DBeaver 中提供的端口号匹配。

#### **④检查连接权限**： ####

确保在 DBeaver 中使用的用户名和密码具有适当的权限以连接到 PostgreSQL 数据库。确认用户具有登录权限并具有适当的数据库访问权限。

检查在 DBeaver 中使用的用户名和密码是否具有适当的权限以连接到 PostgreSQL 数据库，可以执行以下步骤：

*  登录到 CentOS 7 的 PostgreSQL 服务器。
 *  执行以下命令登录到 PostgreSQL 数据库：
    
        psql -U username -d database_name
    
    将 `username` 替换为您在 DBeaver 中使用的用户名，并将 `database_name` 替换为要连接的数据库名称。
 *  如果成功登录（没有报错并且提示符变为 `database_name=>`），则表示该用户具有适当的登录权限。可以使用以下命令检查该用户的其他权限：比如查看所有角色/用户及其权限：![cd73a1e5e53445d19fb0ca0e66198b06.png][]

#### **⑤考虑使用 SSH 隧道**： ####

如果通过公共网络访问 PostgreSQL 服务器存在限制或安全性考虑，您可以考虑使用 SSH 隧道来建立安全连接。通过 SSH 隧道，可以加密和安全地连接到服务器。在 DBeaver 中提供 SSH 隧道的相关设置。

### 5.连接到数据库 ###

点击“连接”按钮，DBeaver将与PostgreSQL数据库建立连接。

### 6.查看和管理数据 ###

完成连接后，可以在左侧的导航器中查看数据库对象（如表、视图等）。通过双击这些对象，您可以浏览和编辑数据，执行查询和其他操作。

![16611f46b4704b398f87bf37b18e8c15.png][]

### 7.窗口数据编辑 ###

在 DBeaver 中连接到 PostgreSQL 数据库后，可以执行以下步骤来打开一个窗口并编辑 SQL：

*  在 DBeaver 左侧的数据库导航树中，展开 PostgreSQL 数据库连接，然后展开该连接下的数据库。
 *  选择要编辑 SQL 的特定数据库对象，如表、视图或存储过程。
 *  右键单击所选对象，在弹出菜单中选择 "SQL Editor" 或类似的选项。这将打开一个新的 SQL 编辑器窗口。
 *  在 SQL 编辑器窗口中，您可以输入和编辑 SQL 语句。您可以执行查询、创建、修改或删除数据库对象，以及执行其他与 PostgreSQL 相关的操作。
 *  在编辑完 SQL 后，您可以点击工具栏上的 "Execute" 或 "Run" 按钮来执行 SQL 语句。
 *  执行结果将显示在 SQL 编辑器下方的结果窗格中。您可以查看返回的数据集、执行计划等。

PostgreSQL 数据库在安装时会创建一些默认的系统表，这些表用于存储数据库和表的元数据信息以及其他系统级别的信息。以下是一些常见的 PostgreSQL 默认表：

*  pg\_catalog.pg\_tables：该表存储了当前数据库中所有表的元数据信息，如表名、所属模式、表的所有者等。
 *  pg\_catalog.pg\_columns：该表包含了当前数据库中所有表的列信息，如列名、数据类型、是否为主键等。
 *  pg\_catalog.pg\_indexes：该表包含了当前数据库中所有表的索引信息，如索引名称、所属表、索引定义等。
 *  pg\_catalog.pg\_views：该表存储了当前数据库中所有视图的元数据信息，如视图名称、视图定义等。
 *  pg\_catalog.pg\_sequences：该表包含了当前数据库中所有序列（sequence）的信息，如序列名称、当前值、增量等。
 *  pg\_catalog.pg\_stats：该表存储了当前数据库中所有表的统计信息，如行数、唯一值数等。

除了上述默认表之外，还有许多其他的系统表用于存储 PostgreSQL 数据库的各种信息。这些表位于 `pg_catalog` 模式下，并且大部分用户并不直接与这些表交互。相反，用户通常使用 SQL 命令或工具来查询和管理这些表的数据。

> **注意：**
> 
> 不同版本的 PostgreSQL 可能会有略微不同的默认表。此外，我们还可以根据需要在数据库中创建自定义表来存储您自己的数据。想获取更详细的信息，建议参考 下PostgreSQL 官方文档（[PostgreSQL 12.2 手册][PostgreSQL 12.2]）或联系系统管理员以获取特定版本的默认表列表和相关信息。

[0d006db8cb5f41e0b8090b51a9dbcb9c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/acf394399ae5476099181c0498321e4a.png
[Download _ DBeaver Community]: https://dbeaver.io/download/
[dd64105946264a2a9e1685abaac0aa59.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/510b0eab974e449195b09e654f39be2d.png
[08c84a93bb1e4ad4b255fcf50f78ed8d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/2a3bf5cbf433400e8156a0aee39b348d.png
[a32afffe975a46e38b37ab0c5e17686f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/ca97caf2695445868bea223fd189a9ff.png
[c83fd4a3256a48ff956c0c3b1800f6dc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/d23c38f56945411d8c5afde12ea6b4c5.png
[037d5ee27c5744fb89bdb71603896d1d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/567c200f27884e5aad75d21b14d74c20.png
[12c4093338e74aebb13956996f8a6917.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/c3ecc2c23fc1431093c94d8f6f6735b6.png
[1f014b59a3d445519f0b548612cef114.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/2d80df47c5bd4680b0b66e05f98bd4ac.png
[7bd6f337f7fa4023b130e7b9265caeaf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/ffc1dc27e1cf4f80896717b25cf9e3b4.png
[c85c9073058f4d34b7ac91e5674d3ff3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/c32d5e4004f94be5b931299f40a21278.png
[640784cf4fdc46beadcfc5a8f7482c79.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/5a7bc3cb1b764a66acc9917702bb40a6.png
[5248edec89824ae8a445f65a57d41c25.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/4a09779bdf7d41c6b27001425a07a5bc.png
[466cf22cfe564557bfcb6ef04646d38b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/f05da13052af41c3983c35831b290832.png
[cd73a1e5e53445d19fb0ca0e66198b06.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/d2a84e9c1c054e31973df96e638bce24.png
[16611f46b4704b398f87bf37b18e8c15.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/8222d7f00a9341fba620eee54fb1a4f3.png
[PostgreSQL 12.2]: http://www.postgres.cn/docs/12/index.html