{"id":217,"date":"2017-05-22T14:56:24","date_gmt":"2017-05-22T06:56:24","guid":{"rendered":"https:\/\/blog.jsjs.org\/?p=217"},"modified":"2017-05-22T14:56:24","modified_gmt":"2017-05-22T06:56:24","slug":"postgresql%e6%95%b0%e6%8d%ae%e5%ba%93%e5%88%9b%e5%bb%ba%e3%80%81%e5%88%a0%e9%99%a4%e3%80%81%e8%a7%92%e8%89%b2%e6%9d%83%e9%99%90","status":"publish","type":"post","link":"https:\/\/blog.jsjs.org\/?p=217","title":{"rendered":"PostgreSQL\u6570\u636e\u5e93\u521b\u5efa\u3001\u5220\u9664\u3001\u89d2\u8272\u6743\u9650"},"content":{"rendered":"<p>\u6700\u8fd1\u4e00\u76f4\u5728\u4f7f\u7528Postgresql\uff0c\u547d\u4ee4\u603b\u662f\u8bb0\u4e0d\u4f4f\uff0c\u5c31\u7d22\u6027\u6574\u7406\u4e0b\u6765\uff0c\u65b9\u4fbf\u81ea\u5df1\uff0c\u4e5f\u7ed9\u5927\u5bb6\u5206\u4eab\u4e0b\u3002<\/p>\n<h1><a name=\"t0\"><\/a>Postgresql\u5e38\u89c1\u64cd\u4f5c\u6307\u4ee4\uff1a<\/h1>\n<p>\u8fde\u63a5<a class=\"replace_word\" title=\"MySQL\u77e5\u8bc6\u5e93\" href=\"http:\/\/lib.csdn.net\/base\/mysql\" target=\"_blank\" rel=\"noopener noreferrer\">\u6570\u636e\u5e93<\/a>, \u9ed8\u8ba4\u7684\u7528\u6237\u548c\u6570\u636e\u5e93\u662fpostgres<br \/>\npsql -U user -d dbname<\/p>\n<p>\u5207\u6362\u6570\u636e\u5e93,\u76f8\u5f53\u4e8e<a class=\"replace_word\" title=\"MySQL\u77e5\u8bc6\u5e93\" href=\"http:\/\/lib.csdn.net\/base\/mysql\" target=\"_blank\" rel=\"noopener noreferrer\">MySQL<\/a>\u7684use dbname<br \/>\n\\c dbname<br \/>\n\u5217\u4e3e\u6570\u636e\u5e93\uff0c\u76f8\u5f53\u4e8emysql\u7684show databases<br \/>\n\\l<br \/>\n\u5217\u4e3e\u8868\uff0c\u76f8\u5f53\u4e8emysql\u7684show tables<br \/>\n\\dt<br \/>\n\u67e5\u770b\u8868\u7ed3\u6784\uff0c\u76f8\u5f53\u4e8edesc tblname,show columns from tbname<br \/>\n\\d tblname<\/p>\n<p>\u521b\u5efa\u6570\u636e\u5e93\uff1a<br \/>\ncreate database [\u6570\u636e\u5e93\u540d];<\/p>\n<p>\u521b\u5efa\u6570\u636e\u5e93\u6307\u5b9a\u7528\u6237\uff1a<\/p>\n<p>create database [\u6570\u636e\u5e93\u540d] owner [\u7528\u6237\u540d];;<\/p>\n<p>\u5220\u9664\u6570\u636e\u5e93\uff1a<br \/>\ndrop database [\u6570\u636e\u5e93\u540d];<br \/>\n*\u91cd\u547d\u540d\u4e00\u4e2a\u8868\uff1a<br \/>\nalter table [\u8868\u540dA] rename to [\u8868\u540dB];<br \/>\n*\u5220\u9664\u4e00\u4e2a\u8868\uff1a<br \/>\ndrop table [\u8868\u540d];<\/p>\n<p>*\u5728\u5df2\u6709\u7684\u8868\u91cc\u6dfb\u52a0\u5b57\u6bb5\uff1a<br \/>\nalter table [\u8868\u540d] add column [\u5b57\u6bb5\u540d] [\u7c7b\u578b];<br \/>\n*\u5220\u9664\u8868\u4e2d\u7684\u5b57\u6bb5\uff1a<br \/>\nalter table [\u8868\u540d] drop column [\u5b57\u6bb5\u540d];<br \/>\n*\u91cd\u547d\u540d\u4e00\u4e2a\u5b57\u6bb5\uff1a<br \/>\nalter table [\u8868\u540d] rename column [\u5b57\u6bb5\u540dA] to [\u5b57\u6bb5\u540dB];<br \/>\n*\u7ed9\u4e00\u4e2a\u5b57\u6bb5\u8bbe\u7f6e\u7f3a\u7701\u503c\uff1a<br \/>\nalter table [\u8868\u540d] alter column [\u5b57\u6bb5\u540d] set default [\u65b0\u7684\u9ed8\u8ba4\u503c];<br \/>\n*\u53bb\u9664\u7f3a\u7701\u503c\uff1a<br \/>\nalter table [\u8868\u540d] alter column [\u5b57\u6bb5\u540d] drop default;<br \/>\n\u5728\u8868\u4e2d\u63d2\u5165\u6570\u636e\uff1a<br \/>\ninsert into \u8868\u540d ([\u5b57\u6bb5\u540dm],[\u5b57\u6bb5\u540dn],&#8230;&#8230;) values ([\u5217m\u7684\u503c],[\u5217n\u7684\u503c],&#8230;&#8230;);<br \/>\n\u4fee\u6539\u8868\u4e2d\u7684\u67d0\u884c\u67d0\u5217\u7684\u6570\u636e\uff1a<br \/>\nupdate [\u8868\u540d] set [\u76ee\u6807\u5b57\u6bb5\u540d]=[\u76ee\u6807\u503c] where [\u8be5\u884c\u7279\u5f81];<br \/>\n\u5220\u9664\u8868\u4e2d\u67d0\u884c\u6570\u636e\uff1a<br \/>\ndelete from [\u8868\u540d] where [\u8be5\u884c\u7279\u5f81];<br \/>\ndelete from [\u8868\u540d];&#8211;\u5220\u7a7a\u6574\u4e2a\u8868<br \/>\n\u521b\u5efa\u8868\uff1a<br \/>\ncreate table ([\u5b57\u6bb5\u540d1] [\u7c7b\u578b1] ;,[\u5b57\u6bb5\u540d2] [\u7c7b\u578b2],&#8230;&#8230;&lt;,primary key (\u5b57\u6bb5\u540dm,\u5b57\u6bb5\u540dn,&#8230;)&gt;;);<\/p>\n<p>\\copyright\u00a0\u00a0\u00a0\u00a0 \u663e\u793a PostgreSQL \u7684\u4f7f\u7528\u548c\u53d1\u884c\u6761\u6b3e<br \/>\n\\encoding [\u5b57\u5143\u7f16\u7801\u540d\u79f0]<br \/>\n\u663e\u793a\u6216\u8bbe\u5b9a\u7528\u6237\u7aef\u5b57\u5143\u7f16\u7801<br \/>\n\\h [\u540d\u79f0]\u00a0\u00a0\u00a0\u00a0\u00a0 SQL \u547d\u4ee4\u8bed\u6cd5\u4e0a\u7684\u8bf4\u660e\uff0c\u7528 * \u663e\u793a\u5168\u90e8\u547d\u4ee4<br \/>\n\\prompt [\u6587\u672c] \u540d\u79f0<br \/>\n\u63d0\u793a\u7528\u6237\u8bbe\u5b9a\u5185\u90e8\u53d8\u6570<br \/>\n\\password [USERNAME]<br \/>\nsecurely change the password for a user<br \/>\n\\q\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u9000\u51fa psql<\/p>\n<p>\u53ef\u4ee5\u4f7f\u7528pg_dump\u548cpg_dumpall\u6765\u5b8c\u6210\u3002\u6bd4\u5982\u5907\u4efdsales\u6570\u636e\u5e93\uff1a<br \/>\npg_dump drupal&gt;\/opt\/Postgresql\/backup\/1.bak<\/p>\n<h1><a name=\"t1\"><\/a>\u7528\u6237\u3001\u89d2\u8272\u53ca\u6743\u9650\u95ee\u9898\uff1a<\/h1>\n<p>\u89d2\u8272<br \/>\nPostgreSQL\u4f7f\u7528<strong>\u89d2\u8272<\/strong>\u7684\u6982\u5ff5\u7ba1\u7406\u6570\u636e\u5e93\u8bbf\u95ee\u6743\u9650\u3002 \u6839\u636e\u89d2\u8272\u81ea\u8eab\u7684\u8bbe\u7f6e\u4e0d\u540c\uff0c\u4e00\u4e2a\u89d2\u8272\u53ef\u4ee5\u770b\u505a\u662f\u4e00\u4e2a\u6570\u636e\u5e93\u7528\u6237\uff0c\u6216\u8005\u4e00\u7ec4\u6570\u636e\u5e93\u7528\u6237\u3002 \u89d2\u8272\u53ef\u4ee5\u62e5\u6709\u6570\u636e\u5e93\u5bf9\u8c61(\u6bd4\u5982\u8868)\u4ee5\u53ca\u53ef\u4ee5\u628a\u8fd9\u4e9b\u5bf9\u8c61\u4e0a\u7684\u6743\u9650\u8d4b\u4e88\u5176\u5b83\u89d2\u8272\uff0c \u4ee5\u63a7\u5236\u8c01\u62e5\u6709\u8bbf\u95ee\u54ea\u4e9b\u5bf9\u8c61\u7684\u6743\u9650\u3002<br \/>\n\u64cd\u4f5c\u89d2\u8272\u7684\u8bed\u53e5\uff1a<code class=\"sql\"><span class=\"hljs-operator\"><span class=\"hljs-keyword\"><br \/>\n<\/span><\/span><\/code><\/p>\n<p><code class=\"sql\"><span class=\"hljs-operator\"><span class=\"hljs-keyword\">create<\/span> <span class=\"hljs-keyword\">role<\/span> db_role1;<\/span> <span class=\"hljs-comment\">\/*--\u521b\u5efa\u89d2\u8272*\/<\/span><\/code><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>\n<pre class=\"hljs sql\"><code class=\"sql\"><span class=\"hljs-operator\"><span class=\"hljs-keyword\">drop<\/span> <span class=\"hljs-keyword\">role<\/span> db_role1;<\/span>  <span class=\"hljs-comment\">\/*--\u5220\u9664\u89d2\u8272*\/<\/span>\n<span class=\"hljs-operator\">select rolename <span class=\"hljs-keyword\">from<\/span> pg_roles;<\/span> <span class=\"hljs-comment\">\/*--\u67e5\u770b\u6240\u6709\u89d2\u8272*\/<\/span>\n\/du  <span class=\"hljs-comment\">--\u5728\u547d\u4ee4\u683c\u5f0f\u4e0b\u67e5\u770b\u6240\u6709\u89d2\u8272\u7684\u547d\u4ee4<\/span><\/code><\/pre>\n<div>\u6587\uff0fbruce_wu\uff08\u7b80\u4e66\u4f5c\u8005\uff09<br \/>\n\u539f\u6587\u94fe\u63a5\uff1ahttp:\/\/www.jianshu.com\/p\/b09d0b29faa9<br \/>\n\u8457\u4f5c\u6743\u5f52\u4f5c\u8005\u6240\u6709\uff0c\u8f6c\u8f7d\u8bf7\u8054\u7cfb\u4f5c\u8005\u83b7\u5f97\u6388\u6743\uff0c\u5e76\u6807\u6ce8\u201c\u7b80\u4e66\u4f5c\u8005\u201d\u3002<\/p>\n<p>\u89d2\u8272\u7684\u6743\u9650<\/p><\/div>\n<\/li>\n<li>\u4e00\u4e2a\u6570\u636e\u5e93\u89d2\u8272\u53ef\u4ee5\u6709\u5f88\u591a\u6743\u9650\uff0c\u8fd9\u4e9b\u6743\u9650\u5b9a\u4e49\u4e86\u89d2\u8272\u548c\u62e5\u6709\u89d2\u8272\u7684\u7528\u6237\u53ef\u4ee5\u505a\u7684\u4e8b\u60c5\u3002\n<pre class=\"hljs sql\"><code class=\"sql\"><span class=\"hljs-operator\"><span class=\"hljs-keyword\">create<\/span> <span class=\"hljs-keyword\">role<\/span> db_role1 LOGIN;<\/span> <span class=\"hljs-comment\">--\u521b\u5efa\u5177\u6709\u767b\u5f55\u6743\u9650\u7684\u89d2\u8272db_role1<\/span>\n<span class=\"hljs-operator\"><span class=\"hljs-keyword\">create<\/span> <span class=\"hljs-keyword\">role<\/span> db_role2 SUPERUSER;<\/span> <span class=\"hljs-comment\">--\u521b\u5efa\u5177\u6709\u8d85\u7ea7\u7528\u6237\u6743\u9650\u7684\u89d2\u8272<\/span>\n<span class=\"hljs-operator\"><span class=\"hljs-keyword\">create<\/span> <span class=\"hljs-keyword\">role<\/span> db_role3 CREATEDB;<\/span> <span class=\"hljs-comment\">--\u521b\u5efa\u5177\u6709\u521b\u5efa\u6570\u636e\u5e93\u6743\u9650\u7684\u89d2\u8272<\/span>\n<span class=\"hljs-operator\"><span class=\"hljs-keyword\">create<\/span> <span class=\"hljs-keyword\">role<\/span> db_role4 CREATEROLE <span class=\"hljs-comment\">--\u521b\u5efa\u5177\u6709\u521b\u5efa\u89d2\u8272\u6743\u9650\u7684\u89d2\u8272<\/span>\n<span class=\"hljs-keyword\">alter<\/span> <span class=\"hljs-keyword\">role<\/span> db_role1 nologin nocreatedb;<\/span> <span class=\"hljs-comment\">--\u4fee\u6539\u89d2\u8272\u53d6\u6d88\u767b\u5f55\u548c\u521b\u5efa\u6570\u636e\u5e93\u6743\u9650<\/span><\/code><\/pre>\n<\/li>\n<li>\u7528\u6237<br \/>\n\u5176\u5b9e\u7528\u6237\u548c\u89d2\u8272\u90fd\u662f\u89d2\u8272\uff0c\u53ea\u662f\u7528\u6237\u662f\u5177\u6709\u767b\u5f55\u6743\u9650\u7684\u89d2\u8272\u3002<\/p>\n<pre class=\"hljs sql\"><code class=\"sql\"><span class=\"hljs-operator\"><span class=\"hljs-keyword\">create<\/span> <span class=\"hljs-keyword\">user<\/span> db_user1 <span class=\"hljs-keyword\">password<\/span> <span class=\"hljs-string\">'123'<\/span>;<\/span> <span class=\"hljs-comment\">--\u521b\u5efa\u7528\u6237<\/span>\n<span class=\"hljs-operator\"><span class=\"hljs-keyword\">create<\/span> <span class=\"hljs-keyword\">role<\/span> db_user1 <span class=\"hljs-keyword\">password<\/span> <span class=\"hljs-string\">'123'<\/span> LOGIN;<\/span>  <span class=\"hljs-comment\">--\u540c\u4e0a\u4e00\u53e5\u7b49\u4ef7<\/span>\n<span class=\"hljs-operator\"><span class=\"hljs-keyword\">drop<\/span> <span class=\"hljs-keyword\">user<\/span> db_user1;<\/span>   <span class=\"hljs-comment\">--\u5220\u9664\u7528\u6237<\/span>\n<span class=\"hljs-operator\"><span class=\"hljs-keyword\">alter<\/span> <span class=\"hljs-keyword\">user<\/span> db_user1 <span class=\"hljs-keyword\">password<\/span> <span class=\"hljs-string\">'123456'<\/span>;<\/span> <span class=\"hljs-comment\">--\u4fee\u6539\u5bc6\u7801<\/span>\n<span class=\"hljs-operator\"><span class=\"hljs-keyword\">alter<\/span> <span class=\"hljs-keyword\">user<\/span> db_user1 createdb createrole;<\/span> <span class=\"hljs-comment\">--\u5bf9\u7528\u6237\u6388\u6743<\/span><\/code><\/pre>\n<\/li>\n<li>\u8d4b\u4e88\u89d2\u8272\u63a7\u5236\u6743\u9650<br \/>\n\u6211\u4eec\u53ef\u4ee5\u4f7f\u7528GRANT \u548cREVOKE\u547d\u4ee4\u8d4b\u4e88\u7528\u6237\u89d2\u8272\uff0c\u6765\u63a7\u5236\u6743\u9650\u3002<\/p>\n<pre class=\"hljs sql\"><code class=\"sql\"><span class=\"hljs-operator\"><span class=\"hljs-keyword\">create<\/span> <span class=\"hljs-keyword\">user<\/span> db_user1;<\/span> <span class=\"hljs-comment\">--\u521b\u5efa\u7528\u62371<\/span>\n<span class=\"hljs-operator\"><span class=\"hljs-keyword\">create<\/span> <span class=\"hljs-keyword\">user<\/span> db_user2;<\/span> <span class=\"hljs-comment\">--\u521b\u5efa\u7528\u62372<\/span>\n<span class=\"hljs-operator\"><span class=\"hljs-keyword\">create<\/span> <span class=\"hljs-keyword\">role<\/span> db_role1 createdb createrole;<\/span> <span class=\"hljs-comment\">--\u521b\u5efa\u89d2\u82721<\/span>\n<span class=\"hljs-operator\"><span class=\"hljs-keyword\">grant<\/span> db_role1 <span class=\"hljs-keyword\">to<\/span> db_user1,db_user2;<\/span> <span class=\"hljs-comment\">--\u7ed9\u7528\u62371,2\u8d4b\u4e88\u89d2\u82721,\u4e24\u4e2a\u7528\u6237\u5c31\u62e5\u6709\u4e86\u521b\u5efa\u6570\u636e\u5e93\u548c\u521b\u5efa\u89d2\u8272\u7684\u6743\u9650<\/span>\n<span class=\"hljs-operator\"><span class=\"hljs-keyword\">revoke<\/span> db_role1 <span class=\"hljs-keyword\">from<\/span> db_user1;<\/span> <span class=\"hljs-comment\">--\u4ece\u7528\u62371\u79fb\u9664\u89d2\u82721\uff0c\u7528\u6237\u4e0d\u5728\u62e5\u6709\u89d2\u82721\u7684\u6743\u9650<\/span><\/code><\/pre>\n<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u6700\u8fd1\u4e00\u76f4\u5728\u4f7f\u7528Postgresql\uff0c\u547d\u4ee4\u603b\u662f\u8bb0\u4e0d\u4f4f\uff0c\u5c31\u7d22\u6027\u6574\u7406\u4e0b\u6765\uff0c\u65b9\u4fbf\u81ea\u5df1\uff0c\u4e5f\u7ed9\u5927\u5bb6\u5206\u4eab\u4e0b\u3002 Postgresql\u5e38\u89c1\u64cd\u4f5c\u6307\u4ee4\uff1a \u8fde\u63a5\u6570\u636e\u5e93, \u9ed8\u8ba4\u7684\u7528\u6237\u548c\u6570\u636e\u5e93\u662fpostgres psql -U user -d dbname \u5207\u6362\u6570\u636e\u5e93,\u76f8\u5f53\u4e8eMySQL\u7684use dbname \\c dbname \u5217\u4e3e\u6570\u636e\u5e93\uff0c\u76f8\u5f53\u4e8emysql\u7684show databases \\l \u5217\u4e3e\u8868\uff0c\u76f8\u5f53\u4e8emysql\u7684show tables \\dt \u67e5\u770b\u8868\u7ed3\u6784\uff0c\u76f8\u5f53\u4e8edesc [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[1],"tags":[],"class_list":["post-217","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=\/wp\/v2\/posts\/217","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=217"}],"version-history":[{"count":0,"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=\/wp\/v2\/posts\/217\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=217"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=217"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.jsjs.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=217"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}