基本

切换和连接

$ sudo -u postgres psql

列出所有数据库

postgres=# \l

连接到名为 postgres 的数据库

postgres=# \c postgres

断开

postgres=# \q

postgres=# \!

psql 命令

参数

示例

说明

[-d]

psql -d mydb

连接到数据库

-U

psql -U john mydb

以特定用户身份连接

-h -p

psql -h localhost -p 5432 mydb

连接到主机/端口

-U -h -p -d

psql -U admin -h 192.168.1.5 -p 2506 -d mydb

连接远程 PostgreSQL

-W

psql -W mydb

强制密码

-c

psql -c ‘\c postgres’ -c ‘\dt’

执行 SQL 查询或命令

-H

psql -c “\l+” -H postgres > database.html

生成 HTML 报告

-l

psql -l

列出所有数据库

-f

psql mydb -f file.sql

从文件执行命令

-V

psql -V

打印 psql 版本

获得帮助

\h

SQL 命令语法帮助

\h DELETE

DELETE SQL 语句语法

\?

PostgreSQL 命令列表

在 PostgreSQL 控制台中运行

PostgreSQL 工作

Recon 观察

显示版本

SHOW SERVER_VERSION;

显示系统状态

\conninfo

显示环境变量

SHOW ALL;

列出用户

SELECT rolname FROM pg_roles;

显示当前用户

SELECT current_user;

显示当前用户的权限

\du

显示当前数据库

SELECT current_database();

显示数据库中的所有表

\dt

列出函数

\df

Databases 数据库

列出数据库

\l

连接到数据库

\c

显示当前数据库

SELECT current_database();

创建数据库

CREATE DATABASE WITH OWNER ;

删除数据库

DROP DATABASE IF EXISTS ;

重命名数据库

ALTER DATABASE RENAME TO ;

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

\d+

SELECT column_name, data_type, character_maximum_length

FROM INFORMATION_SCHEMA.COLUMNS

WHERE table_name = ‘

‘;

创建表

CREATE TABLE

(

,

);

创建表，主键自增

CREATE TABLE

(

SERIAL PRIMARY KEY

);

删除表

DROP TABLE IF EXISTS

CASCADE;

Permissions 权限

成为 postgres 用户，如果您有权限错误

sudo su – postgres

psql

授予 对数据库的所有权限

GRANT ALL PRIVILEGES ON DATABASE TO ;

授予数据库连接权限

GRANT CONNECT ON DATABASE TO ;

授予架构权限

GRANT USAGE ON SCHEMA public TO ;

授予函数权限

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ;

授予在所有表上选择、更新、插入、删除的权限

GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO ;

在表上授予权限

GRANT SELECT, UPDATE, INSERT ON

TO ;

授予对表的选择权限

GRANT SELECT ON ALL TABLES IN SCHEMA public TO ;

Columns 列

添加栏目

ALTER TABLE

IFEXISTS

ADD [<constraints>];

更新栏

ALTER TABLE

IFEXISTS

ALTER TYPE [<constraints>];

删除列

ALTER TABLE

IFEXISTS

DROP ;

将列更新为自增主键

ALTER TABLE

ADD COLUMN SERIAL PRIMARY KEY;

使用自动递增的主键插入表中

INSERT INTO

VALUES (DEFAULT, );

INSERT INTO

(,)

VALUES ( , );

Data 数据

选择 所有数据

SELECT * FROM

;

读取一行数据

SELECT * FROM

LIMIT1;

搜索数据

SELECT * FROM

WHERE = <value>;

插入 数据

INSERT INTO

VALUES( , );

更新 数据

UPDATE

SET = , =

WHERE = ;

删除 所有数据

DELETE FROM

;

删除特定数据

DELETE FROM

WHERE = <value>;

Users 用户

列出角色

SELECT rolname FROM pg_roles;

创建用户

CREATE USER WITH PASSWORD ”;

删除用户

DROP USER IF EXISTS ;

