22. Databases

This chapter includes databases that range from single-user read/write to industrial database servers with transaction support. Generally, you will be sent here to satisfy dependencies to other applications although building a SQL server on a base LFS system is entirely possible.

22.1 Important Notes About Upgrading Database Server Software

Note

This section is about reinstalling database software when an existing database is in use. It is not applicable for initial installations or if there is no existing database for the package being updated, but users should read through it to become aware of issues that can arise in the future.

Let’s start this chapter with a dramatic screenshot of an error that really happened. This error will not occur if you are installing database software for the first time:

$ sudo systemctl status postgresql
-- postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Tue 2021-10-26 17:11:53 CDT; 2min 49s ago
    Process: 17336 ExecStart=/usr/bin/pg_ctl -s -D ${PGROOT}/data start -w -t 120 (code=exited, status=1/FAILURE)
        CPU: 7ms

Oct 26 17:11:53 SVRNAME systemd[1]: Starting PostgreSQL database server...
Oct 26 17:11:53 SRVNAME postgres[17338]: 2021-10-26 17:11:53.420 CDT [17338] FATAL:
                database files are incompatible with server
Oct 26 17:11:53 SRVNAME postgres[17338]: 2021-10-26 17:11:53.420 CDT [17338] DETAIL:
                The data directory was initialized by PostgreSQL version 13,
                which is not compatible with this version 14.0.
Oct 26 17:11:53 SRVNAME postgres[17336]: pg_ctl: could not start server
Oct 26 17:11:53 SRVNAME postgres[17336]: Examine the log output.
Oct 26 17:11:53 SRVNAME systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
Oct 26 17:11:53 SRVNAME systemd[1]: postgresql.service: Failed with result 'exit-code'.
Oct 26 17:11:53 SRVNAME systemd[1]: Failed to start PostgreSQL database server.

To avoid situations like this (i.e., your database server software refuses to start), read the following discussion of the best way to upgrade a DBMS (Database Management System).

The root cause of the error shown above was an upgrade of the server software to a newer major version which left the data files untouched. In this case, the administrator was able to recover the DBMS without any loss of data.

Even if you are doing an initial DBMS install, read through this section. It provides information about implementing backup and restore procedures (or at least a strategy for creating them) which will satisfy your needs and guarantee the safety of your data.

Upgrade Database Server Packages

Database systems work on files which hold the database metadata and the data itself. The internal structure of these files is optimized for use by the server software. When such server software is upgraded, the new software may utilize a different file format than had previously been used. Sometimes the new software can work with the old format as well as the new one—but without the performance improvements the new format provides. Other times, the new server software will reformat the data files automatically after the upgrade.

Unfortunately, the most likely case is that the new server software complains about out of date file formats and exits. When this happens, and you have overwritten the old server software, you may end up with a broken system and lost data.

Changes in data file formats usually happen at major version changes, but they can also occur at other times. Before upgrading any DBMS software, check the documentation to see if this upgrade makes changes which require reformatting the database.

Of course, if you have databases with content that is not easily rebuilt, it is always a good idea to create backups of the database from time to time. Before upgrading the server software, you should run another backup.

Upgrade by Backup and Restore

Note

A backup is useless if there is no verified process to restore the data from this backup. When running a database server, you should not only create backups; you should also verify that the restore process really works. The time to test the restore procedure is before you urgently need to recover lost data.

Most database server software provides some basic tools to create backups of your data. Usually the backups created with those tools can be read by newer versions of the software (via a restore tool). Using older restore tools with newer backup data is a bad idea; you should never blindly assume that it will work. It might, but usually it doesn’t.

The easiest way to upgrade your database files is to

Since you already have a backup procedure in place (and you have tested your restore procedure, right?), this might be the easiest way to upgrade as you can use your well known processes to upgrade just as you always do—at least in terms of the backup and restore.

Upgrade the Database Files by Using System Tools

Some database systems (for instance Postgresql) provide a tool which can reformat (upgrade) the existing database files to the new format. If you need to restore from a backup (for example, running the upgrade tool failed) you will have to reinstall the old software to recover your data.

Even though the reformatting tools might work as advertised, you should create a full backup before running them. A failure could cause serious damage to the database.

Notes for Specific DBMS

PostgreSQL

Upstream documentation for Backup/Restore: https://www.postgresql.org/docs/current/backup.html

