Ubuntu 20.04下安裝Postgresql
- 前言
- 安裝postgresql-12
- Troubleshooting
- 啟動並允許其開機自啟動
- Redmine設定
- 在資料庫中儲存中文
- 解除安裝
- Postgresql學習資源
前言
本篇基於How to Install Redmine on Ubuntu 16.04,並修改部份指令使得它可以在Ubuntu 20.04下work。
postgresql12_5">安裝postgresql-12
安裝postgresql的dependencies:
sudo apt -y install libpqxx-dev protobuf-compiler
安裝postgresql:
echo "deb http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update -y
# this will install postgresql-13, but one of redmine's plugin full_text_search not support postgresql-13
# sudo apt -y install postgresql
sudo apt -y install postgresql-12
檢查安裝了什麼:
apt list --installed | grep postgres
輸出如下,總共安裝了四個套件:
postgresql-12/focal-pgdg,now 12.6-1.pgdg20.04+1 amd64 [installed]
postgresql-client-12/focal-pgdg,now 12.6-1.pgdg20.04+1 amd64 [installed,automatic]
postgresql-client-common/focal-pgdg,now 225.pgdg20.04+1 all [installed,automatic]
postgresql-common/focal-pgdg,now 225.pgdg20.04+1 all [installed,automatic]
安裝路徑:
/var/lib/postgresql
Troubleshooting
在How to Install Redmine on Ubuntu 16.04這篇教學中寫入/etc/apt/sources.list.d/pgdg.list
的內容是:
echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main"
如果照著做,在實際安裝時:
sudo apt -y install postgresql
會出現以下錯誤:
Reading package lists... Done
Building dependency tree
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:
The following packages have unmet dependencies:
postgresql : Depends: postgresql-13 but it is not going to be installed
E: Unable to correct problems, you have held broken packages.
這是因為教學裡用的Ubuntu版本是16.04。
如果要讓它能在Ubuntu 20.04下work,需將 /etc/apt/sources.list.d/pgdg.list
的內容改為:
deb http://apt.postgresql.org/pub/repos/apt/ focal-pgdg main
註:Ubuntu各版本的開發代號可至Ubuntu version history查看。
啟動並允許其開機自啟動
systemctl start postgresql
systemctl enable postgresql
注:在docker container中使用sudo service postgresql start
來啟動
Redmine設定
為了Redmine所做的設定如下:
sudo passwd postgres #在此處設定密碼為postgres
sudo su - postgres
createuser redmine
psql
在psql
的shell中:
ALTER USER redmine WITH ENCRYPTED password 'DBPassword';
CREATE DATABASE redmine WITH ENCODING='UTF8' OWNER=redmine;
\q
exit # switch to original user
在資料庫中儲存中文
本節參考Postgres 10+ 的正體中文筆劃排序。
切換為postgres
使用者並進入postgresql的shell:
sudo su - postgres
psql
試著創建一個能儲存中文的資料庫:
create database chinese with template=template0 encoding='utf8' lc_collate='zh_TW.utf8' LC_CTYPE='zh_TW.utf8';
如果提示說zh_TW.utf8是invalid的:
ERROR: invalid locale name: "zh_TW.utf8"
則執行:
sudo locale-gen zh_TW.UTF-8
然後要重新開機。
解除安裝
解除安裝指令:
sudo apt autoremove --purge postgresql-12
Dry run:
sudo apt autoremove --dry-run postgresql-12
輸出如下:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages will be REMOVED:
postgresql-12 postgresql-12-pgdg-pgroonga postgresql-common sysstat
0 upgraded, 0 newly installed, 4 to remove and 156 not upgraded.
Remv postgresql-12-pgdg-pgroonga [2.2.9-1]
Remv postgresql-12 [12.6-1.pgdg20.04+1]
Remv postgresql-common [225.pgdg20.04+1]
Remv sysstat [12.2.0-2]
Postgresql學習資源
Everything about database,business.(Most for PostgreSQL).
PostgreSQL教學及文檔