{"id":2462,"date":"2026-02-27T15:08:27","date_gmt":"2026-02-27T07:08:27","guid":{"rendered":"https:\/\/www.madbull.site\/?p=2462"},"modified":"2026-03-03T10:43:49","modified_gmt":"2026-03-03T02:43:49","slug":"pgsql%e5%9f%ba%e6%9c%ac%e6%93%8d%e4%bd%9c","status":"publish","type":"post","link":"https:\/\/www.madbull.site\/?p=2462","title":{"rendered":"PostgreSQL\u57fa\u7840\u64cd\u4f5c"},"content":{"rendered":"\n

\u4e00\u3001PostgreSQL \u57fa\u7840\u8bed\u6cd5\u901f\u89c8<\/h2>\n\n\n\n

1. \u8fde\u63a5\u6570\u636e\u5e93<\/h3>\n\n\n\n
# \u5207\u6362\u5230 postgres \u7528\u6237\u5e76\u8fdb\u5165 psql<\/em>\nsudo -u postgres psql\n\n# \u6216\u6307\u5b9a\u6570\u636e\u5e93<\/em>\npsql -U username -d dbname -h localhost -p 5432<\/code><\/pre>\n\n\n\n
2. \u6570\u636e\u5e93\u4e0e\u7528\u6237\u7ba1\u7406<\/h3>\n\n\n\n
-- \u521b\u5efa\u7528\u6237\uff08\u89d2\u8272\uff09<\/em>\nCREATE USER myuser WITH PASSWORD 'mypass';\n\n-- \u521b\u5efa\u6570\u636e\u5e93\uff0c\u5e76\u6307\u5b9a\u6240\u6709\u8005<\/em>\nCREATE DATABASE mydb OWNER myuser;\n\n-- \u5220\u9664\u6570\u636e\u5e93\uff08\u9700\u5148\u65ad\u5f00\u8fde\u63a5\uff09<\/em>\nDROP DATABASE mydb;\n\n-- \u5220\u9664\u7528\u6237\uff08\u9700\u5148\u6e05\u7406\u5176\u62e5\u6709\u7684\u5bf9\u8c61\uff09<\/em>\nDROP USER myuser;<\/code><\/pre>\n\n\n\n
3. \u8868\u64cd\u4f5c<\/h3>\n\n\n\n
-- \u521b\u5efa\u8868<\/em>\nCREATE TABLE users (\n    id SERIAL PRIMARY KEY,\n    name VARCHAR(100) NOT NULL,\n    email TEXT UNIQUE,\n    created_at TIMESTAMP DEFAULT NOW()\n);\n\n-- \u67e5\u770b\u8868\u7ed3\u6784<\/em>\n\\d users\n\n-- \u63d2\u5165\u6570\u636e<\/em>\nINSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');\n\n-- \u67e5\u8be2\u6570\u636e<\/em>\nSELECT * FROM users WHERE name = 'Alice';\n\n-- \u66f4\u65b0\u6570\u636e<\/em>\nUPDATE users SET email = 'new@example.com' WHERE id = 1;\n\n-- \u5220\u9664\u6570\u636e<\/em>\nDELETE FROM users WHERE id = 1;<\/code><\/pre>\n\n\n\n
4. \u5e38\u7528\u5143\u547d\u4ee4\uff08psql \u5185\u90e8\u547d\u4ee4\uff09<\/h3>\n\n\n\n
\u547d\u4ee4<\/th>\u4f5c\u7528<\/th><\/tr><\/thead>
\\l<\/code><\/td>\u5217\u51fa\u6240\u6709\u6570\u636e\u5e93<\/td><\/tr>
\\c dbname<\/code><\/td>\u5207\u6362\u6570\u636e\u5e93<\/td><\/tr>
\\dt<\/code><\/td>\u5217\u51fa\u5f53\u524d\u6570\u636e\u5e93\u6240\u6709\u8868<\/td><\/tr>
\\d tablename<\/code><\/td>\u67e5\u770b\u8868\u7ed3\u6784<\/td><\/tr>
\\du<\/code><\/td>\u5217\u51fa\u6240\u6709\u7528\u6237\/\u89d2\u8272<\/td><\/tr>
\\q<\/code><\/td>\u9000\u51fa psql<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
\u4e8c\u3001PostgreSQL \u6570\u636e\u5bfc\u51fa\u5de5\u5177\uff1apg_dump<\/code><\/h2>\n\n\n\n
pg_dump<\/code> \u662f PG \u5b98\u65b9\u63d0\u4f9b\u7684\u903b\u8f91\u5907\u4efd\u5de5\u5177<\/strong>\uff0c\u53ef\u5bfc\u51fa\u7ed3\u6784\u3001\u6570\u636e\u6216\u4e24\u8005\uff0c\u652f\u6301\u591a\u79cd\u683c\u5f0f\u3002<\/p>\n\n\n\n
\u2705 \u5bfc\u51fa\u8868\u7ed3\u6784\uff08\u4e0d\u542b\u6570\u636e\uff09<\/h3>\n\n\n\n
pg_dump -U postgres -d mydb --schema-only > schema.sql<\/code><\/pre>\n\n\n\n
\u2705 \u5bfc\u51fa\u5355\u8868\u6570\u636e\uff08\u4e0d\u542b\u7ed3\u6784\uff09<\/h3>\n\n\n\n
pg_dump -U postgres -d mydb -t users --data-only > users_data.sql<\/code><\/pre>\n\n\n\n
\u2705 \u5bfc\u51fa\u6574\u4e2a\u6570\u636e\u5e93\uff08\u7ed3\u6784+\u6570\u636e\uff09<\/h3>\n\n\n\n
# \u6587\u672c\u683c\u5f0f\uff08\u9ed8\u8ba4\uff0c\u4eba\u7c7b\u53ef\u8bfb\uff09<\/em>\npg_dump -U postgres -d mydb > full_backup.sql\n\n# \u81ea\u5b9a\u4e49\u4e8c\u8fdb\u5236\u683c\u5f0f\uff08\u63a8\u8350\u7528\u4e8e\u5927\u5e93\uff0c\u652f\u6301\u5e76\u884c\u6062\u590d\uff09<\/em>\npg_dump -U postgres -d mydb -Fc > full_backup.dump<\/code><\/pre>\n\n\n\n
\ud83d\udd27 \u5e38\u7528\u9009\u9879<\/h3>\n\n\n\n
\u9009\u9879<\/th>\u8bf4\u660e<\/th><\/tr><\/thead>
-t table<\/code><\/td>\u6307\u5b9a\u8868<\/td><\/tr>
-n schema<\/code><\/td>\u6307\u5b9a schema<\/td><\/tr>
--clean<\/code><\/td>\u5728\u5efa\u8868\u524d\u52a0 DROP<\/code> \u8bed\u53e5<\/td><\/tr>
--no-owner<\/code><\/td>\u4e0d\u8f93\u51fa owner \u4fe1\u606f\uff08\u4fbf\u4e8e\u8fc1\u79fb\uff09<\/td><\/tr>
-Z 9<\/code><\/td>\u538b\u7f29\uff08\u4ec5 -Fc<\/code> \u683c\u5f0f\u652f\u6301\uff09<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n
\u4e09\u3001PostgreSQL \u6570\u636e\u5bfc\u5165\u5de5\u5177\uff1apsql<\/code> \u4e0e pg_restore<\/code><\/h2>\n\n\n\n
\u2705 \u5bfc\u5165 SQL \u6587\u4ef6\uff08\u6587\u672c\u683c\u5f0f\uff09<\/h3>\n\n\n\n
# \u786e\u4fdd\u76ee\u6807\u6570\u636e\u5e93\u5df2\u5b58\u5728<\/em>\ncreatedb -U postgres mydb_new\n\n# \u5bfc\u5165<\/em>\npsql -U postgres -d mydb_new -f backup.sql<\/code><\/pre>\n\n\n\n
\n
\u26a0\ufe0f \u5982\u679c SQL \u6587\u4ef6\u5305\u542b CREATE DATABASE<\/code>\uff0c\u9700\u5148\u624b\u52a8\u521b\u5efa\u5e93\uff0c\u56e0\u4e3a psql<\/code> \u4e0d\u80fd\u8de8\u5e93\u6267\u884c\u3002<\/p>\n<\/blockquote>\n\n\n\n
\u2705 \u5bfc\u5165\u81ea\u5b9a\u4e49\u683c\u5f0f\uff08.dump\uff09<\/h3>\n\n\n\n
# \u4f7f\u7528 pg_restore\uff08\u652f\u6301\u9009\u62e9\u6027\u6062\u590d\uff09<\/em>\npg_restore -U postgres -d mydb_new full_backup.dump\n\n# \u5e76\u884c\u6062\u590d\uff08\u52a0\u901f\u5927\u5e93\u5bfc\u5165\uff09<\/em>\npg_restore -j 4 -U postgres -d mydb_new full_backup.dump<\/code><\/pre>\n\n\n\n
\ud83d\udd12 \u5bfc\u5165\u65f6\u7684\u5b89\u5168\u5efa\u8bae<\/h3>\n\n\n\n
# \u9047\u9519\u505c\u6b62\uff0c\u907f\u514d\u90e8\u5206\u5bfc\u5165<\/em>\npsql -v ON_ERROR_STOP=1 -U postgres -d mydb -f backup.sql<\/code><\/pre>\n\n\n\n
\n\n\n\n
\u56db\u3001\u9ad8\u7ea7\u573a\u666f\uff1a\u5168\u5b9e\u4f8b\u5907\u4efd\u4e0e\u6062\u590d<\/h2>\n\n\n\n
\u5907\u4efd\u6240\u6709\u6570\u636e\u5e93 + \u7528\u6237\u6743\u9650<\/h3>\n\n\n\n
# \u5907\u4efd\u5168\u5c40\u5bf9\u8c61\uff08\u7528\u6237\u3001\u89d2\u8272\u7b49\uff09<\/em>\npg_dumpall -U postgres --globals-only > globals.sql\n\n# \u5907\u4efd\u6240\u6709\u6570\u636e\u5e93<\/em>\npg_dumpall -U postgres > all_databases.sql<\/code><\/pre>\n\n\n\n
\u6062\u590d\u6574\u4e2a\u5b9e\u4f8b<\/h3>\n\n\n\n
psql -U postgres -f globals.sql    # \u5148\u6062\u590d\u7528\u6237<\/em>\npsql -U postgres -f all_databases.sql<\/code><\/pre>\n\n\n\n
\n
\u2705 \u8fd9\u662f\u8fc1\u79fb\u6574\u4e2a PostgreSQL \u670d\u52a1\u5668\u7684\u6807\u51c6\u505a\u6cd5\u3002<\/p>\n<\/blockquote>\n\n\n\n
\n\n\n\n
\u4e94\u3001\u5e38\u89c1\u95ee\u9898\u4e0e\u6700\u4f73\u5b9e\u8df5<\/h2>\n\n\n\n
\u2753 Q1\uff1a\u5bfc\u51fa\u65f6\u63d0\u793a\u201cpeer authentication failed\u201d\uff1f<\/h3>\n\n\n\n
\u89e3\u51b3<\/strong>\uff1a\u4f7f\u7528 sudo -u postgres<\/code> \u6267\u884c\uff0c\u6216\u4fee\u6539 \/etc\/postgresql\/*\/main\/pg_hba.conf<\/code> \u5c06 local<\/code> \u8ba4\u8bc1\u65b9\u5f0f\u6539\u4e3a trust<\/code> \u6216 md5<\/code>\u3002<\/p>\n\n\n\n
\u2753 Q2\uff1a\u5982\u4f55\u5bfc\u51fa CSV\uff1f<\/h3>\n\n\n\n
-- \u5728 psql \u4e2d\u6267\u884c<\/em>\nCOPY (SELECT * FROM users) TO '\/tmp\/users.csv' WITH CSV HEADER;<\/code><\/pre>\n\n\n\n
\n
\u6ce8\u610f\uff1a\u8def\u5f84\u5fc5\u987b\u662f\u6570\u636e\u5e93\u670d\u52a1\u5668\u672c\u5730\u8def\u5f84\uff0c\u4e14 postgres \u7528\u6237\u6709\u5199\u6743\u9650\u3002<\/p>\n<\/blockquote>\n\n\n\n
\u2705 \u6700\u4f73\u5b9e\u8df5<\/h3>\n\n\n\n
    \n
  1. \u751f\u4ea7\u73af\u5883\u5907\u4efd<\/strong>\uff1a\u4f7f\u7528 pg_dump -Fc<\/code> + \u5b9a\u671f pg_dumpall --globals-only<\/code>\uff1b<\/li>\n\n\n\n
  2. \u8fc1\u79fb\u6570\u636e\u5e93<\/strong>\uff1a\u5bfc\u51fa\u65f6\u52a0 --no-owner --no-privileges<\/code>\uff0c\u907f\u514d\u6743\u9650\u51b2\u7a81\uff1b<\/li>\n\n\n\n
  3. \u5927\u8868\u5bfc\u51fa<\/strong>\uff1a\u4f18\u5148\u7528 COPY<\/code> \u683c\u5f0f\uff08\u9ed8\u8ba4\uff09\uff0c\u800c\u975e --inserts<\/code>\uff1b<\/li>\n<\/ol>\n\n\n\n
    \n\n\n\n
    \u516d\u3001\u5de5\u5177\u5bf9\u6bd4\u603b\u7ed3<\/h2>\n\n\n\n
    \u5de5\u5177<\/th>\u7528\u9014<\/th>\u8f93\u51fa\u683c\u5f0f<\/th>\u9002\u7528\u573a\u666f<\/th><\/tr><\/thead>
    pg_dump<\/code><\/td>\u5355\u5e93\/\u5355\u8868\u5907\u4efd<\/td>SQL \/ custom \/ tar<\/td>\u65e5\u5e38\u5907\u4efd\u3001\u8fc1\u79fb<\/td><\/tr>
    pg_dumpall<\/code><\/td>\u5168\u5b9e\u4f8b\u5907\u4efd<\/td>SQL<\/td>\u670d\u52a1\u5668\u8fc1\u79fb<\/td><\/tr>
    psql<\/code><\/td>\u6267\u884c SQL \u811a\u672c<\/td>\u2014<\/td>\u5bfc\u5165 .sql<\/code> \u6587\u4ef6<\/td><\/tr>
    pg_restore<\/code><\/td>\u6062\u590d\u81ea\u5b9a\u4e49\u683c\u5f0f<\/td>\u2014<\/td>\u5feb\u901f\u6062\u590d\u5927\u5e93<\/td><\/tr>
    COPY<\/code><\/td>\u8868\u7ea7\u6570\u636e\u5bfc\u51fa<\/td>CSV \/ binary<\/td>ETL\u3001\u6570\u636e\u5206\u6790<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    <\/p>\n","protected":false},"excerpt":{"rendered":"
    \u4e00\u3001PostgreSQL \u57fa\u7840\u8bed\u6cd5\u901f\u89c8 1. \u8fde\u63a5\u6570\u636e\u5e93 2. \u6570\u636e\u5e93\u4e0e\u7528\u6237\u7ba1\u7406 3. \u8868\u64cd\u4f5c 4. \u5e38\u7528\u5143\u547d […]<\/p>\n","protected":false},"author":1,"featured_media":386,"comment_status":"open","ping_status":"open","sticky":false,"template":"wp-custom-template-my","format":"standard","meta":{"footnotes":""},"categories":[5,131],"tags":[718,717],"class_list":["post-2462","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","category-131","tag-pgsql","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/www.madbull.site\/index.php?rest_route=\/wp\/v2\/posts\/2462","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.madbull.site\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.madbull.site\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.madbull.site\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.madbull.site\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2462"}],"version-history":[{"count":3,"href":"https:\/\/www.madbull.site\/index.php?rest_route=\/wp\/v2\/posts\/2462\/revisions"}],"predecessor-version":[{"id":2468,"href":"https:\/\/www.madbull.site\/index.php?rest_route=\/wp\/v2\/posts\/2462\/revisions\/2468"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.madbull.site\/index.php?rest_route=\/wp\/v2\/media\/386"}],"wp:attachment":[{"href":"https:\/\/www.madbull.site\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2462"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.madbull.site\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2462"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.madbull.site\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2462"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}