{"id":19606,"date":"2017-06-22T06:23:48","date_gmt":"2017-06-21T22:23:48","guid":{"rendered":"https:\/\/2blog.ilc.edu.tw\/25793\/2017\/06\/22\/postgresql-server-%e5%9f%ba%e6%9c%ac%e6%93%8d%e4%bd%9c\/"},"modified":"2017-06-22T06:23:48","modified_gmt":"2017-06-21T22:23:48","slug":"postgresql-server-%e5%9f%ba%e6%9c%ac%e6%93%8d%e4%bd%9c","status":"publish","type":"post","link":"https:\/\/2blog.ilc.edu.tw\/25793\/2017\/06\/22\/postgresql-server-%e5%9f%ba%e6%9c%ac%e6%93%8d%e4%bd%9c\/","title":{"rendered":"PostgreSQL Server \u57fa\u672c\u64cd\u4f5c"},"content":{"rendered":"

1. \u5efa\u7acb\u4f7f\u7528\u8005 testuser \u53ca\u5bc6\u78bc
# su – postgres<\/span>
$ \/bin\/createuser -P testuser<\/span>
\u8f38\u5165\u65b0\u8eab\u4efd\u7684\u5bc6\u78bc:<\/span>
\u518d\u8f38\u5165\u4e00\u6b21:<\/span><\/p>\n

2. \u522a\u9664\u4f7f\u7528\u8005
$ \/bin\/dropuser testuser<\/span>[@more@]<\/p>\n

3. \u5efa\u7acb\u8cc7\u6599\u5eab testdb\uff0c\u4e26\u6307\u5b9a\u7ba1\u7406\u8005\u00a0 testuser
$ \/bin\/createdb -O testuser testdb<\/span><\/p>\n

4. \u9032\u884c\u9023\u7dda
$ psql -U testuser testdb<\/span>
\u7528\u6236 testuser \u7684\u5bc6\u78bc:<\/span>
psql (9.2.18)<\/span>
\u8f38\u5165 “help” \u986f\u793a\u8aaa\u660e\u3002<\/span><\/p>\n

testdb=>help<\/span>
\u60a8\u6b63\u5728\u4f7f\u7528 PostgreSQL \u6307\u4ee4\u5217\u4ecb\u9762 psql\u3002<\/span>
\u8f38\u5165: copyright \u986f\u793a\u767c\u884c\u689d\u6b3e<\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 h \u986f\u793a SQL \u6307\u4ee4\u7684\u8aaa\u660e<\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ? \u986f\u793a psql \u6307\u4ee4\u7684\u8aaa\u660e<\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 g \u6216\u8005\u4ee5\u5206\u865f (;) \u7d50\u5c3e\u4ee5\u57f7\u884c\u67e5\u8a62<\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 q \u7d50\u675f<\/span><\/p>\n

5. \u5efa\u7acb\u8cc7\u6599\u8868
testdb=> create table users (<\/span>
testdb(> name varchar(30),<\/span>
testdb(> age integer,<\/span>
testdb(> mobiletel varchar(10),<\/span>
testdb(> address varchar(80));<\/span>
CREATE TABLE<\/span><\/p>\n

6. \u63d2\u5165\u8cc7\u6599
testdb=> insert into users values (‘\u9673\u5c0f\u83ef’,31,’0910001002′,’\u982d\u57ce\u93ae\u958b\u862d\u8def123\u865f’);<\/span>
INSERT 0 1<\/span>
testdb=> insert into users values (‘\u5433\u5c0f\u525b’,38,’0910003004′,’\u982d\u57ce\u93ae\u958b\u862d\u8def456\u865f’);<\/span>
INSERT 0 1<\/span><\/p>\n

7. \u700f\u89bd\u8cc7\u6599
testdb=> select * from users;<\/span>
\u00a0 name\u00a0 | age | mobiletel\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 address<\/span>
——–+—–+————+——————-<\/span>
\u00a0\u9673\u5c0f\u83ef |\u00a0 31 | 0910001002 | \u982d\u57ce\u93ae\u958b\u862d\u8def123\u865f<\/span>
\u00a0\u5433\u5c0f\u525b |\u00a0 38 | 0910003004 | \u982d\u57ce\u93ae\u958b\u862d\u8def456\u865f<\/span>
(2 \u7b46\u8cc7\u6599\u5217)<\/span><\/p>\n

testdb=> select * from users where age=31;<\/span>
\u00a0 name\u00a0 | age | mobiletel\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 address<\/span>
——–+—–+————+——————-<\/span>
\u00a0\u9673\u5c0f\u83ef |\u00a0 31 | 0910001002 | \u982d\u57ce\u93ae\u958b\u862d\u8def123\u865f<\/span><\/p>\n

testdb=> select * from users where name=’\u5433\u5c0f\u525b’;<\/span>
\u00a0 name\u00a0 | age | mobiletel\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 address<\/span>
——–+—–+————+——————-<\/span>
\u00a0\u5433\u5c0f\u525b |\u00a0 38 | 0910003004 | \u982d\u57ce\u93ae\u958b\u862d\u8def456\u865f<\/span><\/p>\n

testdb=> update users set mobiletel=’0910005006′ where name=’\u5433\u5c0f\u525b’;<\/span>
UPDATE 1<\/span>
testdb=> select * from users where name=’\u5433\u5c0f\u525b’;<\/span>
\u00a0 name\u00a0 | age | mobiletel\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 address<\/span>
——–+—–+————+——————-<\/span>
\u00a0\u5433\u5c0f\u525b |\u00a0 38 | 0910005006 | \u982d\u57ce\u93ae\u958b\u862d\u8def456\u865f<\/span><\/p>\n

testdb=> delete from users where name=’\u5433\u5c0f\u525b’;<\/span>
DELETE 1<\/span>
testdb=> select * from users;<\/span>
\u00a0 name\u00a0 | age | mobiletel\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 address<\/span>
——–+—–+————+——————-<\/span>
\u00a0\u9673\u5c0f\u83ef |\u00a0 31 | 0910001002 | \u982d\u57ce\u93ae\u958b\u862d\u8def123\u865f<\/span><\/p>\n

8. \u522a\u9664\u8cc7\u6599\u8868
testdb=> drop table users;<\/span>
DROP TABLE<\/span><\/p>\n

9.
testdb=> l<\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u8cc7\u6599\u5eab\u6e05\u55ae<\/span>
\u00a0\u00a0 \u540d\u7a31\u00a0\u00a0\u00a0 |\u00a0 \u64c1\u6709\u8005\u00a0 | \u5b57\u5143\u7de8\u78bc |\u00a0\u00a0 Collate\u00a0\u00a0 |\u00a0 \u8f49\u63db\u578b\u5225\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u5b58\u53d6\u6b0a\u9650<\/span>
———–+———-+———-+————-+————-+———————–<\/span>
\u00a0postgres\u00a0 | postgres | UTF8\u00a0\u00a0\u00a0\u00a0 | zh_TW.UTF-8 | zh_TW.UTF-8 |<\/span>
\u00a0template0 | postgres | UTF8\u00a0\u00a0\u00a0\u00a0 | zh_TW.UTF-8 | zh_TW.UTF-8 | =c\/postgres\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +<\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | postgres=CTc\/postgres<\/span>
\u00a0template1 | postgres | UTF8\u00a0\u00a0\u00a0\u00a0 | zh_TW.UTF-8 | zh_TW.UTF-8 | =c\/postgres\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 +<\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | postgres=CTc\/postgres<\/span>
\u00a0testdb\u00a0\u00a0\u00a0 | testuser | UTF8\u00a0\u00a0\u00a0\u00a0 | zh_TW.UTF-8 | zh_TW.UTF-8 |<\/span>
(4 \u7b46\u8cc7\u6599\u5217)<\/span><\/p>\n

testdb=> du<\/span>
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u89d2\u8272\u6e05\u55ae<\/span>
\u00a0\u89d2\u8272\u540d\u7a31 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u5c6c\u6027\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | \u6210\u54e1\u5c6c\u65bc<\/span>
———-+———————————–+———-<\/span>
\u00a0postgres | \u8d85\u7d1a\u7528\u6236, \u5efa\u7acb\u89d2\u8272, \u5efa\u7acb DB, \u8907\u88fd | {}<\/span>
\u00a0testuser |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | {}<\/span><\/p>\n

\u547d\u4ee4\u5217\u64cd\u4f5c
# sudo -u postgres psql -c “CREATE USER testuser WITH PASSWORD ‘\u5bc6\u78bc’;”<\/span>
# sudo -u postgres psql -c “CREATE DATABASE testdb;”<\/span>
# sudo -u postgres psql -c “GRANT ALL PRIVILEGES ON DATABASE testdb to testuser;”<\/span><\/p>\n

# sudo -u postgres psql -c “ALTER USER testuser WITH SUPERUSER;”<\/span>
# sudo -u postgres psql -c “ALTER ROLE testuser NOSUPERUSER;”<\/span>
# sudo -u postgres psql -c “ALTER ROLE testuser NOCREATEDB;”<\/span><\/p>\n

$ \/bin\/pg_dump -f users_dump.sql -U testuser -W testdb<\/span>
$ \/bin\/psql -U testuser testdb -f users_dump.sql<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

1. \u5efa\u7acb\u4f7f\u7528\u8005 testuser \u53ca\u5bc6\u78bc# su – postgres$ \/bin\/create … <\/p>\n

\u95b1\u8b80\u5168\u6587\u3008PostgreSQL Server \u57fa\u672c\u64cd\u4f5c\u3009<\/span><\/a><\/p>\n","protected":false},"author":55,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[150],"tags":[],"class_list":["post-19606","post","type-post","status-publish","format-standard","hentry","category-c-78533"],"meta_key":{"_pingme":["1"],"_encloseme":["1"],"lt_post_id":["689380"]},"_links":{"self":[{"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/posts\/19606"}],"collection":[{"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/users\/55"}],"replies":[{"embeddable":true,"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/comments?post=19606"}],"version-history":[{"count":0,"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/posts\/19606\/revisions"}],"wp:attachment":[{"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/media?parent=19606"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/categories?post=19606"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/2blog.ilc.edu.tw\/25793\/wp-json\/wp\/v2\/tags?post=19606"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}