日常学习

Postgres

January 13, 2017

pg创建只读用户

SELECT date_trunc('minute', dd) :: TIMESTAMP
FROM generate_series('2016-10-01' :: TIMESTAMP, '2016-12-31' :: TIMESTAMP, '10 minute' :: INTERVAL) dd;

CREATE USER read_from_pg WITH ENCRYPTED PASSWORD '...';
GRANT CONNECT ON DATABASE test to read_from_pg;
\c test
GRANT USAGE ON SCHEMA public to read_from_pg;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO read_from_pg;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_from_pg;

select * from empsalary;
update empsalary set salary=9999 where empno=10;

幻腾pg表结构

select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name = 'logs';

INSERT into big_logs (id, time, ip, device_ip, teleport_addr,op_code, params, dir,device_online,teleport_online) (
  SELECT * from logs where time > '2017-02-17 00:00:00'
);

create table big_logs (id bigint, time timestamp, ip inet, device_ip integer, teleport_addr integer, op_code varchar(255), params varchar(1024), dir integer, device_online boolean, teleport_online boolean)
SELECT setval('big_logs_id_seq', 2147483646);

logs	index_logs_on_op_code	op_code
logs	index_logs_on_teleport_addr	teleport_addr
logs	index_logs_on_teleport_addr_and_time	teleport_addr
logs	index_logs_on_teleport_addr_and_time	time
logs	index_logs_on_time	time
logs	logs_pkey	id

CREATE INDEX index_big_logs_on_device_ip ON big_logs (device_ip);
-- CREATE INDEX index_logs_on_teleport_addr ON big_logs (teleport_addr);
CREATE INDEX index_big_logs_on_time ON big_logs (time);
CREATE INDEX index_big_logs_on_time_and_device_ip ON big_logs (time, device_ip);
CREATE INDEX index_big_logs_on_teleport_addr ON big_logs (teleport_addr);
CREATE INDEX index_big_logs_on_time_and_teleport_addr ON big_logs (time, teleport_addr);
CREATE INDEX big_logs_pkey ON big_logs (id);


select
 t.relname as table_name,
 i.relname as index_name,
 a.attname as column_name
from
 pg_class t,
 pg_class i,
 pg_index ix,
 pg_attribute a
where
 t.oid = ix.indrelid
 and i.oid = ix.indexrelid
 and a.attrelid = t.oid
 and a.attnum = ANY(ix.indkey)
 and t.relkind = 'r'
 and t.relname like '%big_logs%'
order by
 t.relname,
 i.relname;