我们专注服务于当下互联网基础设施建设与云计算、大数据时代的各种需求!

PostgrepSQL一些常用操作

$ ./createuser -P -d -a -e admin
Enter password for new role:
Enter it again:
CREATE ROLE admin PASSWORD ‘md5adac7a79de00846615c9e82e44271065′ SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
$ ./psql
psql (9.2.1, server 8.2.17)
WARNING: psql version 9.2, server version 8.2.
Some psql features might not work.
Type “help” for help.

pgsql=# \du
List of roles
Role name | Attributes | Member of
———–+———————————–+———–
admin | Superuser, Create role, Create DB |
pgsql | Superuser, Create role, Create DB |

pgsql=# select * from pg_roles;

用户管理:
添加用户:
命令:createuser [-a] [-A] [-d] [-D] [-e] [-P] [-h 主机名] [-p port] 用户名
参数说明:
[-a]:允许创建其他用户,相当于创建一个超级用户;
[-A]:不允许此用户创建其他用户;
[-d]:允许此用户创建数据库;
[-D]:不允许此用户创建数据库;
[-e]:将执行过程显示到Shell上;
[-P]:创建用户时,同时设置密码;
[-h 主机名]:为某个主机上的Postgres创建用户;
[-p port]:与-h参数一同使用,指定主机的端口。
事实上,createuser只是对CREATE USER的封装。
A.不带参数的创建用户
createuser testuser
Shall the new user be allowed to create databases? (y/n) n ——–是否可以创建数据库:否
Shall the new user be allowed to create more new users? (y/n) n ———是否可以创建新用户:否
CREATE USER
注:不带数创建用户时,Postgres会询问此用户的权限,上面的例子创建了一个普通用户; 这等于CREATE USER testuser NOCREATEDB NOCREATEUSER;
B.为指定的主机和端口上创建用户
createuser -h 192.168.1.5 -p 5000 -D -A -e testuser
CREATE USER
注:这个命令为主机192.168.1.5的5000端口创建用户testuser, 此用户不可以创建数据库和其他用户。
C.创建超级用户
createuser -P -d -a -e testuser
Enter password for new user: testuser
Enter it again: testuser
CREATE USER
注:这个命令在本地创建一个超级用户(-a),可以创建数据库(-d), 同时要求设置密码。 这等于CREATE USER testuser PASSWORD ‘testuser’ CREATEDB CREATEUSER;
删除用户:
命令:dropuser [-i] [-h] [-p] [-e] 用户名
参数说明:
[ -i]:删除用户前,要求确认;
[-h 主机名]:删除某个主机上的Postgres用户;
[-p port]:与-h参数一同使用,指定主机的端口;
[-e]:将执行过程显示到Shell上。
事实上,dropuser只是对DROP USER的封装。
A.删除本地的Postgres用户
dropuser testuser
DROP USER
B.删除远程Postgres服务器上的用户
dropuser -h192.168.1.5 -p 5000 -i -e testuser
User “testuser” and any owned databases will be permanently deleted.
Are you sure? (y/n) y
DROP USER
注:此命令删除主机192.168.1.5(-h)的5000端口(-p)的用户testuser,并且需要确认(-i); 这等于DROP USER “testuser”;
创建删除数据库:
A.创建数据库
创建我的第一个数据库sk
postgres是支持SQL标准最高的关系型数据库管理系统,当然,它支持CREATE DATABASE命令创建数据库,在此基础上,还对此命令做了扩展,
增加了许多属性设置。
比如owner、template、ecoding、tablespace等。
那么,利用CREATE DATABASE创建sk数据库语句如下:
CREATE DATABASE sk ENCODING ‘LATIN1′ TEMPLATE template0 OWNER sk TABLESPACE skspace;

另外,postgres还提供的自己的方言来创建数据库createdb,同样支持多个属性设置。
比如–tablespace、–encoding、–owner、–template等。
那么,利用createdb创建sk数据库语句如下:
createdb sk –encoding LATIN1 –template template0 –owner sk –tablespace skspace;

事实上,createdb只是给create database命令做的一个外壳,目的是提高创建语句的便利性。
但作为我个人,还是喜欢使用CREATE DATABASE 命令来创建数据库。
B.删除数据库
删除我刚才创建的数据库sk:
dropdb sk
DROP DATABASE sk

1. 创建数据库
createdb -h ip -U user dbname. (ip 为目标机,本机可不要-h参数; user是登录数据库的角色)
2. 备份数据库
pg_dump -h ip -U user -E utf-8 dbname > mysql.bak(mysql.bak是备份文件名字)
3. 恢复数据库
psql -h ip -U user -d dbname -f mysql.bak
这里只是基本的参数,详细参数信息可用–help查看。