MariaDB

Upstream documentation for Backup/Restore: https://mariadb.com/kb/en/backup-and-restore-overview/

Sqlite

Do not underestimate Sqlite. It is a feature-rich DBMS. The main difference from the two big players above is that Sqlite does not provide access via a network API. Sqlite databases are always stored on the machine running the program which uses the database. The manipulation of data content is done via API calls to library functions directly within the program.

In the upstream documentation you may find the following useful:

Documentation of the sqlite3 command line tool: https://www.sqlite.org/cli.html

Documentation of backup API calls: https://www.sqlite.org/backup.html

Unfortunately, there is no dedicated chapter in the upstream documentation talking about backup/restore, but there are several articles about it on the Internet. Here is an example.

Documentation for Backup/Restore: https://database.guide/backup-sqlite-database/

Berkeley DB

Like Sqlite, this software acts on local database files; there is no network interface.

The relevant resources to back up/restore a Berkeley database are the man pages for db_dump and its counterpart db_load.

22.2 Berkeley DB-5.3.28

Introduction to Berkeley DB

The Berkeley DB package contains programs and utilities used by many other applications for database related functions.

This package is known to build and work properly using an LFS 11.3 platform.

Package Information

Berkeley DB Dependencies

Optional

libnsl-2.0.0 and Sharutils-4.15.2 (for the uudecode command)

User Notes: https://wiki.linuxfromscratch.org/blfs/wiki/db

Installation of Berkeley DB

First apply a fix so that this will compile with current versions of g++:

sed -i 's/\(__atomic_compare_exchange\)/\1_db/' src/dbinc/atomic.h

Install Berkeley DB by running the following commands:

cd build_unix                        &&
../dist/configure --prefix=/usr      \
                  --enable-compat185 \
                  --enable-dbm       \
                  --disable-static   \
                  --enable-cxx       &&
make

Now, as the root user:

make docdir=/usr/share/doc/db-5.3.28 install &&

chown -v -R root:root                        \
      /usr/bin/db_*                          \
      /usr/include/db{,_185,_cxx}.h          \
      /usr/lib/libdb*.{so,la}                \
      /usr/share/doc/db-5.3.28

Command Explanations

cd build_unix && ../dist/configure –prefix=/usr…: This replaces the normal ./configure command, as Berkeley DB comes with various build directories for different platforms.

--enable-compat185: This switch enables building the DB-1.85 compatibility API.

--enable-cxx: This switch enables building C++ API libraries.

--enable-dbm: Enables legacy interface support needed by some older packages.

make docdir=/usr/share/doc/db-5.3.28 install: This installs the documentation in the standard location instead of /usr/docs.

chown -v -R root:root …:This command changes the ownership of various installed files from the uid:gid of the builder to root:root.

--enable-tcl --with-tcl=/usr/lib: Enables Tcl support in DB and creates the libdb_tcl libraries.

--enable-java: Enables Java support in DB and creates the libdb_java libraries. Support is currently broken with Java-7.x.

Contents

Installed Programs: db_archive, db_checkpoint, db_deadlock, db_dump, db_hotbackup, db_load, db_log_verify, db_printlog, db_recover, db_replicate, db_stat, db_tuner, db_upgrade, and db_verify

Installed Libraries: libdb.so, libdb_cxx.so, and libdb_tcl.so

Installed Directory: /usr/share/doc/db-5.3.28

Short Descriptions

db_archive prints the pathnames of log files that are no longer in use.

db_checkpoint is a daemon process used to monitor and checkpoint database logs.

db_deadlock is used to abort lock requests when deadlocks are detected.

db_dump converts database files to a flat file format readable by db_load.

db_hotbackup creates “hot backup” or “hot failover” snapshots of Berkeley DB databases.

db_load is used to create database files from flat files created with db_dump.

db_log_verify verifies the log files of a database.

db_printlog converts database log files to human readable text.

db_recover is used to restore a database to a consistent state after a failure.

db_replicate is a daemon process that provides replication/HA services on a transactional environment.

db_stat displays database environment statistics.

db_tuner analyzes the data in a btree database, and suggests a page size that is likely to deliver optimal operation.

db_upgrade is used to upgrade database files to a newer version of Berkeley DB.

db_verify is used to run consistency checks on database files.

