创建pgsql的数据库

创建pgsql的数据库

mkdir -p /var/lib/pgsql/data      //创建pgsql的数据库目录
cd /var/lib/pgsql 
chown postgres.postgres data     //改变目录的所属用户用组
su  postgres                     //切换到postgres，不然初始化不
initdb /var/lib/pgsql/data   //初始化数据库 提示错误就用下面这条命令
This user must also own the server process.  
The database cluster will be initialized with locale en_US.UTF-8.  
fixing permissions on existing directory /var/lib/pgsql/data ... ok  
creating directory /var/lib/pgsql/data/global ... ok  
creating directory /var/lib/pgsql/data/pg_xlog ... ok  
creating directory /var/lib/pgsql/data/pg_xlog/archive_status ... ok  
creating directory /var/lib/pgsql/data/pg_clog ... ok  
creating directory /var/lib/pgsql/data/pg_subtrans ... ok  
creating directory /var/lib/pgsql/data/pg_twophase ... ok  
creating directory /var/lib/pgsql/data/pg_multixact/members ... ok  
creating directory /var/lib/pgsql/data/pg_multixact/offsets ... ok  
creating directory /var/lib/pgsql/data/base ... ok  
creating directory /var/lib/pgsql/data/base/1 ... ok  
creating directory /var/lib/pgsql/data/pg_tblspc ... ok  
selecting default max_connections ... 100  
selecting default shared_buffers ... 1000  
creating configuration files ... ok  
creating template1 database in /var/lib/pgsql/data/base/1 ... ok  
initializing pg_authid ... ok  
Enter new superuser password:     //超级管理员密码  
Enter it again:                   //确认密码  
initdb: removing contents of data directory "/var/lib/pgsql/data"  
这样就初始化就好了。
listen_addresses = '*'     //监听所有ip的连接，默认是本机  
port = 5432             //这个不开也行，默认就是5432端口  
//    用pgadmin远程连接权限是要放大 listen_addresses = '*' 全部放开，当然也可以，设置局域网的IP段.
# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD  
# "local" is for Unix domain socket connections only  
local   all         all                               trust  
# IPv4 local connections:  
host    all         all         127.0.0.1/32          md5  
host    all         all         0.0.0.0/0             md5   // 所有IP和用户，密码对都可以连接  一般用户不用
# IPv6 local connections:  
host    all         all         ::1/128               md5 
创建角色与用户
CREATE ROLE 语法
CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
创建david 角色和sandy 用户
postgres=# CREATE ROLE david;　　//默认不带LOGIN属性
CREATE ROLE
postgres=# CREATE USER sandy;　　//默认具有LOGIN属性
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of 
-----------+------------------------------------------------+-----------
david | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}
postgres=# 
postgres=# SELECT rolname from pg_roles ;
rolname 
----------
postgres
david
sandy
(3 rows)
postgres=# SELECT usename from pg_user; //角色david 创建时没有分配login权限，所以没有创建用户
usename 
----------
postgres
sandy