【PostgreSQL】數(shù)據(jù)庫(kù)部署

[root@wallet01?~]#?wget?https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-centos96-9.6-3.noarch.rpm?--no-check-certificate
[root@wallet01?~]#?rpm?-ivh?pgdg-centos96-9.6-3.noarch.rpm

[root@wallet01?~]#?useradd?postgres
[root@wallet01?~]#?id?postgres
uid=501(postgres)?gid=501(postgres)?groups=501(postgres)

[root@wallet01?~]#?rpm?-e?postgresql?postgresql-devel

[root@wallet01?~]#?yum?install?-y?postgresql96-server.x86_64?postgresql96-contrib.x86_64
[root@wallet01?~]#?service?postgresql-9.6?initdb
Initializing?database:?????????????????????????????????????[??OK??]

[root@wallet01?~]#?vi?/var/lib/pgsql/9.6/data/postgresql.conf
listen_addresses?=?'0.0.0.0'
port?=?5432?

[root@wallet01?~]#?service?postgresql-9.6?start
Starting?postgresql-9.6?service:???????????????????????????[??OK??]

[root@wallet01?~]#?service?postgresql-9.6?status
postgresql-9.6?(pid??2146)?is?running...

[root@wallet01?~]#?netstat?-tunlp?|?grep?postmaster
tcp????????0??????0?0.0.0.0:5432????????0.0.0.0:*????????LISTEN??????2414/postmaster

[root@wallet01?~]#?vi?/etc/profile
PATH=$PATH:/usr/pgsql-9.6/bin

[root@wallet01?~]#?su?-?postgres
[postgres@wallet01?~]$?psql
psql?(9.6.12)
Type?"help"?for?help.
postgres=#?\l
??????????????????????????????????List?of?databases
???Name????|??Owner???|?Encoding?|???Collate???|????Ctype????|???Access?privileges???
-----------+----------+----------+-------------+-------------+-----------------------
?postgres??|?postgres?|?UTF8?????|?en_US.UTF-8?|?en_US.UTF-8?|?
?template0?|?postgres?|?UTF8?????|?en_US.UTF-8?|?en_US.UTF-8?|?=c/postgres??????????+
???????????|??????????|??????????|?????????????|?????????????|?postgres=CTc/postgres
?template1?|?postgres?|?UTF8?????|?en_US.UTF-8?|?en_US.UTF-8?|?=c/postgres??????????+
???????????|??????????|??????????|?????????????|?????????????|?postgres=CTc/postgres
(3?rows)

參數(shù)解析
listen_address：服務(wù)器監(jiān)聽(tīng)客戶(hù)端連接的TPC/IP地址

port：服務(wù)器監(jiān)聽(tīng)的TPC端口

max_connections：允許和數(shù)據(jù)庫(kù)連接的最大并發(fā)連接數(shù)

superuser_reserved_connections：為超級(jí)用戶(hù)連接而保留的連接數(shù)

tcp_keepalives_idle：在一個(gè)TCP連接中空閑多長(zhǎng)時(shí)間后會(huì)發(fā)送一個(gè)keepalive報(bào)文（建議值180）

tcp_keepalives_interval：在一個(gè)空閑TCP連接中，在發(fā)送第一個(gè)keepalive報(bào)文后如果在該參數(shù)指定的時(shí)間間隔內(nèi)沒(méi)有收到對(duì)
端的響應(yīng)報(bào)文，則開(kāi)始發(fā)送第二個(gè)keepalive報(bào)文（建議值10）

tcp_keepalives_count：在一個(gè)空閑TCP連接中，在發(fā)送keepalive報(bào)文后，如果一直沒(méi)有收到對(duì)端的響應(yīng)報(bào)文，最多發(fā)送該參數(shù)
指定次數(shù)報(bào)文后，認(rèn)為T(mén)CP連接已中斷（建議值3）

shared_buffers：數(shù)據(jù)庫(kù)實(shí)例可使用的共享內(nèi)存區(qū)域

temp_buffers：每個(gè)數(shù)據(jù)庫(kù)會(huì)話(huà)使用的臨時(shí)內(nèi)存區(qū)，只用于訪(fǎng)問(wèn)臨時(shí)表，在服務(wù)進(jìn)程中分配的，屬于本地內(nèi)存

work_mem：排序與散列操作在使用臨時(shí)磁盤(pán)文件之前可使用的內(nèi)存區(qū)域，屬于本地內(nèi)存

