PostgreSQL05-常用命令

1、服务管理命令

-- 16 为主版本，main 为实例名
systemctl start/stop/status postgresql@16-main
-- 安全停止（等待事务结束）
sudo -u postgres pg_ctl start -D /var/lib/postgresql/16/main
-- 强制停止
sudo -u postgres pg_ctl stop -D /var/lib/postgresql/16/main -m immediate

2、元命令

1、常用元命令

\c 数据库名      -- 切换到指定数据库
\conninfo       -- 显示当前连接的数据库、用户、端口等信息

\dt        -- 列出当前数据库中所有表（仅显示 public schema 下的表）
\dt+       -- 列出当前数据库中所有表及详细信息（大小、描述等）
\dn        -- 列出当前数据库中的所有 schema（模式）
\di        -- 列出当前数据库中的所有索引
\dv        -- 列出当前数据库中的所有视图
\df        -- 列出当前数据库中的所有函数
\du        -- 列出所有数据库用户及权限信息
\dp 表名   -- 查看指定表的权限分配情况
\d 表名    -- 查看指定表的结构（字段、类型、约束、索引等）
\d+ 表名   -- 查看指定表的详细结构（含存储信息、注释等）

\echo 内容                   -- 在 psql 中输出指定内容
\echo :value                -- 显示变量value的值
\echo :PROMPT[1,2,3]        -- 显示提示符格式
\encoding [字元编码名称]      -- 显示或设定用户端字元编码

\q            -- 退出 psql 交互模式

\h sql语句    -- 查看 SQL 命令的语法帮助（如 \h SELECT 查看 SELECT 语法）

\i 文件名     -- 执行指定 SQL 文件中的命令

\l           -- 列出所有数据库

\o 文件名     -- 将后续命令的输出重定向到指定文件（再次执行 \o 关闭重定向）

\password 用户名     -- 修改指定用户的密码

\set                 -- 查看当前 psql 的环境变量设置
\set name value      -- 设置变量
\sf func_name        -- 查看函数定义

\timing              -- 开启 / 关闭命令执行时间统计（显示 SQL 执行耗时）

\watch second        -- 以second间隔秒数，反复执行上一条sql

\x                   -- 纵向显示查询结果

?                    -- 列出所有 psql 元命令的帮助信息

2、获取元命令对应的SQL代码

psql -U ap -E \l

3、常用命令

select oid,datname from pg_database where datname = 'auto';     -- 查看auto库的oid
select oid,relfilenode from pg_class where relname = 'product'; -- 查看product表的oid
select now()            -- 查看时间
select version()        -- 查看版本
select txid_current()   -- 查看当前事务

create database db_name owner user_name         --  创建数据库

alter database dbname owner to new_owner;       --  修改数据库所有者
alter table table_name rename to new_name;      --  重命名一个表
alter table table_name add column column_name type_name;            --  在已有的表里添加字段
alter table table_name drop column column_name;                     --  删除表中的字段
alter table table_name alter column_name type type_name(350)        --  修改数据库列属性
alter table table_name rename column column_name to new_name;       --   重命名一个字段
alter table table_name alter column column_name drop default;       --  去除缺省值
alter table table_name alter column column_name set default new_default_value;       --  给一个字段设置缺省值

4、psql命令

1、命令行执行sql语句

psql -U ap -d auto -c "select * from product where id = 6"

-c 必选项，后接sql语句
-A 命令紧凑，输出没有空格
-t 只显示数据，不显示选项名

输出格式示例

id | name | auto_type |       product_type        |      software_versions       | lic_name
---+------+-----------+---------------------------+------------------------------+----------
 6 | BVS  | 1         | {NX3-X,NX3-S,NX3-E,NX3-P} | {V6.0.1.0,V6.0.1.4,V6.0.2.0} | bvs

其他示例命令

psql --version     -- 查看数据库版本
psql -l            -- 列出所有数据库   (数据库用户可操作)

2、执行文件中的sql语句

psql -U ap -f test.sql -q

-f 指定文件名，*.sql中有sql语句就能执行
-q 取消命令的输出

3、psql传递变量

\set v_id 2                              # \set 设置变量
select * from product where id =:v_id    # 使用变量
\set v_id                                # 取消变量


