工作中會用到很多小工具杯缺，特此記錄。
批量導(dǎo)出表
只導(dǎo)出insert語句
pg_dump -h host -p 5432 -U postgres -a -t t1 -t t2 --inserts -f /opt/temp.sql -d mcsas
導(dǎo)出全部表結(jié)構(gòu)和模式
pg_dump -h host -p 5432 -U postgres -t t1 -t t2 -f /opt/temp.sql -d mcsas
數(shù)據(jù)庫備份
pg_dump -h master -p 5432-U postgres -w -f /home/postgres/test.backup test
數(shù)據(jù)庫還原
pg_restore -h master -p 5432-U postgres -w -d test /home/postgres/test.backup
如果是文本格式的dump裹纳，直接使用
psql的 \i xxx.backup
shp導(dǎo)入pg
使用pgadmin3的可視化工具，也可以通過命令行
用法：shp2pgsql [<options>] <shapefile> [[<schema>.]<table>]
OPTIONS:
-s ： [<from>:]<srid> 設(shè)置 SRID字段，默認(rèn)0勉躺，不能和 -D一起使用侈百。
(-d|a|c|p) 常用的互斥操作選項
-d 刪除之前的表瓮下，重建一個表導(dǎo)入shp數(shù)據(jù)。
-a 將shp數(shù)據(jù)追加到已有的表钝域，在同一個schema下讽坏。
-c 創(chuàng)建一個新表，然后導(dǎo)入shp數(shù)據(jù)例证，不指定操作選項會默認(rèn)這個路呜。
-p 預(yù)備模式，只創(chuàng)建表织咧，不導(dǎo)入數(shù)據(jù)拣宰。
-g <geocolumn> 指定表的圖形列，（更多用在append模式下）
-D Use postgresql dump format (defaults to SQL insert statements).
-e 獨立執(zhí)行烦感，不使用事務(wù)巡社。.和-D不相容。
-G Use geography type (requires lon/lat data or -s to reproject).
-k Keep postgresql identifiers case.
-i 對dbf中所有的integer子彈使用int4類型
-I 對geocolumn創(chuàng)建空間索引手趣。
-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 創(chuàng)建單圖形而不是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 只導(dǎo)入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.
舉例如下肥荔，導(dǎo)入一個shp，指定geomcolumn名稱為geom朝群，建立空間字段燕耿，圖形類型是單義圖形。
shp2pgsql -c -g geom -D -s 4326 -S -i -I shaperoads.shp myschema.roadstable | psql -d roadsdb
pg導(dǎo)出shp
用法: pgsql2shp [<options>] <database> [<schema>.]<table>
pgsql2shp [<options>] <database> <query>
OPTIONS:
-f <filename> 導(dǎo)出文件名稱
-h <host> 數(shù)據(jù)庫host
-p <port> 數(shù)據(jù)庫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數(shù)據(jù)中public的schema中test表導(dǎo)出為shp誉帅。
pgsql2shp -h host -p 5432 -u postgres -f /opt/test.shp testdb public.test
數(shù)據(jù)庫備庫
pg_basebackup -D $PGDATA -Fp -Xs -v -P -h master -p 5432 -U repuser
數(shù)據(jù)庫同步時間線
pg_rewind --target-pgdata=/home/postgres/data --source-server='host=slave port=5432 user=postgres dbname=postgres'
刪除表重復(fù)數(shù)據(jù)
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
新建事務(wù)臨時表
create temp table tt(id int,name text) on commit drop; --事務(wù)結(jié)束就消失
create temp table tt(id int,name text) on commit delete rows; --事務(wù)結(jié)束數(shù)據(jù)消失
create temp table tt(id int,name text) on commit preserver rows; --數(shù)據(jù)存在整個會話周期中
賦予用戶讀取schema權(quán)限
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執(zhí)行sql
/*NO LOAD BALANCE*/ select * from abc;
強制斷開所有連接
select pg_terminate_backend(pg_stat_activity.pid) from pg_stat_activity where datname='tt';
copy導(dǎo)入csv
copy sexit from 'e:/sexit.csv' delimiter as '|' csv quote as '''';
