postgres使用pg_ctl 命令

想要用pg_ctl等一系列的命令，需要配置环境变量：

PATH=$PATH:$HOME/.local/bin:$HOME/bin:/usr/local/pgsql/bin
export PGDATA=/usr/local/pgsql/data
export PATH

在.bash_profile 文件中添加上面的环境变量

然后source  .bash_profile   使之生效。

在启动的时候，报错：pg_ctl: no database directory specified and environment variable PGDATA unset

需要把/usr/local/pgsql/data 的用户名和属组为 postgres  并且目录权限为0700　

chmod -R 0700 　/usr/local/pgsql/data

命令配置完成，可以使用。

停止服务：

[postgres@master pgsql]$ pg_ctl stop
waiting for server to shut down...2018-12-20 17:27:15.726 CST [30069] LOG:  received fast shutdown request
.2018-12-20 17:27:15.728 CST [30069] LOG:  aborting any active transactions
2018-12-20 17:27:15.728 CST [30214] FATAL:  terminating connection due to administrator command
2018-12-20 17:27:15.729 CST [30213] FATAL:  terminating connection due to administrator command
2018-12-20 17:27:15.730 CST [30215] FATAL:  terminating connection due to administrator command
2018-12-20 17:27:15.732 CST [30069] LOG:  worker process: logical replication launcher (PID 30076) exited with exit code 1
2018-12-20 17:27:15.732 CST [30216] FATAL:  terminating connection due to administrator command
2018-12-20 17:27:15.735 CST [30071] LOG:  shutting down
2018-12-20 17:27:15.745 CST [30069] LOG:  database system is shut down
 done
server stopped

启动服务：

[postgres@master pgsql]$ pg_ctl start
waiting for server to start....2018-12-20 17:27:53.811 CST [30324] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2018-12-20 17:27:53.811 CST [30324] LOG:  listening on IPv6 address "::", port 5432
2018-12-20 17:27:53.813 CST [30324] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-12-20 17:27:53.828 CST [30325] LOG:  database system was shut down at 2018-12-20 17:27:15 CST
2018-12-20 17:27:53.830 CST [30324] LOG:  database system is ready to accept connections
 done
server started

登入数据库默认用户为postgres

[postgres@master pgsql]$ psql 
psql (10.5)
Type "help" for help.

postgres=# select user;
   user   
----------
 postgres
(1 row)

postgres=# 

指定用户登入-zhang

[postgres@master pgsql]$ psql -d mydb -U zhang
psql (10.5)
Type "help" for help.

mydb=> 

默认用户
postgres安装完成后，会自动在操作系统和postgres数据库中分别创建一个名为postgres的用户以及一个同样名为postgres的数据库。

psql -U zhang -d mydb -h 192.168.1.200 -W

[postgres@master ~]$ psql -U zhang -d mydb -h 192.168.1.200 -W 
Password for user zhang: 
psql (10.5)
Type "help" for help.

mydb=>

参数含义: -U指定用户 -d要连接的数据库 -h要连接的主机 -W提示输入密码。

创建用户：

createuser username   这个用法和下面的create user 是一样的

在PostgresSQL命令行中使用CREATE ROLE指令创建：

CREATE ROLE rolename;

在PostgresSQL命令行中使用CREATE USER指令创建

CREATE USER username;

 

CREATE USER和CREATE ROLE的区别在于，CREATE USER指令创建的用户默认是有登录权限的，而CREATE ROLE没有。

创建用户时设定用户属性

CREATE ROLE role_name WITH optional_permissions;

创建用户时设定登录权限。

CREATE ROLE username WITH LOGIN;

 

修改用户属性

ALTER ROLE username WITH attribute_options;

通过以下方式禁止用户登录

ALTERROLE username WITH NOLOGIN;

设置访问权限

GRANT permission_type ON table_name TO role_name;

eg：

   GRANT UPDATE ON tablename TO use_role; --赋予use_role tablename表的update权限
   GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to use_role; --赋予use_role所有表的SELECT权限

特殊符号:ALL代表所访问权限，PUBLIC代表所有用户

GRANT ALL ON demo TO demo_role; --赋给用户所有权限
GRANT SELECT ON demo TO PUBLIC; --将SELECT权限赋给所有用户

撤销用户访问权限
语法格式如下:
REVOKE permission_type ON table_name FROM user_name;