PostgreSQL 离线安装（Ubuntu）

描述：

生产环境使用PostgreSQL，存在安装指定版本数据库的需求，以及内网机器离线安装数据库的需求。

生产环境使用的操作系统是Ubuntu，所以可以通过一台相同版本可访问互联网的Ubuntu机器，进行相关数据库软件包和依赖包的下载，然后打包，传输到内网机器，进行离线安装数据库。

1.配置PostgreSQL的安装源

1.1Ubuntu源

Ubuntu 默认官方源，包含 PostgreSQL，安装页面可以参考：Linux 下载（Ubuntu）

apt install postgresql

查看软件包的所有来源

root@local:~# apt-cache madison postgresql-14
postgresql-14 | 14.20-0ubuntu0.22.04.1 | http://archive.ubuntu.com/ubuntu jammy-updates/main amd64 Packages
postgresql-14 | 14.20-0ubuntu0.22.04.1 | http://security.ubuntu.com/ubuntu jammy-security/main amd64 Packages
postgresql-14 | 14.2-1ubuntu1 | http://archive.ubuntu.com/ubuntu jammy/main amd64 Packages
root@local:~# apt-cache madison postgresql
postgresql |     14+238 | http://archive.ubuntu.com/ubuntu jammy/main amd64 Packages

1.2PostgreSQL Apt 软件仓库

如果Ubuntu版本默认存储库中包含的不是所需要的版本，可以通过PostgreSQL Apt 软件仓库进行安装，该仓库包含在PostgreSQL支持周期内的所有受支持的PostgreSQL版本，并提供自动更新

自动存储库配置

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

手动配置apt软件源

sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
. /etc/os-release
sudo sh -c "echo 'deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $VERSION_CODENAME-pgdg main' > /etc/apt/sources.list.d/pgdg.list"
sudo apt update

安装PostgreSQL：（将18替换为您所需的版本号）

sudo apt install postgresql-18

查看软件包，只有一个版本，默认自动更新最新稳定版本

postgres@xxx:/home/postgres$ apt-cache madison postgresql-14
postgresql-14 | 14.12-1.pgdg20.04+1 | http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 Packages

1.3存档存储库

如果要安装指定版本，或者在不支持的旧版Ubuntu系统安装，则需要通过存档数据库进行安装。

apt-archive

手动添加软件源条目

sudo sh -c 'echo "deb https://apt-archive.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg-archive main" > /etc/apt/sources.list.d/pgdg.list'

sudo sh -c 'echo "deb-src https://apt-archive.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg-archive main" >> /etc/apt/sources.list.d/pgdg.list'

sudo apt update

查看软件包，存在所有版本

postgres@xxx:/home/postgres$ apt-cache madison postgresql-14
postgresql-14 | 14.12-1.pgdg20.04+1 | http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 Packages
postgresql-14 | 14.12-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.11-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.10-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.9-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.8-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.7-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.6-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.5-2.pgdg20.04+2 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.5-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.4-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.3-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.2-1.pgdg20.04+1+b1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.2-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.1-2.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.1-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14.0-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14~rc1-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14~beta3-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14~beta2-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages
postgresql-14 | 14~beta1-1.pgdg20.04+1 | https://apt-archive.postgresql.org/pub/repos/apt focal-pgdg-archive/main amd64 Packages

2.打包

2.1通过apt下载相关包和依赖

只下载postgresql-11相关包，而不安装，默认下载的deb包位于/var/cache/apt/archives/，可以提前清理其中的deb包，防止和下载的postgresql-11相关的依赖包混淆。

检查/var/cache/apt/archives/目录

查看是否存在*.deb文件，如果存在，可以删除或者移动到某个备份目录，防止后续软件包混淆

# 检查 /var/cache/apt/archives/ 是否包含 *.deb 文件，如果存在，需要先进行处理
ls /var/cache/apt/archives/

下载软件包和依赖

不指定具体版本，默认获取最新支持版本及相关依赖软件包

sudo apt install -d postgresql-11

指定版本下载，这里指定下载postgresql-14的14.10-1.pgdg20.04+1版本

