PostgreSQL 常用命令

postgresql:

cd /opt/truck_acrm/jar
screen -S email_screen /usr/local/java/jdk1.7.0_79/bin/java -jar ./email-proxy-2d1.jar

备份：pg_dump -h localhost -p 5432 -U postgres -W -F c -b -v -f "/home/dbackup/mbafcsmseton_jock.backup" mbafcsmseton
恢复：pg_restore -h 192.168.0.100 -p 5432 -U postgres -W -d us2011 -v "/root/us2010.backup"

pg_restore -h localhost -p 5432 -U postgres -W -d mbafcsms_test -v "/data/database_backup/data/2019100604_mbafcsms.backup"
pg_restore -h localhost -p 5432 -U postgres -W -d mblcsms_test -v "/data/database_backup/data/2019100604_mblcsms.backup"

pg_dump -h localhost -p 5432 -U postgres -W -F c -b -v -f "/home/dback_eton/mbafcsmseton_2019-06-22.backup" mbafcsmseton
pg_dump -h localhost -p 5431 -U postgres -W -F c -b -v -f "/home/dback_eton/mbclsmseton_2019-06-22.backup" mbclsmseton

PS：“-d us2011 ”中的“us2011 ”数据库需要事先建立

pg_dump -U postgres -t mbafc_sms_inbox -f /home/inbox.sql mbafcsmseton;

psql -d database -U postgres -f dump.sql

--查询当前使用的连接数
SELECT count() FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
--查询当前连接信息
select from pg_stat_activity
--找到空闲进程并杀掉
SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE current_query='';

--kill
SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname='xxxx'

--query
SELECT * FROM pg_stat_activity WHERE datname='xxxx'

scp是有Security的文件copy，基于ssh登录。操作起来比较方便，比如要把当前一个文件copy到远程另外一台主机上，可以如下命令。

scp -r /home/daisy/full.tar.gz root@172.19.2.75:/home/root

scp -r /home/database_bak/mbclsmseton_2019-06-22.bak root@192.168.169.10:/data

copy ( select * from i_nice_call_data ) to '/opt/nice_t_all_2019_11_25.csv' with csv header ;

copy ( select * from pg_stat_activity ) to '/home/2016-01-01.csv' with csv header ;

--查看所有表大小
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;

--删除重复的数据，保留一条
delete from people
where peopleName in (select peopleName from people group by peopleName having count(peopleName) > 1)
and peopleId not in (select min(peopleId) from people group by peopleName having count(peopleName)>1)

select * from pg_stat_activity where procpid='50802';