22.3 lmdb-0.9.29

Introduction to lmdb

The lmdb package is a fast, compact, key-value embedded data store. It uses memory-mapped files, so it has the read performance of a pure in-memory database while still offering the persistence of standard disk-based databases, and is only limited to the size of the virtual address space

This package is known to build and work properly using an LFS 11.3 platform.

Package Information

User Notes: https://wiki.linuxfromscratch.org/blfs/wiki/lmdb

Installation of lmdb

Note

This package extracts to lmdb-LMDB_0.9.29.

Install lmdb by running the following commands:

cd libraries/liblmdb &&
make                 &&
sed -i 's| liblmdb.a||' Makefile

This package does not come with a test suite.

Now, as the root user:

make prefix=/usr install

Command Explanations

sed … liblmdb.a … Makefile: The package executables use a static library so it must be created. This command suppresses installation of the static library.

Contents

Installed Program: mdb_copy, mdb_dump, mdb_load, and mdb_stat

Installed Library: liblmdb.so

Installed Directories: None

22.4 MariaDB-10.6.12

Introduction to MariaDB

MariaDB is a community-developed fork and a drop-in replacement for the MySQL relational database management system.

This package is known to build and work properly using an LFS 11.3 platform.

Package Information

Note

The installed size of MariaDB is 658 MB, but this can be reduced by about 252 MB, if desired, by removing the /usr/share/mysql/test directory after installation.

MariaDB Dependencies

Required

CMake-3.25.2

libevent-2.1.12

Optional

Boost-1.81.0, libaio-0.3.113, libxml2-2.10.3, Linux-PAM-1.5.2, MIT Kerberos V5-1.20.1, pcre2-10.42, Ruby-3.2.1, sphinx-6.1.3, unixODBC-2.3.11, Valgrind-3.20.0, Groonga, KyTea, Judy, lz4, MeCab, MessagePack, mruby, MyRocks, Snappy, and ZeroMQ

User Notes: https://wiki.linuxfromscratch.org/blfs/wiki/mariadb

Installation of MariaDB

Warning

MariaDB and MySQL cannot be installed on the same system without extensive changes to the build configuration of one of the two applications.

For security reasons, running the server as an unprivileged user and group is strongly encouraged. Issue the following (as root) to create the user and group:

groupadd -g 40 mysql &&
useradd -c "MySQL Server" -d /srv/mysql -g mysql -s /bin/false -u 40 mysql

Install MariaDB by running the following commands:

mkdir build &&
cd    build &&

cmake -DCMAKE_BUILD_TYPE=Release                      \
      -DCMAKE_INSTALL_PREFIX=/usr                     \
      -DGRN_LOG_PATH=/var/log/groonga.log             \
      -DINSTALL_DOCDIR=share/doc/mariadb-10.6.12       \
      -DINSTALL_DOCREADMEDIR=share/doc/mariadb-10.6.12 \
      -DINSTALL_MANDIR=share/man                      \
      -DINSTALL_MYSQLSHAREDIR=share/mysql             \
      -DINSTALL_MYSQLTESTDIR=share/mysql/test         \
      -DINSTALL_PAMDIR=lib/security                   \
      -DINSTALL_PAMDATADIR=/etc/security              \
      -DINSTALL_PLUGINDIR=lib/mysql/plugin            \
      -DINSTALL_SBINDIR=sbin                          \
      -DINSTALL_SCRIPTDIR=bin                         \
      -DINSTALL_SQLBENCHDIR=share/mysql/bench         \
      -DINSTALL_SUPPORTFILESDIR=share/mysql           \
      -DMYSQL_DATADIR=/srv/mysql                      \
      -DMYSQL_UNIX_ADDR=/run/mysqld/mysqld.sock       \
      -DWITH_EXTRA_CHARSETS=complex                   \
      -DWITH_EMBEDDED_SERVER=ON                       \
      -DSKIP_TESTS=ON                                 \
      -DTOKUDB_OK=0                                   \
      .. &&
make

To test the results, issue: make test. One test, test-connect, is known to fail.

Note

A more extensive set of tests can be run with the following:

pushd mysql-test
./mtr --parallel <N> --mem --force
popd

Where N is the number of tests to run in parallel. Over 5400 tests are run in about 24 SBU with N=4. A few tests may fail, mainly due to character set issues.

