如何导入PostgreSQL数据库数据
\u5982\u4f55\u5bfc\u5165PostgreSQL\u6570\u636e\u5e93\u6570\u636e\u7528\u6211\u5199\u7684\u8f6f\u4ef6\u53ef\u4ee5\u7528Excel\u5bfc\u5165\u5230PostgreSQL\uff0c\u4f60\u4e0a\u7f51\u627efitreport\u5c31\u884c\u4e86\u3002
\u6216\u8005\u8fd9\u6837\u505a\uff1a
\u53ef\u4ee5\u5199\u8bed\u53e5\u6216\u91c7\u7528\u4ee5\u4e0b\u7684\u65b9\u6cd5
\u7f51\u4e0a\u8f6c\u5f55\u4e0b\u6765\u7684\u65b9\u6cd5\uff1a
\u6b65\u9aa4\uff1a
1.\u5c06excel\u8868\u683c\u5b57\u6bb5\uff0c\u6309\u7167postgresql \u6570\u636e\u5e93\u4e2d\u8868\u7684\u5b57\u6bb5\u987a\u5e8f\u6765\u6574\u7406\u6570\u636e\uff0c\u5e76\u4fdd\u5b58\u4e3acsv\u6587\u4ef6\u3002
2.\u7528\u8bb0\u4e8b\u672c\u6253\u5f00csv\u6587\u4ef6\uff0c\u53e6\u5b58\u4e3aUTF-8\u683c\u5f0f\u3002
3.\u4f7f\u7528PgAdmin\u5ba2\u6237\u7aef\u94fe\u63a5postgresql\u6570\u636e\u5e93\uff0c\u6267\u884c\u5982\u4e0b\u811a\u672c\uff0c\u5bfc\u5165csv\u6587\u4ef6\u5230PostgreSQL\u6570\u636e\u8868\uff1a
copy testdata from 'd:/test/testdata.csv' delimiter as',' csv quote as '"'
\u6ce8\uff1atestdata \u662fpostgreSQL\u6570\u636e\u5e93\u8868\u7684\u540d\u79f0\u3002
\u6ce8\u610f\u4e8b\u9879\uff1a
1.test\u76ee\u5f55\u9700\u8981\u8d4b\u4e88postgres \u7528\u6237\u53ef\u8bfb\u5199\u7684\u6743\u9650\uff0c\u5426\u5219\u4f1a\u6709\u5982\u4e0b\u62a5\u9519\u4fe1\u606f\uff1a
ERROR: could not open file "d:/testdata2.csv" forwriting: Permission denied
2.csv\u6587\u4ef6\u8981\u4e3autf-8\u683c\u5f0f\uff0c\u5426\u5219\u5bfc\u5165\u65f6\u53ef\u80fd\u4f1a\u6709\u62a5\u9519\uff1a
ERROR: invalid bytesequence for encoding "UTF8": 0xcdf5
3.\u6ce8\u610fwindows\u73af\u5883\u4e0b\u6587\u4ef6\u8def\u5f84\u7684\u8868\u8ff0\u65b9\u5f0f\uff0c\u5982\uff1ad:/test/data.csv
1\u3001\u5b89\u88c5postgresql
yum install postgresql postgresql-server
mysql\u5360\u7528\u7aef\u53e33306 pgsql\u662f5432
2\u3001\u5bfc\u5165\u6574\u4e2a\u6570\u636e\u5e93
psql -U postgres(\u7528\u6237\u540d) \u6570\u636e\u5e93\u540d(\u7f3a\u7701\u65f6\u540c\u7528\u6237\u540d) < /data/dum.sql
3\u3001\u5bfc\u51fa\u6574\u4e2a\u6570\u636e\u5e93
pg_dump -h localhost -U postgres(\u7528\u6237\u540d) \u6570\u636e\u5e93\u540d(\u7f3a\u7701\u65f6\u540c\u7528\u6237\u540d) >/data/dum.sql
4\u3001\u5bfc\u51fa\u67d0\u4e2a\u8868
pg_dump -h localhost -U postgres(\u7528\u6237\u540d) \u6570\u636e\u5e93\u540d(\u7f3a\u7701\u65f6\u540c\u7528\u6237\u540d) -t table(\u8868\u540d) >/data/dum.sql
5\u3001\u538b\u7f29\u65b9\u6cd5
\u4e00\u822c\u7528dump\u5bfc\u51fa\u6570\u636e\u4f1a\u6bd4\u8f83\u5927\uff0c\u63a8\u8350\u4f7f\u7528xz\u538b\u7f29
\u538b\u7f29\u65b9\u6cd5 xz dum.sql \u4f1a\u751f\u6210 dum.sql.xz \u7684\u6587\u4ef6
6\u3001xz\u538b\u7f29\u6570\u636e\u5012\u6570\u6570\u636e\u5e93\u65b9\u6cd5
xzcat /data/dum.sql.xz | psql -h localhost -U postgres(\u7528\u6237\u540d) \u6570\u636e\u5e93\u540d(\u7f3a\u7701\u65f6\u540c\u7528\u6237\u540d)
1、运行“命令提示符”。
2、切换至PostgreSQL数据库安装目录中的bin目录下。
3、执行此目录下的shp2pgsql命令：“shp2pgsql c:\road.shp road > c:\road.sql”。
4、如将此文件直接导入数据库（不推荐）：“shp2pgsql -c c:\road.shp road sjzmap | psql -d sjzmap”。
5、使用pgAdmin3 选择数据库，再导入表。
注：
Drops the database table before creating a new table with the data in the Shape file.
Appends data from the Shape file into the database table. Note that to use this option to load multiple files, the files must have the same attributes and same data types.
Creates a new table and populates it from the Shape file.
Only produces the table creation SQL code, without adding any actual data. This can be used if you need to completely separate the table creation and data loading steps.
Use the PostgreSQL "dump" format for the output data. This can be combined with -a, -c and -d. It is much faster to load than the default "insert" SQL format. Use this for very large data sets.
Creates and populates the geometry tables with the specified SRID.
Keep identifiers' case (column, schema and attributes). Note that attributes in Shapefile are all UPPERCASE.
Coerce all integers to standard 32-bit integers, do not create 64-bit bigints, even if the DBF header signature appears to warrant it.
Create a GiST index on the geometry column.
Output WKT format, for use with older (0.x) versions of PostGIS. Note that this will introduce coordinate drifts and will drop M values from shapefiles.
Specify encoding of the input data (dbf file). When used, all attributes of the dbf are converted from the specified encoding to UTF8. The resulting SQL output will contain a command, so that the backend will be able to reconvert from UTF8 to whatever encoding the database is configured to use internally.
绛旓細navcat鏈 mysql 鍜 pgsql 杩欎袱涓増鏈,鐢╩ysql鐗 瀵煎嚭,(鏈夊ソ澶氱鏍煎紡,鎴戜竴鑸敤access)鐒跺悗鐢 pgsql閭ｄ釜鐗堟湰 鍘瀵煎叆 Pg http://www.navcat.com pg鐗 Key;濮撳悕(Name)锛歒soho 缁勭粐(Organization)锛歸ww.Ysoho.com 娉ㄥ唽鐮(Serial)锛歂AVK-NLIA-OY7D-ZYRO Mysql鐗 Key:濮撳悕(Name)锛歸ww.Ysoho....
绛旓細鍙互鐨1銆佸湪鐩殑SQL Server鏁版嵁搴撴湇鍔″櫒涓婂畨瑁匫RACLE Client杞欢鎴栬匫RACLE ODBC Driver.閫氳繃oracle瀹㈡埛绔紝鍦╪etmanager涓缓绔嬧滄湇鍔★紙TNS server锛夆濓紝骞舵祴璇曡繛鎺ヤ笂oracle鏁版嵁搴撴甯2銆佸湪sqlserver鐨凞TS(瀵煎叆鍜屽鍑烘暟鎹伐鍏)杩涜锛1锛夐夋嫨鏁版嵁婧愶細鏁版嵁婧愶細oracle in oracle9i閫夋嫨锛氱敤鎴/绯荤粺DSN:鐐瑰嚮鏂板缓DSN...
绛旓細濡傛灉琛ㄧ粨鏋勫湪mysql鍜宲ostgresql鐨勭粨鏋勫樊鍒笉澶э紝閭ｄ箞鐩镐簰杩佺Щ鏄瘮杈冪畝鍗曠殑銆傚彲浠ラ氳繃鐢╯hell鑴氭湰鏉ュ鐞嗚兘寰堝ソ鐨勫疄鐜般傜涓锛歮ysql 瀵煎嚭鏂囨湰鏂囦欢銆傜浜岋細shell澶勭悊鏂囨湰鏂囦欢鐨勬牸寮忥紝浣夸箣閫傚悎postgresql瀵煎叆鏍煎紡锛岀敓鎴愯嚦鐩爣鏂囨湰鏂囦欢銆傜涓夛細鍦╬ostgresql涓墽琛岀洰鏍囨枃鏈枃浠跺嵆鍙
绛旓細7.鍏舵锛屽湪杩欎釜鐣岄潰涓紝閫夋嫨鈥滃鍒朵竴涓垨澶氫釜琛ㄦ垨瑙嗗浘鐨勬暟鎹濄8.鍦ㄦ鐣岄潰涓户缁紝骞跺崟鍑烩滀笅涓姝モ濇寜閽9.鐒跺悗鍦ㄨ繖涓晫闈紝鍕鹃夆滅珛鍗宠繍琛屸濄10.鏈鍚庯紝鍗曞嚮姝ょ晫闈腑鐨勨滃畬鎴愨濇寜閽濡備綍浣跨敤python杩炴帴鏁版嵁搴擄紝鎻掑叆骞舵煡璇㈡暟鎹紵python3.2==>ORM(鎴栬呮暟鎹簱鎺ュ彛)==>鏁版嵁搴撴暟鎹簱:PostgreSQL9銆...
绛旓細瀹夎鐩綍鎵惧埌raster2pgsql鎵鍦ㄨ矾寰勶紝骞跺湪鍛戒护鎻愮ず绗﹀垏鎹㈠埌璇ヨ矾寰勶紝杩愯鍛戒护琛岃鍙ャ佸洖杞︺佽緭鍏ュ瘑鐮併佸洖杞︼紝鍗冲彲灏唗if鏁版嵁瀵煎叆鍒postgresql涓PostgreSQL鏄竴绉嶇壒鎬ч潪甯搁綈鍏ㄧ殑鑷敱杞欢鐨勫璞-鍏崇郴鍨嬫暟鎹簱绠＄悊绯荤粺锛圤RDBMS锛夛紝鏄互鍔犲窞澶у璁＄畻鏈虹郴寮鍙戠殑POSTGRES锛4銆2鐗堟湰涓哄熀纭鐨勫璞″叧绯诲瀷鏁版嵁搴撶鐞嗙郴缁熴侾OS...
绛旓細PostgreSQL鎷ユ湁娲昏穬涓斿簽澶х殑寮鍙戣呯ぞ鍖猴紝涓板瘜鐨勬彃浠跺拰鎵╁睍璁╁叾鍔熻兘鏃犺竟鐣岋紝寮哄ぇ鐨勫伐鍏峰拰绗笁鏂瑰簱涓哄紑鍙戣呮彁渚涗簡鏃犲敖鐨勫垱鏂扮┖闂淬傝繖浣垮緱PostgreSQL鍦ㄦ寔缁敼杩涘拰閫傚簲鎬ф柟闈㈣秴瓒婁簡MySQL銆傚湪鍩烘湰鎿嶄綔涓婏紝PostgreSQL鐨勮娉曠畝娲佹槗鎳傘傚垱寤鸿〃鐨勫懡浠ゆ槸杩欐牱鐨勶細CREATE TABLE 琛ㄥ悕 (鍒楀畾涔); 鎻掑叆鏁版嵁閫氳繃锛欼NSERT INTO ...
绛旓細搴旇鏄PostgreSQL鏁版嵁搴撶殑澶囦唤鏂囦欢銆傛暟鎹簱鐨瀵煎叆瀵煎嚭鏄渶甯哥敤鐨勫姛鑳戒箣涓锛屾瘡绉嶆暟鎹簱閮芥彁渚涙湁杩欐柟闈㈢殑宸ュ叿锛孭ostgreSQL鎻愪緵鐨勫搴斿伐鍏蜂负pg_mp鍜宲g_restore銆俻g_mp鏄敤浜庡浠絇ostgreSQL鏁版嵁搴撶殑宸ュ叿銆傚畠鍙互鍦ㄦ暟鎹簱姝ｅ湪浣跨敤鐨勬椂鍊欒繘琛屽畬鏁翠竴鑷寸殑澶囦唤锛屽苟涓嶉樆濉炲叾瀹冪敤鎴峰鏁版嵁搴撶殑璁块棶銆傝浆鍌ㄦ牸寮忓彲浠ユ槸涓涓剼鏈...
绛旓細濡傛灉鎯宠鍦PostgreSQL涓瓨鍌ㄤ簩杩涘埗鏁版嵁锛屼緥濡傚瓨鍌╓ord銆丒xcel鏂囨。锛屽浘鐗囨枃浠剁瓑锛屽彲浠ヤ娇鐢╞ytea绫诲瀷鐨勫垪銆俠ytea绫诲瀷鏄疨ostgreSQL鐗规湁鐨勫瓨鍌ㄤ簩杩涘埗鏁版嵁鐨勫瓧娈电被鍨嬶紝涓嶴QL鏍囧噯涓殑BLOB鍜孊INARY LARGE OBJECT绫诲瀷寮傛洸鍚屽伐銆傝繖鍦≒ostgreSQL鏂囨。鐨刡ytea绫诲瀷浠嬬粛涓湁鎵璇存槑銆傛帴涓嬫潵鍏堣璇濡備綍鍚戣〃涓鎻掑叆銆佹洿鏂癰ytea鏁版嵁...
绛旓細鎴愬憳鐨勫姛鑳姐傛湁浜嗕笉鍚屾暟鎹簱绫诲瀷鐨勮繛鎺ヨ兘鍔涳紝Navicat Premium 鏀寔鍦 MySQL銆丱racle銆PostgreSQL銆丼QLite 鍙 SQL Server 涔嬮棿浼犺緭鏁版嵁銆傚畠鏀寔澶ч儴浠 MySQL銆丱racle銆丳ostgreSQL銆丼QLite 鍙 SQL Server 鐨勫姛鑳姐俷avicat濡備綍杩炴帴mysql锛1銆侀鍏堢數鑴戜笂蹇呴』瀹夎浜唌ysql鐨勬暟鎹簱銆傦紙濡傛灉涓嶆竻妤氳嚜宸辨槸鍚﹀凡缁忓畨瑁呮垚鍔...
绛旓細灏咷itLab鐨勬暟鎹簱瀵煎叆闃块噷浜PostgreSQL RDS GitLab鐨勬暟鎹簱鐢ㄧ殑鏄疨ostgreSQL锛屼箣鍓嶇敱浜庨樋閲屼簯RDS涓嶆敮鎸丳ostgreSQL锛屽彧鑳藉皢GitLab鐨勬暟鎹簱閮ㄧ讲鍦ㄤ簯鏈嶅姟鍣ㄤ笂銆6鏈1鏃ュ緱鐭ラ樋閲屼簯鎺ㄥ嚭浜哖ostgreSQL RDS锛屼簬鏄珛椹皢GitLab鐨勬暟鎹簱杩佺Щ鑷砅ostgreSQL RDS銆備笅闈㈠垎浜竴涓嬭縼绉荤殑涓昏鎿嶄綔姝ラ锛1锛夊湪GitLab鏈嶅姟鍣ㄤ笂澶囦唤Git...
