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 -wpostgresql-12 | tail -1WARNING: 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 ... okSuccess. You can now start the database server using:pg_ctlcluster 12 main startVer Cluster Port Status OwnerData directoryLog file 12main5432 downpostgres /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 postgres6957810 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 postgres69580695780 07:54 ?00:00:00 postgres: 12/main: checkpointer postgres69581695780 07:54 ?00:00:00 postgres: 12/main: background writer postgres69582695780 07:54 ?00:00:00 postgres: 12/main: walwriter postgres69583695780 07:54 ?00:00:00 postgres: 12/main: autovacuum launcher postgres69584695780 07:54 ?00:00:00 postgres: 12/main: stats collector postgres69585695780 07:54 ?00:00:00 postgres: 12/main: logical replication launcher

6. 检查端口

root@developer:~# netstat -anlp | grep 5432 tcp00 127.0.0.1:54320.0.0.0:*LISTEN69578/postgres unix2[ ACC ]STREAMLISTENING24607269578/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 root37 Aug 242020 pg_archivecleanup -> ../share/postgresql-common/pg_wrapper lrwxrwxrwx 1 root root37 Aug 242020 pg_basebackup -> ../share/postgresql-common/pg_wrapper lrwxrwxrwx 1 root root37 Aug 242020 pgbench -> ../share/postgresql-common/pg_wrapper -rwxr-xr-x 1 root root9707 Aug 242020 pg_buildext -rwxr-xr-x 1 root root1229 Aug 242020 pg_config -rwxr-xr-x 1 root root6262 Aug 242020 pg_conftool -rwxr-xr-x 1 root root 34684 Aug 242020 pg_createcluster -rwxr-xr-x 1 root root 23919 Aug 242020 pg_ctlcluster -rwxr-xr-x 1 root root7603 Aug 242020 pg_dropcluster lrwxrwxrwx 1 root root37 Aug 242020 pg_dump -> ../share/postgresql-common/pg_wrapper lrwxrwxrwx 1 root root37 Aug 242020 pg_dumpall -> ../share/postgresql-common/pg_wrapper lrwxrwxrwx 1 root root37 Aug 242020 pg_isready -> ../share/postgresql-common/pg_wrapper -rwxr-xr-x 1 root root5268 Aug 242020 pg_lsclusters lrwxrwxrwx 1 root root37 Aug 242020 pg_receivewal -> ../share/postgresql-common/pg_wrapper lrwxrwxrwx 1 root root37 Aug 242020 pg_receivexlog -> ../share/postgresql-common/pg_wrapper lrwxrwxrwx 1 root root37 Aug 242020 pg_recvlogical -> ../share/postgresql-common/pg_wrapper -rwxr-xr-x 1 root root5887 Aug 242020 pg_renamecluster -rwxr-xr-x 1 root root 30968 Sep9 12:59 pgrep lrwxrwxrwx 1 root root37 Aug 242020 pg_restore -> ../share/postgresql-common/pg_wrapper -rwxr-xr-x 1 root root 33434 Aug 242020 pg_upgradecluster -rwxr-xr-x 1 root root7859 Aug 242020 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", localallpostgrestrust

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

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.5postgresql-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 includelocalepgsqlshare

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

root@developer:~# useradd -u 2000 -c "PostgreSQL db user" -b /home -m -k /etc/skel -s /bin/bashpostgres 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=https://www.it610.com/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 ... okinitdb: 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)

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