postgresql常用命令

最新推荐文章于 2024-05-17 23:39:39 发布

寰宇001

最新推荐文章于 2024-05-17 23:39:39 发布

阅读量3.7k

点赞数 3

分类专栏：数据库常用命令文章标签： postgresql 数据库 database

本文链接：https://blog.csdn.net/qq_40907977/article/details/102837411

版权

数据库同时被 2 个专栏收录

162 篇文章 15 订阅

订阅专栏

常用命令

73 篇文章 3 订阅

订阅专栏

环境：Ubuntu 16.04 LTS
数据库版本：9.6.6

注意：PostgreSQL中的不同类型的权限有SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER,CREATE,CONNECT,TEMPORARY,EXECUTE 和 USAGE。

1. 登录PG数据库

以管理员身份 postgres 登陆，然后通过

#psql -U postgres
#sudo -i -u postgres

$ psql  xc_hzh_linan                    #登录xc_hzh_linan数据库
$ psql -U postgres test               #以postgres用户身份，登录test数据库
psql -h localhost -p 5432 -U postgress testdb

界面效果为:

postgres@ubuntu:~$ psql
psql (9.5.19)
Type "help" for help.

postgres=#

简单说一下pgsql 的相关命令

使用反斜线作为命令前缀.

退出    \q
列出所有的数据库      \l
列出所有的数据库的大小      \l+
更改当前连接的数据库       \c
列出当前数据库的连接信息    \connect
列出当前数据库和连接的详细信息 \conninfo
查看当前数据库里面的表和拥有者和表大小         \dt +
展示所有用户           \dg

查看所有表名的列表             \d
获取表结构                   \d a
展示所有用户               \du
查看t_sms表的结构      \d t_sms 
展示数据库里面的所有的表         \dt
列出所有的数据库的详细信息（包括数据库大小和字符格式）         \l+
显示用户访问权限。                            \z或\dp
显示所有可设置的访问权限                     \h GRAN
显示用户的对所有数据库表的详细访问权限     \dp或者\z
确认当前连接的用户为超级用户postgres，且该用户后创建角色和数据库的权限等     #select current_user;
在超级用户连接postgres后，设置不允许普通用户a连接数据库         #alter role a nologin;

使用普通用户a连接数据库正常                   #\c highgo a

查看当前连接的用户名：
foo=#select * from current_user;
或
foo=#select user;

查看所有用户名：
foo=#\du

数据库表的备份与恢复：
pg_dump -h 192.168.2.242 -U postgres -p 5432 -c --if-exists -t t_sms dev_huishishuju > t_sms.db 远程备份

在这里插入图片描述
使用案例：

t_gj_tzy=# \l+
                                                                   List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+------------+------------+-----------------------+---------+------------+--------------------------------------------
 postgres  | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 |                       | 7079 kB | pg_default | default administrative connection database
 t_gj_tzy  | wsgjjkzg | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =Tc/wsgjjkzg         +| 21 GB   | pg_default | 
           |          |          |            |            | wsgjjkzg=CTc/wsgjjkzg |         |            | 
 template0 | postgres | UTF8     | zh_CN.utf8 | zh_CN.utf8 | =c/postgres          +| 6969 kB | pg_default | unmodifiable empty database

PostgreSQL 查找当前数据库的所有表 select * from pg_tables where schemaname = 'public';

查看每个表的大小

postgres=# \dt+
                                 List of relations
 Schema |            Name            | Type  |  Owner   |    Size    | Description 
--------+----------------------------+-------+----------+------------+-------------
 public | acknowledges               | table | postgres | 0 bytes    | 
 public | actions                    | table | postgres | 16 kB      | 
 public | alerts                     | table | postgres | 8192 bytes | 
 public | application_discovery      | table | postgres | 0 bytes    | 
 public | application_prototype      | table | postgres | 8192 bytes | 
 public | application_template       | table | postgres | 40 kB      | 
 public | applications               | table | postgres | 56 kB      | 
 public | auditlog                   | table | postgres | 0 bytes    | 
 public | auditlog_details           | table | postgres | 8192 bytes |

添加索引

CREATE INDEX idx_ke_kid

  ON base.keyword_engine

  USING btree

  (keyword_id);

2、创建用户以及相关权限设置

创建用户

create role zhaobsh;

create user wzq with password '123456';                       #设置用户，并设置密码
create database xc_hzh_linan owner xchzhlinan ;         #创建数据库以及所属用户

更改用户密码

alter role zhaobsh with password 'Test6530';

or

\password zhaobsh

