Beyond Linux® From Scratch (systemd 版) - Version r12.0-1055+

第22章 データベース

PostgreSQL-16.1

PostgreSQL の概要

PostgreSQL は先進的なオブジェクトリレーショナルデータベース管理システム (object-relational database management system; ORDBMS) です。 これは Berkeley Postgres データベース管理システムからの派生です。

[注記]

注記

LFS や依存パッケージが本ブックに示す最新安定バージョンでなかった場合には、BLFS 開発版においては、パッケージのビルドや処理実行が適切に行われないことがあります。

パッケージ情報

  • ダウンロード (HTTP): https://ftp.postgresql.org/pub/source/v16.1/postgresql-16.1.tar.bz2

  • ダウンロード MD5 sum: 9cbfb9076ed06384471802b850698a6d

  • ダウンロードサイズ: 23 MB

  • 必要ディスク容量: 245 MB （テスト実施時はさらに 25 MB）

  • 概算ビルド時間: 0.8 SBU （parallelism=4; テスト実施時はさらに 0.2 SBU）

PostgreSQL の依存パッケージ

任意

ICU-74.1, libxml2-2.12.3, libxslt-1.1.39, OpenLDAP-2.6.6, Linux-PAM-1.5.3, MIT Kerberos V5-1.21.2, Bonjour

任意 (ドキュメント再生成時)

fop-2.9, docbook-4.5-dtd, docbook-dsssl-1.79, DocBook-utils-0.6.14, OpenJade-1.3.2, SGMLSpm-1.1

編集ノート: https://wiki.linuxfromscratch.org/blfs/wiki/postgresql

PostgreSQL のインストール

For enhanced security, it is better to have a dedicated group and user for running the PostgreSQL server. First, issue as the root user: 

groupadd -g 41 postgres &&
useradd -c "PostgreSQL Server" -g postgres -d /srv/pgsql/data \
        -u 41 postgres
[注記]

注記

There are several configuration items that add additional functionality with optional packages to PostgreSQL. Use ./configure --help to see a list.

以下のコマンドを実行して PostgreSQL をビルドします。 

sed -i '/DEFAULT_PGSOCKET_DIR/s@/tmp@/run/postgresql@' src/include/pg_config_manual.h &&

./configure --prefix=/usr          \
            --enable-thread-safety \
            --docdir=/usr/share/doc/postgresql-16.1 &&
make

There are a number of programs in the contrib/ directory. If you are going to run this installation as a server and wish to build some of them, enter make -C contrib or make -C contrib/<SUBDIR-NAME> for each subdirectory.

Tests must be run as an unprivileged user because they need to start a temporary server and this is prevented as the root user. For the same reason, you need to stop all PostgreSQL servers if any are running. If a previous version of PostgreSQL is installed, it may be necessary to use --disable-rpath with configure to avoid failures, but installing the binaries created using this switch is not recommended. To test the results, issue: make check.

[注記]

注記

If you are installing PostgreSQL to upgrade an existing installation, there are important steps that you need to follow. If the major version of the new build is greater than the previous version, there is a chance that the data file format has changed. new software cannot act on the existing data files. In this case, the server will not start because the old programs have been overwritten, so the data is unavailable until it's file format has been converted.

Before upgrading an existing installation of PostgreSQL, check the documentation for any considerations that you must keep in mind during the upgrade. Note that new major versions might use a different binary format in the data objects, causing potential incompatibilities. For more information, check out upstream's documentation about upgrading PostgreSQL here. https://www.postgresql.org/docs/current/upgrading.html.

At this point, you may have both the old and the new binaries installed on your filesystem. These binaries can be used to perform an upgrade of your existing database files. For the following instructions it is assumed that

  • The actual data files are stored in /srv/pgsql/data

  • The upgraded data files will be stored in /srv/pgsql/newdata

  • There is enough disk space to hold the actual data files twice. The upgrade is not an inline upgrade but it will copy the data to new database files.

First, do a temporary install which makes access to the new binaries much easier: 

make DESTDIR=$(pwd)/DESTDIR install

