PostgreSQL 常用管理命令

登录命令

#连接指定服务器上的数据库

psql -h IP -p 端口 -U 用户名 -d 数据库名 -W

psql是PostgreSQL的一个命令行交互式客户端工具，它具有非常丰富的功能，类似于Oracle的命令行工具sqlplus。

psql连接和管理数据库的常用参数如下：
-h
#数据库所在的IP地址
-p
#（默认5432）数据库的监听端口
-U
#用户名
-d
#数据库名称
-W
#要求输入密码
-v
#用于显示附加信息
-f
#指定要运行的脚本文件
-a
#以更友好的方式显示查询的结果
-l
#显示数据库列表

psql完整命令及用法

test_pg-# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display result in crosstab
  \errverbose            show most recent error message at maximum verbosity
  \g [(OPTIONS)] [FILE]  execute query (and send result to file or |pipe);
                         \g with no arguments is equivalent to a semicolon
  \gdesc                 describe result of query, without executing it
  \gexec                 execute query, then execute each value in its result
  \gset [PREFIX]         execute query and store result in psql variables
  \gx [(OPTIONS)] [FILE] as \g, but forces expanded output mode
  \q                     quit psql
  \watch [SEC]           execute query every SEC seconds


Help
  \? [commands]          show help on backslash commands
  \? options             show help on psql command-line options
  \? variables           show help on special variables
  \h [NAME]              help on syntax of SQL commands, * for all commands


Query Buffer
  \e [FILE] [LINE]       edit the query buffer (or file) with external editor
  \ef [FUNCNAME [LINE]]  edit function definition with external editor
  \ev [VIEWNAME [LINE]]  edit view definition with external editor
  \p                     show the contents of the query buffer
  \r                     reset (clear) the query buffer
  \w FILE                write query buffer to file


Input/Output
  \copy ...              perform SQL COPY with data stream to the client host
  \echo [-n] [STRING]    write string to standard output (-n for no newline)
  \i FILE                execute commands from file
  \ir FILE               as \i, but relative to location of current script
  \o [FILE]              send all query results to file or |pipe
  \qecho [-n] [STRING]   write string to \o output stream (-n for no newline)
  \warn [-n] [STRING]    write string to standard error (-n for no newline)


Conditional
  \if EXPR               begin conditional block
  \elif EXPR             alternative within current conditional block
  \else                  final alternative within current conditional block
  \endif                 end conditional block


Informational
  (options: S = show system objects, + = additional detail)
  \d[S+]                 list tables, views, and sequences
  \d[S+]  NAME           describe table, view, sequence, or index
  \da[S]  [PATTERN]      list aggregates
  \dA[+]  [PATTERN]      list access methods
  \dAc[+] [AMPTRN [TYPEPTRN]]  list operator classes
  \dAf[+] [AMPTRN [TYPEPTRN]]  list operator families
  \dAo[+] [AMPTRN [OPFPTRN]]   list operators of operator families
  \dAp[+] [AMPTRN [OPFPTRN]]   list support functions of operator families
  \db[+]  [PATTERN]      list tablespaces
  \dc[S+] [PATTERN]      list conversions
  \dconfig[+] [PATTERN]  list configuration parameters
  \dC[+]  [PATTERN]      list casts
  \dd[S]  [PATTERN]      show object descriptions not displayed elsewhere
  \dD[S+] [PATTERN]      list domains
  \ddp    [PATTERN]      list default privileges
  \dE[S+] [PATTERN]      list foreign tables
  \des[+] [PATTERN]      list foreign servers
  \det[+] [PATTERN]      list foreign tables
  \deu[+] [PATTERN]      list user mappings
  \dew[+] [PATTERN]      list foreign-data wrappers
  \df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]]
                         list [only agg/normal/procedure/trigger/window] functions
  \dF[+]  [PATTERN]      list text search configurations
  \dFd[+] [PATTERN]      list text search dictionaries
  \dFp[+] [PATTERN]      list text search parsers
  \dFt[+] [PATTERN]      list text search templates
  \dg[S+] [PATTERN]      list roles
  \di[S+] [PATTERN]      list indexes
  \dl[+]                 list large objects, same as \lo_list
  \dL[S+] [PATTERN]      list procedural languages
  \dm[S+] [PATTERN]      list materialized views
  \dn[S+] [PATTERN]      list schemas
  \do[S+] [OPPTRN [TYPEPTRN [TYPEPTRN]]]
                         list operators
  \dO[S+] [PATTERN]      list collations
  \dp     [PATTERN]      list table, view, and sequence access privileges
  \dP[itn+] [PATTERN]    list [only index/table] partitioned relations [n=nested]
  \drds [ROLEPTRN [DBPTRN]] list per-database role settings
  \dRp[+] [PATTERN]      list replication publications
  \dRs[+] [PATTERN]      list replication subscriptions
  \ds[S+] [PATTERN]      list sequences
  \dt[S+] [PATTERN]      list tables
  \dT[S+] [PATTERN]      list data types
  \du[S+] [PATTERN]      list roles
  \dv[S+] [PATTERN]      list views
  \dx[+]  [PATTERN]      list extensions
  \dX     [PATTERN]      list extended statistics
  \dy[+]  [PATTERN]      list event triggers
  \l[+]   [PATTERN]      list databases
  \sf[+]  FUNCNAME       show a function's definition
  \sv[+]  VIEWNAME       show a view's definition
  \z      [PATTERN]      same as \dp


