Ubuntu 下编译安装 PostgreSQL

前言

最近有同学问到在 Ubuntu 下如何编译安装 PostgreSQL。这次内容就来说一下如何在 Ubuntu 操作系统中安装 PostgreSQL 数据库。那么，安装 PostgreSQL 对于 DBA 来说是再简单不过的事儿了，我们知道 PostgreSQL 在 Redhat 家族系统中有三种安装方式，分别是源码安装，rpm 方式安装和yum 方式安装，当然，rpm 安装和 yum 安装可以当做是同类型的安装，除此之外，就是使用已经编译好的二进制安装。那么对于Ubuntu操作系统来说，除了一键化使用 apt-get install 来安装和 redhat 家族安装不同以外，源码安装和二进制安装都一样。但如果使用源码安装，需要解决一些依赖。本文就是在最新版本的 Ubuntu 服务器操作系统上安装 PostgreSQL。

NOTE:当前 Ubuntu 操作系统上已经集成了最新的 PostgreSQL 安装包，版本为 12.9 版本

一 、 使用 apt-get 安装

apt-get 命令为 Ubuntu 系统管理软件的命令，利用该命令可以管理，移除，清空，检查等在 Ubuntu 上安装的软件。

1. 操作系统版本
操作系统为 Ubuntu 20.04.3 服务器操作系统

root@developer:~# lsb_release -a
No LSB modules are available.
Distributor ID:Ubuntu
Description:Ubuntu 20.04.3 LTS
Release:20.04
Codename:focal

2. 查看 Ubuntu 上可用的 PostgreSQL 包

通过 apt list 可以列出 PostgreSQL 相关的软件包

root@developer:~# apt list | grep -w  postgresql-12 | tail -1

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

postgresql-12/focal-updates,focal-security 12.9-0ubuntu0.20.04.1 amd64

3. 执行安装

postgresql-12/focal-updates,focal-security 12.9-0ubuntu0.20.04.1 amd64
        root@developer:~# apt-get install postgresql-12 -y 
        Reading package lists... Done
        Building dependency tree       
        Reading state information... Done
        The following packages were automatically installed and are no longer required:
          libpython2.7 libpython2.7-dev libpython2.7-minimal libpython2.7-stdlib
        Use 'apt autoremove' to remove them.
        The following NEW packages will be installed:
          postgresql-12
        0 upgraded, 1 newly installed, 0 to remove and 10 not upgraded.
        Need to get 0 B/13.5 MB of archives.
        After this operation, 41.1 MB of additional disk space will be used.
        Preconfiguring packages ...
        Selecting previously unselected package postgresql-12.
        (Reading database ... 158185 files and directories currently installed.)
        Preparing to unpack .../postgresql-12_12.9-0ubuntu0.20.04.1_amd64.deb ...
        Unpacking postgresql-12 (12.9-0ubuntu0.20.04.1) ...
        Setting up postgresql-12 (12.9-0ubuntu0.20.04.1) ...
        Creating new PostgreSQL cluster 12/main ...
        /usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/12/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 "en_US.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/12/main ... ok
        creating subdirectories ... ok
        selecting dynamic shared memory implementation ... posix
        selecting default max_connections ... 100
        selecting default shared_buffers ... 128MB
        selecting default time zone ... Etc/UTC
        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:

            pg_ctlcluster 12 main start

        Ver Cluster Port Status Owner    Data directory              Log file
        12  main    5432 down   postgres /var/lib/postgresql/12/main /var/log/postgresql/postgresql-12-main.log
        update-alternatives: using /usr/share/postgresql/12/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
        Processing triggers for postgresql-common (214ubuntu0.1) ...
        Building PostgreSQL dictionaries from installed myspell/hunspell packages...
          en_us
        Removing obsolete dictionary files:

4. 根据上面输出，启动 PostgreSQL 数据库集簇

root@developer:~# pg_ctlcluster 12 main start

这里需要注意一下的是，Ubuntu 版本中自带的 PostgreSQL 数据库版本对于数据库集簇管理命令封装为 pg_ctlcluster 命令。因此，该命令可以执行和 pg_ctl 类似的一些动作，如启动、停止、重启、加载等。

5. 检查进程

root@developer:~# ps -ef |grep postgres
postgres   69578       1  0 07:54 ?        00:00:00 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf
postgres   69580   69578  0 07:54 ?        00:00:00 postgres: 12/main: checkpointer   
postgres   69581   69578  0 07:54 ?        00:00:00 postgres: 12/main: background writer   
postgres   69582   69578  0 07:54 ?        00:00:00 postgres: 12/main: walwriter   
postgres   69583   69578  0 07:54 ?        00:00:00 postgres: 12/main: autovacuum launcher   
postgres   69584   69578  0 07:54 ?        00:00:00 postgres: 12/main: stats collector   
postgres   69585   69578  0 07:54 ?        00:00:00 postgres: 12/main: logical replication launcher

