PostgreSQL is an open source Object-Relational Database Management System (ORDBMS), based on the original POSTGRE source code from the University of California, Berkeley, which supports most SQL standards and offers many modern features.

PostgreSQL uses a C/S architecture. The server-side process (called postgres) manages the database files, receives connections from the client, and performs database operations on behalf of the client; the client-side process initiates connections to the server and sends database operation commands. The server and client processes can be located on the same host or on different hosts, and if they are located on different hosts, they communicate over TCP/IP. The PostgreSQL server can handle concurrent connections from clients at the same time. This is achieved by starting a new process for each connection, and the new process does not affect the work of the original postgres process.

In this article, we will do a source installation of PostgreSQL 13.3 on a CentOS 7.6 host and use it briefly.

1 Hosting Requirements

PostgreSQL can be run on most modern Unix-compatible platforms, and this host CentOS 7.6 meets the requirements.

The following packages are required to build PostgreSQL.

a) GNU make version 3.80+

1
2
$ make --version
GNU Make 3.82
1
2
$ gcc --version
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44)

c) tar for decompressing the source code zip file

1
2
$ tar --version
tar (GNU tar) 1.26

d) GNU Readline Library

is used by the PostgreSQL command line tool psql to record each command typed, which can then be reused using the arrow keys.

1
2
3
$ rpm -qa | grep readline
readline-devel-6.2-11.el7.x86_64
readline-6.2-11.el7.x86_64

e) zlib compression library

Supports pg_dump and pg_restore compression archives.

1
2
3
$ rpm -qa | grep zlib
zlib-devel-1.2.7-18.el7.x86_64
zlib-1.2.7-18.el7.x86_64

2 PostgreSQL installation

Before the following command is executed, the current user is the non-root sudoer account larry.

1
2
$ whoami
larry

a) Get the source code zip file

Go to the user root directory, download the PostgreSQL 13.3 source code zip file, and unzip it to the current directory when you are done.

1
2
3
$ cd /home/larry
$ wget https://ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz
$ tar -zxvf postgresql-13.3.tar.gz

b) Configure, build, test, and install

After the previous step, a directory postgresql-13.3 will be created in the current directory, which you can enter to configure, build, test, and install.

1
2
3
4
5
$ cd /home/larry/postgresql-13.3
$ ./configure       # 源码树配置及依赖变量检查
$ make all          # 构建
$ make check        # 回归测试
$ sudo make install # 使用root权限进行安装,因默认会安装到/usr/local/pgsql

Check the installation directory /usr/local/pgsql/ and find that it contains several folders.

1
2
$ ls /usr/local/pgsql/
bin  include  lib  share

c) Configure the data directory and start

It is recommended to run PostgreSQL with a separate account (postgres) that only has access to the data managed by the server (in particular, this account should not have access to the PostgreSQL executables either, in case they are tampered with by infected service processes) and does not share data with other daemons.

The following command creates a new account postgres with the current sudoer user larry, creates a new /pusr/local/pgsql/data data folder and gives control permissions to postgres.

1
2
3
$ sudo adduser postgres
$ sudo mkdir /usr/local/pgsql/data
$ sudo chown postgres /usr/local/pgsql/data

Next, switch the user to postgres, initialize the database, and start the service.

1
2
3
4
$ sudo su - postgres
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data # 初始化数据库
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l server.log start # 启动服务,并指定日志输出文件server.log
server started

At this point, the PostgreSQL service has been started.

d) Setting up boot-up

Edit the /etc/rc.d/rc.local file with root privileges and add the startup command.

1
$ sudo vi /etc/rc.d/rc.local
1
su - postgres -c 'cd /home/postgres && /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l server.log start'

3 Simple use of PostgreSQL

Create a database test and use the PostgreSQL interactive command line program psql to test the connection.

1
2
$ sudo su - postgres
$ /usr/local/pgsql/bin/createdb test
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
$ /usr/local/pgsql/bin/psql test
psql (13.3)
Type "help" for help.

test=# SELECT version();
                                                 version
--------------------------------------------------------------------------------------------------------
PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

test=# \q

At this point, we have finished installing and testing the source code for PostgreSQL.