logo头像

You name it , I got it !

將openproject提供的docker container中的 postgresql 從 9.6 升級到 10


因為工作上的需求,要提供一台openproject的伺服器給關係企業使用
選擇了用 openproject (不是我選的,UI難看死了)
在openproject的官方網站就提供了docker安裝的方式
https://www.openproject.org/docker/
想當然爾,人家都這麼貼心的提供官方版的安裝方式了,當然就依照這篇連結的方式做了一台暫時的Server出來提供測試

But (對,又是這個But )

這個官方版的docker image,是把 openproject 8.0.1 配上 postgresql 9.6
對,就是那個 2016/09 release 的 postgresql 9.6 !
現在都2018/10了,為什麼還要用這麼舊的版本?
於是老闆說了,把他改成 postgresql-10 ,然後和openproject 分開來吧
啊….分開來這個我還不會吶…
想說先來試試看在container內把postgresql 升級上去試試看
以下就是大概的過程筆記

基本設定

這邊要進入container 去執行指令

1
docker exec -it openproject /bin/bash

檢查一下作業系統版本

1
2
3
4
5
6
7
8
9
10
11
12
cat /etc/os-release

root@3d714961c8a4:/usr/src/app# cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 9 (stretch)"
NAME="Debian GNU/Linux"
VERSION_ID="9"
VERSION="9 (stretch)"
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"
root@3d714961c8a4:/usr/src/app#

嗯哼, debian 9 這個要記下來
因為底層的作業系統一般來說,是不會裝什麼套件給你用的,所以要用到的套件都要自己裝
先補上一些會用到的指令

1
2
apt update
apt install vim net-tools

新增 postgresql 10 的 repository

因為預設debian 9 無法安裝postgresql 10
所以要參考這邊的文章
https://www.postgresql.org/download/linux/debian/

先加入官方的 repository

1
echo " deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main" > /etc/apt/sources.list.d/pgdg.list

然後加入 key
這邊要注意的是,官方文件會需要用sudo,但是在container內不需要

1
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc |apt-key add -

再做 apt update

1
apt-get update

到這邊為止,準備工作完成了,接下來可以開始安裝 postgresql-10

1
apt-get install postgresql-10 postgresql-client-10 postgresql-contrib

讓子彈飛一會兒,沒有意外的話,這樣就安裝完了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
root@3d714961c8a4:/usr/src/app# apt install  postgresql-10
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
libpq-dev libpq5 pgdg-keyring postgresql-client-10 postgresql-client-common postgresql-common
Suggested packages:
postgresql-doc-10 locales-all libjson-perl
The following NEW packages will be installed:
pgdg-keyring postgresql-10 postgresql-client-10
The following packages will be upgraded:
libpq-dev libpq5 postgresql-client-common postgresql-common
4 upgraded, 3 newly installed, 0 to remove and 20 not upgraded.
Need to get 6921 kB of archives.
After this operation, 29.3 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 libpq-dev amd64 10.5-1.pgdg90+1 [160 kB]
Get:2 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 libpq5 amd64 10.5-1.pgdg90+1 [165 kB]
Get:3 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 pgdg-keyring all 2017.3 [10.3 kB]
Get:4 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 postgresql-common all 192.pgdg90+1 [222 kB]
Get:5 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 postgresql-client-common all 192.pgdg90+1 [82.5 kB]
Get:6 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 postgresql-client-10 amd64 10.5-1.pgdg90+1 [1343 kB]
Get:7 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 postgresql-10 amd64 10.5-1.pgdg90+1 [4939 kB]
Fetched 6921 kB in 12s (548 kB/s)
debconf: delaying package configuration, since apt-utils is not installed
(Reading database ... 33426 files and directories currently installed.)
Preparing to unpack .../0-libpq-dev_10.5-1.pgdg90+1_amd64.deb ...
Unpacking libpq-dev (10.5-1.pgdg90+1) over (9.6.10-0+deb9u1) ...
Preparing to unpack .../1-libpq5_10.5-1.pgdg90+1_amd64.deb ...
Unpacking libpq5:amd64 (10.5-1.pgdg90+1) over (9.6.10-0+deb9u1) ...
Selecting previously unselected package pgdg-keyring.
Preparing to unpack .../2-pgdg-keyring_2017.3_all.deb ...
Unpacking pgdg-keyring (2017.3) ...
Preparing to unpack .../3-postgresql-common_192.pgdg90+1_all.deb ...
Leaving 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Unpacking postgresql-common (192.pgdg90+1) over (181+deb9u2) ...
Preparing to unpack .../4-postgresql-client-common_192.pgdg90+1_all.deb ...
Unpacking postgresql-client-common (192.pgdg90+1) over (181+deb9u2) ...
Selecting previously unselected package postgresql-client-10.
Preparing to unpack .../5-postgresql-client-10_10.5-1.pgdg90+1_amd64.deb ...
Unpacking postgresql-client-10 (10.5-1.pgdg90+1) ...
Selecting previously unselected package postgresql-10.
Preparing to unpack .../6-postgresql-10_10.5-1.pgdg90+1_amd64.deb ...
Unpacking postgresql-10 (10.5-1.pgdg90+1) ...
Setting up libpq5:amd64 (10.5-1.pgdg90+1) ...
Processing triggers for libc-bin (2.24-11+deb9u3) ...
Setting up pgdg-keyring (2017.3) ...
Removing apt.postgresql.org key from trusted.gpg: Warning: The postinst maintainerscript of the package pgdg-keyring
Warning: seems to use apt-key (provided by apt) without depending on gnupg or gnupg2.
Warning: This will BREAK in the future and should be fixed by the package maintainer(s).
Note: Check first if apt-key functionality is needed at all - it probably isn't!
OK
Setting up libpq-dev (10.5-1.pgdg90+1) ...
Setting up postgresql-client-common (192.pgdg90+1) ...
Installing new version of config file /etc/postgresql-common/supported_versions ...
Setting up postgresql-common (192.pgdg90+1) ...
debconf: unable to initialize frontend: Dialog
debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)
debconf: falling back to frontend: Readline
Replacing config file /etc/postgresql-common/createcluster.conf with new version
invoke-rc.d: could not determine current runlevel
invoke-rc.d: policy-rc.d denied execution of start.
Setting up postgresql-client-10 (10.5-1.pgdg90+1) ...
update-alternatives: using /usr/share/postgresql/10/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode
Setting up postgresql-10 (10.5-1.pgdg90+1) ...
debconf: unable to initialize frontend: Dialog
debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)
debconf: falling back to frontend: Readline
Creating new PostgreSQL cluster 10/main ...
/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main --auth-local peer --auth-host md5
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/10/main ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start

