使用psql连接TDSQL PG

本节主要介绍如何使用 psql 连接 TDSQL PG。

前置条件

通过 psql 连接 TDSQL PG：

• 通过浏览器访问一体化管控平台，在左侧导航栏选择实例管理，点击需要连接的实例ID， 选择节点管理页面。
• 如果是集中式实例，点击数据节点；如果是分布式实例，点击计算节点。
• 将鼠标移动到节点IP地址后的号上，弹出的提示框中显示了该节点配置信息，支持快捷复制。
  • bin 目录：数据库的可执行程序所在的目录
  • data 目录：数据库的数据文件目录
  • 环境变量配置：登录此节点，psql连接数据库前需要配置的环境变量。
  • psql 连接：psql连接此数据库节点的参数信息
• 根据节点IP地址，登录到相应的服务器，执行上一步环境变量配置项中的命令。

连接方式

psql可以通过四种方式连接实例，分别是：使用参数连接、使用conninfo字符串或者URI连接、配置SSL连接、配置环境变量后快捷连接。

使用参数连接

下面列出了参数连接需要配置的内容，建议直接查看一体化管控平台，复制节点的psql 连接信息：

• -h 数据库连接地址，分布式实例通常是计算节点的IP
• -p数据库服务的端口号
• -U 数据库用户名
• -d 需要访问的数据库名称
  命令格式如下：

psql -h <数据库连接地址> -p <端口号> -U <用户名> -d <数据库名称>

示例

以 tbase 身份登录 TDSQL 数据库：

psql -h 10.211.55.39 -p 11345 -U tbase -d postgres

使用conninfo字符串或者URI连接

psql通过conninfo字符串或者URI连接数据库，同样需要在conninfo或URI中指定数据库连接地址等参数。

conninfo: psql "host=<数据库连接地址> port=<端口号> dbname=<数据库名> user=用户名"

URI: postgresql://[用户名[:密码]@][数据库连接地址][:端口号][/数据库名][?param1=value1&...]

示例

示例1：通过conninfo连接数据库：

psql "host=10.211.55.39 port=11345 dbname=postgres user=tbase"

示例2：通过URI连接数据库：

psql postgresql://tbase@10.211.55.39:11345/postgres    

配置SSL连接

TDSQL支持通过配置单/双向认证，开启SSL连接加密后，通过psql连接数据库。

配置单项认证连接

单项认证仅需要服务端持有证书，客户端需要验证服务端证书的有效性，确认后开启通信。

创建服务端证书：

示例采用自签名证书进行演示，实际生产环境中应该使用权威证书机构签署的证书。

注意：

1. 请登录一体化管控平台，在实例的节点管理页面，查看该节点配置的data 目录，确认data 目录的完整路径。
2. root.crt、server.crt和server.key需要拷贝到节点data 目录下，并通过chown命令，将文件的所有者修改成与postgresql.conf一致，保证数据库可执行程序有权限读取。
3. 配置完成后需要通过pg_ctl reload命令让数据库加载新配置的参数，也可以重启数据库使得参数生效。

1. 通过下述命令生成证书

# 1. 创建证书签名请求和密钥文件
openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=root"
# 2. 创建根证书颁发机构
openssl x509 -req -in root.csr -text -days 3650 -extfile /etc/pki/tls/openssl.cnf  -extensions v3_ca -signkey root.key -out root.crt
# 3. 创建由根证书颁发机构签名的服务器证书 
openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=postgres"    
chmod og-rwx server.key # 确认只有文件拥有者有读写权限，移除其他用户和组用户的权限
openssl x509 -req -in server.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt

2. 将上述命令生成的root.crt、server.crt和server.key拷贝到节点数据目录下，并通过chown命令，将文件的所有者修改成与postgresql.conf文件相同。
3. 修改该节点data 目录下的postgresql.conf文件，配置ssl参数。

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