Now, as the root user:

make install

Command Explanations

-DWITH_EMBEDDED_SERVER=ON: This switch enables compiling the embedded server library needed by certain applications, such as Amarok.

-DWITH_EXTRA_CHARSETS=complex: This switch enables support for the complex character sets.

-DSKIP_TESTS=ON: This switch disables tests for MariaDB Connector/C which are not supported without additional setup.

-DWITHOUT_SERVER=ON: Use this switch if you don’t want the server and would like to build the client only.

Note

There are numerous options available to cmake. Check the output of the cmake . -LH for additional customization options.

Configuring MySQL

Config Files

/etc/mysql/my.cnf and ~/.my.cnf

Configuration Information

Create basic /etc/mysql/my.cnf using the following command as the root user:

install -v -dm 755 /etc/mysql &&
cat > /etc/mysql/my.cnf << "EOF"
# Begin /etc/mysql/my.cnf

# The following options will be passed to all MySQL clients
[client]
#password       = your_password
port            = 3306
socket          = /run/mysqld/mysqld.sock

# The MySQL server
[mysqld]
port            = 3306
socket          = /run/mysqld/mysqld.sock
datadir         = /srv/mysql
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
sort_buffer_size = 512K
net_buffer_length = 16K
myisam_sort_buffer_size = 8M

# Don't listen on a TCP/IP port at all.
skip-networking

# required unique id between 1 and 2^32 - 1
server-id       = 1

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# InnoDB tables are now used by default
innodb_data_home_dir = /srv/mysql
innodb_log_group_home_dir = /srv/mysql
# All the innodb_xxx values below are the default ones:
innodb_data_file_path = ibdata1:12M:autoextend
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 128M
innodb_log_file_size = 48M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

# End /etc/mysql/my.cnf
EOF

You can now install a database and change the ownership to the unprivileged user and group (perform as the root user):

mysql_install_db --basedir=/usr --datadir=/srv/mysql --user=mysql &&
chown -R mysql:mysql /srv/mysql

Further configuration requires that the MariaDB server is running. Start the server using the following commands as the root user:

install -v -m755 -o mysql -g mysql -d /run/mysqld &&
mysqld_safe --user=mysql 2>&1 >/dev/null &

A default installation does not set up a password for the administrator, so use the following command as the root user to set one.

mysqladmin -u root password

Configuration of the server is now finished. Shut the server down using the following command as the root user:

mysqladmin -p shutdown

Systemd Unit

Install the mysqld.service unit included in the blfs-systemd-units-20220720 package as the root user to start the MariaDB server during system boot-up.

make install-mysqld

Note

If you have an existing database already and this installation of binaries was just an upgrade to a newer version, check the upstream documentation for upgrading. It is recommended to run:

mariadb-upgrade

Contents

Installed Programs: aria_chk, aria_dump_log, aria_ftdump, aria_pack, aria_read_log, aria_s3_copy, galera_new_cluster, galera_recovery, innochecksum, mariabackup, mariadb, mariadbd, mariadb-access, mariadb-admin, mariadb-backup, mariadb-binlog, mariadb-check, mariadb-client-test, mariadb-client-test-embedded, mariadb-conv, mariadb-convert-table-format, mariadb-find-rows, mariadbd-multi, mariadbd-safe, mariadbd-safe-helper, mariadb-dump, mariadb-dumpslow, mariadb-embedded, mariadb-fix-extensions, mariadb-hotcopy, mariadb-import, mariadb-install-db, mariadb-ldb, mariadb-plugin, mariadb-secure-installation, mariadb-setpermission, mariadb-service-convert, mariadb-show, mariadb-slap, mariadb-test, mariadb-test-embedded, mariadb-tzinfo-to-sql, mariadb-upgrade, mariadb-waitpid, mariadb_config, mbstream, msql2mysql, my_print_defaults, myisamchk, myisam_ftdump, myisamlog, myisampack, mysql_config, mytop, perror, replace, resolve_stack_dump, resolveip, sst_dump, wsrep_sst_common, wsrep_sst_mariabackup, wsrep_sst_mysqldump, and wsrep_sst_rsync

Installed Libraries: libmariadbclient.a, libmariadb.so, libmariadbd.so, libmysqlclient.a, libmysqlclient_r.a (symbolic links to libmariadbclient.a), libmysqld.{so,a}, libmysqlservices.a, and several under /usr/lib/mysql/plugin/

