1"use strict";(self.webpackChunkpjchender_notes=self.webpackChunkpjchender_notes||[]).push([[12227],{28453:(e,n,s)=>{s.d(n,{R:()=>d,x:()=>c});var a=s(96540);const l={},r=a.createContext(l);function d(e){const n=a.useContext(r);return a.useMemo((function(){return"function"==typeof e?e(n):{...n,...e}}),[n,e])}function c(e){let n;return n=e.disableParentContext?"function"==typeof e.components?e.components(l):e.components||l:d(e.components),a.createElement(r.Provider,{value:n},e.children)}},55239:(e,n,s)=>{s.r(n),s.d(n,{assets:()=>t,contentTitle:()=>c,default:()=>h,frontMatter:()=>d,metadata:()=>a,toc:()=>o});const a=JSON.parse('{"id":"database/psql-cli","title":"[PSQL] PostgreSQL CLI","description":"- postgreSQL @ official documentation","source":"@site/docs/database/[PSQL] PostgreSQL CLI.md","sourceDirName":"database","slug":"/database/psql-cli","permalink":"/database/psql-cli","draft":false,"unlisted":false,"editUrl":"https://github.com/pjchender/pjchender.github.io/tree/main/docs/database/[PSQL] PostgreSQL CLI.md","tags":[],"version":"current","lastUpdatedAt":1775885633000,"frontMatter":{"id":"psql-cli","title":"[PSQL] PostgreSQL CLI","sidebar_label":"[PSQL] PSQL CLI","keywords":["database","psql","cli"]},"sidebar":"docs","previous":{"title":"[PSQL] PSQL JOINS","permalink":"/database/psql-joins"},"next":{"title":"[PSQL] \u89d2\u8272\u6b0a\u9650","permalink":"/database/psql-roles-privilege"}}');var l=s(74848),r=s(28453);const d={id:"psql-cli",title:"[PSQL] PostgreSQL CLI",sidebar_label:"[PSQL] PSQL CLI",keywords:["database","psql","cli"]},c=void 0,t={},o=[{value:"TL;DR",id:"tldr",level:2},{value:"psql \u6307\u4ee4",id:"psql-\u6307\u4ee4",level:2},{value:"\u767b\u5165 psql",id:"\u767b\u5165-psql",level:3},{value:"\u53ef\u4ee5\u4f7f\u7528\u7684\u53c3\u6578",id:"\u53ef\u4ee5\u4f7f\u7528\u7684\u53c3\u6578",level:3},{value:"\u767b\u5165 psql \u4ecb\u9762\u5f8c",id:"\u767b\u5165-psql-\u4ecb\u9762\u5f8c",level:2}
1,{value:"SQL",id:"sql",level:3},{value:"\u5efa\u7acb\u65b0\u7684 Database",id:"\u5efa\u7acb\u65b0\u7684-database",level:4},{value:"Meta-Commands\uff08slash, backslash commands\uff09",id:"meta-commandsslash-backslash-commands",level:3},{value:"createdb, dropdb \u6307\u4ee4",id:"createdb-dropdb-\u6307\u4ee4",level:2},{value:"\u5176\u4ed6",id:"\u5176\u4ed6",level:2},{value:"Dump Database",id:"dump-database",level:3},{value:"\u7d42\u6b62\u5361\u4f4f\u7684 PSQL \u9023\u7dda",id:"\u7d42\u6b62\u5361\u4f4f\u7684-psql-\u9023\u7dda",level:3},{value:"\u932f\u8aa4\u8655\u7406",id:"\u932f\u8aa4\u8655\u7406",level:2},{value:"\u6aa2\u8996\u932f\u8aa4\u8a0a\u606f log",id:"\u6aa2\u8996\u932f\u8aa4\u8a0a\u606f-log",level:3},{value:"\u7d42\u6b62 process",id:"\u7d42\u6b62-process",level:3},{value:"\u5b89\u88dd Setup",id:"\u5b89\u88dd-setup",level:2},{value:"MAC: \u900f\u904e homebrew \u9032\u884c\u5b89\u88dd",id:"mac-\u900f\u904e-homebrew-\u9032\u884c\u5b89\u88dd",level:3},{value:"\u5efa\u7acb\u4f7f\u7528\u8005\u548c db",id:"\u5efa\u7acb\u4f7f\u7528\u8005\u548c-db",level:3},{value:"\u5efa\u7acb database cluster",id:"\u5efa\u7acb-database-cluster",level:3},{value:"\u5f9e terminal \u9032\u5165 db",id:"\u5f9e-terminal-\u9032\u5165-db",level:3},{value:"Ubuntu",id:"ubuntu",level:3},{value:"\u53c3\u8003",id:"\u53c3\u8003",level:2}];function i(e){const n={a:"a",blockquote:"blockquote",code:"code",h2:"h2",h3:"h3",h4:"h4",li:"li",p:"p",pre:"pre",strong:"strong",ul:"ul",...(0,r.R)(),...e.components};return(0,l.jsxs)(l.Fragment,{children:[(0,l.jsxs)(n.blockquote,{children:["\n",(0,l.jsxs)(n.ul,{children:["\n",(0,l.jsxs)(n.li,{children:[(0,l.jsx)(n.a,{href:"https://www.postgresql.org/docs/current/",children:"postgreSQL"})," @ official documentation"]}),"\n",(0,l.jsx)(n.li,{children:(0,l.jsx)(n.a,{href:"https://www.postgresqltutorial.com/",children:"PostgreSQL Tutorial"})}),"\n"]}),"\n"]}),"\n",(0,l.jsx)(n.h2,{id:"tldr",children:"TL;DR"}),"\n",(0,l.jsx)(n.p,{children:"\u5728 Terminal bash \u4e2d\uff1a"}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-sh",children:"psql -l # \u5217\u51fa\u6240\u6709 database\n\npsql -U aaronchen minicare_demo # \u4ee5 aaronchen \u7684 role \u767b\u5165 minicare_demo \u9019\u500b database\n\ncreateuser pjchender # createuser \u5efa\u7acb\u4f7f\u7528\u8005\ncreatedb <database-name> -O pjchender -E utf8 # createdb \u5efa\u7acb database\ndropdb aaronchen\n"})}),"\n",(0,l.jsx)(n.p,{children:"\u5728 PSQL IRB \u4e2d\uff1a"}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-bash",children:"# help\n\\? # \u986f\u793a\u548c psql IRB \u6709\u95dc\u7684\u6307\u4ee4\u8aaa\u660e\n\\h # \u986f\u793a\u548c sql \u6709\u95dc\u7684\u6307\u4ee4\u8aaa\u660e\n\n# \u8cc7\u6599\u5eab\u8207\u8cc7\u6599\u8868\u8cc7\u8a0a\n\\du # \u5217\u51fa\u6240\u6709 roles\n\\l # \u5217\u51fa\u6240\u6709 databases\n\\z # \u5217\u51fa\u6240\u6709 tables\n\\dt # \u5217\u51fa\u76ee\u524d\u8cc7\u6599\u5eab\u7684\u6240\u6709\u8cc7\u6599\u8868\n\n# \u9023\u7dda\u8cc7\u8a0a\n\\c [db_name] # \u9023\u7dda\u5230\u53e6\u4e00\u500b\u8cc7\u6599\u5eab\n\\conninfo # \u9023\u7dda\u8cc7\u8a0a\n\\password [username] # \u4fee\u6539\u67d0 username \u7684\u5bc6\u78bc\n"})}),"\n",(0,l.jsx)(n.h2,{id:"psql-\u6307\u4ee4",children:"psql \u6307\u4ee4"}),"\n",(0,l.jsxs)(n.blockquote,{children:["\n",(0,l.jsxs)(n.p,{children:[(0,l.jsx)(n.a,{href:"https://www.postgresql.org/docs/current/app-psql.html",children:"psql \u5b8c\u6574\u6307\u4ee4"})," @ postgresql documentation"]}),"\n"]}),"\n",(0,l.jsx)(n.h3,{id:"\u767b\u5165-psql",children:"\u767b\u5165 psql"}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-bash",children:"psql [dbname]\npsql -U [role] [dbname] # \u4ee5 postgres \u7684 role \u767b\u5165 postgres \u7684 database\n\npsql -U postgres # \u4f7f\u7528 postgres \u7684 role \u767b\u5165 \u7684 postgres \u9019\u500b database\npsql -U aaronchen minicare_demo # \u4ee5 aaronchen \u7684 role \u767b\u5165 minicare_demo \u9019\u500b database\npsql -U postgres -d minicare_demo # \u4ee5 postgres \u7684 role \u767b\u5165 minicare_demo \u9019\u500b database\n"})}),"\n",(0,l.jsx)(n.h3,{id:"\u53ef\u4ee5\u4f7f\u7528\u7684\u53c3\u6578",children:"\u53ef\u4ee5\u4f7f\u7528\u7684\u53c3\u6578"}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-bash",children:"# options for psql\n-l # --list\uff0c\u5217\u51fa\u6240\u6709 database\n\n# \u767b\u5165\u76f8\u95dc\n-d dbname # --dbname=dbname\n-h host # --host=host\n-p # --port=port\n-U username # --username=username\n-w # --no-password\n-W # --password\n\n# \u5176\u4ed6\n-V # --version\uff0c\u986f\u793a psql \u7684\u7248\u672c\n-a # --echo-all\n-e # --echo-queries\n\n# \u8a2d\u5b9a\u74b0\u5883\u8b8a\u6578\n-v assignment\n--set=assignment\n--variable=assignment\n\n# \u5e6b\u52a9\n-?\n--help[=topic]\n"})}),"\n",(0,l.jsx)(n.h2,{id:"\u767b\u5165-psql-\u4ecb\u9762\u5f8c",children:"\u767b\u5165 psql \u4ecb\u9762\u5f8c"}),"\n",(0,l.jsx)(n.h3,{id:"sql",children:"SQL"}),"\n",(0,l.jsxs)(n.p,{children:["\u767b\u5165 psql \u5f8c\u53ef\u4ee5\u76f4\u63a5\u64b0\u5beb SQL\uff0c\u53ef\u4ee5\u63db\u884c\u8f38\u5165 SQL\uff0c\u552f\u6709\u6536\u5230 ",(0,l.jsx)(n.code,{children:";"})," \u6642\u624d\u6703\u958b\u59cb\u57f7\u884c\u3002"]}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-bash",children:"psql [dbname]\nCREATE DATABASE [new_dbname] # \u5efa\u7acb minicare_demo \u9019\u500b database\n\\c [new_dbname]\n"})}),"\n",(0,l.jsx)(n.h4,{id:"\u5efa\u7acb\u65b0\u7684-database",children:"\u5efa\u7acb\u65b0\u7684 Database"}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-sql",children:"-- \u5efa\u7acb\u65b0\u7684 database\nCREATE DATABASE my_new_db;\n\n-- \u78ba\u8a8d\u5df2\u5efa\u7acb\uff08\u5217\u51fa\u6240\u6709 databases\uff09\n\\l\n\n-- \u96e2\u958b\n\\q\n"})}),"\n",(0,l.jsx)(n.h3,{id:"meta-commandsslash-backslash-commands",children:"Meta-Commands\uff08slash, backslash commands\uff09"}),"\n",(0,l.jsxs)(n.p,{children:["\u767b\u5165 psql \u5f8c\uff0c\u4efb\u4f55\u4ee5 ",(0,l.jsx)(n.code,{children:"\\"})," \u958b\u982d\u7684\u6307\u4ee4\u90fd\u88ab\u8996\u70ba\u662f ",(0,l.jsx)(n.strong,{children:"meta-command"}),"\uff0c\u9019\u4e9b\u6307\u4ee4\u5c07\u6703\u88ab psql \u672c\u8eab\u6240\u8655\u7406\u3002meta-commands \u7684\u5f62\u5f0f\u90fd\u662f\u5728 ",(0,l.jsx)(n.code,{children:"\\"})," \u5f8c\u63a5 ",(0,l.jsx)(n.strong,{children:"command verb"}),"\uff0c\u518d\u63a5\u53c3\u6578\uff08arguments\uff09\uff1a"]}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-bash",children:"# \u8cc7\u6599\u5eab\u76f8\u95dc\n$ \\c [ dbname [ username ] [ host ] [ port ] | conninfo ] # \\connect [dbname] \u8207\u8a72 database \u9023\u7dda\n$ \\encoding [ encoding ]\n$ \\du # \u5217\u51fa\u6240\u6709 roles\n$ \\l # \u5217\u51fa\u6240\u6709 database\n\n# \u8cc7\u6599\u8868\u76f8\u95dc\n$ \\z [pattern] # \u5217\u51fa\u6240\u6709 table\n\n# \u8a2d\u5b9a\u74b0\u5883\u8b8a\u6578\n$ \\set foo bar # \u5c07 foo \u8a2d\u70ba bar\n$ \\echo :foo # \u53d6\u5f97 foo \u7684\u8b8a\u6578\u503c\n\n# \u5176\u4ed6\n$ \\h create table # \u67e5\u8a62\u7528\u6cd5\n$ \\! pwd # \u53d6\u5f97\u7576\u524d\u8cc7\u6599\u593e\u540d\u7a31\n$ \\q # \\quit\uff0c\u96e2\u958b\n\n# \u5e33\u865f\u76f8\u95dc\n$ \\password [ username ]\n"})}),"\n",(0,l.jsx)(n.p,{children:"\u53d6\u5f97\u8b8a\u6578\u540d\u7a31\uff1a"}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-bash",children:"# \u8b8a\u6578\u540d\u7a31 DBNAME, ENCODING, HOST, PORT, USER\n$ \\echo :DBNAME\n"})}),"\n",(0,l.jsx)(n.h2,{id:"createdb-dropdb-\u6307\u4ee4",children:"createdb, dropdb \u6307\u4ee4"}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-bash",children:"# options for createdb\n-p port # --port=port\n-h host # --host=host\n-U username # --username=username\n-O owner # --owner=owner\n-w # --no-password\n-W # --password\n-e # --echo\n-E encoding # --encoding=encoding\n"})}),"\n",(0,l.jsx)(n.p,{children:"\u53ef\u4ee5\u76f4\u63a5\u5728 Shell \u4e2d\u4f7f\u7528\u6307\u4ee4\u65b0\u589e\u6216\u522a\u9664 database\uff1a"}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-bash",children:"# create <db_name> -O <db_owner> -E utf8\ncreatedb demo\ncreatedb minicare_test -O postgres -E utf8\n\ndropdb demo\n"})}),"\n",(0,l.jsx)(n.h2,{id:"\u5176\u4ed6",children:"\u5176\u4ed6"}),"\n",(0,l.jsx)(n.h3,{id:"dump-database",children:"Dump Database"}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-bash",children:"# \u5c07\u8cc7\u6599\u5eab\u7684\u5167\u5bb9 dump \u4e0b\u4f86\uff08\u5099\u4efd\uff09\npg_dump \\\n-U pjchender \\\n-h pjchender.us-west-2.rds.amazonaws.com \\\n-p 5432 pjchender-prod > pjchender-prod-dump\n\n# \u5c07 dump \u4e0b\u4f86\u7684\u6a94\u6848\u704c\u5165\u8cc7\u6599\u5eab\uff08\u9084\u539f\uff09\npsql test < pjchender-prod-dump\n"})}),"\n",(0,l.jsxs)(n.blockquote,{children:["\n",(0,l.jsx)(n.p,{children:(0,l.jsx)(n.a,{href:"https://axiomq.com/blog/backup-and-restore-a-postgresql-database/",children:"Backup and Restore a PostgreSQL Database"})}),"\n"]}),"\n",(0,l.jsx)(n.h3,{id:"\u7d42\u6b62\u5361\u4f4f\u7684-psql-\u9023\u7dda",children:"\u7d42\u6b62\u5361\u4f4f\u7684 PSQL \u9023\u7dda"}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-sql",children:"SELECT pg_terminate_backend(pid)\nFROM pg_stat_activity\nWHERE state = 'idle';\n"})}),"\n",(0,l.jsxs)(n.blockquote,{children:["\n",(0,l.jsx)(n.p,{children:(0,l.jsx)(n.a,{href:"https://ravenonhill.blogspot.com/2017/04/postgres-hanging-query.html",children:"\u505c\u6b62\u5361\u4f4f\u7684 Postgres Session \u7684 SQL \u6307\u4ee4"})}),"\n"]}),"\n",(0,l.jsx)(n.h2,{id:"\u932f\u8aa4\u8655\u7406",children:"\u932f\u8aa4\u8655\u7406"}),"\n",(0,l.jsx)(n.h3,{id:"\u6aa2\u8996\u932f\u8aa4\u8a0a\u606f-log",children:"\u6aa2\u8996\u932f\u8aa4\u8a0a\u606f log"}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-bash",children:"# Mac\n$ cat /u
1sr/local/var/log/postgres.log\n"})}),"\n",(0,l.jsx)(n.h3,{id:"\u7d42\u6b62-process",children:"\u7d42\u6b62 process"}),"\n",(0,l.jsx)(n.p,{children:"\u5982\u679c\u96fb\u8166\u64cd\u4f5c\u5230\u4e00\u534a\u7576\u6a5f\uff0c\u53ef\u80fd\u6703\u5c0e\u81f4 PID \u5361\u4f4f\uff0c\u53ef\u4ee5\u5230\u8a72\u8cc7\u6599\u593e\u628a\u6a94\u6848\u522a\u9664\u5373\u53ef\uff1a"}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-sh",children:"cd /usr/local/var/postgres\nrm postmaster.pid\n"})}),"\n",(0,l.jsx)(n.h2,{id:"\u5b89\u88dd-setup",children:"\u5b89\u88dd Setup"}),"\n",(0,l.jsx)(n.h3,{id:"mac-\u900f\u904e-homebrew-\u9032\u884c\u5b89\u88dd",children:"MAC: \u900f\u904e homebrew \u9032\u884c\u5b89\u88dd"}),"\n",(0,l.jsx)(n.p,{children:"\u8a18\u5f97\u5148\u5b89\u88dd homebrew \u548c homebred-services\uff1a"}),"\n",(0,l.jsxs)(n.blockquote,{children:["\n",(0,l.jsxs)(n.p,{children:[(0,l.jsx)(n.a,{href:"https://wiki.postgresql.org/wiki/Homebrew",children:"postgreSQL"})," @ Homebrew"]}),"\n"]}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-bash",children:"brew install postgresql\nbrew services start postgresql\npsql postgres\n"})}),"\n",(0,l.jsx)(n.h3,{id:"\u5efa\u7acb\u4f7f\u7528\u8005\u548c-db",children:"\u5efa\u7acb\u4f7f\u7528\u8005\u548c db"}),"\n",(0,l.jsxs)(n.blockquote,{children:["\n",(0,l.jsxs)(n.p,{children:[(0,l.jsx)(n.a,{href:"https://www.postgresql.org/docs/current/tutorial-createdb.html",children:"Creating a Database"})," @ psql"]}),"\n"]}),"\n",(0,l.jsxs)(n.p,{children:["\u76f4\u63a5\u5728 Terminal \u4f7f\u7528\u95dc\u9375\u5b57 ",(0,l.jsx)(n.code,{children:"createuser"})," \u5efa\u7acb\u4f7f\u7528\u8005\uff1a"]}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-sh",children:"# createuser -- define a new PostgreSQL user account\n$ createuser pjchender\n"})}),"\n",(0,l.jsxs)(n.p,{children:["\u4f7f\u7528\u95dc\u9375\u5b57 ",(0,l.jsx)(n.code,{children:"createdb"})," \u5efa\u7acb database"]}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-sh",children:"###\n# createdb -- create a new PostgreSQL database\n# -O: \u6307\u5b9a db \u7684\u4f7f\u7528\u8005\n# -E: \u6307\u5b9a\u4ee3\u78bc\n###\n\n$ createdb <database-name> -O pjchender -E utf8\n$ dropdb <database-name>\n"})}),"\n",(0,l.jsx)(n.h3,{id:"\u5efa\u7acb-database-cluster",children:"\u5efa\u7acb database cluster"}),"\n",(0,l.jsxs)(n.p,{children:["\u900f\u904e\u95dc\u9375\u5b57 ",(0,l.jsx)(n.code,{children:"initdb"})," \u53ef\u4ee5\u5efa\u7acb\u65b0\u7684 PostgreSQL database cluster\u3002"]}),"\n",(0,l.jsx)(n.p,{children:"database cluster \u662f databases \u7684\u96c6\u5408\u3002"}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-sh",children:"initdb /usr/local/var/postgres -E utf8\n"})}),"\n",(0,l.jsx)(n.p,{children:"\u63a5\u8457\u5c31\u53ef\u4ee5\u900f\u904e Postico \u9023\u63a5\u5230 PostgreSQL\u3002"}),"\n",(0,l.jsx)(n.h3,{id:"\u5f9e-terminal-\u9032\u5165-db",children:"\u5f9e terminal \u9032\u5165 db"}),"\n",(0,l.jsxs)(n.p,{children:["\u900f\u904e ",(0,l.jsx)(n.code,{children:"psql"})," \u6307\u4ee4\u53ef\u4ee5\u9032\u5165 db \u4e2d\u6e2c\u8a66 sql \u6307\u4ee4\uff08\u6703\u51fa\u73fe ",(0,l.jsx)(n.code,{children:"=>"}),"\uff09\uff0c\u900f\u904e ",(0,l.jsx)(n.code,{children:"\\q"})," \u53ef\u4ee5\u96e2\u958b"]}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-sh",children:"$ psql <database_name>\n\n\u2026=>...\n\n\\q\n"})}),"\n",(0,l.jsx)(n.h3,{id:"ubuntu",children:"Ubuntu"}),"\n",(0,l.jsx)(n.pre,{children:(0,l.jsx)(n.code,{className:"language-bash",children:"# \u5b89\u88dd psql cli\n$ sudo apt-get install postgresql-client\n"})}),"\n",(0,l.jsx)(n.h2,{id:"\u53c3\u8003",children:"\u53c3\u8003"}),"\n",(0,l.jsxs)(n.ul,{children:["\n",(0,l.jsx)(n.li,{children:(0,l.jsx)(n.a,{href:"http://postgresguide.com/",children:"Postgres Guide"})}),"\n",(0,l.jsxs)(n.li,{children:[(0,l.jsx)(n.a,{href:"http://postgresguide.com/utilities/backup-restore.html",children:"\u5099\u4efd\u8207\u9084\u539f\u8cc7\u6599\u5eab"})," @ Postgres Guide"]}),"\n"]})]})}function h(e={}){const{wrapper:n}={...(0,r.R)(),...e.components};return n?(0,l.jsx)(n,{...e,children:(0,l.jsx)(i,{...e})}):i(e)}}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.