postgresql基本语法

启动和停止数据库：
postgres -D /export/pgsql/data/ > /export/pgsql/log/pg_server.log 2>&1 &
pg_ctl stop -D /export/pgsql/data/
1.登录
  sudo su - postgres  #需要使用postgres用户
  psql -h localhost -p 5432 #-h指定主机名，-p指定监听端口号
  \c testdb testch  
  show search_path; --查看当前的查找路径，schema
  set search_path= "$user", public,wala;
  \d --查看wala schema下的所有表、视图、序列等数据库对象
  \d c_comment --describe database objects' DEFINITION


-- 显示当前默认schema
show search_path;
 
-- 设置默认schema,退出后再登录，就失效了
set search_path='scrm';


2.查看当前正在执行的语句
\d pg_stat_activity  --\d=desc tablename
select datname,usename,client_hostname,client_port,application_name,query 
from pg_stat_activity 
where query!='<IDLE>';
select pid,usename,client_hostname,state,query from pg_stat_activity;
  
1.查看数据库软件版本
select version();




2.查看服务器运行时间
select date_trunc('second',current_timestamp - pg_postmaster_start_time()) as uptime;




3.查看数据库大小
select datname, pg_database_size(datname)/1024/1024 "dbsize M" from pg_database;


查看表的大小
select  pg_size_pretty(pg_relation_size('test01'));


查看表的索引的大小
select pg_size_pretty(pg_indexes_size('test01'));


查看表空间大小
select pg_size_pretty(pg_tablespace_size('pg_global')); 
select pg_size_pretty(pg_tablespace_size('pg_default')); 


4.切换日志
设置日志启动方式：
log_truncate_on_rotation = on(postgres.conf)
select pg_rotate_logfile();


切换WAL日志
select pg_switch_xlog();


手动checkpoint
checkpoint;


4.根据后台进程号，查看当前执行的sql语句：
select * 
from (
select pg_stat_get_backend_pid(s.backendid) as procid,
pg_stat_get_backend_activity(s.backendid) as current_query 
from (
select pg_stat_get_backend_idset() as backendid
) 
as s
) as querystring
where procid=xxxx;


找出长时间运行的SQL
select pid,usename,query_start,query from pg_stat_activity;


SELECT procpid, start, now() - start AS lap, 
current_query 
FROM 
(SELECT 
backendid, 
pg_stat_get_backend_pid(S.backendid) AS procpid, 
pg_stat_get_backend_activity_start(S.backendid) AS start, 
pg_stat_get_backend_activity(S.backendid) AS current_query 
FROM 
(SELECT pg_stat_get_backend_idset() AS backendid) AS S 
) AS S 
WHERE 
current_query <> '<IDLE>' 
ORDER BY 
lap DESC;


5.杀死某个session,杀死长时间运行的SQL
select pg_cancel_backend($SID);
select pg_terminate_backend($SID);




mkdir -p /u01/app/postgres/data/tbs_gpsp on dbserver3,dbserver4
---/opt/postgres/data/pg_tbsdir/tbs_gpsp
CREATE ROLE gpsp LOGIN PASSWORD 'PASSWORD'  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE TABLESPACE tbs_gpsp OWNER gpsp LOCATION '/u01/app/postgres/data/tbs_gpsp';
--CREATE TABLESPACE tbs_gpsp OWNER gpsp LOCATION '/opt/postgres/data/pg_tbsdir/tbs_gpsp';




CREATE DATABASE gpsp  WITH OWNER = gpsp  ENCODING = 'UTF8'  TABLESPACE = tbs_gpsp LC_COLLATE = 'en_US.UTF8'  LC_CTYPE = 'en_US.UTF8' CONNECTION LIMIT = -1;
CREATE SCHEMA gpsp AUTHORIZATION gpsp;




--GRANT ALL ON SCHEMA gpsp TO gpsp;
CREATE TABLE gpsp.joblock(
  jobname character varying(80) NOT NULL,
  firetime character varying(20),
  ip character varying(15),
  status character varying(10),
  nextfire character varying(20) NOT NULL,
  CONSTRAINT pk_joblock PRIMARY KEY (jobname , nextfire)
);
ALTER TABLE gpsp.joblock OWNER TO gpsp;