Installed Directories: /etc/mysql, /usr/{include,lib,share}/mysql, /usr/share/groonga{-normalizer-mysql}, and /usr/share/doc/mariadb-10.6.12

Short Descriptions

There are several symlinks from mysql* to their mariadb counterparts. These are maintained for compatibility.

The Perl DBI modules must be installed for some of the MariaDB support programs to function properly.

Short Descriptions

aria_chk is used to check, repair, optimize, sort and get information about Aria tables.

aria_dump_log is a tool to dump the contents of Aria log pages.

aria_ftdump displays full-text index information.

aria_pack is a tool to generate compressed, read-only Aria tables.

aria_read_log displays Aria log file contents.

aria_s3_copy copies an aria table to and from AWS S3.

galera_new_cluster bootstraps a new Galera cluster.

galera_recovery recovers data from a Galera cluster.

innochecksum prints checksums for InnoDB files.

mariabackup is an open source backup tool for InnoDB and XtraDB.

mariadb is a simple SQL shell with input line editing capabilities.

mariadbd is the MySQL server daemon.

mariadb-access checks the access privileges for a host name, user name, and database combination.

mariadb-admin is a client for performing administrative operations.

mariadb-binlog reads binary log files.

mariadb-check performs table maintenance: It checks, repairs, optimizes, or analyzes tables.

mariadb-client-test is used for testing aspects of the MySQL client API that cannot be tested using mysqltest and its test language.

mariadb-client-test-embedded is a tool to test the client API for the embedded server.

mariadb-conv converts character sets for use with MariaDB.

mariadb-convert-table-format converts the tables in a database to use a particular storage engine.

mariadbd-multi is designed to manage several mysqld processes that listen for connections on different Unix socket files and TCP/IP ports.

mariadbd-safe is the recommended way to start a mysqld server on Unix and NetWare.

mariadb-dump is a backup program.

mariadb-dumpslow parses MySQL slow query log files and prints a summary of their contents.

mariadb-embedded is a MySQL client statically linked to libmariadbd.

mariadb-find-rows reads files containing SQL statements and extracts statements that match a given regular expression or that contain USE db_name or SET statements.

mariadb-fix-extensions converts the extensions for MyISAM (or ISAM) table files to their canonical forms.

mariadb-hotcopy locks the table, flushes the table and then performs a copy of the database.

mariadb-import reads a range of data formats, and inserts the data into a database.

mariadb-install-db initializes the MySQL data directory and creates the system tables that it contains, if they do not exist.

mariadb-ldb is the RocksDB tool.

mariadb-plugin is a utility that enables MySQL administrators to manage which plugins a MySQL server loads.

mariadb-secure-installation is a tool to improve MySQL installation security.

mariadb-service-convert generates a systemd unit based on the current mariadb settings.

mariadb-setpermission sets permissions in the MySQL grant tables.

mariadb-show shows the structure of a MariaDB database.

mariadb-slap is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage.

mariadb-test runs a test case against a MySQL server and optionally compares the output with a result file.

mariadb-test-embedded is similar to the mysqltest command but is built with support for the libmysqld embedded server.

mariadb-tzinfo-to-sql loads the time zone tables in the mysql database.

mariadb-upgrade examines all tables in all databases for incompatibilities with the current version of MySQL Server.

mariadb-waitpid signals a process to terminate and waits for the process to exit.

mariadb_config gets compiler flags for using the MariaDB Connector/C.

mbstream is an utility for sending InnoDB and XTraDB backups over a stream.

msql2mysql is a tool to convert mSQL programs for use with MySQL.

my_print_defaults displays the options from option groups of option files.

myisam_ftdump displays information about FULLTEXT indexes in MyISAM tables.

myisamchk gets information about your database tables or checks, repairs, or optimizes them.

myisamlog displays MyISAM log file contents.

myisampack is a tool for compressing MyISAM tables.

mysql_config provides you with useful information for compiling your MySQL client and connecting it to MySQL.

mytop is a console-based tool for monitoring the threads and overall performance of a MySQL server.

perror is a utility that displays descriptions for system or storage engine error codes.

replace is a MariaDB/MySQL extension to the SQL standard.