ALTER USER postgres WITH PASSWORD 'postgres';

注：

密码postgres要用引号引起来
命令最后有分号

修改数据库属主

alter database "GSCloud1903" owner to zhaobsh;
ALTER ROLE user_4 WITH CREATEROLE;            /*赋予创建角色的权限*/  
alter database "GSCloud1903" rename to zhaobsh;              --修改schema的名称

授权给用户
PostgreSQL中预定义了许多不同类型的内置权限，如：SELECT、INSERT、UPDATE、DELETE、RULE、REFERENCES、TRIGGER、CREATE、TEMPORARY、EXECUTE和USAGE。

grant all on database "GSCloud1903" to zhaobsh;                          #赋予用户zhaobsh数据库所有权限

grant UPDATE ON database "GSCloud1903" to zhaobsh;             # 赋予用户zhaobsh数据库更新权限

/* 赋给用户表的所有权限 */
GRANT ALL ON tablename TO user; 

/* 赋给用户数据库的所有权限 */
GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser;

/* 撤销用户权限 */
REVOKE privileges ON tablename FROM user;

连接用户和数据库
使用创建的用户能登录查看相应数据库，则可进行下一步。

[root@localhost ~]# psql -h localhost -U monitoring -p 54321 wiseucmsg

3、撤销用户权限

撤销用户 runoob权限：

=# REVOKE ALL ON COMPANY FROM runoob;

删除用户：

runoobdb=# drop user runoob;

设置是 supseruser 以及登录权限

alter user zhaobsh superuser login

修改指定用户指定权限

alter user postgres superuser createrole createdb replication;
注意：如果出现 FATAL： role '...' is not permitted to log in.  的错误

alter user postgres superuser createrole createdb replication login;

4、查看用户权限

1、查看某用户的表权限

select * from information_schema.table_privileges where grantee='user_name';

2、查看usage权限表

select * from information_schema.usage_privileges where grantee='user_name';

3、查看存储过程函数相关权限表

select * from information_schema.routine_privileges where grantee='user_name';

4、建用户授权

create user user_name;
alter user user_name with password '';
alter user user_name with CONNECTION LIMIT  20;#连接数限制

创建数据库

CREATE DATABASE dbname;

postgres=# create database dbtest owner username; -- 创建数据库指定所属者
CREATE DATABASE

或者

$ createdb -h localhost -p 5432 -U postgres runoobdb
password ******

将数据库得权限，全部赋给某个用户

postgres=# grant all on database dbtest to username; -- 将dbtest所有权限赋值给username
GRANT

设置是 supseruser 以及 登录权限
alter user zhaobsh superuser login

5、修改linux系统中postgres用户的密码

PostgreSQL会创建一个默认的linux用户postgres，修改该用户密码的方法如下：
步骤一：删除用户postgres的密码

sudo  passwd -d postgres

步骤二：设置用户postgres的密码

sudo -u postgres passwd

系统提示输入新的密码

Enter new UNIX password: 

Retype new UNIX password: 

passwd: password updated successfully

6、使用pgAdmin客户端工具创建数据库

pgAdmin 工具提供了完整操作数据库的功能：
在这里插入图片描述

7、数据库的导入导出

导入整个数据库

psql -U username databasename < /data/dum.sql -- 用户名和数据库名
pg_dump ga_zj_taizhou.sql | psql wang_wang

或者

进入具体的数据库导入
postgres@ubuntu:~$ psql
psql (9.6.6)
Type "help" for help.

postgres=# \c wang_wang     
You are now connected to database "wang_wang" as user "postgres".
wang_wang=# \i ga_zj_taizhou.sql

在这里插入图片描述
注意： 因为是从其他地方备份出来的sql文件，从一个数据库导入另一台数据库可能会报下面的错误

\i /root/test.sql            #导入数据库数据
psql:ga_zj_taizhou.sql:1408597: ERROR:  role "pm" does not exist
psql:ga_zj_taizhou.sql:1408598: ERROR:  role "laoyw" does not exist

解决办法：
格式：create user 自定义用户名称 superuser;

postgres=# create user root superuser ;

然后\q退出数据库，再实现psql -d your_db -f sql_path
例如：

# psql -d mz -f /usr/mydownloads/proj_llmrs.sql

8、忘记postgres用户密码怎么办？

环境：

Ubuntu 16.04
postgres : apt-get install postgresql 9.5版本

sudo vim /etc/postgresql/9.5/main/pg_hba.conf

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

重启postgressql服务。

~$ systemctl restart postgresq

免密码登录