6. 检查端口

root@developer:~# netstat -anlp | grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      69578/postgres      
unix  2      [ ACC ]     STREAM     LISTENING     246072   69578/postgres       /var/run/postgresql/.s.PGSQL.5432

7. 登录数据库

root@developer:~# psql -U postgres -d postgres -p 5432
psql: error: FATAL:  Peer authentication failed for user "postgres"

这里在登录数据库的时候报对等认证错误，那么我们知道连接认证都是基于 pg_hba.conf 条目来配置的，因此该报错需要修改 pg_hba.conf 配置文件。

修改配置文件需要知道使用 apt-get 将文件都分发到哪个目录才可以。在 Ubuntu 系统中，通过 whereis 可找到软件安装在哪里，如下，PostgreSQL 软件安装的位置

root@developer:~# whereis  -u postgresql
postgresql: /usr/lib/postgresql /etc/postgresql /usr/share/postgresql

上面输出可以看到，软件都被安装到上面三个目录，当然，可执行程序被安装到 /usr/bin 目录下，如下

root@developer:~# cd /usr/bin/
root@developer:/usr/bin# ls -l pg*
lrwxrwxrwx 1 root root    37 Aug 24  2020 pg_archivecleanup -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root    37 Aug 24  2020 pg_basebackup -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root    37 Aug 24  2020 pgbench -> ../share/postgresql-common/pg_wrapper
-rwxr-xr-x 1 root root  9707 Aug 24  2020 pg_buildext
-rwxr-xr-x 1 root root  1229 Aug 24  2020 pg_config
-rwxr-xr-x 1 root root  6262 Aug 24  2020 pg_conftool
-rwxr-xr-x 1 root root 34684 Aug 24  2020 pg_createcluster
-rwxr-xr-x 1 root root 23919 Aug 24  2020 pg_ctlcluster
-rwxr-xr-x 1 root root  7603 Aug 24  2020 pg_dropcluster
lrwxrwxrwx 1 root root    37 Aug 24  2020 pg_dump -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root    37 Aug 24  2020 pg_dumpall -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root    37 Aug 24  2020 pg_isready -> ../share/postgresql-common/pg_wrapper
-rwxr-xr-x 1 root root  5268 Aug 24  2020 pg_lsclusters
lrwxrwxrwx 1 root root    37 Aug 24  2020 pg_receivewal -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root    37 Aug 24  2020 pg_receivexlog -> ../share/postgresql-common/pg_wrapper
lrwxrwxrwx 1 root root    37 Aug 24  2020 pg_recvlogical -> ../share/postgresql-common/pg_wrapper
-rwxr-xr-x 1 root root  5887 Aug 24  2020 pg_renamecluster
-rwxr-xr-x 1 root root 30968 Sep  9 12:59 pgrep
lrwxrwxrwx 1 root root    37 Aug 24  2020 pg_restore -> ../share/postgresql-common/pg_wrapper
-rwxr-xr-x 1 root root 33434 Aug 24  2020 pg_upgradecluster
-rwxr-xr-x 1 root root  7859 Aug 24  2020 pg_virtualenv

修改 pg_hba.conf 文件通过 unix socket 认证方式为 trust

root@developer:~# cd /etc/postgresql/12/main/
root@developer:/etc/postgresql/12/main# vi pg_hba.conf 
root@developer:/etc/postgresql/12/main# cat pg_hba.conf | grep trust
# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",
local   all             postgres                                trust

再次尝试连接，连接前需要从新加载基于主机的配置文件

root@developer:~# pg_ctlcluster reload 12 main
root@developer:~# psql -U postgres -d postgres -p 5432
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.


postgres=# 

可以看到数据库版本为 12.9 版本。

8. 使用系统命令管理数据库实例

postgresql.service 系统守护文件和 redhat 家族系统一样，依然存放在 /usr/lib/systemd/system 目录下

root@developer:/usr/lib/systemd/system# pwd
/usr/lib/systemd/system
root@developer:/usr/lib/systemd/system# ls postgresql.service 
postgresql.service

那么使用 apt-get 安装的 PostgreSQL 数据库也可以使用 systemctl 命令进行管理和维护

二、 使用源码编译安装

1. 上传安装包到 /opt/software 目录并解压

root@developer:~# cd /opt/software/
root@developer:/opt/software# ls
postgresql-13.5.tar.bz2
root@developer:/opt/software# tar -jxf postgresql-13.5.tar.bz2 
root@developer:/opt/software# ls
postgresql-13.5  postgresql-13.5.tar.bz2

