断开有有活动的链接:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname=‘testdb’ AND pid<>pg_backend_pid();
数据库csv导入:
psql -h 10.96.50.10 -p 5432 -U user database
\copy dtdream_accounting_entry(batch,period,gl_date,vendor_num,vendor_name,invoice_code,invoice_type,company,cstcentre,account,account_description,typ
e,description,project_code,product,region,bm,sm,ic,spare,currency,accounted_dr,accounted_cr,net_amount,state) from 'F:/账套导入模板.csv’with csv header;
创建只读用户:
1.创建一个用户名为readonly密码为ropass的用户
CREATE USER readonly WITH ENCRYPTED PASSWORD ‘ropass’;
2.用户只读事务
alter user readonly set default_transaction_read_only=on;
3.把所有库的语言的USAGE权限给到readonly
GRANT USAGE ON SCHEMA public to readonly;
4.授予select权限(这句要进入具体数据库操作在哪个db环境执行就授予那个db的权)
\c dbname;(\l 查看数据库列表)
grant select on all tables in schema public to readonly;
重启:/etc/init.d/postgresql restart
配置文件:/etc/postgresql/9.5/main
导入整个数据库
psql -U postgres(用户名) 数据库名(缺省时同用户名) < /data/dum.sql 要先切换到postgres用户
导出整个数据库
pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名) >/data/dum.sql
导出某个表
pg_dump -h localhost -U postgres(用户名) 数据库名(缺省时同用户名) -t table(表名) >/data/dum.sql
更改数据库名:ALTER DATABASE name RENAME TO new_name
更改数据库owner:ALTER DATABASE name OWNER TO new_owner
查询主键:
SELECT
pg_constraint.conname AS pk_name,
pg_attribute.attname AS colname,
pg_type.typname AS typename
FROM
pg_constraint
INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.oid
INNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attnum = pg_constraint.conkey [ 1 ]
INNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid
WHERE
pg_class.relname = ‘table_name’
AND pg_constraint.contype = ‘p’