Next, create a directory which is writable by the postgres user, as the root user: 

install -d -o postgres $(pwd)/DESTDIR/tmp

Now, stop the existing instance of PostgreSQL and start the upgrade process as the root user: 

pushd $(pwd)/DESTDIR/tmp
systemctl stop postgresql
su postgres -c "../usr/bin/initdb -D /srv/pgsql/newdata"
su postgres -c "../usr/bin/pg_upgrade \
                    -d /srv/pgsql/data    -b /usr/bin \
                    -D /srv/pgsql/newdata -B ../usr/bin"
popd

At this point, your database files are available in two locations on disk. The old data is located in /srv/pgsql/data, and the new data is in /srv/pgsql/newdata. Backing up the old database files is suggested before continuing.

Next, remove the old database files, and rename the new data directory as the root user: 

rm -rf /srv/pgsql/data
mv /srv/pgsql/newdata /srv/pgsql/data

root ユーザーになって以下を実行します。 

make install      &&
make install-docs

If you made any of the contrib/ programs, as the root user: 

make -C contrib/<SUBDIR-NAME> install
[ヒント]

ヒント

If you only intend to use PostgreSQL as a client to connect to a server on another machine, your installation is complete and you should not run the remaining commands.

If you have upgraded an existing database, skip the rest of the commands because your database is ready to use. If this is the first time you install PostgreSQL, continue with the initialization.

root ユーザーとなり、以下のコマンドを実行してデータベースクラスターを初期化します。 

install -v -dm700 /srv/pgsql/data &&
install -v -dm755 /run/postgresql &&
chown -Rv postgres:postgres /srv/pgsql /run/postgresql

Now, initialize the database as the root user: 

su - postgres -c '/usr/bin/initdb -D /srv/pgsql/data'

コマンド説明

sed -i ...: This sed changes the server socket location from /tmp to /run/postgresql.

--enable-thread-safety: This switch makes the client libraries thread-safe by allowing concurrent threads in libpq and ECPG programs to safely control their private connection handles.

--with-openssl: builds the package with support for OpenSSL encrypted connections.

--with-perl: builds the PL/Perl server-side language.

--with-python: builds the PL/Python server-side language. Python3 is used by default, Python2 is no longer supported.

--with-tcl: builds the PL/Tcl server-side language.

PostgreSQL の設定

設定ファイル

$PGDATA/pg_ident.con, $PGDATA/pg_hba.conf, and $PGDATA/postgresql.conf

The PGDATA environment variable is used to distinguish database clusters from one another by setting it to the value of the directory which contains the cluster desired. The three configuration files exist in every PGDATA/ directory. Details on the format of the files and the options that can be set in each can be found in /usr/share/doc/postgresql-16.1/html/index.html.

Systemd Unit

blfs-systemd-units-20231205 パッケージに含まれる ユニット postgresql.service をインストールします。 

make install-postgresql

Starting the PostgreSQL Server and Creating a Sample Database

The database server can be manually started with the following command (as the root user): 

su - postgres -c '/usr/bin/postgres -D /srv/pgsql/data > \
                  /srv/pgsql/data/logfile 2>&1 &'
[注記]

注記

If you are scripting this part, you should wait for the server to start before going on, by adding for example sleep 2 after the above command.

The instructions below show how to create a database, add a table to it, insert some rows into the table and select them, to verify that the installation is working properly. Still as user root, issue: 

su - postgres -c '/usr/bin/createdb test' &&
echo "create table t1 ( name varchar(20), state_province varchar(20) );" \
    | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Billy', 'NewYork');" \
    | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Evanidus', 'Quebec');" \
    | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Jesse', 'Ontario');" \
    | (su - postgres -c '/usr/bin/psql test ') &&
echo "select * from t1;" | (su - postgres -c '/usr/bin/psql test')

When you are done with testing, you can shut down the server, by issuing as root: 

su - postgres -c "/usr/bin/pg_ctl stop -D /srv/pgsql/data"

パッケージ構成

