PostgreSQL Cheatsheet

环境配置#

安装客户端及服务端工具#

详情参考 https://www.postgresql.org/download/ 链接中所指示的步骤

(仅) 安装客户端连接工具#

# Debian 操作系统
sudo apt update
sudo apt install postgresql-client

# MacOS 操作系统
brew install libpq

服务管理#

Linux 操作系统下使用 systemctl 进行服务管理，服务名称为 postgresql，可以使用的命令例如：

# 启动服务
systemctl status postgresql

# 重启服务
systemctl restart postgresql

# 重载配置文件
systemctl reload postgresql

# 停止服务
systemctl stop postgresql

用户管理#

-- 创建用户
CREATE USER <username> WITH PASSWORD '<password>';

-- 赋予权限 (单个数据库)
GRANT ALL PRIVILEGES ON DATABASE <database> TO <username>;

-- 赋予权限 (单个表)
ALTER TABLE <table_name> OWNER TO <username>;

-- 修改密码
ALTER USER <username> WITH PASSWORD <new_password>;

PostgreSQL 内置语句#

-- 列出服务器所有数据库
\l
\list

-- 使用特定的数据库
\c <db_name>
\connect <db_name>

-- 列出数据库中所有表
\dt

-- 列出数据库中所有表及视图
\d
\dtvmsE

-- 获取指定表的结构
\d <table_name>

-- 导入 SQL 文件
\i <filename>
\include <filename>

DDL (Data Definition Language)#

-- 创建数据库
CREATE DATABASE mydb;

-- 删除数据库
DROP DATABASE mydb;

psql (PostgreSQL interactive terminal)#

在命令行执行 SQL 语句#

psql -h <host> -d <db_name> -U <username> -d <database> -c "<sql>"

在命令行免密执行 SQL 语句#

方式一：环境变量#

export PGPASSWORD='<password>'

配置完成后正常执行命令即可，例如：

方式二：.pgpass 配置文件#

在用户根目录 (例如 ~/) 创建 .pgpass 文件，文件内容如下所示 (替换尖括号内的内容为实际信息)：

<host>:<port>:<db_name>:<username>:<password>

接下来需要设置 .pgpass 文件的权限，使只有用户自己可以读取和写入该文件：

chmod 600 "$HOME/.pgpass"

之后尝试免密执行命令：

psql -h <host> -d <db_name> -U <username> -c "<sql>"

此外，可以通过配置 PGPASSFILE 环境变量指定自定义路径的 .pgpass:

export PGPASSFILE="/path/to/.pgpass"

方式三： pg_service.conf 配置文件#

在用户主目录创建 .pg_service.conf 配置文件，并填入以下内容 (替换尖括号内的内容为实际信息)：

[myservice]
host=<host>
port=<port>
dbname=<db_name>
user=<username>
password=<password>

之后配置 PGSERVICE 环境变量为配置文件中的指定名称：

export PGSERVICE="myservice"

最后尝试免密执行命令：

psql -c "<sql>"

注意，使用 .pg_service.conf 可以存储多个服务连接配置，切换不同的配置只需要将 PGSERVICE 环境变量修改特定的名称。

此外，可以通过配置 PGSERVICEFILE 环境变量指定自定义路径的 .pg_service.conf:

export PGPASSFILE="/path/to/.pg_service.conf"