Postgresql日常运维操作

1.进入postgres用户的控制台

psql -U postgres -p 端口

 

2.查看有哪些数据库

\l

\c 切换数据库

\c - username 切换用户

\x 格式化显示

 

3.选择postgresql 这个数据库

\c postfresql

 

4.创建用户

create user username with login password 'password';

 

5.修改用户密码

ALTER USER username WITH PASSWORD 'passwd';

 

6.查看所有用户

select * from pg_user;

7.配置连接权限

vi $PGDATA/pg_hba.conf

 

8.开启记录执行时间

\timing

9.数据导出

pg_dump -U postgres -t 表名 库名 -p端口 | gzip > xxx.gz            ##导出表数据

 

pg_dump -h localhost -U postgres -p 端口号  库名  > xxxxxx.sql    ##导出库数据

 

COPY (select * from user where name='lisi') TO 'xxxx.csv' WITH csv;   ##导出查询的数据

 

10.导入数据

 

COPY table_name FROM '/tmp/data/test.csv' WITH csv;         ##导入(查询)数据

 

gunzip -c xxx.gz | psql -U postgres 库名 -p 端口号            ##导入压缩包数据

 

11.查询用户权限

select * from INFORMATION_SCHEMA.role_table_grants where grantee='username';

 

12.授权数据库所有权限给用户

GRANT ALL PRIVILEGES ON DATABASE exampledb TO dbuser;

 

 

13.用户授权

grant all privileges on all tables in schema public  to ddpguser;

grant all privileges on all sequences in schema public  to ddpguser;

grant all privileges on all tables in schema log   to ddpguser;

grant all privileges on all sequences in schema log   to ddpguser;

grant all privileges on all tables in schema crontab  to ddpguser;

grant all privileges on all sequences in schema crontab  ddpguser;

 

14.查询物化视图

SELECT nspname FROM pg_namespace;

 

15.查询所有的schema

select distinct(schemaname) from pg_tables;

 

16.查询运行中的进程

select * from pg_stat_activity where state='active';

 

17.查询当前连接数:

select count(1) from pg_stat_activity;

 

18.查询最大连接数

show max_connections;

 

19.查询数据库锁

select * from pg_locks;

 

20.查询锁对应的表跟库

select a.locktype,a.database,a.pid,a.mode,a.relation

from pg_locks as a

where a.mode='ExclusiveLock'

 

21、创建表空间

create tablespace tbs_devertise location '/walarchive/tbs_devertise';

 

22、修改表空间

alter table devertise.device_order20160801 set tablespace tbs_devertise;

psql -U postgres 库名 -p端口号 -At -f aaa.sql

 

23、修改数据库空间

ALTER DATABASE name SET TABLESPACE new_tablespace;