vim test.sql
select * from product where id =:v_id;   # 引用变量
psql -v v_id=2 mydb pguser -f test.sql   # -v 指定变量的实际值

5、变量的使用

1、通过 \set 将常用查询语句保存为变量

将查询活跃连接的SQL存储为变量 active_session。执行 :active_session 可快速执行SQL

\set active_session '
  SELECT
    pid,
    usename,
    datname,
    query,
    client_addr,
    state
  FROM pg_stat_activity
  WHERE
    pid <> pg_backend_pid()
    AND state = ''active''
  ORDER BY query;
'

:active_session

将查询等待状态连接的SQL存储为变量 wait_event。执行 :wati_event 可快速执行SQL

\set wait_event '
  SELECT
    pid,
    usename,
    datname,
    query,
    client_addr,
    wait_event_type,
    wait_event
  FROM pg_stat_activity
  WHERE
    pid <> pg_backend_pid()
    AND wait_event IS NOT NULL
  ORDER BY
    wait_event_type,
    wait_event;
'
:wait_event

6、数据导入导出

6.1、SQL 文件导入导出（适用于结构 + 数据迁移）

1. 导出 SQL 文件（pg_dump）

导出整个数据库（包含表结构、数据、索引、权限等）

pg_dump -U postgres -d app_db -f /backup/app_db_full.sql

-U 指定用户
-d 指定数据库
-f 指定输出文件路径）

仅导出表结构（不含数据）

pg_dump -U postgres -d app_db -s -f /backup/app_db_schema.sql

-s 表示只导出 schema）

仅导出单张表的结构和数据

pg_dump -U postgres -d app_db -t user_table -f /backup/user_table.sql

-t 指定表名，多张表用多个 -t）

2. 导入 SQL 文件（psql）

先创建目标数据库（若不存在）

CREATE DATABASE app_db_new WITH OWNER app_user;

导入 SQL 文件到目标库

psql -U postgres -d app_db_new -f /backup/app_db_full.sql

6.2、自定义格式备份（高效压缩，支持灵活恢复）

1. 导出自定义格式文件（pg_dump）

全库导出（压缩率高，支持单表恢复）

pg_dump -U postgres -d app_db -F c -f /backup/app_db.dump

-F c 表示自定义格式，比 SQL 文件更小，恢复更快

单表导出

pg_dump -U postgres -d app_db -t user_table -F c -f /backup/user_table.dump

2. 导入自定义格式文件（pg_restore）

恢复全库到新库

pg_restore -U postgres -d app_db_new /backup/app_db.dump

仅恢复单张表（需目标库已存在表结构）

pg_restore -U postgres -d app_db_new -t user_table /backup/app_db.dump

恢复时重命名表（如需迁移到不同表名）
pg_restore -U postgres -d app_db_new --rename-table=old_table:new_table /backup/app_db.dump

6.3、CSV 文本文件（适用于单表数据批量导入导出）

1. 导出表数据为 CSV（psql 元命令或 SQL）

用 psql 的 \copy 命令（客户端执行，无需服务器文件权限）

psql -U postgres -d app_db -c "\copy (SELECT * FROM user_table) TO '/data/user_table.csv' WITH CSV HEADER;"

# HEADER 表示包含表头，即字段名

用 SQL 语句（需服务器有文件写入权限）

COPY (SELECT id, name FROM user_table WHERE status = 'active') TO '/var/lib/postgresql/user_active.csv' WITH CSV;

2. 从 CSV 导入数据到表（\copy 或 COPY）

用 \copy 导入（客户端执行，支持本地 CSV 文件）

psql -U postgres -d app_db -c "\copy user_table (id, name, create_time) FROM '/data/new_users.csv' WITH CSV HEADER;"

# 需确保 CSV 字段顺序与表字段匹配，HEADER 表示跳过首行表头

用 SQL 导入（服务器读取本地文件）：

COPY user_table FROM '/var/lib/postgresql/batch_users.csv' WITH CSV DELIMITER ',';

6.4、跨库 / 跨服务器迁移（直接传输）

通过管道直接导出并导入（无需中间文件）：

# 左侧导出本地库，通过管道直接导入到远程服务器的 app_db_remote 库，-h 指定远程 IP
pg_dump -U postgres -d app_db -F c | pg_restore -U postgres -d app_db_remote -h 192.168.1.200

