pgsql常用命令

断开有有活动的链接：
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’