【PostgreSQL】數據庫部署

[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)

參數解析
listen_address：服務器監聽客戶端連接的TPC/IP地址

port：服務器監聽的TPC端口

max_connections：允許和數據庫連接的最大并發連接數

superuser_reserved_connections：為超級用戶連接而保留的連接數

tcp_keepalives_idle：在一個TCP連接中空閑多長時間后會發送一個keepalive報文（建議值180）

tcp_keepalives_interval：在一個空閑TCP連接中，在發送第一個keepalive報文后如果在該參數指定的時間間隔內沒有收到對
端的響應報文，則開始發送第二個keepalive報文（建議值10）

tcp_keepalives_count：在一個空閑TCP連接中，在發送keepalive報文后，如果一直沒有收到對端的響應報文，最多發送該參數
指定次數報文后，認為TCP連接已中斷（建議值3）

shared_buffers：數據庫實例可使用的共享內存區域

temp_buffers：每個數據庫會話使用的臨時內存區，只用于訪問臨時表，在服務進程中分配的，屬于本地內存

work_mem：排序與散列操作在使用臨時磁盤文件之前可使用的內存區域，屬于本地內存

maintenance_work_mem：在維護性操作中可使用的內存區域

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

fsync：決定是否使用fsync系統調用，將文件系統中的臟頁寫到物理磁盤

synchronous_commit：提交一個事務是否需要等待將WAL日志寫入磁盤后在返回

wal_sync_method：指定向磁盤寫WAL日志的方法

full_page_writes：數據庫實例會在檢查點之后對頁面第一次修改時將整個頁面寫入WAL日志

wal_buffers：WAL日志使用的內存區域，屬于數據庫實例共享內存

log_destination：stderr,csvlog,syslog

log_directory：日志輸出的路徑

log_filename：日志文件名

log_rotation_age：日志超過多長時間，就生成一個新的文件

log_rotation_size：日志超過多大，就生成一個新的文件

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

[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?執行外部文件中的SQL命令

\set?AUTOCOMMIT?off?關閉自動提交

\set?ECHO_HIDDEN?on?顯示某一個命令實際執行的SQL