Introduction to PostgreSQL
PostgreSQL is an advanced
object-relational database management system (ORDBMS), derived
from the Berkeley Postgres database management system.
![[Note]](../images/note.png)
Note
Development versions of BLFS may not build or run
some packages properly if LFS or dependencies have been updated
since the most recent stable versions of the books.
Package Information
PostgreSQL Dependencies
Optional
ICU-74.1,
libxml2-2.12.1,
libxslt-1.1.39,
OpenLDAP-2.6.6,
Linux-PAM-1.5.3,
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 postgresNote
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.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.
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
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"
popdAt 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]](../images/tip.png)
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.1/html/index.html.
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"