远程登陆
psql -h 主机 -U 用户 -p端口 -W -d 数据库 #-W表示密码，例如：psql -h 192.168.137.3 -U postgres -p 5432 -W -d postgres
远程执行命令
psql -h 192.168.137.3 -p 5432 postgres postgres -A -c "select * from test limit 10"
查看所有数据库，owner，字符编码，访问权限
\l
切换数据库
\c postgres
查看所有数据库信息
select * from pg_database; --用于navicate
查看某个库的大小
select pg_size_pretty(pg_database_size('库名'));
查看当前库里面的所有表
\dt
查看表结构
\d test
select * from information_schema.columns where table_schema='public' and table_name='表名'; #使用navicate查看表结构
修改表的属主
alter table 表名 owner to 用户;
查看某个表的大小
select pg_table_size('表名');
查看表的主键
SELECT pg_constraint.conname AS pk_name FROM pg_constraint INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid WHERE pg_class.relname = 'landing_welcome_cdl' AND pg_constraint.contype = 'p';
显示所有用户
\du
select * from pg_user; #navicate客户端上面使用
查看当前用户
testdb=> select user; user ------ test (1 row)
修改数据库的拥有者
postgres=# alter database devdb owner to devdb; ALTER DATABASE
#查询数据库默认表空间
postgres=# select d.datname,p.spcname from pg_database d, pg_tablespace p where d.datname='devdb' and p.oid = d.dattablespace; datname | spcname ---------+------------ devdb | pg_default (1 row)
#修改数据库的表空间
ALTER DATABASE 数据库名称 SET TABLESPACE 表空间名称; 需注意三个问题： 1 不能在当前数据库下面修改 2 数据库不能存在表或者索引已经指定默认的表空间 3 必须没有人连接这个数据库
创建用户
create user username with password '****';
删除用户
drop user username;
修改用户密码
alter user postgres with password 'password';
创建数据库
create database dbtest owner username; -- 创建数据库指定所属者
修改数据库名称
alter database devdb rename to dev_update;
将数据库权限赋给某个用户
grant all on database dbtest to username; -- 将dbtest所有权限赋值给username
schema是啥
schema概念有点像命名空间或者把它想像成一个文件系统中的目录,差别就是这个schema下不能再有schema嵌套.
各个对象比如表、函数等存放在各个schema下,同一个schema下不能有重复的对象名字,但在不同schema下可以重复.
创建schema
create schema test; #默认谁创建owner就是谁
创建schema并指定owner
create schema test authorization user;
修改schema的owner
alter schema test owner to other_user;
删除schema
drop schema test;
删除schema和它下面的对象(表)
drop schema test cascade;
schema下对象的操作
db01=# create table schema01.t1(id int); #创建对象 CREATE TABLE db01=# insert into schema01.t1 values(1); #插入一个值 INSERT 0 1 db01=# select * from t1; #查询，直接查对象报错 ERROR: relation "t1" does not exist LINE 1: select * from t1; ^ db01=# select * from schema01.t1; #查询加上shema.对象 id ---- 1 (1 row) db01=# select * from db01.schema01.t1; #某个数据库下面的shema的对象 id ---- 1 (1 row)
查看schema
\dn;
查看表，索引的大小
select pg_size_pretty(pg_relation_size('cdp_order_item')); #查看表的大小 select pg_size_pretty(pg_indexes_size('cdp_order_item')); #查看索引的大小
查看表对应的索引和schema
select schemaname,relname,indexrelname from pg_stat_user_indexes limit 20; #schema,表名，索引名
查看当前用户和当前库
#查看当前用户 postgres=> select user; user -------- dbuser (1 row) #查看当前库 postgres=> select current_database(); current_database ------------------ postgres (1 row)
赋予所有用户对表的所有权限
grant all on test to public;
注意：
可以建大写的表名但是要加上双引号，而且使用工具navicat点击的时候显示没有权限，但是可以通过命令查看
经常执行postgres命令的时候会杂乱显示，可以设置竖排查看
\x
定制命令，进入数据库使用 :命令 查询
vim ~/.psqlrc #查询等待事件 \set wait_event 'select pid, usename, datname, query, client_addr from pg_stat_activity where pid <> pg_backend_pid() and wait_event is not null order by wait_event_type;' #查询数据库连接数 \set connections_jqs 'select usename, datname, client_addr, count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1,2,3 order by 1,2,4 desc;' #查询活动会话 \set active_session_jqs 'select pid, usename, datname, query, client_addr from pg_stat_activity where pid <> pg_backend_pid() and state=\' active\' order by query;'
定制命令使用：
postgres=# :active_session_jqs; pid | usename | datname | query | client_addr -----+---------+---------+-------+------------- (0 rows) postgres=# :wait_event; pid | usename | datname | query | client_addr ------+----------+----------+--------------------------------------------------------------------------+--------------- 7136 | | | | 7138 | postgres | | | 7134 | | | | 7133 | | | | 7135 | | | | 8400 | postgres | postgres | +| 192.168.137.1 | | | SELECT +| | | | db.oid as did, db.datname, db.datallowconn, +| | | | pg_encoding_to_char(db.encoding) AS serverencoding, +| | | | has_database_privilege(db.oid, 'CREATE') as cancreate, datlastsysoid+| | | | FROM +| | | | pg_database db +| | | | WHERE db.oid = 13287 | (6 rows) postgres=# :connections_jps; ERROR: syntax error at or near ":" LINE 1: :connections_jps; ^ postgres=# :connections_jqs; #没弄明白为啥第二次可以得出结果 usename | datname | client_addr | count ----------+----------+---------------+------- postgres | postgres | 192.168.137.1 | 1 postgres | | | 1 | | | 4 (3 rows)
修改时区的三种方式
--修改时区的方法 1. 全局参数
postgres配置文件修改时区：168服务器
grep timezone postgresql.conf
log_timezone = 'UTC'
timezone = 'UTC'
#重新加载生效
cd /data/postgres/
./bin/pg_ctl -D ./data reload
2. 数据库级配置 alter database dbname set timezone='UTC'; pipeline=# select * from pg_db_role_setting ; setdatabase | setrole | setconfig -------------+---------+-------------------------------------- 14930 | 0 | {TimeZone=UTC} 3. 用户级配置 alter role rolname set timezone='UTC'; 或者 alter role all set timezone='UTC'; pipeline=# select * from pg_db_role_setting ; setdatabase | setrole | setconfig -------------+---------+-------------------------------------- 14930 | 0 | {TimeZone=UTC} 0 | 0 | {TimeZone=UTC}
查看时间和时区
--查看pg的时区与时间 postgres=# select now(); now ------------------------------- 2015-11-18 17:42:28.755732-08 (1 row) --查看时区 postgres=# show time zone; TimeZone ------------ US/Pacific
处理空闲idle进程,参数单位为毫秒，可以设置为30000 ---》30秒
[postgres@cdppgdev data]$ cat postgresql.conf |grep idle_in_transaction_session_timeout #idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
其他