インストールプログラム: clusterdb, createdb, createuser, dropdb, dropuser, ecpg, initdb, pg_amcheck, pg_archivecleanup, pg_basebackup, pg_checksums, pg_config, pg_controldata, pg_ctl, pg_dump, pg_dumpall, pg_isready, pg_receivewal, pg_recvlogical, pg_resetwal, pg_restore, pg_rewind, pg_test_fsync, pg_test_timing, pg_upgrade, pg_verifybackup, pg_waldump, pgbench, postgres, psql, reindexdb, vacuumdb, optionally, if Tcl support has been built, pltcl_delmod, pltcl_listmod, pltcl_loadmod, and optionally (in contrib/) oid2name, pg_standby, vacuumlo, and many others
インストールライブラリ: libecpg.{so,a}, libecpg_compat.{so,a}, libpgcommon.a, libpgcommon_shlib.a, libpgfeutils.a, libpgport.a, libpgport_shlib.a, libpgtypes.{so,a}, libpq.{so,a}, various charset modules and optionally programming language modules under /usr/lib/postgresql
インストールディレクトリ: /usr/include/{libpq,postgresql}, /usr/lib/postgresql, /usr/share/{doc/postgresql-16.1,postgresql}, /srv/pgsql

概略説明

clusterdb

is a utility for reclustering tables in a PostgreSQL database

createdb

creates a new PostgreSQL database

createuser

defines a new PostgreSQL user account

dropdb

removes a PostgreSQL database

dropuser

removes a PostgreSQL user account

ecpg

is the embedded SQL preprocessor

initdb

creates a new database cluster

oid2name

resolves OIDs (Object IDs) and file nodes in a PostgreSQL data directory

pg_amcheck

checks for corruption in one or more PostgreSQL databases

pg_archivecleanup

cleans up PostgreSQL WAL (write-ahead log) archive files

pg_basebackup

takes base backups of a running PostgreSQL cluster

pg_checksums

enables, disables, or checks data checksums in a PostgreSQL database cluster

pg_config

retrieves PostgreSQL version information

pg_controldata

returns information initialized during initdb, such as the catalog version and server locale

pg_ctl

controls stopping and starting the database server

pg_dump

dumps database data and metadata into scripts which are used to recreate the database

pg_dumpall

recursively calls pg_dump for each database in a cluster

pg_isready

checks the connection status of a PostgreSQL server

pg_receivewal

is used to stream write-ahead logs from a PostgreSQL server

pg_recvlogical

controls PostgreSQL logical decoding streams

pg_resetwal

resets the write-ahead log and other control information of a PostgreSQL database cluster

pg_restore

creates databases from dump files created by pg_dump

pg_rewind

synchronizes a PostgreSQL data directory with another data directory that was forked from the first one

pg_standby

supports the creation of a PostgreSQL warm standby server

pg_test_fsync

determines the fastest wal_sync method for PostgreSQL

pg_test_timing

measures timing overhead

pg_upgrade

upgrades a PostgreSQL server instance

pg_verifybackup

verifies the integrity of a base backup of a PostgreSQL cluster

pg_waldump

displays a human-readable rendering of the write-ahead log of a PostgreSQL database cluster

pgbench

runs a benchmark test on PostgreSQL

pltcl_delmod

is a support script used to delete a module from a PL/Tcl table. The command requires the Pgtcl package to be installed

pltcl_listmod

is a support script used to list the modules in a PL/Tcl table. The command requires the Pgtcl package to be installed

pltcl_loadmod

is a support script used to load a module into a PL/Tcl table. The command requires the Pgtcl package to be installed too

postgres

is the PostgreSQL database server

psql

is a console based database shell

reindexdb

is a utility for rebuilding indexes in a database

vacuumdb

compacts databases and generates statistics for the query analyzer

vacuumlo

removes orphaned large objects from a PostgreSQL database

libecpg.{so,a}

contains functions to support embedded SQL in C programs

libecpg_compat.{so,a}

is the ecpg compatibility library

libgport.a

is the port-specific subsystem of the Postgres backend

libpgtypes.{so,a}

contains functions for dealing with Postgres data types

libpq.{so,a}

is the C programmer's API to Postgres