2. 安装必要的依赖

apt-get install -y systemtap-sdt-dev
apt-get install -y libicu-dev
apt-get install -y libreadline-dev
apt-get install -y zlib1g-dev
apt-get install -y libssl-dev
apt-get install -y libpam-dev
apt-get install -y libxml2-dev
apt-get install -y libxslt-dev
apt-get install -y libldap-dev
apt-get install -y libsystemd-dev
apt-get install -y getext
apt-get install -y tcl-dev
apt-get install -y libpython3-dev
apt-get install -y libperl-dev

3. 切换到数据库软件解压目录，并执行编译前配置

切换目录

root@developer:~# cd /opt/software/postgresql-13.5/
root@developer:/opt/software/postgresql-13.5# 

执行编译前检查

export PREFIX="/usr/local/pg13"
export PGPORT=10000
./configure \
--prefix=${PREFIX} \
--exec-prefix=${PREFIX}/pgsql \
--bindir=${PREFIX}/pgsql/bin \
--sysconfdir=${PREFIX}/etc \
--libdir=${PREFIX}/pgsql/lib \
--includedir=${PREFIX}/include \
--datarootdir=${PREFIX}/share \
--localedir=${PREFIX}/locale \
--mandir=${PREFIX}/locale/man \
--docdir=${PREFIX}/locale/doc \
--htmldir=${PREFIX}/locale/html \
--enable-nls='en_US zh_CN' \
--with-perl \
--with-python \
--with-tcl \
--with-icu \
--with-openssl \
--with-ldap \
--with-pam \
--with-systemd \
--with-libxml \
--with-libxslt \
--with-readline \
--with-zlib \
--with-pgport=${PGPORT}

4. 执行编译和安装命令

root@developer:/opt/software/postgresql-13.5# make world -j8 && make install-world -j8

编译安装的位置如下：

root@developer:/usr/local/pg13# pwd
/usr/local/pg13
root@developer:/usr/local/pg13# ls
include  locale  pgsql  share

5. 创建操作系统用户和数据库集簇存放目录

创建 postgres 用户

root@developer:~# useradd -u 2000 -c "PostgreSQL db user" -b /home -m -k /etc/skel -s /bin/bash  postgres
root@developer:~# passwd postgres
New password: 
Retype new password: 
passwd: password updated successfully

创建数据库集簇存放目录 /data/pg13/pgdata

root@developer:~# mkdir -p /data/pg13/pgdata

授权

root@developer:~# chown postgres.postgres -R /data

6. 切换到 postgres 用户配置环境变量

root@developer:~# su - postgres
    postgres@developer:~$ vi .bashrc 
    postgres@developer:~$ tail -4 .bashrc 
    export PGHOME=/usr/local/pg13
    export PGDATA=/data/pg13/pgdata
    export LD_LIBRARY_PATH=${PGHOME}/pgsql/lib:${LD_LIBRARY_PATH}
    export PATH=${PGHOME}/pgsql/bin:${PATH}
    postgres@developer:~$ . .bashrc

7. 初始化数据库集簇

postgres@developer:~$ initdb -D $PGDATA -k 
    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 "en_US.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 enabled.

    fixing permissions on existing directory /data/pg13/pgdata ... ok
    creating subdirectories ... ok
    selecting dynamic shared memory implementation ... posix
    selecting default max_connections ... 100
    selecting default shared_buffers ... 128MB
    selecting default time zone ... Etc/UTC
    creating configuration files ... ok
    running bootstrap script ... ok
    performing post-bootstrap initialization ... ok
    syncing data to disk ... ok

    initdb: warning: enabling "trust" authentication for local connections
    You can change this by editing pg_hba.conf or using the option -A, or
    --auth-local and --auth-host, the next time you run initdb.

    Success. You can now start the database server using:

        pg_ctl -D /data/pg13/pgdata -l logfile start

8. 启动数据库

postgres@developer:~$ pg_ctl start -D $PGDATA -l /tmp/logfile
waiting for server to start.... done
server started

9. 登录数据库

postgres@developer:~$ psql -d postgres
psql (13.5)
Type "help" for help.


postgres=# select version();
                                               version                                                
------------------------------------------------------------------------------------------------------
PostgreSQL 13.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)

总结
上面两种方式为 PostgreSQL 在 Ubuntu 操作系统下的安装。需要注意的是 Ubuntu 系统中，使用 apt-get 安装的 PostgreSQL 是基于发行 PostgreSQL 的二次封装软件。因此命令和源码编译安装的会有所区别。如集簇的启动，数据库参数的修改，数据库的删除和创建，用户的创建和删除等等。