PostgreSQL工具杂记
工作中会用到很多小工具，特此记录。
批量导出表
只导出insert语句
pg_dump -h host -p 5432 -U postgres -a -t t1 -t t2 --inserts -f /opt/temp.sql -d mcsas
导出全部表结构和模式
pg_dump -h host -p 5432 -U postgres -t t1 -t t2 -f /opt/temp.sql -d mcsas
数据库备份
pg_dump -h master -p 5432-U postgres -w -f /home/postgres/test.backup test
数据库还原
pg_restore -h master -p 5432-U postgres -w -d test /home/postgres/test.backup
如果是文本格式的dump，直接使用
psql的 \i xxx.backup
shp导入pg
使用pgadmin3的可视化工具，也可以通过命令行
用法：shp2pgsql [<options>] <shapefile> [[<schema>.]<table>]
OPTIONS:
-s ： [<from>:]<srid> 设置 SRID字段，默认0，不能和 -D一起使用。
(-d|a|c|p) 常用的互斥操作选项
-d 删除之前的表，重建一个表导入shp数据。
-a 将shp数据追加到已有的表，在同一个schema下。
-c 创建一个新表，然后导入shp数据，不指定操作选项会默认这个。
-p 预备模式，只创建表，不导入数据。
-g <geocolumn> 指定表的图形列，（更多用在append模式下）
-D Use postgresql dump format (defaults to SQL insert statements).
-e 独立执行，不使用事务。.和-D不相容。
-G Use geography type (requires lon/lat data or -s to reproject).
-k Keep postgresql identifiers case.
-i 对dbf中所有的integer子弹使用int4类型
-I 对geocolumn创建空间索引。
-m <filename> Specify a file containing a set of mappings of (long) column
names to 10 character DBF column names. The content of the file is one or
more lines of two names separated by white space and no trailing or
leading space. For example:
COLUMNNAME DBFFIELD1
AVERYLONGCOLUMNNAME DBFFIELD2
-S 创建单图形而不是MULTI 图形。
-t <dimensionality> 强制指定图形为 '2D', '3DZ', '3DM', or '4D'
-w Output WKT instead of WKB. Note that this can result in
coordinate drift.
-W <encoding> Specify the character encoding of Shape's
attribute column. (default: "UTF-8")
-N <policy> 空图形策略 (insert*,skip,abort).
-n 只导入DBF文件
-T <tablespace> Specify the tablespace for the new table.
Note that indexes will still use the default tablespace unless the
-X flag is also used.
-X <tablespace> Specify the tablespace for the table's indexes.
This applies to the primary key, and the spatial index if
the -I flag is used.
-? Display this help screen.
举例如下，导入一个shp，指定geomcolumn名称为geom，建立空间字段，图形类型是单义图形。
shp2pgsql -c -g geom -D -s 4326 -S -i -I shaperoads.shp myschema.roadstable | psql -d roadsdb
pg导出shp
用法: pgsql2shp [<options>] <database> [<schema>.]<table>
pgsql2shp [<options>] <database> <query>
OPTIONS:
-f <filename> 导出文件名称
-h <host> 数据库host
-p <port> 数据库port
-P <password> 指定密码
-u <user> 指定用户
-g <geometry_column> 指定输出geom列名称
-b Use a binary cursor.
-r Raw mode. Do not assume table has been created by the loader. This would
not unescape attribute names and will not skip the 'gid' attribute.
-k Keep PostgreSQL identifiers case.
-m <filename> Specify a file containing a set of mappings of (long) column
names to 10 character DBF column names. The content of the file is one or
more lines of two names separated by white space and no trailing or
leading space. For example:
COLUMNNAME DBFFIELD1
AVERYLONGCOLUMNNAME DBFFIELD2
-? Display this help screen.
举例如下，将testdb数据中public的schema中test表导出为shp。
pgsql2shp -h host -p 5432 -u postgres -f /opt/test.shp testdb public.test
数据库备库
pg_basebackup -D $PGDATA -Fp -Xs -v -P -h master -p 5432 -U repuser
数据库同步时间线
pg_rewind --target-pgdata=/home/postgres/data --source-server='host=slave port=5432 user=postgres dbname=postgres'
删除表重复数据
DELETE FROM weather
WHERE ctid
NOT IN (
SELECT max(ctid)
FROM weather
GROUP BY city, temp_lo, temp_hi, prcp, date
);
跨表更新
update test t1 set field1=t2.field1 from test2 t2 where t1.id=t2.id
新建事务临时表
create temp table tt(id int,name text) on commit drop; --事务结束就消失
create temp table tt(id int,name text) on commit delete rows; --事务结束数据消失
create temp table tt(id int,name text) on commit preserver rows; --数据存在整个会话周期中
赋予用户读取schema权限
alter default privileges in schema public grant all on tables to freerep;
alter default privileges in schema public revoke all on tables to freerep;
pgpool强制由master执行sql
/*NO LOAD BALANCE*/ select * from abc;
强制断开所有连接
select pg_terminate_backend(pg_stat_activity.pid) from pg_stat_activity where datname='tt';
copy导入csv
copy sexit from 'e:/sexit.csv' delimiter as '|' csv quote as '''';