Ver Cluster Port Status Owner Data directory Log file
10 main 5433 down postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
update-alternatives: using /usr/share/postgresql/10/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
invoke-rc.d: could not determine current runlevel
invoke-rc.d: policy-rc.d denied execution of start.

用 pg_lsclusters 檢查看看

1
2
3
4
root@3d714961c8a4:/usr/src/app# pg_lsclusters              
Ver Cluster Port Status Owner Data directory Log file
9.6 main 5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
10 main 5433 offline postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log

這邊有點小問題,因為我裝完postgresql-10的時候,不知道是預設沒有啟動,還是啟動失敗,總之就是offline
不過因為待會就要把這個 postgresql 10 先砍掉,所以就算了,沒管他

升級Potsgresql 9.6

首先,要先把剛剛裝的10給砍掉,為什麼?我也不清楚 XD

1
pg_dropcluster 10 main --stop

接下來這個很重要,我在這邊卡了一陣子
文件上是說要關閉9.6的服務,可是我在docker裡面測試了很久,就是不讓我關掉。
後來發現,因為在這個container中,postgresql是用 supervisord來啟動的,所以要透過supervisorctl來停止postgresql的服務才可以,不然下指令停止服務,supervisor又會自動開起來。

1
supervisorctl stop postgres

postgres這個名稱在 /etc/supervisor/conf.d的設定檔裡面可以找到
服務停止後,就可以來升級9.6了
一樣,讓子彈飛一會兒…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
pg_upgradecluster -m upgrade 9.6 main

root@3d714961c8a4:/usr/src/app# supervisorctl stop postgres
postgres: stopped
root@3d714961c8a4:/usr/src/app# ls
CHANGELOG.md Gemfile Procfile.dev browserslist db extra log public tslint.json
CODE_OF_CONDUCT.md Gemfile.lock README.md ci docker features package.json script vendor
CONTRIBUTING.md Gemfile.plugins Rakefile codecov.yml docker-compose.pullpreview.yml files packaging spec vendored-plugins
Dockerfile Guardfile app config docker-compose.yml frontend passenger.8080.pid spec_legacy
Dockerfile.public Procfile bin config.ru docs lib passenger.8080.pid.lock tmp
root@3d714961c8a4:/usr/src/app# pg_upgradecluster -m upgrade 9.6 main
Disabling connections to the old cluster during upgrade...
Restarting old cluster with restricted connections...
Removed stale pid file.
Stopping old cluster...
Creating new PostgreSQL cluster 10/main ...
/usr/lib/postgresql/10/bin/initdb -D /var/lib/postgresql/10/main --auth-local peer --auth-host md5 --encoding UTF8 --lc-collate C --lc-ctype C
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/10/main ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