maintenance_work_mem：在維護(hù)性操作中可使用的內(nèi)存區(qū)域

wal_level：決定有多少信息可寫(xiě)入WAL日志中

fsync：決定是否使用fsync系統(tǒng)調(diào)用，將文件系統(tǒng)中的臟頁(yè)寫(xiě)到物理磁盤(pán)

synchronous_commit：提交一個(gè)事務(wù)是否需要等待將WAL日志寫(xiě)入磁盤(pán)后在返回

wal_sync_method：指定向磁盤(pán)寫(xiě)WAL日志的方法

full_page_writes：數(shù)據(jù)庫(kù)實(shí)例會(huì)在檢查點(diǎn)之后對(duì)頁(yè)面第一次修改時(shí)將整個(gè)頁(yè)面寫(xiě)入WAL日志

wal_buffers：WAL日志使用的內(nèi)存區(qū)域，屬于數(shù)據(jù)庫(kù)實(shí)例共享內(nèi)存

log_destination：stderr,csvlog,syslog

log_directory：日志輸出的路徑

log_filename：日志文件名

log_rotation_age：日志超過(guò)多長(zhǎng)時(shí)間，就生成一個(gè)新的文件

log_rotation_size：日志超過(guò)多大，就生成一個(gè)新的文件

log_truncate_on_rotation：當(dāng)生成的新文件的文件名已存在，是否覆蓋同名舊文件

[postgres@king01?~]$?psql?-h?192.168.1.201?-p?5432?tpcc?tpcc????????????
Password?for?user?tpcc:?
psql?(9.6.12)
Type?"help"?for?help.
tpcc=>?\l
??????????????????????????????????List?of?databases
???Name????|??Owner???|?Encoding?|???Collate???|????Ctype????|???Access?privileges???
-----------+----------+----------+-------------+-------------+-----------------------
?postgres??|?postgres?|?UTF8?????|?en_US.UTF-8?|?en_US.UTF-8?|?
?template0?|?postgres?|?UTF8?????|?en_US.UTF-8?|?en_US.UTF-8?|?=c/postgres??????????+
???????????|??????????|??????????|?????????????|?????????????|?postgres=CTc/postgres
?template1?|?postgres?|?UTF8?????|?en_US.UTF-8?|?en_US.UTF-8?|?=c/postgres??????????+
???????????|??????????|??????????|?????????????|?????????????|?postgres=CTc/postgres
?tpcc??????|?tpcc?????|?UTF8?????|?en_US.UTF-8?|?en_US.UTF-8?|?
(4?rows)

tpcc=>?\dt
??????????List?of?relations
?Schema?|????Name????|?Type??|?Owner?
--------+------------+-------+-------
?public?|?customer???|?table?|?tpcc
?public?|?district???|?table?|?tpcc
?public?|?history????|?table?|?tpcc
?public?|?item???????|?table?|?tpcc
?public?|?new_order??|?table?|?tpcc
?public?|?order_line?|?table?|?tpcc
?public?|?orders?????|?table?|?tpcc
?public?|?stock??????|?table?|?tpcc
?public?|?warehouse??|?table?|?tpcc
(9?rows)

tpcc=>?\di
??????????????????List?of?relations
?Schema?|?????Name??????|?Type??|?Owner?|???Table????
--------+---------------+-------+-------+------------
?public?|?customer_i1???|?index?|?tpcc??|?customer
?public?|?customer_i2???|?index?|?tpcc??|?customer
?public?|?district_i1???|?index?|?tpcc??|?district
?public?|?item_i1???????|?index?|?tpcc??|?item
?public?|?new_order_i1??|?index?|?tpcc??|?new_order
?public?|?order_line_i1?|?index?|?tpcc??|?order_line
?public?|?orders_i1?????|?index?|?tpcc??|?orders
?public?|?orders_i2?????|?index?|?tpcc??|?orders
?public?|?stock_i1??????|?index?|?tpcc??|?stock
?public?|?warehouse_i1??|?index?|?tpcc??|?warehouse
(10?rows)