Large Objects
  \lo_export LOBOID FILE write large object to file
  \lo_import FILE [COMMENT]
                         read large object from file
  \lo_list[+]            list large objects
  \lo_unlink LOBOID      delete a large object


Formatting
  \a                     toggle between unaligned and aligned output mode
  \C [STRING]            set table title, or unset if none
  \f [STRING]            show or set field separator for unaligned query output
  \H                     toggle HTML output mode (currently off)
  \pset [NAME [VALUE]]   set table output option
                         (border|columns|csv_fieldsep|expanded|fieldsep|
                         fieldsep_zero|footer|format|linestyle|null|
                         numericlocale|pager|pager_min_lines|recordsep|
                         recordsep_zero|tableattr|title|tuples_only|
                         unicode_border_linestyle|unicode_column_linestyle|
                         unicode_header_linestyle)
  \t [on|off]            show only rows (currently off)
  \T [STRING]            set HTML <table> tag attributes, or unset if none
  \x [on|off|auto]       toggle expanded output (currently off)


Connection
  \c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo}
                         connect to new database (currently "test_pg")
  \conninfo              display information about current connection
  \encoding [ENCODING]   show or set client encoding
  \password [USERNAME]   securely change the password for a user


Operating System
  \cd [DIR]              change the current working directory
  \getenv PSQLVAR ENVVAR fetch environment variable
  \setenv NAME [VALUE]   set or unset environment variable
  \timing [on|off]       toggle timing of commands (currently off)
  \! [COMMAND]           execute command in shell or start interactive shell


Variables
  \prompt [TEXT] NAME    prompt user to set internal variable
  \set [NAME [VALUE]]    set internal variable, or list all if no parameters
  \unset NAME            unset (delete) internal variable

创建和删除命令

#创建用户和密码

create user 用户名 password '密码';

#查询用户

select * from pg_user;

#设置只读权限

alter user 用户名 set default_transaction_read_only = on;

#设置可操作的数据库

grant all on database 数据库名 to 用户名;

#授权可操作的模式和权限

-- 授权
grant select on all tables in schema public to 用户名;

-- 授权
GRANT ALL ON TABLE public.user TO mydata;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE public.user TO mydata_dml;
GRANT SELECT ON TABLE public.user TO mydata_qry;

#撤回在public模式下的权限

revoke select on all tables in schema public from 用户名;

#撤回在information_schema模式下的权限

revoke select on all tables in schema information_schema from 用户名;

#撤回在pg_catalog模式下的权限

revoke select on all tables in schema pg_catalog from 用户名;

#撤回对数据库的操作权限

revoke all on database 数据库名 from 用户名;

#删除用户

drop user 用户名;

权限的设置、撤销

#设置只读权限

alter user 用户名 set default_transaction_read_only = on;

#设置可操作的数据库

grant all on database 数据库名 to 用户名;

#设置可操作的模式和权限

grant select,insert,update,delete on all tables in schema public to 用户名;

#撤回在public模式下的权限

revoke select on all tables in schema public from 用户名;

#撤回在information_schema模式下的权限

revoke select on all tables in schema information_schema from 用户名;

#撤回在pg_catalog模式下的权限

revoke select on all tables in schema pg_catalog from 用户名;

#撤回对数据库的操作权限

revoke all on database 数据库名 from 用户名;

SCHEMA

#CREATE SCHEMA 语句来创建模式，语法格式如下：

CREATE SCHEMA myschema.mytable (...);

#创建和当前用户同名模式（schema）

注意：用户名与 schema 同名，且用户具有访问改 schema 的权限，用户连入数据库时，默认即为当前 schema。

create schema AUTHORIZATION CURRENT_USER;

#自定义创建模式（schema）

create schema 模式名称;

注意：如果不创建scheme，并且语句中不写scheme，则默认scheme使用内置的public。

#查看数据库下的所有（schema）

select * from information_schema.schemata;

#切换schema

set search_path to test_schema

示例：

test_pg=# select current_schema();
current_schema
----------------
postgres
(1 row)
test_pg=# set search_path to schema_test;
SET
test_pg=# select current_schema();
current_schema
----------------
schema_test
(1 row)

数据库管理

#查询所有数据库

select datname from pg_database;

#创建数据库

create database 数据库名 owner 所属用户 encoding UTF8;

注意：创建完数据库，需要切换到数据库下，创建和当前用户同名scheme，删除数据库后schema也会一并删除：

-- 重新登陆到新数据库下，执行如下语句

create schema AUTHORIZATION CURRENT_USER;

