postgreSQL常用命令

一、查看数据库当前连接状态
1、.查看被锁定表 ：SELECT pg_class.relname AS table, pg_database.datname AS database, pid, mode, granted FROM pg_locks, pg_class, pg_database WHERE pg_locks.relation = pg_class.oid AND pg_locks.database = pg_database.oid;

2、查看客户端连接情况：SELECT client_addr ,client_port,waiting,query_start,current_query FROM pg_stat_activity;

3、查看数据库大小：SELECT pg_size_pretty(pg_database_size('MTPS')) As fulldbsize;

4、查看表结构：select * from information_schema.columns；

5、显示默认表空间：show default_tablespace;

6、查看Postgresql的连接状况：select * from pg_stat_activity;

7、查看数据库表大小：select relname, pg_size_pretty(pg_relation_size('relname')) from pg_stat_user_tables where schemaname = 'public' order by pg_relation_size('relname') desc;

   查看单个表的大小：select pg_size_pretty(pg_relation_size('table_name'));

8、查看主从复制状态：SELECT * from pg_stat_replication ;

9、查看主从状态：SELECT * from pg_is_in_recovery();

10、暂停/恢复主从复制：pg_xlog_replay_pause()；

     pg_xlog_replay_resume()；

11、

二、psql

1、修改密码：alter user postgres with password 'new password'

2、创建库并引用postgis模板：create database cetcnav template postgis;

3、修改库的属主：alter database cetcnav owner to terra;

4、添加索引：CREATE INDEX t_gps_20131111_idx_vehicle_id ON t1(list);

5、删除索引：drop INDEX t_gps_20131111_idx_vehicle_id;

6、插入数据：INSERT INTO t_vehicle_login (vehicle_id,password) select id,snumber from t_vehicle;

7、过滤重复：SELECT distinct(vehicle_id),vnumber,snumber,warrant_code,warrant_result from t_vehicle_warrant；根据某个字段去重

8、导出授权的用户：pg_dumpall -h localhost  -U postgres -v --roles-only -f test.sql；

9、创建用户：CREATE USER user1 WITH PASSWORD '123456';

10、

三、查看数据库系统参数

1、设置执行超过指定秒数的sql语句输出到日志 ：log_min_duration_statement = 3

2、查看客户端编码：show client_encoding;

3、