[CENTOS] Build PostgreSQL from source

Introduction

I talked about PostgreSQL in my most recent project. I think it's been about 5 years since I haven't touched Postgre. I don't remember clearly, but I think the last one I used was the 9 series. I thought I'd go home and look it up, but I was wondering where to look it up. Let's build from source! I decided to share what I did. This time I built it on Ubuntu 18.04. By the way, I pulled the Ubuntu image with Docker. Docker is indebted to me on a daily basis.

Added on March 8: </ font> Added how to build on ** CentOS **. I pulled Centos: 7 with Docker as above.

The way to build Ubuntu and CentOS is exactly the same. The only difference is the command for the first package installation.

PRE INSTALLATION

① Update the package list

First, update the DB that manages the package. (The package itself is not updated.)

Ubuntu.command


root@2b4511afa430:/# apt-get update
Get:1 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]
Get:2 http://archive.ubuntu.com/ubuntu bionic InRelease [242 kB]
Get:3 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]
....
....
  • CentOS will be skipped.

② Install the build library

The libraries required for the build are described on the following page. https://www.postgresql.org/docs/12/install-requirements.html

  • Since it is 12.2 to build this time, it is the above URL. You can install it by referring to the above site, but I put it all at once with the following one command.

Ubuntu.command


# apt-get install build-essential

CentOS.command


# yum groupinstall "Development Tools"

There are two other important packages. It is possible to build without using them, but on the other hand, some useful functions will not be available.

  1. readline: A tool that displays a history of commands when you press an arrow key
  2. zlib: Tools used by pg_dump and pg_restore to compress data

Ubuntu.command


# apt-get install libreadline-dev zlib1g-dev

CentOS.command


# yum install readline-devel zlib-devel

③ Download source

Download the source for the version you want to build from the link below. https://www.postgresql.org/ftp/source/ This time, I will use 12.2. Download and unzip with the following command.

# cd /usr/local/src/

# wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.gz

# tar zxf postgresql-12.2.tar.gz
                                                                                                                                   
  • If "wget" does not exist, install it with the following command.

Ubuntu.command


# apt-get install wget

CentOS.command


# yum install wget

INSTALLATION It's finally time to build. ① configure This time, we will prepare a separate directory for building. This way, if something goes wrong, you can delete that directory and rebuild it so that the previous data doesn't get in the way.

root@2b4511afa430:/usr/local/src# cd postgresql-12.2
root@2b4511afa430:/usr/local/src/postgresql-12.2# mkdir tmp_build_dir
root@2b4511afa430:/usr/local/src/postgresql-12.2# cd tmp_build_dir/
root@2b4511afa430:/usr/local/src/postgresql-12.2/tmp_build_dir#  

Run the following command from this location:

/usr/local/src/postgresql-12.2/configure --prefix=/opt/postgresql12

** --prefix ** will appear later, but important files such as executable files and library files will be installed in the directory specified here.

  • If you want to know more, please google with "--prefix".

What we are doing here is running the "configure" file in the source directory and checking the environment variables and libraries needed for the installation. As a result of checking, MakeFile is created.

② make Next, compile based on the MakeFile created by configure.

# make

It will take some time, but it is OK if the following message appears at the end.

abridgement
make[1]: Entering directory '/usr/local/src/postgresql-12.2/tmp_build_dir/config'
make[1]: Nothing to be done for 'all'.
make[1]: Leaving directory '/usr/local/src/postgresql-12.2/tmp_build_dir/config'
All of PostgreSQL successfully made. Ready to install.

③ install Then, install the one compiled above in the directory specified by **--prefix **.

# make install

Finally, if you see the following message, it's OK.

abridgement
/usr/bin/install -c -m 755 /usr/local/src/postgresql-12.2/config/missing '/opt/postgresql12/lib/pgxs/config/missing'
make[1]: Leaving directory '/opt/temp/build_dir/config'
PostgreSQL installation complete.

The contents of the installation destination are as follows.

