PostgreSQL Client Applications
- clusterdb — cluster a PostgreSQL database
- createdb — create a new PostgreSQL database
- createuser — define a new PostgreSQL user account
- dropdb — remove a PostgreSQL database
- dropuser — remove a PostgreSQL user account
- ecpg — embedded SQL C preprocessor
- pg_basebackup — take a base backup of a PostgreSQL cluster
- pgbench — run a benchmark test on PostgreSQL
- pg_config — retrieve information about the installed version of PostgreSQL
- pg_dump — extract a PostgreSQL database into a script file or other archive file
- pg_dumpall — extract a PostgreSQL database cluster into a script file
- pg_isready — check the connection status of a PostgreSQL server
- pg_receivewal — stream write-ahead logs from a PostgreSQL server
- pg_recvlogical — control PostgreSQL logical decoding streams
- pg_restore — restore a PostgreSQL database from an archive file created by pg_dump
- psql — PostgreSQL interactive terminal
- reindexdb — reindex a PostgreSQL database
- vacuumdb — garbage-collect and analyze a PostgreSQL database
登陆控制台
psql -U postgres -h localhost -W -d baseName
一、用户和权限管理
# 可以使用 \h CREATE ROLE 查看语法
CREATE ROLE rolename;
CREATE USER username;
# 例子
CREATE USER jalena WITH CREATEDB LOGIN PASSWORD 'jalena';
当然还可以直接使用客户端工具直接创建。 createuser --interactive
CREATE USER和CREATE ROLE的区别在于，CREATE USER指令创建的用户默认是有登录权限的，而CREATE ROLE没有。
# 修改用户权限
ALTER USER jalena WITH NOCREATEDB; # 取消数据库创建权限
ALTER USER jalena WITH PASSWORD 'password'; # 修改密码
定义存取权限
# 授予jalena对demo库所有的权限
GRANT ALL PRIVILEGES ON DATABASE demo TO jalena;
# 只授予某个权限
GRANT UPDATE ON DATABASE demo TO jalena;
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO jalena;
# 特殊符号《ALL》代表所有权限，《PUBLIC》代表所有用户
GRANT ALL ON demo TO jalena; # 赋给用户所有全选
GRANT SELECT ON demo TO PUBLIC; # 将SELECT权限赋予所有用户
二、备份
PostgreSql提供了2个备份命令，分别为pg_dump和pg_dumpall，这种方式可以在数据库正在使用的时候进行完整一致的备份， 并不阻塞其它用户对数据库的访问。
使用说明可以使用pg_dump --help查看。
pg_dump -F c -f <filename.dmp> -C -E UTF-8 -U <DataBaseUser> <DataBaseName>
pg_dumpall -U postgres -F c -c -f all.dmp
三、恢复
CREATE USER jalena WITH CREATEDB PASSWORD 'password';
CREATE DATABASE demo OWNER jalena;
GRANT ALL PRIVILEGES ON DATABASE odam to inspur;
pg_restore -j5 -U <DBUSER> -W -d <DBNAME> -v <FILEPATH>
四、表与文件之间的拷贝
postgresql提供了COPY命令用于表与文件（标准输出、标准输入）之间的相互拷贝，COPY TO由表至文件，COPY FROM由文件至表。
# 将整张表拷贝至标准输出
copy res_users to stdout;
# 将表的部分字段拷贝至标准输出，且输出字段名称，字段间使用','分割
copy res_users(id,login) to stdout delimiter ',' csv header;
# 将查询结果拷贝至标准输出
copy(select id,login from res_users) to stdout delimiter '|' quote '"' csv header;
将标准输入拷贝至表中需要注意几点
- 字段间分割符默认使用【Tab】键
- 换行使用回车键
- 结束使用反斜线加英文标点（\.）
- 指定字段顺序，不然会出现错误的赋值
#将标准输入拷贝至表中
copy res_users(id,login) from stdin;
>> 1 login1
>> 2 login2
>> \.
# 从标准输入拷贝至表中，并将标准输入第一行作为字段名，字段分隔符为','
copy res_users(id,login) from stdin delimiter ',' csv header;
>> 1,login1
>> 2,login2
>> \.
以上是表与标准输入、输出之间的相互拷贝，表与文件的拷贝和以上完全相同，只是将标准输入、输出换成文件。需要注意的是
- 数据库用户必须有文件所在路径的写权限
- 如果表存在中文字符，导出至csv文件时需要设置编码为GBK，否则使用excel打开中文显示乱码
- 将文件导入表中时任然需要考虑编码问题
# 将表拷贝至csv文件中
copy res_users to 'D:/res_users.csv' delimiter ',' csv header;
# 以GBK编码拷贝到cvs文件
copy res_users to 'D:/res_users.csv' delimiter ',' csv header encoding 'GBK';
# 将文件导入至表中
copy res_users from 'd:/res_users.csv' delimiter ',' csv header encoding 'GBK';
五、PSQL常用命令
-- 查看所有psql可执行的命令
\?
-- 退出控制台
\q
-- 退出当前执行命令
q
-- 更改当前工作目录
\cd [目录]
-- 从文件中执行命令
\i 文件
-- 列出全部数据库
\l
-- 列出全部序列
\ds
-- 切换当前数据库到指定的数据库
\c [database_name]
-- 显示所有的表
\dt
-- 显示指定的表的结构
\d [table_name]
-- 显示视图
\dv
-- 列出全部用户
\du or \dg
-- 列出当前数据库和连接的信息
\conninfo
-- 修改用户密码
\password uesrname
