Ubuntu 20.04下安裝Postgresql

前言

本篇基於How to Install Redmine on Ubuntu 16.04，並修改部份指令使得它可以在Ubuntu 20.04下work。

安裝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使用者的密碼，config/database.yml裡的password欄位也得跟著更新。

在資料庫中儲存中文

本節參考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教學及文檔