序列的采用：
nextval('gsp.seq_check_bill')




1.PG序列的创建
方法a：直接在表中指定字段类型是 SERIAL 类型就一了
CREATE TABLE person (id SERIAL,name TEXT);
方法b：先创建序列名称，然后在新建的表中列属性指定序列就可以了，该列需INT类型列
CREATE SEQUENCE person_id_seq; 
CREATE TABLE person (id INT4 NOT NULL DEFAULT nextval('person_id_seq'),name TEXT );


2.PG序列的应用 
查看当前会话的序列值
SELECT currval('person_id_seq');
查看下一个序列值
SELECT nextval('person_id_seq') ;
查看全局的最后一个序列值
select last_value from person_id_seq;
重置序列值
select setval('person_id_seq',8);
3.序列删除当有表字段应用到PG序列时不能直接删除 
drop table table_name; 
drop sequence seq_name;


索引重建：
create index CONCURRENTLY idx_id on table (id);
--重建 
遇到上述失效的索引重建时两个办法，一个是drop index index_name，然后再执行create index concurrently。还有一个是执行reindex index_name命令，但是后者不支持concurrent参数。 


表导出导入
导出表 employee 到默认输出 STDOUT
COPY employee TO STDOUT;




导出表 employee 到 sql 文件：
COPY employee TO '/home/smallfish/employee.sql';


从文件导入数据：
COPY employeenew FROM '/home/smallfish/employee.sql';


导入文件数据，指定间隔符为 | ：
COPY employeenew FROM '/home/smallfish/employee.sql' DELIMITER |;


导出为CVS格式：
COPY employee (name, email) TO '/home/smallfish/employee.csv' CSV HEADER;


create table t (id int,name varchar(20));
insert into t values (1,'chen');
insert into t values (2,'chen123');


Postgresql数据的导入和导出，以及copy命令介绍
如何导出PostgreSQL数据库中的数据：


导出scrm库：
pg_dump -U postgres -f dump.sql scrm;
具体person表：
pg_dump -U postgres -t person -f scrm_person_dump.sql scrm




导入数据时首先创建数据库再用psql导入：
drop database gpsp;
create database gpsp;
psql -d scrm -U postgres -f dump.sql
psql -d scrm -U postgres -f scrm_person_dump.sql


把数据按照自己所想的方式导出，强大的copy命令：
echo "copy students to? stdout DELIMITER '|'"|psql school|head
(students为表名，school为库名，各个字段以|分隔)
echo 'copy (select * from students order by age limit 10) to stdout;' | psql school


导出为CVS格式：
 COPY employee (name, email) TO '/home/smallfish/employee.csv' CSV HEADER;


导出csv文件
COPY gpsp.t2 (id) TO '/home/postgres/android2.csv' CSV HEADER;


导入csv文件:要以管理员身份导入
create table t2 (id);
psql
\c scrm 
COPY scrm.person (id) from  '/home/postgres/person.csv' with CSV HEADER;
备注: 当需要导出表数据到文件时， copy 命令需要以超级用户执行。而 \copy 元子命令不需要。 
由于 copy 命令始终是到数据库服务端找文件，当以文件形式导入导出数据时需以
超级用户执行，权限要求很高，适合数据库管理员操作;而 \copy 命令可在客户端执行导
入客户端的数据文件，权限要求没那么高，适合开发人员，测试人员使用，因为生产库
的权限掌握在 DBA 手中。
备注：由于 copy 命令会到服务端找文件，所以找不到。
set search_path=scrm;
\d


导入csv:
psql -h 103.20.250.36 -p 5432 -U infocenter -d report
create table tempa (id bigint);
alter table infocenter.tempa owner to  infocenter;
 \copy infocenter.tempa (id) from  '/home/postgres/apple.csv' with CSV HEADER;