#切换数据库

\connect  DBNAME
\c  DBNAME

#删除数据库

drop database 数据库名;

注意：删库前需要关闭所有会话，不然会提示：

ERROR:  database "mydb" is being accessed by other users

DETAIL:  There are 8 other sessions using the database.

#关闭数据库所有会话

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='mydb' AND pid<>pg_backend_pid();

表管理

#建表模板语句

create table "t_user" (
"id" bigserial not null,
"username" varchar (64) not null,
"password" varchar (64) not null,
"create_time" timestamp not null default current_timestamp,
"update_time" timestamp not null default current_timestamp,
constraint t_user_pk primary key (id)
);

comment on column "t_user"."id" is '主键';
comment on column "t_user"."username" is '用户名';
comment on column "t_user"."password" is '密码';
comment on column "t_user"."create_time" is '创建时间';
comment on column "t_user"."update_time" is '更新时间';

#查询schema中所有表

select table_name from information_schema.tables where table_schema = 'myuser';

#创建表

CREATE TABLE public.t_user (
  "id" BIGSERIAL NOT NULL,
  "username" VARCHAR(64) NOT NULL,
  "password" VARCHAR(64) NOT NULL,
  "create_time" TIMESTAMP(0) default CURRENT_TIMESTAMP not null,
  "update_time" TIMESTAMP(0) default CURRENT_TIMESTAMP not null
);
-- 注释
COMMENT ON TABLE public.t_user IS '用户表';
COMMENT ON COLUMN public.t_user.id IS '主键';
COMMENT ON COLUMN public.t_user.username IS '用户名';
COMMENT ON COLUMN public.t_user.password IS '密码';
COMMENT ON COLUMN public.t_user.create_time IS '创建时间';
COMMENT ON COLUMN public.t_user.update_time IS '更新时间';

-- 创建自增序列
alter sequence "t_user_ID_seq" restart with 1 increment by 1;
-- 创建主键序列
drop index if exists "t_user_pkey";
alter table "t_user" add constraint "t_user_pkey" primary key ("ID");

#根据已有表结构创建表

create table if not exists 新表 (like 旧表 including indexes including comments including defaults);

#删除表

drop table if exists "t_template" cascade;

#查询表结构

select table_name,column_name,data_type,character_maximum_length from information_schema.columns where table_schema='schema_test' and table_name='t_user';
table_name | column_name |          data_type          | character_maximum_length
------------+-------------+-----------------------------+--------------------------
t_user     | id          | bigint                      |                         
t_user     | username    | character varying           |                       64
t_user     | password    | character varying           |                       64
t_user     | create_time | timestamp without time zone |                         
t_user     | update_time | timestamp without time zone |                         
(5 rows)

#查询表注释

SELECT
a.attname as "字段名",
col_description(a.attrelid,a.attnum) as "注释",
concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '(.*)')) as "字段类型"
FROM
pg_class as c,
pg_attribute as a,
pg_type as t
WHERE
c.relname = 't_batch_task'
and a.atttypid = t.oid
and a.attrelid = c.oid
and a.attnum>0;

--注释信息函数

序号	名         称	返回类型	描述
1	col_description(table_oid, column_number)	text	为一个表列获得注释
2	obj_description(object_oid, catalog_name)	text	为一个数据库对象获得注释
3	obj_description(object_oid)	text	为一个数据库对象获得注释 （已被废弃）
4	shobj_description(object_oid, catalog_name)	text	为一个共享数据库对象获得注释

col_description为一个表列返回注释，该表列由所在表的 OID 和它的列号指定（obj_description不能被用在表列，因为表列没有自己的 OID）。

obj_description的双参数形式返回一个由其 OID 和所在系统目录名称指定的数据库对象的注释。例如，obj_description(123456,'pg_class')将会检索出 OID 为123456的表的注释。obj_description的单参数形式只要求对象 OID。它已经被废弃，因为无法保证 OID 在不同系统目录之间是唯一的；这样可能会返回错误的注释。

shobj_description用起来就像obj_description，但是前者是用于检索共享对象上的注释。某些系统目录对于一个集簇中的所有数据库是全局的，并且其中的对象的描述也是全局存储的。

--其他字符串函数

函         数	返回类型	描   述	例          子	结    果
concat_ws(sep text, str "any" [, str "any" [, ...] ])	text	将除了第一个参数外的其他参数用分隔符串接在一起。第一个参数被用作分隔符字符串。NULL 参数被忽略。	concat_ws(',', 'abcde', 2, NULL, 22)	abcde,2,22

-- 系统目录信息函数

名称	返回类型	描述
format_type(type_oid, typemod)	text	获得一个数据类型的 SQL 名字

函数

详情参考   函数和操作符

索引管理

#创建索引

drop index schema_test.t_index;
create index t_index on schema_test.t_user (id);

#创建唯一索引

drop index if exists schema_test.t_index;
CREATE UNIQUE INDEX index_name on table_name (column_name);

#查看索引

\d t_user