pg(HGDB)学习总结
来源：互联网 发布：个人注册顶级域名流程 编辑：程序博客网 时间：2024/04/29 14:50
创建新用户及设置密码：
highgo=# create user e login password 'highgo123';
CREATE ROLE
highgo=# create user f with password 'highgo123';
CREATE ROLE
highgo=# create user g password 'highgo123';
CREATE ROLE
[highgo@db ~]$ createuser e -P
Enter password for new role:
Enter it again:
Password:
[highgo@db ~]$
[highgo@db ~]$ dropuser e;
Password:
将用户设置为超级用户：
highgo=# alter user e with superuser;
ALTER ROLE
highgo=# alter user e superuser;
ALTER ROLE
创建用户时指定该用户为超级用户：
[highgo@db ~]$ createuser e -P -s
Enter password for new role:
Enter it again:
Password:
[highgo@db ~]$ psql -U e
Password for user e:
psql: 致命错误: 数据库 "e" 不存在
[highgo@db ~]$ psql -d highgo -U e
Password for user e:
psql (3.0.2)
Type "help" for help.
highgo=#
删除用户：
highgo=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
a | | {}
b | Superuser | {}
highgo | Superuser, Create role, Create DB, Replication | {}
t | | {}
test1 | | {}
test2 | | {}
highgo=# drop user a;
错误: 无法删除"a"因为有其它对象倚赖它
DETAIL: 模式 a的属主
highgo=# drop schema a;
DROP SCHEMA
highgo=# drop user a;
DROP ROLE
highgo=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
b | Superuser | {}
highgo | Superuser, Create role, Create DB, Replication | {}
t | | {}
test1 | | {}
test2 | | {}
highgo=# drop role b;
错误: 无法删除"b"因为有其它对象倚赖它
DETAIL: 表 b.test的属主
模式 b的属主
highgo=# drop role b cascade;
错误: 语法错误 在 "cascade" 或附近的
LINE 1: drop role b cascade;
^
highgo=# drop table b.test;
DROP TABLE
highgo=# drop role b cascade;
错误: 语法错误 在 "cascade" 或附近的
LINE 1: drop role b cascade;
^
highgo=# drop role b ;
错误: 无法删除"b"因为有其它对象倚赖它
DETAIL: 模式 b的属主
highgo=# drop schema b;
DROP SCHEMA
highgo=# drop role b ;
DROP ROLE
highgo=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
highgo | Superuser, Create role, Create DB, Replication | {}
t | | {}
test1 | | {}
test2 | | {}
highgo=# drop user t;
错误: 无法删除"t"因为有其它对象倚赖它
DETAIL: 模式 t的属主
数据库 highgo的权限
highgo=# revoke all on database from t;
错误: 关系 "database" 不存在
highgo=# revoke all on database highgo from t;
REVOKE
highgo=# drop user t;
错误: 无法删除"t"因为有其它对象倚赖它
DETAIL: 模式 t的属主
highgo=#
highgo=# drop schema t;
DROP SCHEMA
highgo=# drop user t;
DROP ROLE
highgo=#
highgo=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
highgo | Superuser, Create role, Create DB, Replication | {}
test1 | | {}
test2 | | {}
拥有登录权限的叫做用户，没有登录权限的叫做角色
CREATE ROLE guest LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; --注意上述Role guest拥有LOGIN的权限，所以叫它用户。
CREATE ROLE "group" NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; --注意这里没有LOGIN权限，所以是角色。
前面说到PG的权限管理可以细化到表的某个字段，现在继续用class表和guest用户做实验。
TEST=> \c postgres postgres;
您现在已经连线到数据库 "postgres",用户 "postgres".
postgres=# \c TEST;
您现在已经连线到数据库 "TEST",用户 "postgres".
TEST=# grant select(num) on class to "group";
GRANT
TEST=# \echo 切换到postgres用户连接TEST数据库，对class表的num字段的select权限赋予group角色
切换到postgres用户连接TEST数据库，对class表的num字段的select权限赋予group角色
TEST=# \c TEST guest
用户 guest 的口令：
您现在已经连线到数据库 "TEST",用户 "guest".
TEST=> \echo 切换回guest用户登录TEST数据库
切换回guest用户登录TEST数据库
TEST=> select * from class;
ERROR: permission denied for relation class
TEST=> select num from class;
num
-----
1
2
(2 行记录)
查看某个schema下的所有表名：
testdb=# select schemaname,tablename,tableowner from pg_tables where tablename='tb1';
默认schema不能访问其他schema的对象，如果要访问，那么就赋予usage权限即可。
默认每个用户都可以在public上有create和usage权限，如果要撤销其权限，那么：
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
pg_catalog这个内置schema，这个schema包含系统表以及系统内置的一些函数和操作符，隐含都会搜索该schema
禁止所有用户在public下创建对象：
在创建数据库的时候，PostgreSQL都会创建一个名为“PUBLIC”的Schema，当登录数据库的时候，如果没有进行特殊指定，都是以该Schema进行操作对象的。在默认的情况下，连接到数据库上的所有用户都可以在该Scheam上创建或是访问对象，如果我们想禁用这样的操作，那么，我们只要把在该Schema上的CREATE权限进行回收即可。如下，
demodb=# revoke create on schema public from public;
REVOKE
create user的语法：
create user <username> [ [ with ] option [ ....... ] ]。
option可以指定如下内容：
superuser|nosuperuser：表示创建出来的用户是否是超级用户。只有超级用户才可以创建超级用户。
createdb|nocreatedb：表示创建出来的用户是否有创建数据库的权限。
createrole|nocreaterole：表示创建出来的用户是否具有创建其他用户的权限。
inherit|noinherit：表示创建出来的用户是否拥有某几项角色，这时若指定inherit，则表示用户自动拥有相应的角色的权限。
login|nologin：表示创建出来的用户是否具有登录权限。
[ encrypted|noencrypted ] password 'password'：表示用户的密码在数据字典里是否进行加密。
connection limit NUMBER：表示该用户可以达到的最大并发连接数，默认为-1，表示没有权限。
valid until ‘timestamp‘：用来指定密码的失效时间，如果不指定，则表示永久有效。
in role role_name [,...]：表示用户可以拥有那些角色。
admin role_name [,...]：表示指定的角色是否可以进行with admin option操作。
pg中查看各文件位置：
highgo=# select name,setting from pg_settings where category='File Locations';
name | setting
-------------------+----------------------------------------------
config_file | /hgdata/highgo/HighGoDB/data/postgresql.conf
data_directory | /hgdata/highgo/HighGoDB/data
external_pid_file |
hba_file | /hgdata/highgo/HighGoDB/data/pg_hba.conf
ident_file | /hgdata/highgo/HighGoDB/data/pg_ident.conf
(5 rows)
pg中查看表结构：
highgo=# \d pg_settings
View "pg_catalog.pg_settings"
Column | Type | Modifiers
------------+---------+-----------
name | text |
setting | text |
unit | text |
category | text |
short_desc | text |
extra_desc | text |
context | text |
vartype | text |
source | text |
min_val | text |
max_val | text |
enumvals | text[] |
boot_val | text |
reset_val | text |
sourcefile | text |
sourceline | integer |
对于9.4以及之后的版本postgresql.auto.conf的优先级高于postgresql.conf，如果这两个文件中存在同名配置项，则系统会优先使用前者设定的值。
终止异常数据库连接：
select * from pg_stat_activity; --获取procpid
取消连接上的活动会话：
select pg_cancel_backend(pid);
终止该连接：
select pg_terminate_backend(pid);
一次性终止某个用户的所有连接：
select pg_terminate_backend(pid) from pf_stat_activity where username='some_role';
9.2之前的版本可以执行如下语句：
select pg_terminate_backend(procpid) from pf_stat_activity where username='some_role';
创建登录角色并设置密码：
create role mydb_admin login password 'something';
创建database并设置其所有者：
create database mudb with owner=mydb_admin;
在PostgreSQL中，即使已经将schema中的表和函数的访问权限授予某个角色，在没有此schema的USAGE权限的情况下，此角色依然不能访问此schema种的表或者函数对象。
pg查看表对应的物理文件名：
select * from emp;
select pg_relation_filepath('emp');
查看正在写入的事务日志：
postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/60003F8
(1 row)
postgres=# select pg_xlogfile_name(pg_current_xlog_location());
pg_xlogfile_name
--------------------------
000000010000000000000007
(1 row)
[postgres9.6@db 20170205]$ pwd
/home/postgres9.6/arch/20170205
[postgres9.6@db 20170205]$ ls
000000010000000000000001 000000010000000000000002 000000010000000000000003 000000010000000000000004 000000010000000000000005 000000010000000000000006
postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/70005B8
(1 row)
postgres=# select pg_xlogfile_name(pg_current_xlog_location());
pg_xlogfile_name
--------------------------
000000010000000000000008
(1 row)
[postgres9.6@db pg_clog]$ cd ../pg_xlog/
[postgres9.6@db pg_xlog]$ ls
000000010000000000000007 000000010000000000000008 000000010000000000000009 00000001000000000000000A 00000001000000000000000B archive_status
[postgres9.6@db pg_xlog]$ cd archive_status/
[postgres9.6@db archive_status]$ ls
000000010000000000000007.done
[postgres9.6@db 20170205]$ pwd
/home/postgres9.6/arch/20170205
[postgres9.6@db 20170205]$ ls
000000010000000000000001 000000010000000000000003 000000010000000000000005 000000010000000000000007
000000010000000000000002 000000010000000000000004 000000010000000000000006
[postgres9.6@db pg_xlog]$ pwd
/home/postgres9.6/data/pg_xlog
[postgres9.6@db pg_xlog]$ ls
000000010000000000000008 000000010000000000000009 00000001000000000000000A 00000001000000000000000B 00000001000000000000000C archive_status
[postgres9.6@db pg_xlog]$ cd archive_status/
[postgres9.6@db archive_status]$ ls
[postgres9.6@db archive_status]$
此时archive_status目录为存放归档日志的状态，若归档已经产生，但没有传输成功则为xxx.ready，并且一直会保留直至传输成功，然后状态变为xxx.done;此时目录为空
触发归档有三种方式：
1.手动切换wal日志，select pg_switch_xlog()
2.wal日志写满后触发归档，配置文件默认达到16M后就会触发归档，wal_keep_segments = 16
3.归档超时触发归档，archive_timeout
highgo=# create user e login password 'highgo123';
CREATE ROLE
highgo=# create user f with password 'highgo123';
CREATE ROLE
highgo=# create user g password 'highgo123';
CREATE ROLE
[highgo@db ~]$ createuser e -P
Enter password for new role:
Enter it again:
Password:
[highgo@db ~]$
[highgo@db ~]$ dropuser e;
Password:
将用户设置为超级用户：
highgo=# alter user e with superuser;
ALTER ROLE
highgo=# alter user e superuser;
ALTER ROLE
创建用户时指定该用户为超级用户：
[highgo@db ~]$ createuser e -P -s
Enter password for new role:
Enter it again:
Password:
[highgo@db ~]$ psql -U e
Password for user e:
psql: 致命错误: 数据库 "e" 不存在
[highgo@db ~]$ psql -d highgo -U e
Password for user e:
psql (3.0.2)
Type "help" for help.
highgo=#
删除用户：
highgo=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
a | | {}
b | Superuser | {}
highgo | Superuser, Create role, Create DB, Replication | {}
t | | {}
test1 | | {}
test2 | | {}
highgo=# drop user a;
错误: 无法删除"a"因为有其它对象倚赖它
DETAIL: 模式 a的属主
highgo=# drop schema a;
DROP SCHEMA
highgo=# drop user a;
DROP ROLE
highgo=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
b | Superuser | {}
highgo | Superuser, Create role, Create DB, Replication | {}
t | | {}
test1 | | {}
test2 | | {}
highgo=# drop role b;
错误: 无法删除"b"因为有其它对象倚赖它
DETAIL: 表 b.test的属主
模式 b的属主
highgo=# drop role b cascade;
错误: 语法错误 在 "cascade" 或附近的
LINE 1: drop role b cascade;
^
highgo=# drop table b.test;
DROP TABLE
highgo=# drop role b cascade;
错误: 语法错误 在 "cascade" 或附近的
LINE 1: drop role b cascade;
^
highgo=# drop role b ;
错误: 无法删除"b"因为有其它对象倚赖它
DETAIL: 模式 b的属主
highgo=# drop schema b;
DROP SCHEMA
highgo=# drop role b ;
DROP ROLE
highgo=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
highgo | Superuser, Create role, Create DB, Replication | {}
t | | {}
test1 | | {}
test2 | | {}
highgo=# drop user t;
错误: 无法删除"t"因为有其它对象倚赖它
DETAIL: 模式 t的属主
数据库 highgo的权限
highgo=# revoke all on database from t;
错误: 关系 "database" 不存在
highgo=# revoke all on database highgo from t;
REVOKE
highgo=# drop user t;
错误: 无法删除"t"因为有其它对象倚赖它
DETAIL: 模式 t的属主
highgo=#
highgo=# drop schema t;
DROP SCHEMA
highgo=# drop user t;
DROP ROLE
highgo=#
highgo=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
highgo | Superuser, Create role, Create DB, Replication | {}
test1 | | {}
test2 | | {}
拥有登录权限的叫做用户，没有登录权限的叫做角色
CREATE ROLE guest LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; --注意上述Role guest拥有LOGIN的权限，所以叫它用户。
CREATE ROLE "group" NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; --注意这里没有LOGIN权限，所以是角色。
前面说到PG的权限管理可以细化到表的某个字段，现在继续用class表和guest用户做实验。
TEST=> \c postgres postgres;
您现在已经连线到数据库 "postgres",用户 "postgres".
postgres=# \c TEST;
您现在已经连线到数据库 "TEST",用户 "postgres".
TEST=# grant select(num) on class to "group";
GRANT
TEST=# \echo 切换到postgres用户连接TEST数据库，对class表的num字段的select权限赋予group角色
切换到postgres用户连接TEST数据库，对class表的num字段的select权限赋予group角色
TEST=# \c TEST guest
用户 guest 的口令：
您现在已经连线到数据库 "TEST",用户 "guest".
TEST=> \echo 切换回guest用户登录TEST数据库
切换回guest用户登录TEST数据库
TEST=> select * from class;
ERROR: permission denied for relation class
TEST=> select num from class;
num
-----
1
2
(2 行记录)
查看某个schema下的所有表名：
testdb=# select schemaname,tablename,tableowner from pg_tables where tablename='tb1';
默认schema不能访问其他schema的对象，如果要访问，那么就赋予usage权限即可。
默认每个用户都可以在public上有create和usage权限，如果要撤销其权限，那么：
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
pg_catalog这个内置schema，这个schema包含系统表以及系统内置的一些函数和操作符，隐含都会搜索该schema
禁止所有用户在public下创建对象：
在创建数据库的时候，PostgreSQL都会创建一个名为“PUBLIC”的Schema，当登录数据库的时候，如果没有进行特殊指定，都是以该Schema进行操作对象的。在默认的情况下，连接到数据库上的所有用户都可以在该Scheam上创建或是访问对象，如果我们想禁用这样的操作，那么，我们只要把在该Schema上的CREATE权限进行回收即可。如下，
demodb=# revoke create on schema public from public;
REVOKE
create user的语法：
create user <username> [ [ with ] option [ ....... ] ]。
option可以指定如下内容：
superuser|nosuperuser：表示创建出来的用户是否是超级用户。只有超级用户才可以创建超级用户。
createdb|nocreatedb：表示创建出来的用户是否有创建数据库的权限。
createrole|nocreaterole：表示创建出来的用户是否具有创建其他用户的权限。
inherit|noinherit：表示创建出来的用户是否拥有某几项角色，这时若指定inherit，则表示用户自动拥有相应的角色的权限。
login|nologin：表示创建出来的用户是否具有登录权限。
[ encrypted|noencrypted ] password 'password'：表示用户的密码在数据字典里是否进行加密。
connection limit NUMBER：表示该用户可以达到的最大并发连接数，默认为-1，表示没有权限。
valid until ‘timestamp‘：用来指定密码的失效时间，如果不指定，则表示永久有效。
in role role_name [,...]：表示用户可以拥有那些角色。
admin role_name [,...]：表示指定的角色是否可以进行with admin option操作。
pg中查看各文件位置：
highgo=# select name,setting from pg_settings where category='File Locations';
name | setting
-------------------+----------------------------------------------
config_file | /hgdata/highgo/HighGoDB/data/postgresql.conf
data_directory | /hgdata/highgo/HighGoDB/data
external_pid_file |
hba_file | /hgdata/highgo/HighGoDB/data/pg_hba.conf
ident_file | /hgdata/highgo/HighGoDB/data/pg_ident.conf
(5 rows)
pg中查看表结构：
highgo=# \d pg_settings
View "pg_catalog.pg_settings"
Column | Type | Modifiers
------------+---------+-----------
name | text |
setting | text |
unit | text |
category | text |
short_desc | text |
extra_desc | text |
context | text |
vartype | text |
source | text |
min_val | text |
max_val | text |
enumvals | text[] |
boot_val | text |
reset_val | text |
sourcefile | text |
sourceline | integer |
对于9.4以及之后的版本postgresql.auto.conf的优先级高于postgresql.conf，如果这两个文件中存在同名配置项，则系统会优先使用前者设定的值。
终止异常数据库连接：
select * from pg_stat_activity; --获取procpid
取消连接上的活动会话：
select pg_cancel_backend(pid);
终止该连接：
select pg_terminate_backend(pid);
一次性终止某个用户的所有连接：
select pg_terminate_backend(pid) from pf_stat_activity where username='some_role';
9.2之前的版本可以执行如下语句：
select pg_terminate_backend(procpid) from pf_stat_activity where username='some_role';
创建登录角色并设置密码：
create role mydb_admin login password 'something';
创建database并设置其所有者：
create database mudb with owner=mydb_admin;
在PostgreSQL中，即使已经将schema中的表和函数的访问权限授予某个角色，在没有此schema的USAGE权限的情况下，此角色依然不能访问此schema种的表或者函数对象。
pg查看表对应的物理文件名：
select * from emp;
select pg_relation_filepath('emp');
查看正在写入的事务日志：
postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/60003F8
(1 row)
postgres=# select pg_xlogfile_name(pg_current_xlog_location());
pg_xlogfile_name
--------------------------
000000010000000000000007
(1 row)
[postgres9.6@db 20170205]$ pwd
/home/postgres9.6/arch/20170205
[postgres9.6@db 20170205]$ ls
000000010000000000000001 000000010000000000000002 000000010000000000000003 000000010000000000000004 000000010000000000000005 000000010000000000000006
postgres=# select pg_switch_xlog();
pg_switch_xlog
----------------
0/70005B8
(1 row)
postgres=# select pg_xlogfile_name(pg_current_xlog_location());
pg_xlogfile_name
--------------------------
000000010000000000000008
(1 row)
[postgres9.6@db pg_clog]$ cd ../pg_xlog/
[postgres9.6@db pg_xlog]$ ls
000000010000000000000007 000000010000000000000008 000000010000000000000009 00000001000000000000000A 00000001000000000000000B archive_status
[postgres9.6@db pg_xlog]$ cd archive_status/
[postgres9.6@db archive_status]$ ls
000000010000000000000007.done
[postgres9.6@db 20170205]$ pwd
/home/postgres9.6/arch/20170205
[postgres9.6@db 20170205]$ ls
000000010000000000000001 000000010000000000000003 000000010000000000000005 000000010000000000000007
000000010000000000000002 000000010000000000000004 000000010000000000000006
[postgres9.6@db pg_xlog]$ pwd
/home/postgres9.6/data/pg_xlog
[postgres9.6@db pg_xlog]$ ls
000000010000000000000008 000000010000000000000009 00000001000000000000000A 00000001000000000000000B 00000001000000000000000C archive_status
[postgres9.6@db pg_xlog]$ cd archive_status/
[postgres9.6@db archive_status]$ ls
[postgres9.6@db archive_status]$
此时archive_status目录为存放归档日志的状态，若归档已经产生，但没有传输成功则为xxx.ready，并且一直会保留直至传输成功，然后状态变为xxx.done;此时目录为空
触发归档有三种方式：
1.手动切换wal日志，select pg_switch_xlog()
2.wal日志写满后触发归档，配置文件默认达到16M后就会触发归档，wal_keep_segments = 16
3.归档超时触发归档，archive_timeout
阅读全文
0 0
- pg(HGDB)学习总结
- pg（hgdb）权限实验
- PG（HGDB）时间函数
- HGDB（pg）行锁现象
- HGDB PG表中添加删除列
- HGDB（pg）建表时指定默认值
- pg（hgdb）默认事务自动提交
- pg（hgdb）执行sql脚本方式
- pg（HGDB）对象名称说明
- HGDB重建模板数据库的方法（适用于PG）
- PG（HGDB）里创建表时不校验列名关键字
- PG（HGDB）修改表的字段类型
- pg（HGDB）在非默认位置创建表空间
- PG（HGDB）中创建-查看-删除extension
- HGDB新版本开始兼容oracle varchar2类型，作用pg里的varchar类型支持一致
- PG-HGDB 把WAL位置转换成WAL文件名和偏移量
- PG(HGDB)里支持多个sql语句在同一行中间用分号隔开
- PG（HGDB）删除数据库时不能有任何连接，否则删除失败
- mysql必知必会读书笔记（1/5） 前言-第8章
- python自然语言处理-马尔科夫模型
- Python花式编程案例集锦（6）
- 快速理解vue的工作方式
- dedecms中{dede:channel}无法调用隐藏栏目？
- pg(HGDB)学习总结
- 如何挖掘消费者的隐性需求？
- Java万年历、杨辉三角、随机验证码实现
- 长假后第一天不想工作，这病咋治？
- java Date型时间比较大小
- 领域应用 | 图数据库及其在恒昌的应用简介
- 【机房收费系统】——表格调用
- NGUI自适应之懵逼梳理
- web移动端滚动条