/usr/lib/postgresql/10/bin/pg_ctl -D /var/lib/postgresql/10/main -l logfile start

Ver Cluster Port Status Owner Data directory Log file
10 main 5433 down postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log


/usr/lib/postgresql/10/bin/pg_upgrade -b /usr/lib/postgresql/9.6/bin -B /usr/lib/postgresql/10/bin -p 5432 -P 5433 -d /etc/postgresql/9.6/main -D /etc/postgresql/10/main
Finding the real data directory for the source cluster ok
Finding the real data directory for the target cluster ok
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for invalid "unknown" user columns ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_clog to new server ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
ok
Copying user relation files
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok
Checking for hash indexes ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
./delete_old_cluster.sh
pg_upgrade output scripts are in /var/log/postgresql/pg_upgradecluster-9.6-10-main.DvAD
Re-enabling connections to the old cluster...
Copying old configuration files...
Copying old start.conf...
Copying old pg_ctl.conf...
Disabling automatic startup of old cluster...
Configuring old cluster to use a different port (5433)...
Starting target cluster on the original port...
Success. Please check that the upgraded cluster works. If it does,
you can remove the old cluster with
pg_dropcluster 9.6 main

Ver Cluster Port Status Owner Data directory Log file
9.6 main 5433 down postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
Ver Cluster Port Status Owner Data directory Log file
10 main 5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log
root@3d714961c8a4:/usr/src/app

順利的話,最後會看到 10版的psql online , 9.6的變成 down了
確認一下資料庫內容是不是正常,ok的話,用剛剛的指令,把9.6給砍了

1
pg_dropcluster 9.6 main --stop

然後修改一下 supervisor 的設定檔 把9.6改成10就可以了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
root@3d714961c8a4:/usr/src/app# cat /etc/supervisor/conf.d/supervisord.conf 
[supervisord]
nodaemon=true

[program:web]
priority=4
user=app
directory=/usr/src/app
command=./docker/web
autorestart=true
stderr_logfile = /var/log/supervisor/%(program_name)s-stderr.log
stdout_logfile = /var/log/supervisor/%(program_name)s-stdout.log

[program:worker]
priority=5
user=app
directory=/usr/src/app
command=./docker/worker
autorestart=true
stderr_logfile = /var/log/supervisor/%(program_name)s-stderr.log
stdout_logfile = /var/log/supervisor/%(program_name)s-stdout.log

[program:memcached]
priority=100
user=app
command=/usr/bin/memcached
autorestart=true
stderr_logfile = /var/log/supervisor/%(program_name)s-stderr.log
stdout_logfile = /var/log/supervisor/%(program_name)s-stdout.log

[program:cron]
priority=100
user=app
directory=/usr/src/app
command=./docker/cron
autorestart=true
stderr_logfile = /var/log/supervisor/%(program_name)s-stderr.log
stdout_logfile = /var/log/supervisor/%(program_name)s-stdout.log

[program:apache2]
priority=2
directory=/usr/src/app
command=./docker/proxy
stderr_logfile = /var/log/supervisor/%(program_name)s-stderr.log
stdout_logfile = /var/log/supervisor/%(program_name)s-stdout.log

[program:postfix]
priority=100
directory=/etc/postfix
command=/usr/sbin/postfix -c /etc/postfix start
startsecs=0
autorestart=false
stderr_logfile = /var/log/supervisor/%(program_name)s-stderr.log
stdout_logfile = /var/log/supervisor/%(program_name)s-stdout.log

[program:postgres]
user=postgres
priority=1
command=/usr/lib/postgresql/10/bin/postgres -D /var/lib/postgresql/10/main -c config_file=/etc/postgresql/10/main/postgresql.conf
autorestart=true
stderr_logfile = /var/log/supervisor/%(program_name)s-stderr.log
stdout_logfile = /var/log/supervisor/%(program_name)s-stdout.log
root@3d714961c8a4:/usr/src/app#

再透過 supervisor把postgres服務叫回來
然後檢查openproject的web界面,用admin登入看看服務、頁面是不是正常

openproject web page

看來沒有問題,收工!

p.s 記得用 docker export 或者是 docker save 作備份,或者丟去dockerhub 也行