pgsql已經更新到beta11了不同版本的伺服器啟動或相關指令、配置可能會有不同是以得根據pg版本進行操作。下面記錄一些工作中常用到的一些操作主要包括服務啟動、備份/恢複資料、資料目錄遷移、常見操作指令
本文環境
postgres : v10.3
os: MAC
雖然已經在
kong部署 中介紹了postgres的部署為了行文連貫性這裡再簡單記錄下pg的啟動相關指令。服務啟動
安裝
brew install postgresql
which psql
~$ /usr/local/bin/psql 執行安裝指令後會連帶安裝很多有用的pg指令如pg_ctl , postgres, psql等等
啟動pg服務
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start 這裡指定了啟動的目錄因為pg預設安裝在/usr/local/var/postgres目錄下也可以直接實用pg_ctl start來啟動但是必須設定環境變量PGDATA=/usr/local/var/postgres否則會報錯
$ pg_ctl start
pg_ctl: no database directory specified and environment variable PGDATA unset
Try "pg_ctl --help" for more information. 設定開機自動啟動
ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist 常用指令
檢視版本
pg_ctl -V 檢視資料庫狀态、操作
#啟動資料庫
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
#日志路徑
cat /usr/local/var/postgres/server.log
#檢視資料庫狀态
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log status
#停止
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log stop -s -m fast
#檢視資料庫程序
ps -ef |grep postgres 或 ps auxwww | grep postgres 建立資料庫使用者
createuser test_user -P 建立資料庫/密碼
createdb db_name -O db_pwd -E UTF8 -e 遠端進入到psql指令行
psql -U pg_user -d pg_db -h pg_host -p 5432 指令總結
1.使用者實用程式
createdb 建立一個新的PostgreSQL的資料庫和SQL語句CREATE DATABASE 相同
createuser 建立一個新的PostgreSQL的使用者和SQL語句CREATE USER 相同
dropdb 删除資料庫
dropuser 删除使用者
pg_dump 将PostgreSQL資料庫導出到一個腳本檔案
pg_dumpall 将所有的PostgreSQL資料庫導出到一個腳本檔案
pg_restore 從一個由pg_dump或pg_dumpall程式導出的腳本檔案中恢複PostgreSQL資料庫
psql 一個基于指令行的PostgreSQL互動式用戶端程式
vacuumdb 清理和分析一個PostgreSQL資料庫它是用戶端程式psql環境下SQL語句VACUUM的shell腳本封裝二者功能完全相同
2. 系統實用程式
1). pg_ctl 啟動、停止、重新開機PostgreSQL服務比如pg_ctl start 啟動PostgreSQL服務它和service postgresql start相同
2). pg_controldata 顯示PostgreSQL服務的内部控制資訊
3). psql 切換到PostgreSQL預定義的資料庫超級使用者postgres啟用用戶端程式psql并連接配接到自己想要的資料庫比如說
psql template1
出現以下界面說明已經進入到想要的資料庫可以進行想要的操作了。
template1=#
3. 在資料庫中的一些指令
template1=# \l 檢視系統中現存的資料庫
template1=# \q 退出用戶端程式psql
template1=# \c 從一個資料庫中轉到另一個資料庫中如template1=# \c sales 從template1轉到sales
template1=# \dt 檢視表
template1=# \d 檢視表結構
template1=# \di 檢視索引
[基本資料庫操作]==============
1). *建立資料庫 create database [資料庫名];
2). *檢視資料庫清單 \d
3). *删除資料庫 . drop database [資料庫名];
建立表 create table ([字段名1] [類型1] <references 關聯表名(關聯的字段名)>;,[字段名2] [類型2],......<,primary key (字段名m,字段名n,...)>;);
*檢視表名清單 \d
*檢視某個表的狀況 \d [表名]
*重命名一個表 alter table [表名A] rename to [表名B];
*删除一個表 drop table [表名];
[表内基本操作]===============
*在已有的表裡添加字段 alter table [表名] add column [字段名] [類型];
*删除表中的字段 alter table [表名] drop column [字段名];
*重命名一個字段 alter table [表名] rename column [字段名A] to [字段名B];
*給一個字段設定預設值 alter table [表名] alter column [字段名] set default [新的預設值];
*去除預設值 alter table [表名] alter column [字段名] drop default;
在表中插入資料 insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......);
修改表中的某行某列的資料 update [表名] set [目标字段名]=[目标值] where [該行特征];
删除表中某行資料 delete from [表名] where [該行特征];
delete from [表名];--删空整個表
4. PostgreSQL使用者認證
PostgreSQL資料目錄中的pg_hba.conf的作用就是使用者認證可以在/usr/local/pgsql/data中找到。
有以下幾個例子可以看看
(1)允許在本機上的任何身份連接配接任何資料庫
TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all all trust(無條件進行連接配接)
(2)允許IP位址為192.168.1.x的任何主機與資料庫sales連接配接
host sales all 192.168.1.0 255.255.255.0 ident sameuser(表明任何作業系統使用者都能夠以同名資料庫使用者進行連接配接)
5. 來一個完整的建立PostgreSQL資料庫使用者的示例吧
(1)進入PostgreSQL進階使用者
(2)啟用用戶端程式并進入template1資料庫
(3)建立使用者
template1=# CREATE USER hellen WITH ENCRYPED PASSWORD'zhenzhen'
(4)因為設定了密碼是以要編輯pg_hba.conf使使用者和配置檔案同步。
在原有記錄上面添加md5
local all hellen md5
(5)使用新使用者登入資料庫
template1=# \q
psql -U hellen -d template1
PS在一個資料庫中如果要切換使用者要使用如下指令
template1=# \!psql -U tk -d template1
6. 設定使用者特定的權限
還是要用例子來說明
建立一個使用者組
sales=# CREATE GROUP sale;
添加幾個使用者進入該組
sales=# ALTER GROUP sale ADD USER sale1,sale2,sale3;
授予使用者級sale針對表employee和products的SELECT權限
sales=# GRANT SELECT ON employee,products TO GROUP sale;
在sale中将使用者user2删除
sales=# ALTER GROUP sale DROP USER sale2;
7. 備份資料庫
可以使用pg_dump和pg_dumpall來完成。比如備份sales資料庫
pg_dump sales>/home/tk/pgsql/backup/1.bak
pg資料目錄介紹
預設資料目錄如下
/usr/local/var/postgres Linux中可能是
/var/lib/pgsql/9.5/data
或
/var/lib/postgres/data
或
/home/postgres/data
或其它路徑 資料目錄結構大緻如下
PG_VERSION #pg版本如10
base/ #每個 database 會在 base 目錄下有一個子目錄存儲資料庫檔案
global/ #存放的檔案用于存儲全局的系統表資訊和全局控制資訊
pg_commit_ts/ #包含已送出事務的時間
pg_dynshmem/ #包含動态共享記憶體子系統使用的檔案
pg_hba.conf
pg_ident.conf
pg_logical/ #包含邏輯解碼的狀态資料
pg_multixact/ #包含多事務狀态資料等待鎖定的并發事務
pg_notify/ #包含LISTEN/NOTIFY狀态資料
pg_replslot/ #包含複制槽資料
pg_serial/ #包含了已經送出的序列化事務的有關資訊
pg_snapshots/ #包含導出的快照
pg_stat/ #包含統計子系統的永久檔案
pg_stat_tmp/ #包含統計子系統的臨時檔案
pg_subtrans/ #包含子事務狀态資料
pg_tblspc/ #包含表空間的符号連結
pg_twophase/ #包含預備事務的狀态檔案
pg_wal/ #包含wal日志
pg_xact/
postgresql.auto.conf #一個用于存儲由ALTER SYSTEM 設定的配置參數的檔案
postgresql.conf
postmaster.opts #一個記錄伺服器最後一次啟動時使用的指令行參數的檔案
server.log #pg記錄檔如果有報錯也會記錄在這裡 base目錄介紹
base 目錄是最重要的一個目錄放的是每一個 database 的資料。base 目錄裡的每一個數字目錄對于一個 database 的 oid 可以通過 檢視 pg_database 這張表檢視每一個 資料庫的 oid
$ cd /usr/local/var/postgres/base
total 0
drwx------ 192 5 18 15:04 ./
drwx------ 832 6 1 22:57 ../
drwx------ 9440 5 18 14:49 1/
drwx------ 9440 5 18 14:49 12557/
drwx------ 9472 6 1 22:31 12558/
drwx------ 14336 6 1 22:33 16385/
$ 通過資料庫檢視
select oid, datname from pg_database ;
oid datname
---------------------
12558 postgres
16385 my_test_db
1 template1
12557 template0 每一張表的資料大部分又是放在 base/(dboid)/(relfilenode) 這個檔案裡面
select relname, relowner, relfilenode from pg_class where relowner = 16384;
relname | relowner | relfilenode
-----------------------+----------+-------------
pg_toast_24589 | 16384 | 24592
pg_toast_24589_index | 16384 | 24594
pg_toast_24595 | 16384 | 24598
pg_toast_24595_index | 16384 | 24600
item_id_seq | 16384 | 24601
Feed_pkey | 16384 | 167963
feed | 16384 | 24589
item | 16384 | 24595
pg_toast_168003 | 16384 | 168006
pg_toast_168003_index | 16384 | 168008
tmp | 16384 | 168003
(11 rows)
feed 這張表資料在 base/16384/24589 檔案裡item 這張表的資料放在 base/16386/24595 這個檔案裡。也可以用 pg_relation_filepath 這個函數查詢
select pg_relation_filepath('item');
pg_relation_filepath
----------------------
base/16385/24595
(1 row)
當然實際的存儲不會這麼簡單。每一張表的檔案都會有一些附加的存儲檔案如檔案名後加上 _fsm 的是空閑空間映射表 (Free Space Map)。另外 base/(dboid)/(relfilenode) 這個檔案超過 1GB 以後Postgres 會把這個檔案拆分成不超過 1G 的多個檔案檔案末尾加上 .1 .2 .3 … 做編号。 如 24589 24589.1 24589.2 。據說這是因為某些檔案系統支援的最大檔案大小有限制(如 fat32 隻支援最大 4G )的檔案。
global目錄介紹
global下有四種檔案
-
pg_control
用于存儲全局控制資訊
-
pg_filenode.map
是pg_class裡relfilenode為0的系統表OID與檔案的寫死映射每個使用者建立的資料庫目錄下也有同名檔案。
-
pg_internal.init
是系統表的cache檔案用于加快讀取。預設不存在查詢系統表後自動産生.
-
全局系統表檔案
數字命名的檔案用于存儲系統表的内容。它們在pg_class裡的relfilenode都為0是靠pg_filenode.map将OID與檔案寫死映射。注不是所有的系統表的relfilenode都為0
其它檔案是需要到pg_class裡根據OID查到對應的relfilenode來與檔案名比對的。
例如tab1的relfilenode是16385那麼16385這個檔案就是tab1的資料檔案
空閑空間映射表
名字以_fsm結尾的檔案是資料檔案對應的FSM(free space map)檔案用map方式來辨別哪些block是空閑的。用一個Byte而不是bit來辨別一個block。對于一個有N個位元組的block它在_fsm檔案中第blknum個位元組中記錄的值是(31+N)/32。通過這種方式辨別一個block空閑位元組數。FSM中不是簡單的數組而是一個三層的樹形結構。FSM檔案是在需要用到它時才自動産生的。
可見性映射表檔案
名字以_vm結尾的檔案是資料檔案對應的VM(visibility map)。PostgreSQL中在做多版本并發控制時是通過在元組頭上辨別“已無效”來實作删除或更新的最後通過VACUUM功能來清理無效資料回收空閑空間。在做VACUUM時就使用VM開快速查找包含無效元組的block。VM僅是個簡單的bitmap,一個bit對應一個block
注系統表分為全局系統表和庫級系統表。
全局系統表位于global下例如pg_database,pg_tablespace,pg_auth_members這種存儲系統級對象的表。
庫級系統表位于資料庫目錄下例如pg_type,pg_proc,pg_attribute這種存儲庫級對象的表。
值得注意的是pg_class位于庫級目錄的裡但也包含全局系統表資訊是以研發或運維人員在改動全局系統表資訊時需要注意。
data
├── global # under global, all the filenode is hard-code(select oid,relname,relfilenode from pg_class where relfilenode=0 order by oid;)
│ ├── 1136 # pg_pltemplate
│ ├── 1137 # pg_pltemplate_name_index
│ ├── 1213 # pg_tablespace
│ ├── 1214 # pg_shdepend
│ ├── 1232 # pg_shdepend_depender_index
│ ├── 1233 # pg_shdepend_reference_index
│ ├── 1260 # pg_authid
│ ├── 1261 # pg_auth_members
│ ├── 1262 # pg_database
│ ├── 2396 # pg_shdescription
│ ├── 2397 # pg_shdescription_o_c_index
│ ├── 2671 # pg_database_datname_index
│ ├── 2672 # pg_database_oid_index
│ ├── 2676 # pg_authid_rolname_index
│ ├── 2677 # pg_authid_oid_index
│ ├── 2694 # pg_auth_members_role_member_index
│ ├── 2695 # pg_auth_members_member_role_index
│ ├── 2697 # pg_tablespace_oid_index
│ ├── 2698 # pg_tablespace_spcname_index
│ ├── 2846 # pg_toast_2396
│ ├── 2847 # pg_toast_2396_index
│ ├── 2964 # pg_db_role_setting
│ ├── 2965 # pg_db_role_setting_databaseid_rol_index
│ ├── 2966 # pg_toast_2964
│ ├── 2967 # pg_toast_2964_index
│ ├── 3592 # pg_shseclabel
│ ├── 3593 # pg_shseclabel_object_index
│ ├── 4060 # pg_toast_3592x
│ ├── 4061 # pg_toast_3592_index
│ ├── 6000 # pg_replication_origin
│ ├── 6001 # pg_replication_origin_roiident_index
│ ├── 6002 # pg_replication_origin_roname_index
│ ├── pg_control # global control file, use pgcheck -pc to see it.
│ ├── pg_filenode.map # system table (oid -> filenode) mapping file, use pgcheck -pm to see it.
│ └── pg_internal.init # system table cache file, use pgcheck -pr to see it. View Code
表空間目錄介紹
my_test_db=# select oid,* from pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
49162 | dbspace | 10 | |
(3 rows) 每一個Oid都在/pg_tblspc下對應一個名為Oid的軟連結檔案指向真正的space目錄。
postgresql.conf檔案
檔案存儲路徑連接配接配置資源使用優化配置日志設定等
修改需要重新開機資料庫
主要配置如下
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.
#data_directory = 'ConfigDir' # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '' # write an extra PID file
# (change requires restart)
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart) pg_hba.conf 檔案
伺服器主機連接配接驗證配置檔案
資料備份和恢複
備份庫
pg_dump -h PG_HOST -U PG_USER DATABASE > DATABASE.bak 備份表
pg_dump -h localhost -U postgres(使用者名) 資料庫名(預設時同使用者名) -t table(表名) >data.bak pg_dump指令參數說明
pg_dump 把一個資料庫轉儲為純文字檔案或者是其它格式.
用法:
pg_dump [選項]... [資料庫名字]
一般選項:
-f, --file=FILENAME 輸出檔案或目錄名
-F, --format=c|d|t|p 輸出檔案格式 (定制, 目錄, tar)
明文 (預設值))
-j, --jobs=NUM 執行多個并行任務進行備份轉儲工作
-v, --verbose 詳細模式
-V, --version 輸出版本資訊然後退出
-Z, --compress=0-9 被壓縮格式的壓縮級别
--lock-wait-timeout=TIMEOUT 在等待表鎖逾時後操作失敗
-?, --help 顯示此幫助, 然後退出 恢複庫/表
psql -h PG_HOST -U PG_USER -d DATABASE < DATABASE.bak
或 psql -h PG_HOST -U PG_USER -d DATABASE -f DATABASE.bak 注意
- 如果直接進入PostgreSQL的安裝目錄bin下執行指令可能會出現 找不到pg_dumppsql的現象可以用指令的絕對路徑如/usr/local/bin/psql 等
- 如果是遠端備份和恢複必須保證資料庫允許外部通路的權限
常見資料庫操作
基本和mysql類似
# 建立新表
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
# 插入資料
INSERT INTO user_tbl(name, signup_date) VALUES('張三', '2013-12-22');
# 選擇記錄
SELECT * FROM user_tbl;
# 更新資料
UPDATE user_tbl set name = '李四' WHERE name = '張三';
# 删除記錄
DELETE FROM user_tbl WHERE name = '李四' ;
# 添加欄位
ALTER TABLE user_tbl ADD email VARCHAR(40);
# 更新結構
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
# 更名欄位
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
# 删除欄位
ALTER TABLE user_tbl DROP COLUMN email;
# 表格更名
ALTER TABLE user_tbl RENAME TO backup_tbl;
# 删除表格
DROP TABLE IF EXISTS backup_tbl; 資料目錄遷移
通常資料目錄會安裝在系統盤而系統盤的空間有限當資料庫量大或日志多時會撐爆硬碟造成服務停掉是以通常将資料遷移到資料盤
在CentOS 系統中pg的預設資料安裝目錄是
/var/lib/pgsql/{version}/data 資料庫服務一般開機自動啟動那麼就可以順藤摸瓜找到相關的service。
對于postgresql10它的服務名為postgresql-10.service該檔案的路徑為
/usr/lib/systemd/system/postgresql-10.service postgresql-10.service的檔案内容如下
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades. If you want to customize, the
# best way is to create a file "/etc/systemd/system/postgresql-10.service",
# containing
# .include /lib/systemd/system/postgresql-10.service
# ...make your changes here...
# For more info about custom unit files, see
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F
# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.
# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-setup.
[Unit]
Description=PostgreSQL 10 database server
Documentation=https://www.postgresql.org/docs/10/static/
After=syslog.target
After=network.target
[Service]
Type=notify
User=postgres
Group=postgres
# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.
# Location of database directory
Environment=PGDATA=/home/pgsql/data/
# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0
ExecStartPre=/usr/pgsql-10/bin/postgresql-10-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-10/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0
[Install]
WantedBy=multi-user.target 如果postgresql沒有作為服務加到systemctl 則修改對應的配置檔案postgresql.conf中的data_directory的值通過pg_ctl啟動和下面操作一樣
從上面來看裡面用到了PGDATA Environment後面需要修改該變量但是得先執行下列步驟
1. 關閉資料庫服務
service postgresql-10 stop 2. 拷貝原先的data目錄到新的data目錄
sudo cp -rf /var/lib/pgsql/10/data /home/pgsql/data 3. 設定使用者和權限
# 修改data_directory的所有者
sudo chown -R postgres:postgres /home/pgsql/data
# 設定資料目錄權限
sudo chmod 700 /home/pgsql/data 4. 上述2、3完成後可以修改service檔案裡面的enviroment變量可以将這個變量修改為自己的data目錄
sudo vi postgresql-10.service
修改這個檔案中的
Environment=PGDATA=/var/lib/pgsql/10/data/
将其修改為自己的新的資料路徑
Environment=PGDATA=/home/psql/data/
修改PGDATA路徑之後就可以執行下面的指令讓其立即生效
sudo systemctl daemon-reload 5. 重新開機資料庫
service postgresql-10 start 6. 驗證
show data_directory; 1如果還沒有改正過來可以看下PGDATA這個變量的位置
這個變量的位置在哪呢這裡因為postgresql安裝後會自動生成一個postgres使用者是以猜測這個使用者下存在PGDATA環境變量
su - postgres 進入postgres使用者下檢視目前使用者的環境變量
echo $PGDATA列印出PGDATA變量值可以看到這個變量值還是之前那個值。是以我們可以知道了是這個地方有問題。
于是修改這個使用者的環境變量設定檔案
vi ~/.bash_profile修改裡面的PGDATA路徑
source ~/.bash_profile使檔案生效
再重新開機資料庫服務
2可能需要postgres使用者的宿主目錄
cat /etc/passwd
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
sudo usermod -d /home/data/pgsql -u 26 postgres
檢視資料庫使用者
SELECT u.usename AS "User name", u.usesysid AS "User ID"
, CASE
WHEN u.usesuper
AND u.usecreatedb THEN CAST('superuser, createdatabase' AS pg_catalog.text)
WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
WHEN u.usecreatedb THEN CAST('create database' AS pg_catalog.text)
ELSE CAST('' AS pg_catalog.text)
END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;
-- 或者直接這麼查
select * from pg_catalog.pg_user 3注意postgresql已經作為服務添加到了systemctl
MAC 系統資料目錄遷移思路同上。
參考
https://blog.csdn.net/guanguoxiang/article/details/46375653 https://www.jianshu.com/p/cd8c5b988e52 中文文檔 https://blog.csdn.net/wk1134314305/article/details/79054248作者
zhoujie出處
http://www.cnblogs.com/zhoujie/本文版權歸作者和部落格園共有歡迎轉載但未經作者同意必須保留此段聲明且在文章頁面明顯位置給出原文連接配接不然我擔心部落格園找你算賬
如果您覺得本文對你有幫助請豎起您的大拇指右下角點推薦也可以關注我