localhost:~$ psql -U postgres -h 127.0.0.1
psql (9.5.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#

修改密码并退出

postgres=# alter user postgres with password '123';
ALTER ROLE
postgres=# \q

修改回认证并退出： trust改为md5

sudo vim /etc/postgresql/9.5/main/pg_hba.conf

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust

重启并登陆：

parallels@parallels-vm:~$ systemctl restart postgresql
parallels@parallels-vm:~$ psql -U postgres -h 127.0.0.1
Password for user postgres: 
psql (9.5.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

Ubuntu系统

创建新用户

root@ubuntu:~# useradd wang
root@ubuntu:~# passwd wang
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully

常见报错：
没有家目录,新用户登陆错误

root@ubuntu:~# su - wang
No directory, logging in with HOME=/
$

Linux授予某个用户对某个文件夹的读写权限

$ sudo chmod -R 755 /data/data1

$ chown -R user1.user1 1.txt
查看目录的权限
data# ls -l

备份数据
从外网拷贝到本地
scp -r *.sql zhaobsh@192.187.100.1:/data/data1

导入数据
登录数据库
postgres@dbmaser:~$ psql -U wsgjjkzg -d t_gj_tzy < /data/data1/t_gj_tzy.20191029235826.sql

查看导入的数据
su - postgresl
\c GSCloud1903 #切换到GSCloud1903
postgres=# \c t_gj_tzy
You are now connected to database “t_gj_tzy” as user “postgres”.

\c dt+ #查看当前数据库里面的表和拥有者和表大小

清空数据库中所有数据

//删除public模式以及模式里面所有的对象
DROP SCHEMA public CASCADE;
//创建public模式
CREATE SCHEMA public;

清除全表，建议使用truncate

2种方法都只删数据，保留表结构

方法一，适用数据量较小的情况

delete from  tablename;

方法二，适合删除大量数据，速度快

TRUNCATE TABLE tablename;

若该表有外键，要用级联方式删所有关联的数据

TRUNCATE TABLE tablename CASCADE;

test03=# select * from test;  
 id | info | crt_time   
----+------+----------  
(0 rows)

注意：

//当表没有其他关系时
TRUNCATE TABLE tablename;
//当表中有外键时，要用级联方式删所有关联的数据
TRUNCATE TABLE tablename CASCADE;

清除所有超过1w条数据的表数据。

SELECT * FROM count_em_all(10000) AS r ORDER BY r.num_rows DESC;

扩展
在 mysql中，只需要执行：

TRUNCATE table_name;

即可，数据会情况，而且自增id也会变回0；

但在 postgresql 则稍有不同，因为 postgresql 的自增id是通过序列 sequence来完成的，

所以情况数据后，还需要还原序列 sequence：

TRUNCATE bigtable, fattable RESTART IDENTITY;

官方文档：https://www.postgresql.org/docs/9.2/static/sql-truncate.html

另一种方式：（因为版本不同，可能命令不同）

truncate table table_name;
alter sequence seq_name start 1;

postgres数据库中删除指定的数据的sql语句

查询数据库中default_table表中的name字段为空的条件

SELECT * from default_table WHERE name is null;

删除数据库中default_table表的name字段为空的数据

DELETE  FROM default_table WHERE  name is null;

创建索引语句

create index i_jdbc_sql_record_zh01 on t_jdbc_sql_record(c_bh_group,dt_zxsj,N_CHECKSTATUS,C_PSSQL_HASH);
alter table t_jdbc_sql_content add constraint t_jdbc_sql_content_pkey primary key (C_BH);

异步提交和unlogged table

-- 异步提交,更改完重启数据库
 alter system set synchronous_commit to off;
 -- unlogged table 
 create unlogged table t_jdbc_sql_record 
 ...
 create unlogged table t_jdbc_sql_content

POSTGRESQL 数据库导入导出

导入整个数据库

psql -U postgres(用户名)  数据库名(缺省时同用户名) < /data/dum.sql

导出整个数据库
1.导出数据库：方式一：pg_dump -U postgres -f c:\db.sqlpostgis

方式二：pg_dump -Upostgres postgis > c:\db.sql

pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)   >/data/dum.sql

导出某个表

pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)  -t table(表名) >/data/dum.sql

导入具体表：

psql -d postgis -f c:\ dump.sqlpostgres

压缩方法

一般用dump导出数据会比较大，推荐使用xz压缩

压缩方法  xz dum.sql 会生成 dum.sql.xz 的文件


xz压缩数据倒数数据库方法

xzcat /data/dum.sql.xz | psql -h localhost -U postgres(用户名) 数据库名(缺省时同用户名)