# 不安装 postgresql-server-dev,libpq-dev
# 只下载不安装
sudo apt install postgresql-14=14.10-1.pgdg20.04+1 postgresql-client-14=14.10-1.pgdg20.04+1 libpq5=14.10-1.pgdg20.04+1 -d

# 安装postgresql-server-dev,libpq-dev包，因为后续安装工具，可能需要相关dev包编译
# 只下载不安装
sudo apt install postgresql-14=14.10-1.pgdg20.04+1 postgresql-client-14=14.10-1.pgdg20.04+1 postgresql-server-dev-14=14.10-1.pgdg20.04+1 libpq-dev=14.10-1.pgdg20.04+1 libpq5=14.10-1.pgdg20.04+1 -d

打包

# 查看下载的deb包和依赖
cd /var/cache/apt/archives/
ls

# 创建目录存放相关deb包
mkdir ~/postgresql-deb
sudo mv /var/cache/apt/archives/*.deb ~/postgresql-deb

# 打包
cd ~
tar -cvf postgresql-deb.tar postgresql-deb

2.2通过获取url下载打包

创建下载文件列表

# 仅下载postgresql-12相关包
sudo apt-get --allow-unauthenticated -y install --print-uris postgresql-12 | cut -d\' -f2 | \
grep -E "http://|https://" > /tmp/download-list-pg12


# 防止后面需要 postgresql-server-dev-12 进行编译工具，可以一起下载，相关的 libpq-dev 包默认也是未安装的
# 添加 postgresql-server-dev-12 下载
sudo apt-get --allow-unauthenticated -y install --print-uris postgresql-12 postgresql-server-dev-12 | cut -d\' -f2 | \
grep -E "http://|https://" > /tmp/download-list-pg12

# 添加 libpq-dev 下载
sudo apt-get --allow-unauthenticated -y install --print-uris postgresql-12 postgresql-server-dev-12 libpq-dev | cut -d\' -f2 | \
grep -E "http://|https://" > /tmp/download-list-pg12


# 这里采用的仅下载postgresql-12，因为安装 postgresql-server-dev-12 需要同时安装的包较多
user@localhost:/tmp$ cat /tmp/download-list-pg12
https://apt-archive.postgresql.org/pub/repos/apt/pool/main/p/postgresql-13/libpq5_13.3-1.pgdg16.04+1_amd64.deb
https://apt-archive.postgresql.org/pub/repos/apt/pool/main/p/pgdg-keyring/pgdg-keyring_2018.2_all.deb
https://apt-archive.postgresql.org/pub/repos/apt/pool/main/p/postgresql-common/postgresql-client-common_226.pgdg16.04+1_all.deb
https://apt-archive.postgresql.org/pub/repos/apt/pool/main/p/postgresql-12/postgresql-client-12_12.7-1.pgdg16.04+1_amd64.deb
https://apt-archive.postgresql.org/pub/repos/apt/pool/main/p/postgresql-common/postgresql-common_226.pgdg16.04+1_all.deb
https://apt-archive.postgresql.org/pub/repos/apt/pool/main/p/postgresql-12/postgresql-12_12.7-1.pgdg16.04+1_amd64.deb
http://mirrors.aliyun.com/ubuntu/pool/main/libc/libcommon-sense-perl/libcommon-sense-perl_3.74-1build1_amd64.deb
http://mirrors.aliyun.com/ubuntu/pool/main/libj/libjson-perl/libjson-perl_2.90-1_all.deb
http://mirrors.aliyun.com/ubuntu/pool/main/libt/libtypes-serialiser-perl/libtypes-serialiser-perl_1.0-1_all.deb
http://mirrors.aliyun.com/ubuntu/pool/main/libj/libjson-xs-perl/libjson-xs-perl_3.010-2build1_amd64.deb
http://mirrors.aliyun.com/ubuntu/pool/main/l/llvm-toolchain-6.0/libllvm6.0_6.0-1ubuntu2~16.04.1_amd64.deb
http://mirrors.aliyun.com/ubuntu/pool/main/l/lm-sensors/libsensors4_3.4.0-2_amd64.deb
http://mirrors.aliyun.com/ubuntu/pool/main/s/ssl-cert/ssl-cert_1.0.37_all.deb
http://mirrors.aliyun.com/ubuntu/pool/main/s/sysstat/sysstat_11.2.0-1ubuntu0.3_amd64.deb

创建软件包存储路径，下载软件包

user@localhost:/tmp$ mkdir ubuntu_xenial_postgresql_12_7
user@localhost:/tmp$ cd ubuntu_xenial_postgresql_12_7/
user@localhost:/tmp/ubuntu_xenial_postgresql_12_7$ ls
user@localhost:/tmp/ubuntu_xenial_postgresql_12_7$ mv /tmp/download-list-pg12 ./
user@localhost:/tmp/ubuntu_xenial_postgresql_12_7$ ls
download-list-pg12


user@localhost:/tmp/ubuntu_xenial_postgresql_12_7$ wget -i download-list-pg12
--2023-11-09 10:40:48--  https://apt-archive.postgresql.org/pub/repos/apt/pool/main/p/postgresql-13/libpq5_13.3-1.pgdg16.04+1_amd64.deb
Resolving apt-archive.postgresql.org (apt-archive.postgresql.org)... 13.32.121.36, 13.32.121.93, 13.32.121.17, ...
Connecting to apt-archive.postgresql.org (apt-archive.postgresql.org)|13.32.121.36|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 176480 (172K) [application/x-debian-package]
Saving to: ‘libpq5_13.3-1.pgdg16.04+1_amd64.deb’

libpq5_13.3-1.pgdg16.04+1_amd64. 100%[========================================================>] 172.34K   232KB/s    in 0.7s

2023-11-09 10:40:51 (232 KB/s) - ‘libpq5_13.3-1.pgdg16.04+1_amd64.deb’ saved [176480/176480]

--2023-11-09 10:40:51--  https://apt-archive.postgresql.org/pub/repos/apt/pool/main/p/pgdg-keyring/pgdg-keyring_2018.2_all.deb
Reusing existing connection to apt-archive.postgresql.org:443.
HTTP request sent, awaiting response... 200 OK
Length: 10666 (10K) [application/x-debian-package]
Saving to: ‘pgdg-keyring_2018.2_all.deb’

pgdg-keyring_2018.2_all.deb      100%[========================================================>]  10.42K  --.-KB/s    in 0s

...

查看文件列表

user@localhost:/tmp/ubuntu_xenial_postgresql_12_7$ ls
download-list-pg12                           pgdg-keyring_2018.2_all.deb
libcommon-sense-perl_3.74-1build1_amd64.deb  postgresql-12_12.7-1.pgdg16.04+1_amd64.deb
libjson-perl_2.90-1_all.deb                  postgresql-client-12_12.7-1.pgdg16.04+1_amd64.deb
libjson-xs-perl_3.010-2build1_amd64.deb      postgresql-client-common_226.pgdg16.04+1_all.deb
libllvm6.0_6.0-1ubuntu2~16.04.1_amd64.deb    postgresql-common_226.pgdg16.04+1_all.deb
libpq5_13.3-1.pgdg16.04+1_amd64.deb          ssl-cert_1.0.37_all.deb
libsensors4_3.4.0-2_amd64.deb                sysstat_11.2.0-1ubuntu0.3_amd64.deb
libtypes-serialiser-perl_1.0-1_all.deb
user@localhost:/tmp/ubuntu_xenial_postgresql_12_7$ ls *.deb|wc -l
14
user@localhost:/tmp/ubuntu_xenial_postgresql_12_7$ wc -l download-list-pg12
14 download-list-pg12

打包

cd /tmp/
tar -cvf ubuntu_xenial_postgresql_12_7.tar ubuntu_xenial_postgresql_12_7

3.安装

将下载打包的PostgreSQL离线安装包，传输到目标机器，解压，安装

tar -xvf ubuntu_xenial_postgresql_12_7.tar
cd ubuntu_xenial_postgresql_12_7
dpkg -i *.deb