postgresql12/
|-- bin
|-- include
|-- lib
`-- share

POST INSTALLATION

① Add to environment variable

Add the directory containing the executable files to your PATH so that each tool can be run from anywhere. ](Https://qiita.com/dakc/items/e26d7540c98e59c6b101)

export PATH=/opt/postgresql12/bin:$PATH

The above method is temporary. When you finish the timing, the PATH value returns to the original value. If you want it to always be remembered, you need to add the above command to the file that will be executed at startup. For example, ~ / .bash_profile, / etc / profile ,,

① Add user

The Official Site recommends limiting access to DB data to only one dedicated user. ..

As with any server daemon that is accessible to the outside world, it is advisable to run PostgreSQL under a separate user account. This user account should only own the data that is managed by the server, and should not be shared with other daemons.

Add a new user "postgres".

# useradd postgres

A home directory for the "postgres" user is created under / home. If it is not created, delete it with ** userdel postgres ** and specify your home directory as shown below.


# useradd --create-home --home-dir /home/posgtres postgres
# ls -al /home
total 12
drwxr-xr-x 1 root     root     4096 Mar  8 04:32 .
drwxr-xr-x 1 root     root     4096 Mar  8 04:12 ..
drwx------ 2 postgres postgres 4096 Mar  8 04:32 posgtres
  • Create a "data" directory directly under this home directory and save the DB data. I think that "/ var / lib / pgsql / data" and "/ usr / local / pgsql / data" are common. One thing to note here is ** ownership **. As mentioned above, authorize only the "postgres" user so that it cannot be accessed by other users.

② Data initialization

Then, prepare the data and configuration files necessary to start the database server. The Official Site carefully states as follows.

Before you can do anything, you must initialize a database storage area on disk. We call this a database cluster. (The SQL standard uses the term catalog cluster.) A database cluster is a collection of databases that is managed by a single instance of a running database server. After initialization, a database cluster will contain a database named postgres, which is meant as a default database for use by utilities, users and third party applications. The database server itself does not require the postgres database to exist, but many external utility programs assume it exists. Another database created within each cluster during initialization is called template1. As the name suggests, this will be used as a template for subsequently created databases; it should not be used for actual work.

This process is executed by the "postgres" user created earlier.

# su postgres

There are other ways to initialize the data, but I'm going to use "pg_ctl". You can do the following with this ** one command **. ・ Database initialization ・ Start the server ・ Server stop ・ Restart the server And so on First, execute the initialization process with the following command. **-pgdata ** specifies the directory where the data will be created. It will be created even if the specified folder does not exist, but the "postgres" user must have create permission. This time, "data" is specified directly under "/ home / postgres". If you check the permissions when you created the user above, you can see that "/ home / postgres" has all permissions (read, write, execute).

$ pg_ctl --pgdata=/home/posgtres/data initdb
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
...
...abridgement
Success. You can now start the database server using:

    /opt/postgresql12/bin/pg_ctl -D /home/posgtres/data -l logfile start
  • You can also write --pgdata as -D.

③ Start the server

As you may have already noticed, the command to start the DB server is actually displayed at the very end when the initialization process was performed earlier. Except for the ** -l parameter **, the initialization command is exactly the same. However, the last parameter is "start" instead of "initdb". Specify the log file for -l or --log. There is one point to note here. The "postgres" user must have permission to create log files in the path specified here. Taking these into consideration, execute the following command to start the DB server.

$ pg_ctl --pgdata=/home/posgtres/data --log=/home/posgtres/db.log start
waiting for server to start.... done
server started
  • By the way, the server will be started even if you do not specify -l or --log.

④ Stop the server

It's easy to stop the DB server. Just specify stop at the end.

$ pg_ctl --pgdata=/home/posgtres/data stop

pg_ctl is useful for other things besides starting and stopping. See help for more details.

[postgres@af6184923e89 /]$ pg_ctl --help
pg_ctl is a utility to initialize, start, stop, or control a PostgreSQL server.

Usage:
  pg_ctl init[db]   [-D DATADIR] [-s] [-o OPTIONS]
  pg_ctl start      [-D DATADIR] [-l FILENAME] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-p PATH] [-c]
  pg_ctl stop       [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
  pg_ctl restart    [-D DATADIR] [-m SHUTDOWN-MODE] [-W] [-t SECS] [-s]
                    [-o OPTIONS] [-c]
  pg_ctl reload     [-D DATADIR] [-s]
  pg_ctl status     [-D DATADIR]
  pg_ctl promote    [-D DATADIR] [-W] [-t SECS] [-s]
  pg_ctl logrotate  [-D DATADIR] [-s]
  pg_ctl kill       SIGNALNAME PID

...abridgement

⑤ Connection to database

First, start the DB server by referring to (3) above.

$ pg_ctl -D /home/posgtres/data -l /home/posgtres/db.log start
waiting for server to start.... done
server started

Here, the "psql" command is added. It seems to be called a PostgreSQL interactive terminal, but you can think of it as a tool that uses the terminal to query the database and display the results. A DB named "postgres" will be created when the database is initialized. Just run the psql command to connect to this DB. Again, one thing to keep in mind is that you need to switch to the "posgres" user and run it.

$ psql
psql (12.2)
Type "help" for help.

postgres=#

  • Although the parameters are omitted in the above, various specifications can be specified as follows. Try running ** psql --help **.
$ psql --host=localhost --port=5432 --username=postgres --no-password --dbname=postgres

To exit psql, enter "\ q" and press enter.

$ psql
psql (12.2)
Type "help" for help.

postgres=# \q
$

Finally

I wrote PostgreSQL from build to how to connect to the server. I hope it will be helpful for you.

Recommended Posts

Build PostgreSQL from source
Install PostgreSQL from source code on CentOS
Install python from source
Install Apache 2.4.41 from source
Install ansible from source code
SciPy 1.4 source build requires pybind11
Docker Engine --CE source build
Install Apache Maven (from source)
Install Python from source with Ansible
Compile and install Git from source.
[Note] Get data from PostgreSQL with Python
Flow from source code to creating executable