PostgreSQL 好用指令紀錄

#進人 PostgreSQL 終端：

psql --host={host} --port={post} --username={username} --dbname={dbname}

#離開 PostgreSQL 終端：

\q

#列出所有 Database

\l

#使用指定的 Database：

\c {Database 名稱} 

#列出所有的 Table：

\dt 

-------------------------------------------------------------------------------------------------------------

備份資料庫分兩種方式：使用 pg_dump 或 pg_dumpall 工具的 SQL 腳本備份，和使用 pg_basebackup 工具的 物理備份，以下說明：

--------------------------------------- SQL 腳本備份 ------------------------------------------------------------------

#使用 pg_dump 工具備份 Database 的 SQL 腳本，可以用 -U 指定 Database 的 User，其他參數可可參考官方文件：

pg_dump {Database 名稱} -U {Database 的 User} > {自取的備份檔名路徑，例如 /usr/local/my_db_backup.sql}

#使用 pg_dumpall 工具備份所有資料庫和角色，可以用 -U 指定 Database 的 User ()

pg_dumpall -U {Database 的 User} > {自取的備份檔名路徑，例如 /usr/local/my_all_db_backup.sql}

#使用 pg_dump 工具備份時的資料庫還原方式

psql -U {user 名稱} -d {Database 名稱} < {自取的備份檔名路徑，例如 /usr/local/my_all_db_backup.sql}

#使用 pg_dumpall 工具備份時的資料庫還原方式

psql -U {user 名稱} -f {自取的備份檔名路徑，例如 /usr/local/my_all_db_backup.sql}

---------------------------------------- 物理備份 ---------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

#使用 pg_basebackup 工具進行物理備份，以下指令可以得到幾個檔案，例如 backup_manifest、 base.tar.gz 、 pg_wal.tar.gz，其中我們需要的是 base.tar.gz：

pg_basebackup -U {user 名} -D {自行決定的備份資料夾路徑，例如 /usr/local/my_db_backup} -F t -Z 9 -P -v

各參數意思： 

• -D 或 --pgdata：指定備份的目標目錄。
•  -F 或 --format：指定輸出格式，可以是純文本（p）或 tar 格式（t）。
•  -Z：指定壓縮級別，範圍是 0（無壓縮）到 9（最大壓縮）。 
• -P 或 --progress：顯示進度信息。 -v 或 --verbose：顯示詳細的命令行輸出。

#用使用 pg_basebackup 工具進行的物理備份來還原：

方式是直接把整個物理備份的資料夾取代原來 PostgreSQL 的資料夾

先停止 PostgreSQL 服務

systemctl stop postgresql

先把原來的 PostgreSQL 資料夾移去別處，如果有問題可以用來還原回來

mv /var/lib/postgresql/data var/lib/postgresql/data.bak

Note: PostGreSQL 資料夾位置可能不一定一樣，跟使用的環境、版本有關

將物理備份的 base.tar.gz 解壓縮後將資料複製一份到 PostgreSQL 原資料夾路徑

cp /usr/local/my_db_backup/base.tar.gz /var/lib/postgresql/data
tar -xvzf /var/lib/postgresql/data/base.tar.gz

設定 owner 所有權：

chown -R {owner 名}:{owner 名} /var/lib/postgresql/data

再啟動 PostgreSQL 服務

systemctl start postgresql

參考資料：

1. Day 18 PostgreSQL 資料庫備份和恢復
2. PostgreSQL: Documentation: 16: pg_dump