查看postgresql数据库用户系统权限、对象权限的方法

1、查看某用户的系统权限

SELECT * FROM pg_roles WHERE rolname='postgres';

在这里插入图片描述
2、查看某用户的表权限

select * from information_schema.table_privileges where grantee='postgres';

3、查看某用户的usage权限

select * from information_schema.usage_privileges where grantee='postgres';

4、查看某用户在存储过程函数的执行权限

select * from information_schema.routine_privileges where grantee='postgres';

5、查看某用户在某表的列上的权限

select * from information_schema.column_privileges where grantee='postgres';

6、查看当前用户能够访问的数据类型

select * from information_schema.data_type_privileges ;

7、查看用户自定义类型上授予的USAGE权限

select * from information_schema.udt_privileges where grantee='postgres';

工作中常见用法：

1.查看运行时间长的sql

select pid,datname,usename,client_addr,query_start,now()-query_start as time_run, query from pg_stat_activity
where state='active' order by time_run desc limit 20;

2.查看指定表空间下的表和索引

SELECT  c.relname, t.spcname FROM  pg_class c left JOIN pg_tablespace t ON c.reltablespace = t.oid WHERE t.spcname = 'data1tbs';

默认表空间的话where判断 c.reltablespace=0 或 t.spcname is null

3、查看指定pid需要的锁状态

select pc.relname,pl.* from pg_locks pl left join pg_class pc on pc.relfilenode=pl.relation where pl.pid=13912

4、查看未使用索引

select schemaname,relname,indexrelname,idx_scan from pg_stat_user_indexes where idx_scan=0 and schemaname='public' and indexrelname not like '%_pkey'

5、查看指定表的索引

select schemaname,relname,indexrelname,idx_scan from pg_stat_user_indexes where schemaname='public' and relname='visited_article'

6、某个pid在等待的锁的持有者

select pl1.*,pl2.pid,pa.usename,pa.query from pg_locks pl1 left join pg_locks pl2 on pl1.transactionid=pl2.transactionid and pl2.granted
left join pg_stat_activity pa on pl2.pid=pa.pid where pl1.pid=21641 and not pl1.granted

7、查看所有锁的状态

select
     pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted,
     pg_stat_activity.usename,substr(pg_stat_activity.query,1,30), pg_stat_activity.query_start,
     age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.pid
   from pg_stat_activity,pg_locks left
     outer join pg_class on (pg_locks.relation = pg_class.oid) 
   where pg_locks.pid=pg_stat_activity.pid order by query_start limit 100;

8、查看指定schema 里所有的索引大小，按从大到小的顺序排列

select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;

9、查询所有表大小，并且按从大到小排序

select relname, pg_size_pretty(pg_relation_size(relid)) as size from pg_stat_user_tables ORDER BY size desc;

10、查询指定模式下面所有表大小，并按表大小排序

select relname, pg_size_pretty(pg_relation_size(relid)) as size from pg_stat_user_tables where schemaname = 'authevent'  ORDER BY size desc;

–查看DB大小

select pg_size_pretty(pg_database_size('ve'));

–查看表大小(不含索引等信息)

select pg_relation_size('user');                      
select pg_size_pretty(pg_relation_size('use'))

–查看每个DB的使用情况(读,写,缓存,更新,事务等)

select * from pg_stat_database

–查看索引的使用情况

select * from pg_stat_user_indexes;

–查看表所对应的数据文件路径与大小

SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'empsalary';

–查看表空间大小

select pg_tablespace_size('pg_default');

714 境内采集器

SELECT  ip FROM asset_server where is_server=1 and  ip  not like '%163.106.130.%' and ip  not like '132.117.354.%' and idc_id not in(16,9)  and ip not IN('161.10.84.11','180.132.214.110')

27 大服务器

SELECT  ip FROM asset_server where is_server=1 and  (ip   like '%183.106.110.%' or ip  like '122.227.254.%'  or ip  IN('80.102.214.130'))

24 境外采集器

SELECT  ip FROM asset_server where is_server=1 and   idc_id in(16,9) and ip not in('57.39.42.18','143.331.144.190','113.10.10.11')

1 阿里云

SELECT  ip FROM asset_server where  ip  IN('132.10.34.121')

4 v，pn 与浏览器专用 37.69.82.18（“浏览器代理服务器”） “183.211.104.114”

SELECT  * FROM asset_server where is_server=1 and   idc_id in(16,9) and ip in('37.69.82.18','183.211.104.114')