4. 修改该节点data 目录下的pg_hba.conf，配置访问控制，通过hostssl指定SSL连接。

hostssl      all    all    0.0.0.0/0    md5

5. 通过pg_ctl reload命令，让数据库加载新配置的参数；如果pg_ctl命令找不到，请参照前置条件进行环境变量配置；请将下述的$data_dir替换为实际的数据目录。

  pg_ctl reload -D $data_dir

6. psql连接节点，通过提示信息 SSL connection 确认该连接为SSL连接。

psql "host=10.211.55.39 port=11345 dbname=postgres user=tbase"

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

配置双向认证连接

配置双向认证连接需要在单向认证的基础上，配置客户端证书，服务端和客户端都需要确认彼此证书的有效性，确认后开启通信。
请参考“配置单项认证连接”中的示例，创建服务端证书。

注意：

请登录一体化管控平台，在实例的“节点管理”页面，查看该节点配置的“data 目录”，确认数据目录的完整路径。

1. 修改该节点data 目录下的postgresql.conf文件，配置ssl参数。

ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

2. 修改该节点data 目录下的pg_hba.conf，配置访问控制，将认证方法设置为cert，SSL客户端证书认证。

  hostssl      all    all    0.0.0.0/0    cert

3. 通过pg_ctl reload命令，让数据库加载新配置的参数；如果pg_ctl命令找不到，请参照前置条件进行“环境变量配置”；请将下述的$data_dir替换为实际的数据目录。

    pg_ctl reload -D $data_dir

4. 创建客户端证书，示例采用自签名证书进行演示，实际生产环境中应该使用权威证书机构签署的证书.

   注意：

   请将下述的tbase用户名替换为实际连接此数据库节点的用户名。

openssl req -new -nodes -text -out postgresql.csr -keyout postgresql.key  -subj "/CN=tbase"   
chmod og-rwx postgresql.key
openssl x509 -req -in postgresql.csr -text -days 365 -CA root.crt -CAkey root.key -CAcreateserial -out postgresql.crt

5. 使用psql连接
   示例1：通过conninfo字符串连接
   在客户端机器创建～/.postgresql目录，并将上方创建的root.crt，postgresql.crt，postgresql.key复制到～/.postgresql目录。

psql "host=10.211.55.39 port=11345 dbname=postgres user=tbase"

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# 

示例2：通过conninfo字符串连接，分别通过sslcert、sslkey和sslrootcert参数设置postgresql.crt、postgresql.key和root.crt的文件路径。

psql "host=10.211.55.39 port=11345 dbname=postgres user=tbase sslmode=require 
sslcert=/PATH/TO/postgresql.crt sslkey=/PATH/TO/postgresql.key sslrootcert=/PATH/TO/root.crt"

psql (PostgreSQL 10.0 @ xxx 2024-02-21 10:27:15)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# 

示例3: 分别通过环境变量PGSSLROOTCERT、PGSSLCERT、PGSSLKEY设置root.crt、postgresql.crt和postgresql.key的文件路径。

export PGSSLROOTCERT=/PATH/TO/root.crt
export PGSSLCERT=/PATH/TO/postgresql.crt
export PGSSLKEY=/PATH/TO/postgresql.key

psql "host=10.211.55.39 port=11345 dbname=postgres user=tbase sslmode=require"

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# 

配置环境变量后快捷连接

TDSQL PG 支持通过在环境变量中指定主机名称，端口地址，数据库以及数据库用户后通过命令psql即可快速连接。相关环境变量分别为PGHOST，PGPORT，PGDATABASE，PGUSER。

示例

export PGUSER=tbase
export PGHOST=10.211.55.39
export PGDATABASE=postgres
export PGPORT=11345

psql

psql常用命令

更多信息

如果在通过 psql 客户端连接 TDSQL PG的过程中遇到本文档未涉及的问题，您可以查阅PostgreSQL 客户端官方的使用文档。