tpcc=>?\df
????????????????????????????????????????????????????????????????????????List?of?functions
?Schema?|????Name?????|?Result?data?type?|?????????????????????????????????????????????Argument?data?types????????????????????????????????????????
??????|??Type??
--------+-------------+------------------+--------------------------------------------------------------------------------------------------------
------+--------
?public?|?dbms_random?|?integer??????????|?integer,?integer???????????????????????????????????????????????????????????????????????????????????????
??????|?normal
?public?|?delivery????|?integer??????????|?integer,?integer???????????????????????????????????????????????????????????????????????????????????????
??????|?normal
?public?|?neword??????|?numeric??????????|?integer,?integer,?integer,?integer,?integer,?integer???????????????????????????????????????????????????
??????|?normal
?public?|?ostat???????|?SETOF?record?????|?integer,?integer,?integer,?integer,?character?varying??????????????????????????????????????????????????
??????|?normal
?public?|?payment?????|?integer??????????|?integer,?integer,?integer,?integer,?numeric,?integer,?numeric,?character?varying,?character?varying,?nu
meric?|?normal
?public?|?slev????????|?integer??????????|?integer,?integer,?integer??????????????????????????????????????????????????????????????????????????????
??????|?normal
(6?rows)

tpcc=>?\dn
??List?of?schemas
??Name??|??Owner???
--------+----------
?public?|?postgres
(1?row)

tpcc=>?\db
???????List?of?tablespaces
????Name????|??Owner???|?Location?
------------+----------+----------
?pg_default?|?postgres?|?
?pg_global??|?postgres?|?
(2?rows)

tpcc=>?\du
???????????????????????????????????List?of?roles
?Role?name?|?????????????????????????Attributes?????????????????????????|?Member?of?
-----------+------------------------------------------------------------+-----------
?postgres??|?Superuser,?Create?role,?Create?DB,?Replication,?Bypass?RLS?|?{tpcc}
?tpcc??????|????????????????????????????????????????????????????????????|?{}
?
tpcc=>?\dp
???????????????????????????????Access?privileges
?Schema?|????Name????|?Type??|?Access?privileges?|?Column?privileges?|?Policies?
--------+------------+-------+-------------------+-------------------+----------
?public?|?customer???|?table?|???????????????????|???????????????????|?
?public?|?district???|?table?|???????????????????|???????????????????|?
?public?|?history????|?table?|???????????????????|???????????????????|?
?public?|?item???????|?table?|???????????????????|???????????????????|?
?public?|?new_order??|?table?|???????????????????|???????????????????|?
?public?|?order_line?|?table?|???????????????????|???????????????????|?
?public?|?orders?????|?table?|???????????????????|???????????????????|?
?public?|?stock??????|?table?|???????????????????|???????????????????|?
?public?|?warehouse??|?table?|???????????????????|???????????????????|?
(9?rows)

tpcc=>?\pset?border?2
Border?style?is?2.

tpcc=>?\timing?on
Timing?is?on.
tpcc=>?select?*?from?warehouse?where?w_zip?=?'763011111';
+------+------------+--------+-----------+------------------+----------------+-----------------+---------+-----------+
|?w_id?|???w_ytd????|?w_tax??|??w_name???|????w_street_1????|???w_street_2???|?????w_city??????|?w_state?|???w_zip???|
+------+------------+--------+-----------+------------------+----------------+-----------------+---------+-----------+
|????1?|?3000000.00?|?0.1100?|?IONcZx68P?|?ejAv76uu9u1W0Vso?|?hTm1kjwNGHt20A?|?fRpzxLGAhOMU7UY?|?9P??????|?763011111?|
+------+------------+--------+-----------+------------------+----------------+-----------------+---------+-----------+
(1?row)

Time:?3.507?ms

tpcc=>?\x
Expanded?display?is?on.
tpcc=>?select?*?from?warehouse?where?w_zip?=?'763011111';
+-[?RECORD?1?]------------------+
|?w_id???????|?1????????????????|
|?w_ytd??????|?3000000.00???????|
|?w_tax??????|?0.1100???????????|
|?w_name?????|?IONcZx68P????????|
|?w_street_1?|?ejAv76uu9u1W0Vso?|
|?w_street_2?|?hTm1kjwNGHt20A???|
|?w_city?????|?fRpzxLGAhOMU7UY??|
|?w_state????|?9P???????????????|
|?w_zip??????|?763011111????????|
+------------+------------------+

Time:?0.732?ms

\i?執(zhí)行外部文件中的SQL命令

\set?AUTOCOMMIT?off?關(guān)閉自動(dòng)提交

\set?ECHO_HIDDEN?on?顯示某一個(gè)命令實(shí)際執(zhí)行的SQL