pgsql备份.docVIP

pgsql备份 鍜屼换浣曞寘鍚弽璐垫暟鎹殑涓滆タ涓€鏍凤紝PostgreSQL 鏁版嵁搴撲篃搴旇缁忓父澶囦唤銆傚敖绠¤繖涓繃绋嬬浉褰撶畝鍗曪紝 浣嗘槸鎴戜滑杩樻槸搴旇鐞嗚В鍋氳繖浠朵簨鎵€鐢ㄧ殑涓€浜涙妧宸у拰鍋囪銆? 澶囦唤 PostgreSQL 鏁版嵁鏈変笁绉嶅畬鍏ㄤ笉鍚岀殑鏂规硶锛? SQL 杞偍 鏂囦欢绯荤粺绾у埆澶囦唤 鍦ㄧ嚎澶囦唤 姣忕澶囦唤閮芥湁鑷繁鐨勪紭鐐瑰拰缂虹偣銆? SQL 杞偍 SQL 杞偍鐨勬柟娉曢噰鐢ㄧ殑涓绘剰鏄垱寤轰竴涓枃鏈枃浠讹紝杩欎釜鏂囨湰閲岄潰閮芥槸 SQL 鍛戒护锛屽綋鎶婅繖涓枃浠跺洖棣堢粰鏈嶅姟鍣ㄦ椂锛屽皢閲嶅缓涓庤浆鍌ㄦ椂鐘舵€佷竴鏍风殑鏁版嵁搴撱€?PostgreSQL 涓鸿繖涓敤閫旀彁渚涗簡搴旂敤宸ュ叿 pg_dump銆傝繖鏉″懡浠ょ殑鍩烘湰鐢ㄦ硶鏄細 pg_dump dbname outfile 姝ｅ浣犳墍瑙侊紝pg_dump 鎶婄粨鏋滆緭鍑哄埌鏍囧噯杈撳嚭銆?鎴戜滑涓嬮潰灏卞彲浠ョ湅鍒拌繖鏍峰仛鏈変粈涔堝ソ澶勩€? pg_dump 鏄竴涓櫘閫氱殑 PostgreSQL 瀹㈡埛绔簲鐢紙灏界鏄釜鐩稿綋鑱槑鐨勪笢瑗裤€傦級杩欏氨鎰忓懗鐫€浣犲彲浠ヤ粠浠讳綍鍙互璁块棶璇ユ暟鎹簱鐨勮繙绔富鏈轰笂闈㈣繘琛屽浠藉伐浣溿€?浣嗘槸璇疯浣?pg_dump 涓嶄細浠ヤ换浣曠壒娈婃潈闄愯繍琛屻€傚叿浣撹鏉ワ紝 灏辨槸瀹冨繀椤昏鏈変綘鎯冲浠界殑琛ㄧ殑璇绘潈闄愶紝鍥犳锛屽疄闄呬笂浣犲嚑涔庢€绘槸瑕佹垚涓烘暟鎹簱瓒呯骇鐢ㄦ埛銆? 瑕佸０鏄?pg_dump 搴旇浠ュ摢涓敤鎴疯韩浠借繘琛岃仈鎺ワ紝浣跨敤鍛戒护琛岄€夐」 -h host 鍜?-p port銆傜己鐪佷富鏈烘槸鏈湴涓绘満鎴栦綘鐨勭幆澧冨彉閲廝GHOST澹版槑鐨勫€笺€?绫讳技锛岀己鐪佺鍙ｆ槸鐜鍙橀噺PGPORT鎴栵紙濡傛灉瀹冧笉瀛樺湪鐨勮瘽锛夌紪璇戝ソ浜嗙殑缂虹渷鍊笺€傦紙鏈嶅姟鍣ㄩ€氬父鏈夌浉鍚岀殑缂虹渷锛屾墍浠ヨ繕绠楁柟渚裤€傦級 鍜屼换浣曞叾浠?PostgreSQL 瀹㈡埛绔簲鐢ㄤ竴鏍凤紝 pg_dump 缂虹渷鏃剁敤涓庡綋鍓嶆搷浣滅郴缁熺敤鎴峰悕鍚屽悕鐨勬暟鎹簱鐢ㄦ埛鍚嶈繘琛岃仈鎺ャ€?瑕佽鐩栬繖涓悕瀛楋紝瑕佷箞澹版槑 -U 閫夐」锛?瑕佷箞璁剧疆鐜鍙橀噺PGUSER銆?璇锋敞鎰?pg_dump 鐨勮仈鎺ヤ篃鍜屾櫘閫氬鎴峰簲鐢ㄤ竴鏍疯閫氳繃瀹㈡埛璁よ瘉鏈哄埗銆? 鐢?pg_dump 鍒涘缓鐨勫浠藉湪鍐呴儴鏄竴鑷寸殑锛?涔熷氨鏄锛屽湪pg_dump杩愯鐨勬椂鍊欏鏁版嵁搴撶殑鏇存柊灏嗕笉浼氳杞偍銆?pg_dump 宸ヤ綔鐨勬椂鍊欏苟涓嶉樆濉炲叾浠栫殑瀵规暟鎹簱鐨勬搷浣溿€?锛堜絾鏄細闃诲閭ｄ簺闇€瑕佹帓瀹冮攣鐨勬搷浣滐紝姣斿 VACUUM FULL銆傦級 Important: 濡傛灉浣犵殑鏁版嵁搴撶粨鏋勪緷璧栦簬 OID 锛堟瘮濡傝鐢ㄥ仛澶栭敭锛夛紝閭ｄ箞浣犲繀椤诲憡璇?pg_dump 鎶?OID 涔熷€掑嚭鏉ャ€?瑕佸€?OID锛屽彲浠ヤ娇鐢?-o 鍛戒护琛岄€夐」銆?缂虹渷鏃朵篃涓嶄細杞偍澶у璞?銆傚鏋滀綘浣跨敤澶у璞★紝璇峰弬鑰?pg_dump 鐨勫懡浠ゆ墜鍐岄〉銆? 浠庤浆鍌ㄤ腑鎭㈠ pg_dump 鐢熸垚鐨勬枃鏈枃浠跺彲浠ョ敱 psql 绋嬪簭璇诲彇銆?浠庤浆鍌ㄤ腑鎭㈠鐨勫父鐢ㄥ懡浠ゆ槸 psql dbname infile 杩欓噷鐨?infile 灏辨槸浣犵粰pg_dump鍛戒护鐨?outfile鍙傛暟銆傝繖鏉″懡浠や笉浼氬垱寤烘暟鎹簱 dbname锛屼綘蹇呴』鍦ㄦ墽琛宲sql 鍓嶈嚜宸变粠template0鍒涘缓锛堜篃灏辨槸璇达紝鐢ㄥ懡浠?createdb -T template0 dbname锛夈€?psql 鏀寔绫讳技 pg_dump 鐨勯€夐」鐢ㄤ互鎺у埗鏁版嵁搴撴湇鍔″櫒浣嶇疆鍜岀敤鎴峰悕銆傚弬闃?psql 鐨勬墜鍐岃幏鍙栨洿澶氫俊鎭€? 鍦ㄥ紑濮嬭繍琛屾仮澶嶄箣鍓嶏紝鐩爣搴撳拰鎵€鏈夊湪杞偍鍑烘潵鐨勫簱涓嫢鏈夊璞＄殑鐢ㄦ埛锛?浠ュ強鏇剧粡鍦ㄦ煇浜涘璞′笂琚祴浜堟潈闄愮殑鐢ㄦ埛閮藉繀椤诲凡缁忓瓨鍦ㄣ€傚鏋滆繖浜涗笉瀛樺湪锛岄偅涔堟仮澶嶅皢澶辫触锛屽洜涓烘仮澶嶈繃绋嬫棤娉曟妸杩欎簺瀵硅薄鎭㈠鎴愬師鏈夌殑鎵€鏈夋潈鍜?鎴栨潈闄愩€?锛堟湁鏃跺€欎綘甯屾湜鎭㈠鏉冮檺锛屼笉杩囬€氬父浣犱笉闇€瑕佽繖涔堝仛銆傦級 涓€鏃﹀畬鎴愭仮澶嶏紝鍦ㄦ瘡涓暟鎹簱涓婅繍琛?ANALYZE 鏄槑鏅虹殑涓惧姩锛?杩欐牱浼樺寲鍣ㄥ氨鏈夋