6.5、注意事项

pg_dump/pg_restore 需要数据库读取权限（通常 postgres 或超级用户）。
COPY 命令生成的文件存储在服务器，需 postgres 用户有写入权限；\copy 操作客户端文件，需本地用户有读写权限。
导出大表时，优先用自定义格式（-F c）或 CSV，避免 SQL 文件过大。
导入大表时，可临时关闭索引和约束（ALTER TABLE user_table DISABLE TRIGGER ALL;），导入后再开启，提升速度。
生产环境导出时，若需数据一致性，可加 -x 排除权限、-S 指定超级用户，或在事务中执行（pg_dump 默认会保证导出期间的一致性）。

7、创建表空间

在 PostgreSQL 中，表空间（tablespace）用于指定数据库对象（表、索引等）的存储路径，可将不同数据存储在不同磁盘 / 分区，便于管理磁盘空间或隔离数据。

7.1、创建表空间的前提

需在服务器上创建一个空目录，作为表空间的存储路径
且该目录必须由 postgres 用户拥有（或数据库运行用户），确保权限可读写。

# 创建表空间目录
sudo mkdir -p /data/pg_tablespace
# 授权给postgres用户（根据实际运行用户调整）
sudo chown postgres:postgres /data/pg_tablespace
# 仅所有者可读写执行
sudo chmod 700 /data/pg_tablespace

7.2、创建表空间（SQL 命令）

CREATE TABLESPACE 表空间名
  OWNER 用户名
  LOCATION '存储目录路径';
-- OWNER 用户名，该选项可选，指定表空间的所有者，默认当前用户

-- 创建名为ts_app_data的表空间，所有者为app_user，存储路径为/data/pg_tablespace
CREATE TABLESPACE ts_app_data
  OWNER app_user
  LOCATION '/data/pg_tablespace';

7.3、使用表空间（创建对象时指定）

创建数据库、表、索引等对象时，通过 TABLESPACE 子句指定使用的表空间

1、创建数据库时指定表空间

-- 新数据库将存储在表空间所指定的路径
CREATE DATABASE app_db
  OWNER app_user
  TABLESPACE ts_app_data;

2、创建表时指定表空间

-- 新表将存储在表空间所指定的路径
CREATE TABLE user_table (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50)
) TABLESPACE ts_app_data;

3、为已有表迁移表空间

-- 将现有表移到ts_app_data表空间
ALTER TABLE old_table SET TABLESPACE ts_app_data;

7.4、查看表空间信息

1、列出所有表空间

-- 列出所有表空间的基础信息（名称、所有者、位置）
\db

-- 列出所有表空间的详细信息（额外包含大小、描述等）
\db+

SELECT spcname AS 表空间名, spcowner::regrole AS 所有者, spclocation AS 存储路径
FROM pg_tablespace;

- pg_tablespace 是系统表，存储表空间元数据
- spcname 是 tablespace name 的缩写,表示表空间名
- spcowner 是 pg_tablespace 表中的字段，存储表空间所有者的 OID（对象标识符）
- regrole 是一个特殊的数据类型，用于将 OID 转换为对应的角色（用户）名
- spcowner::regrole 表示将spcowner转换为regrole类型

2、查看表所在的表空间

# 会显示 user_table 所在的表空间及其他详细信息（如大小、所有者）
\dt+ user_table

SELECT relname AS 表名, tablespace::regnamespace AS 表空间名
FROM pg_class
WHERE relkind = 'r' AND relname = 'user_table';

-- relkind='r'表示普通表

7.5、删除表空间（谨慎操作）

删除表空间前，需确保该表空间下无任何对象（表、索引等），否则会报错：

-- 先删除表空间中的所有对象（如表、索引）,这里删除user_table表
DROP TABLE IF EXISTS user_table;

-- 再删除表空间,这里删除表空间ts_app_data
DROP TABLESPACE IF EXISTS ts_app_data;

-- 踢掉test_user空闲超过1小时的连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename='test_user'
AND state='idle'
AND now() - query_start > interval '1 hour';

posted @ 2024-05-24 11:36 立勋阅读(70) 评论(0) 收藏举报

刷新页面返回顶部

题