更改 用户密码

ALTER ROLE WITH PASSWORD ”;

Schema

列出 Schemas

\dn

SELECT schema_name FROM information_schema.schemata;

SELECT nspname FROM pg_catalog.pg_namespace;

创建架构

CREATE SCHEMA IF NOT EXISTS ;

删除模式

DROP SCHEMA IF EXISTS CASCADE;

PostgreSQL 命令

表

\d

描述表

\d+

详细描述表格

\dt

列出当前模式中的表

\dt *.*

列出所有模式中的表

\dt .*

列出架构的表

\dp

列出表访问权限

\det[+]

列出外部表

查询缓冲区

\e [FILE]

编辑查询缓冲区(或文件)

\ef [FUNC]

编辑函数定义

\p

显示内容

\r

重置(清除)查询缓冲区

\s [FILE]

显示历史记录或保存到文件

\w FILE

将查询缓冲区写入文件

信息

\l[+]

列出所有数据库

\dn[S+]

列出架构

\di[S+]

列出索引

\du[+]

列出角色

\ds[S+]

列出序列

\df[antw][S+]

列出函数

\deu[+]

列出用户映射

\dv[S+]

列表视图

\dl

列出大对象

\dT[S+]

列出数据类型

\da[S]

列出聚合

\db[+]

列出表空间

\dc[S+]

列出转化

\dC[+]

列出演员表

\ddp

列出默认权限

\dd[S]

显示对象描述

\dD[S+]

列出域

\des[+]

列出国外服务器

\dew[+]

列出外部数据包装器

\dF[+]

列出文本搜索配置

\dFd[+]

列出文本搜索词典

\dFp[+]

列出文本搜索解析器

\dFt[+]

列出文本搜索模板

\dL[S+]

列出程序语言

\do[S]

列出运算符

\dO[S+]

列出排序规则

\drds

列出每个数据库的角色设置

\dx[+]

列出扩展

S：显示系统对象，+：附加细节

连接

\c [DBNAME]

连接到新数据库

\encoding [ENCODING]

显示或设置客户端编码

\password [USER]

更改密码

\conninfo

显示信息

格式化

\a

在未对齐和对齐之间切换

\C [STRING]

设置表格标题，如果没有则取消设置

\f [STRING]

显示或设置未对齐的字段分隔符

\H

切换 HTML 输出模式

\t [on|off]

仅显示行

\T [STRING]

设置或取消设置 HTML

标签属性

\x [on|off]

切换扩展输出

输入输出

\copy …

导入/导出表另见：复制

\echo [STRING]

打印字符串

\i FILE

执行文件

\o [FILE]

将所有结果导出到文件

\qecho [STRING]

输出流的字符串

变量

\prompt [TEXT] NAME

设置变量

\set [NAME [VALUE]]

设置变量(如果没有参数，则列出所有变量)

\unset NAME

删除变量

杂项

\cd [DIR]

更改目录

\timing [on|off]

切换时间

\! [COMMAND]

在shell中执行

\! ls -l

在shell中列出所有

大对象

  • \lo_export LOBOID FILE
  • \lo_import FILE [COMMENT]
  • \lo_list
  • \lo_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 相同的位置)

host all all 0.0.0.0/0 md5

host all all ::/0 md5

重启 PostgreSQL 服务器

$ sudo systemctl restart postgresql

导入/导出 CSV

将表格导出为 CSV 文件

\copy table TO ” CSV

\copy table(col1,col1) TO ” CSV

\copy (SELECT…) TO ” CSV

将 CSV 文件导入表格

\copy table FROM ” CSV

\copy table(col1,col1) FROM ” CSV

ALTER STABLE table ALTER COLUMN userid TYPE integer USING (userid::integer);

ALTER STABLE table ALTER COLUMN reviewerid TYPE int4 USING (reviewerid::integer);

相关文章

Copyright © maxssl.com 版权所有 浙ICP备2022011180号