PostgreSQL常用命令

[size=medium][b](1)psql[/b][/size]

连接数据库
[quote]./psql -U username -d dbname[/quote]
列举数据库
[quote]psql> \l[/quote]
列举表
[quote]psql> \dt[/quote]
执行SQL文件
[quote]psql> \i test.sql[/quote]
查看执行时间
[quote]psql> \timing
psql> select * from tablename;[/quote]
查看表结构
[quote]psql> \d tablename[/quote]
切换数据库
[quote]psql> \c dbname[/quote]
查看帮助
[quote]psql> \?[/quote]
退出 psql
[quote]psql> \q[/quote]

[size=medium][b](2)pg_dump[/b][/size]

备份表：
[quote]./pg_dump -U username -h hostaddress -t tablename dbname > filename ;[/quote]
备份多个表：
[quote]./pg_dump -U username -h hostaddress -t tablenameA -t tablenameB dbname > filename ;[/quote]
备份数据库：
[quote]./pg_dump -U username -h hostaddress dbname > filename ; --默认导出schema和数据COPY文
./pg_dump -U username -h hostaddress --data-only dbname > filename ; --只导出数据
./pg_dump -U username -h hostaddress --inserts dbname > filename ; --数据INSERT文
./pg_dump -U username -h hostaddress --column-inserts dbname > filename ; --带字段名的数据INSERT文[/quote]
恢复数据库：
[quote]./psql -U username -h hostaddress -d dbname < filename ;[/quote]

文本文件模式：
[quote]Backup: $ pg_dump -U {user-name} {source_db} -f {dumpfilename.sql}
Restore: $ psql -U {user-name} -d {desintation_db} -f {dumpfilename.sql}[/quote]
tar.gz文件模式：
[quote]Backup: $ pg_dump -U {user-name} {source_db} -F tar -f {dumpfilename.tar.gz}
Restore: $ psql -U {user-name} -d {desintation_db} -f {dumpfilename.tar.gz}[/quote]

[size=medium][b](3)COPY[/b][/size]

导出表到默认输出STDOUT：
[quote]psql> COPY tablename TO STDOUT;[/quote]
导出表到SQL文件：
[quote]psql> COPY tablename TO '/home/tablename.sql';[/quote]
指定导出间隔符，默认是 \t ，这里为 | ：
[quote]psql> COPY tablename TO STDOUT DELIMITER '|';[/quote]
导出指定字段的数据：
[quote]psql> COPY tablename (name,email) TO STDOUT;[/quote]
导出为CSV格式：
[quote]psql> COPY tablename (name, email) TO '/home/tablename.csv' CSV HEADER;[/quote]
把SELECT文结果导出到CSV：
[quote]COPY (select * from tablename) TO '/tmp/output.csv' DELIMITER ','[/quote]

通过psql实现：
[quote]psql dbname -c "select * from tablename" -A -F, | sed '$d' > /tmp/output.csv[/quote]
[quote]psql dbname -f /usr/local/src/myselect.sql -A -F, | sed '$d' > /tmp/output.csv[/quote]

从文件导入数据：
[quote]psql> COPY tablename FROM '/home/tablename.sql';[/quote]
导入文件数据，指定间隔符为 | ：
[quote]psql> COPY tablename FROM '/home/tablename.sql' DELIMITER |;[/quote]

[size=medium][b](4)其他[/b][/size]

设置编码：
[quote]psql> CREATE DATABASE new_my_db with template=template0 encoding='UTF8' lc_collate='ja_JP.UTF-8' lc_ctype='ja_JP.UTF-8';[/quote]

创建一个只读user
[quote]CREATE ROLE xxx LOGIN PASSWORD 'yyy';
GRANT CONNECT ON DATABASE vl60_acct_rensn TO xxx;
GRANT USAGE ON SCHEMA public TO xxx;

GRANT SELECT ON cmn_account TO xxx;
GRANT SELECT ON paramsetting TO xxx;
GRANT SELECT ON cmn_mailaccount TO xxx;
GRANT SELECT ON log_mailaccount TO xxx;

SELECT 'GRANT SELECT ON ' || relname || ' TO xxx;'
FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
WHERE nspname = 'public' AND relkind IN ('r', 'v')[/quote]