resolve_stack_dump resolves a numeric stack dump to symbols.

resolveip is a utility for resolving IP addresses to host names and vice versa.

sst_sump dumps the content of sst files (the format used by RocksDB).

22.5 PostgreSQL-15.2

Introduction to PostgreSQL

PostgreSQL is an advanced object-relational database management system (ORDBMS), derived from the Berkeley Postgres database management system.

This package is known to build and work properly using an LFS 11.3 platform.

Package Information

PostgreSQL Dependencies

Optional

ICU-72.1, libxml2-2.10.3, libxslt-1.1.37, OpenLDAP-2.6.4, Linux-PAM-1.5.2, MIT Kerberos V5-1.20.1 and Bonjour

Optional (To Regenerate Documentation)

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

User Notes: https://wiki.linuxfromscratch.org/blfs/wiki/postgresql

Installation of 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

Note

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

Install PostgreSQL with the following commands:

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-15.2 &&
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.

Note

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

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

Now, as the root user:

make install      &&
make install-docs

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

make -C contrib/<SUBDIR-NAME> install

Tip

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.

Initialize a database cluster with the following commands issued by the root user:

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'

Command Explanations

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.

Configuring PostgreSQL

Config Files

$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-15.2/html/index.html.

Systemd Unit

Install the postgresql.service unit included in the blfs-systemd-units-20220720 package:

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 &'

Note

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"

Contents

Installed Programs: 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, postmaster (deprecated), 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

Installed Libraries: 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

Installed Directories: /usr/include/{libpq,postgresql}, /usr/lib/postgresql, /usr/share/{doc/postgresql-15.2,postgresql}, and /srv/pgsql

Short Descriptions

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.

postmaster (deprecated, a symlink to postgres) is a multi-user database daemon.

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.

22.6 SQLite-3.40.1

Introduction to SQLite

The SQLite package is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

This package is known to build and work properly using an LFS 11.3 platform.

Package Information

Additional Downloads

Optional Documentation

SQLite Dependencies

Optional

libedit and UnZip-6.0 (required to unzip the documentation)

User Notes: https://wiki.linuxfromscratch.org/blfs/wiki/sqlite

Installation of SQLite

If you downloaded the optional documentation, issue the following command to install the documentation into the source tree:

unzip -q ../sqlite-doc-3400100.zip

Install SQLite by running the following commands:

./configure --prefix=/usr     \
            --disable-static  \
            --enable-fts5     \
            CPPFLAGS="-DSQLITE_ENABLE_FTS3=1            \
                      -DSQLITE_ENABLE_FTS4=1            \
                      -DSQLITE_ENABLE_COLUMN_METADATA=1 \
                      -DSQLITE_ENABLE_UNLOCK_NOTIFY=1   \
                      -DSQLITE_ENABLE_DBSTAT_VTAB=1     \
                      -DSQLITE_SECURE_DELETE=1          \
                      -DSQLITE_ENABLE_FTS3_TOKENIZER=1" &&
make

This package does not come with a test suite.

Now, as the root user:

make install

If you downloaded the optional documentation, issue the following commands as the root user to install it:

install -v -m755 -d /usr/share/doc/sqlite-3.40.1 &&
cp -v -R sqlite-doc-3400100/* /usr/share/doc/sqlite-3.40.1

Command Explanations

--disable-static: This switch prevents installation of static versions of the libraries.

--enable-fts5: This switch enables support for version 5 of the full text search extension.

CPPFLAGS="-DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_FTS3_TOKENIZER=1 -DSQLITE_ENABLE_FTS4=1 -DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_SECURE_DELETE -DSQLITE_ENABLE_UNLOCK_NOTIFY=1 -DSQLITE_ENABLE_DBSTAT_VTAB=1": Applications such as SeaMonkey require these options to be turned on. The only way to do this is to include them in the CFLAGS or CPPFLAGS. We use the latter so the default value (or any value set by the user) of CFLAGS won’t be affected. For further information on what can be specified see https://www.sqlite.org/compile.html.

Contents

Installed Program: sqlite3

Installed Library: libsqlite3.so

Installed Directory: /usr/share/doc/sqlite-3.40.1

Short Descriptions

sqlite3 is a terminal-based front-end to the SQLite library that can evaluate queries interactively and display the results.

libsqlite3.so contains the SQLite API functions.