Postgresql dba的一天

-POSTGRESQL




1.登录
  sudo su - postgres  #需要使用postgres用户
  psql -h localhost -p 1921 #-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= schema  name ;

2.查看当前正在执行的语句
  \d pg_stat_activity  --\d=desc tablename
  select datname,usename,client_hostname,client_port,application_name,current_query 
  from pg_stat_activity 
  where current_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.切换日志
 select pg_rotate_logfile();

切换WAL日志
 select pg_switch_xlog();

手动checkpoing
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(seq_name,new_seq_value);
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数据库中的数据：


导出某个库：
pg_dump -U postgres -f dump.sql gpsp
具体某个表：
pg_dump -U postgres -t gpsp.t -f gpsp_t_dump.sql gpsp






导入数据时首先创建数据库再用psql导入：
drop database gpsp;
create database  gpsp;
psql -d gpsp -U postgres -f 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 gpsp 
COPY gpsp.t2 (id) from  '/home/postgres/android.csv' with CSV HEADER;




备注: 当需要导出表数据到文件时， copy 命令需要以超级用户执行。而 \copy 元子命令不需要。 
由于 copy 命令始终是到数据库服务端找文件，当以文件形式导入导出数据时需以
超级用户执行，权限要求很高，适合数据库管理员操作;而 \copy 命令可在客户端执行导
入客户端的数据文件，权限要求没那么高，适合开发人员，测试人员使用，因为生产库
的权限掌握在 DBA 手中。
备注：由于 copy 命令会到服务端找文件，所以找不到。


set search_path=gpsp;
\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;




   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/91975/viewspace-1784791/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/91975/viewspace-1784791/