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 12.1
platform.
Package Information
PostgreSQL Dependencies
Optional
ICU-74.2, libxml2-2.12.5, libxslt-1.1.39, OpenLDAP-2.6.7,
Linux-PAM-1.6.0, MIT
Kerberos V5-1.21.2 and Bonjour
Optional (To Regenerate Documentation)
fop-2.9,
docbook-4.5-dtd, docbook-dsssl-1.79, DocBook-utils-0.6.14, OpenJade-1.3.2, and SGMLSpm-1.1
Editor 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-16.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
-
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
/etc/rc.d/init.d/postgresql stop
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-16.2/html/index.html
.
Boot Script
Install the /etc/rc.d/init.d/postgresql
init script
included in the blfs-bootscripts-20240209 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"