数据库：一文掌握 PostgreSQL 的各种指令（PostgreSQL指令备忘）
PostgreSQL 备忘清单为您提供了常用的 PostgreSQL 命令和语句。PostgreSQL 工作
入门
切换和连接
$ sudo -u postgres psql
列出全部数据库
postgres=# \l
连接到名为 postgres 的数据库
postgres=# \c postgres
断开
postgres=# \q
postgres=# \!
psql 命令
参数示例分析[-d] <database>psql -d mydb连接到数据库-Upsql -U john mydb以特定用户身份连接-h -ppsql -h localhost -p 5432 mydb连接到主机/端口-U -h -p -dpsql -U admin -h 192.168.1.5 -p 2506 -d mydb连接长途 PostgreSQL-Wpsql -W mydb逼迫暗码-cpsql -c ‘\c postgres’ -c ‘\dt
’执行 SQL 查询或命令-Hpsql -c “\l
+” -H postgres > database.html生成 HTML 陈诉-lpsql -l列出全部数据库-fpsql mydb -f file.sql从文件执行命令-Vpsql -V打印 psql 版本得到帮助
:--\hSQL 命令语法帮助\h DELETEDELETE SQL 语句语法\?PostgreSQL 命令列表 在 PostgreSQL 控制台中运行
Recon 观察
显示版本
SHOW SERVER_VERSION;
显示体系状态
\conninfo
显示情况变量
SHOW ALL;
列出用户
SELECT rolname FROM pg_roles;
显示当前用户
SELECT current_user;
显示当前用户的权限
\du
显示当前数据库
SELECT current_database();
显示数据库中的全部表
\dt
列出函数
\df <schema>
Databases 数据库
列出数据库
\l
连接到数据库
\c <database_name>
显示当前数据库
SELECT current_database();
创建数据库
CREATE DATABASE <database_name> WITH OWNER <username>;
删除数据库
DROP DATABASE IF EXISTS <database_name>;
重命名数据库
ALTER DATABASE <old_name> RENAME TO <new_name>;
Tables 表
列出当前数据库中的表
\dt
SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;全局列表
\dt
*.*.SELECT * FROM pg_catalog.pg_tables 列出表结构
\d <table_name>
\d+ <table_name>
SELECT column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<table_name>';
创建表
CREATE TABLE <table_name>(
<column_name> <column_type>,
<column_name> <column_type>
);
创建表，主键自增
CREATE TABLE <table_name> (
<column_name> SERIAL PRIMARY KEY
);
删除表
DROP TABLE IF EXISTS <table_name> CASCADE;
Permissions 权限
成为 postgres 用户，如果您有权限错误
sudo su - postgres
psql
授予 对数据库的全部权限
GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user_name>;
授予数据库连接权限
GRANT CONNECT ON DATABASE <db_name> TO <user_name>;
授予架构权限
GRANT USAGE ON SCHEMA public TO <user_name>;
授予函数权限
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO <user_name>;
授予在全部表上选择、更新、插入、删除的权限
GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;
在表上授予权限
GRANT SELECT, UPDATE, INSERT ON <table_name> TO <user_name>;
授予对表的选择权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <user_name>;
Columns 列
添加栏目
ALTER TABLE <table_name> IF EXISTS
ADD <column_name> <data_type> [<constraints>];
更新栏
ALTER TABLE <table_name> IF EXISTS
ALTER <column_name> TYPE <data_type> [<constraints>];
删除列
ALTER TABLE <table_name> IF EXISTS
DROP <column_name>;
将列更新为自增主键
ALTER TABLE <table_name>
ADD COLUMN <column_name> SERIAL PRIMARY KEY;
使用自动递增的主键插入表中
INSERT INTO <table_name>
VALUES (DEFAULT, <value1>);
INSERT INTO <table_name> (<column1_name>,<column2_name>)
VALUES ( <value1>,<value2> );
Data 数据
选择 全部数据
SELECT * FROM <table_name>;
读取一行数据
SELECT * FROM <table_name> LIMIT 1;
搜刮数据
SELECT * FROM <table_name> WHERE <column_name> = <value>;
插入 数据
INSERT INTO <table_name> VALUES( <value_1>, <value_2> );
更新 数据
UPDATE <table_name>
SET <column_1> = <value_1>, <column_2> = <value_2>
WHERE <column_1> = <value>;
删除 全部数据
DELETE FROM <table_name>;
删除特定数据
DELETE FROM <table_name>
WHERE <column_name> = <value>;
Users 用户
列出角色
SELECT rolname FROM pg_roles;
创建用户
CREATE USER <user_name> WITH PASSWORD '<password>';
删除用户
DROP USER IF EXISTS <user_name>;
更改 用户暗码
ALTER ROLE <user_name> WITH PASSWORD '<password>';
Schema
列出 Schemas
\dn
SELECT schema_name FROM information_schema.schemata;
SELECT nspname FROM pg_catalog.pg_namespace;
创建架构
CREATE SCHEMA IF NOT EXISTS <schema_name>;
删除模式
DROP SCHEMA IF EXISTS <schema_name> CASCADE;
PostgreSQL 命令
表
:--\d <table>形貌表\d+ <table>详细形貌表格\dt
列出当前模式中的表\dt
*.*列出全部模式中的表\dt
<schema>.*列出架构的表\dp列出表访问权限\det[+]列出外部表 查询缓冲区
:--\e 编辑查询缓冲区(或文件)\ef 编辑函数定义\p显示内容\r重置(清除)查询缓冲区\s 显示汗青记录或保存到文件\w FILE将查询缓冲区写入文件 信息
:--\l
[+]列出全部数据库\dn列出架构\di列出索引\du
[+]列出角色\ds列出序列\df列出函数\deu[+]列出用户映射\dv列表视图\dl列出大对象\dT列出数据类型\da列出聚合\db[+]列出表空间\dc列出转化\dC[+]列出演员表\ddp列出默认权限\dd显示对象形貌\dD列出域\des[+]列出国外服务器\dew[+]列出外部数据包装器\dF[+]列出文本搜刮设置\dFd[+]列出文本搜刮词典\dFp[+]列出文本搜刮解析器\dFt[+]列出文本搜刮模板\dL列出步伐语言\do列出运算符\dO列出排序规则\drds列出每个数据库的角色设置\dx[+]列出扩展 S：显示体系对象，+：附加细节
连接
:--\c 连接到新数据库\encoding 显示或设置客户端编码\password 更改暗码\conninfo
显示信息 格式化
:--\a在未对齐和对齐之间切换\C 设置表格标题，如果没有则取消设置\f 显示或设置未对齐的字段分隔符\H切换 HTML 输出模式\t 仅显示行\T 设置或取消设置 HTML <table> 标签属性\x 切换扩展输出 输入输出
:--\copy ...导入/导出表 另见： 复制\echo 打印字符串\i FILE执行文件\o 将全部结果导出到文件\qecho 输出流的字符串 变量
:--\prompt NAME设置变量\set ]设置变量 (如果没有参数，则列出全部变量)\unset NAME删除变量 杂项
:--\cd 更改目录\timing 切换时间\! 在shell中执行\! ls -l在shell中列出全部 大对象
[*]\l
o_export LOBOID FILE
[*]\l
o_import FILE
[*]\l
o_list
[*]\l
o_unlink LOBOID
各种各样的
备份
使用 pg_dumpall 备份全部数据库
$ pg_dumpall -U postgres > all.sql
使用 pg_dump 备份数据库
$ pg_dump -d mydb -f mydb_backup.sql
[*] -a 只转储数据，而不是模式(schema)
[*] -s 只转储模式，不转储数据
[*] -c 在重新创建之前删除数据库
[*] -C 还原前创建数据库
[*] -t 仅转储命名表
[*] -F 格式(c：自定义，d：目录，t：tar)
使用 pg_dump -? 获取完整的选项列表
规复
使用 psql 规复数据库
$ psql -U user mydb < mydb_backup.sql
使用 pg_restore 规复数据库
$ pg_restore -d mydb mydb_backup.sql -c
[*] -U 指定数据库用户
[*] -c 在重新创建之前删除数据库
[*] -C 还原前创建数据库
[*] -e 如果碰到错误退出
[*] -F 格式(c:自定义，d:目录，t:tar，p:纯文本sql(默认))
使用 pg_restore -? 获取完整的选项列表
长途访问
获取 postgresql.conf 的位置
$ psql -U postgres -c 'SHOW config_file'
附加到 postgresql.conf
listen_addresses = '*'
附加到 pg_hba.conf(与 postgresql.conf 相同的位置)
hostallall0.0.0.0/0md5
hostallall::/0 md5
重启 PostgreSQL 服务器
$ sudo systemctl restart postgresql
导入/导出 CSV
将表格导出为 CSV 文件
\copy table TO '<path>' CSV
\copy table(col1,col1) TO '<path>' CSV
\copy (SELECT...) TO '<path>' CSV
将 CSV 文件导入表格
\copy table FROM '<path>' CSV
\copy table(col1,col1) FROM '<path>' CSV
另见：复制
跨版本升级
pg_upgrade 跨版本升级
$ /usr/lib/postgresql/16/bin/pg_upgrade \
-b /usr/lib/postgresql/15/bin \
-B /usr/lib/postgresql/16/bin \
-d /var/lib/postgresql/15/main \
-D /var/lib/postgresql/16/main \
-o " -c config_file=/etc/postgresql/15/main/postgresql.conf" \
-O " -c config_file=/etc/postgresql/16/main/postgresql.conf"
[*] -b 旧版本二进制文件目录
[*] -B 新版本二进制文件目录
[*] -d 旧版本数据目录
[*] -D 新版本数据目录
[*] -o 旧版本主设置文件
[*] -O 新版本主设置文件
[*] -c 仅check，不执行升级，可先加-c查抄是否有报错，没有报错再运行升级
使用 pg_upgrade -? 获取完整的选项列表
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]