4.6.5 排除境，外阿里云

   SELECT  * FROM asset_server where is_server=1  and idc_id not in(16,9)  and ip not IN('132.110.194.141')

4.6.7 --统计全网服务器（按机房）

select COUNT(ass.ip),ass.idc_id,idc.name  from  asset_server  ass  left join asset_serveridc idc on idc.id=ass.idc_id where ass.is_server=1 group by ass.idc_id,idc.name;

4.6.8 联合状态查询

–查询内存1g的

select ass.ip,ast.memtotal from  asset_server ass inner join (select max(id),sid_id,cast(trim(both ' MB' from memtotal) as integer) as memtotal  from  asset_serverstatus group by sid_id,memtotal ) as  ast
on ass.id=ast.sid_id where ass.is_server=1 and ast.memtotal<1200 order by ass.ip;

–查询内存1g的排除香港

select count(ass.ip) from  asset_server ass inner join (select max(id),sid_id, cast(trim(both ' MB' from memtotal) as integer) as memtotal  from  asset_serverstatus group by sid_id,memtotal ) as  ast
on ass.id=ast.sid_id where ass.is_server=1 and ass.idc_id !=16 and ast.memtotal<1200;

select ass.ip,ast.memtotal from  asset_server ass inner join (select max(id),sid_id,cast(trim(both ' MB' from memtotal) as integer) as memtotal  from  asset_serverstatus group by sid_id,memtotal ) as  ast
on ass.id=ast.sid_id where ass.is_server=1 and ass.idc_id !=16 and ast.memtotal<1200 order by ass.ip;

–查询内存1g的排除香港部署有totalspider的

select count(ass.ip) from  asset_server ass inner join (select max(id),sid_id,cast(trim(both ' MB' from memtotal) as integer) as memtotal  from  asset_serverstatus group by sid_id,memtotal ) as  ast
on ass.id=ast.sid_id where ass.is_server=1 and ass.idc_id !=16 and ass.describe LIKE  '%totalspider%' and ast.memtotal<1200;

–查询内存大于1g 小于2g 排除香港的机器

psql -h db.www.qwq.com -p 5432 -U yun -c "select ip, ast.memtotal from asset_server ass inner join (select max(id),sid_id,cast(trim(both ' MB' from memtotal) as integer) as memtotal from asset_serverstatus group by sid_id,memtotal ) as ast
on ass.id=ast.sid_id where ass.is_server=1 and ass.idc_id !=16 and ast.memtotal>1200 and ast.memtotal<3000;"

查询机房归属地在上海的（代号42）的内存大小

psql yunwei -c " select ass.ip,ast.memtotal from  asset_server ass inner join (select max(id),sid_id,cast(trim(both ' MB' from memtotal) as integer) as memtotal from asset_serverstatus group by sid_id,memtotal ) as ast on ass.id=ast.sid_id where ass.is_server=1 and ast.memtotal < 1024 and ass.idc_id=42 order by ass.ip;"

1、
postgres@ubuntu:~$ psql -U wzq -d template0
psql: FATAL: Peer authentication failed for user “wzq”

其中peer authentication 比较注目，很明显，错误和权限有关联。

参考链接PostgreSQL 修改设置数据库的默认用户以及权限. ：http://www.cnblogs.com/jinanxiaolaohu/p/11452472.html：https://blog.csdn.net/weixin_30265103/article/details/101908371

PostgreSQL清空表并保留表结构、清空数据库还原数据库为新建时的状态的方法：https://blog.csdn.net/u013719339/article/details/79627010

PostgreSQL 如何实现批量更新、删除、插入：https://yq.aliyun.com/articles/74420

postgresql 删除单个表的数据：https://blog.csdn.net/kmust20093211/article/details/47734975

postgresql批量删除表：https://blog.csdn.net/weixin_30384217/article/details/98387285

修改postgres密码：https://blog.csdn.net/qiuyoujie/article/details/89853029

postgres安装和创建用户和创建数据库： https://www.cnblogs.com/weihengblog/p/10082570.html

https://www.jb51.net/article/203163.htm

在这里插入图片描述

寰宇001

关注

3
点赞
踩
24

收藏

觉得还不错? 一键收藏
打赏
0
评论
postgresql常用命令

环境：Ubuntu 16.04 LTS数据库版本：9.6.61. 登录PG数据库.psql -U postgres界面效果为:postgres@ubuntu:~$ psqlpsql (9.5.19)Type “help” for help.postgres=#简单说一下pgsql 的相关命令使用反斜线作为命令前缀.退出 \q列出所有的数据库 \l更改当前连接的数据...
复制链接

扫一扫