Postgres 常用操作汇总

数据库连接

psql -U user_name -h host -p port database_name

备份\恢复

-- 备份  database_name
pg_dump -h host -p port -U user_name -F c -b -v -f /data/database_name_`date +%Y%m%d`.backup database_name
-- 恢复到新库database_name_new
pg_restore -d database_name_new  -v   /data/database_name_backup_file

导出查询结果

psql -h host -U user_name database -c "要执行的sql"> filename.csv

或者可以 连接到数据库，使用

\o filename.csv
	要执行的sql;
\o

执行文件中的sql

\i filename.sql

\命令

\h      	 #查看所有的sql关键字
\?     	 #命令行操作的帮助
\d   \d+  #查看当前schema 中所有的表
\q     	 #退出pg命令行
\d      	 #schema.table 查看表的结构
\x     	 #横纵显示切换
\dT+      #显示扩展类型相关属性及描述
\l     		 #列出所有的数据库
\timing  #显示执行时间
\c database_name        #切换数据库\
\encoding 显示字符集
set search to schema    #切换schema
explain sql             #解释或分析sql执行过程

\COPY

\COPY tablename(field1,field2...) from '/tmp/filename.csv' WITH csv header;
\COPY '要执的sql' to '/tmp/filename.csv' WITH csv header;
... 等等

导出表结构

pg_dump -h host -p 5432 -U postgres -s [-t] [tableName] dbName > /tmp/fileName.sql

查看表大小

--数据库中单个表的大小（不包含索引）
select pg_size_pretty(pg_relation_size('表名'));

--查出所有表（包含索引）并排序
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20

--查出表大小按大小排序并分离data与index
SELECT
table_name,
pg_size_pretty(table_size) AS table_size,
pg_size_pretty(indexes_size) AS indexes_size,
pg_size_pretty(total_size) AS total_size
FROM (
SELECT
table_name,
pg_table_size(table_name) AS table_size,
pg_indexes_size(table_name) AS indexes_size,
pg_total_relation_size(table_name) AS total_size
FROM (
SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
FROM information_schema.tables
) AS all_tables
ORDER BY total_size DESC
) AS pretty_sizes

执行时间

方法1 开启执行时间显示

\timing on
\timing off
``
##### 方法2 查看执行计划

explain [analyze] select * from 表名;

添加analyze会真实执行。不添加只返回计划。

(cost=0.56…165756.44 rows=14397 width=0)

结果说明：
cost=0.56 第一个值 是只返回一条时耗时;
..165756.44执行所有查询耗时。
rows:命中行数