Index: server/databases/databases.xml
===================================================================
--- server/databases/databases.xml (revision edd2feb7a88ec805bbeb59f878e383cf14e80931)
+++ server/databases/databases.xml (revision bcdfc536ebeae6aa0c05246ba118aa5b16eead01)
@@ -19,4 +19,6 @@
+
+
Index: server/databases/postgresql.xml
===================================================================
--- server/databases/postgresql.xml (revision edd2feb7a88ec805bbeb59f878e383cf14e80931)
+++ server/databases/postgresql.xml (revision bcdfc536ebeae6aa0c05246ba118aa5b16eead01)
@@ -167,4 +167,63 @@
+
+ Hold off from continuing in case you are building
+ PostgreSQL to upgrade an existing
+ installation. In case the major version of the new build is
+ different to the existing one, its quite likely that the
+ new software cannot act on the existing data files. In this case,
+ the server will not start and the old binaries has been overwritten
+ so you got stuck with an unusable database.
+ At this moment, you have both, the old and the new binaries
+ on disk. They can be used to perform an upgrade of your existing
+ database right now. For the folloging 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.
+
+
+
+
+ Do a temporary install which makes access to the new
+ binaries a bit easier:
+ make DESTDIR=$(pwd)/DESTDIR install
+
+ Create a directory which is writable by the
+ postgres 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 -D /srv/pgsql/newdata -b /usr/bin -B ../usr/bin"
+popd
+
+ At this stage, you have your database files two times on
+ disk, one is the old data in /srv/pgsql/data
+ and the new ones in /srv/pgsql/newdata.
+ May this is the time you do a last backup of the old database files.
+
+ 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:
@@ -188,4 +247,11 @@
+
+
+ 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.
+
Index: server/databases/upgradedb.xml
===================================================================
--- server/databases/upgradedb.xml (revision bcdfc536ebeae6aa0c05246ba118aa5b16eead01)
+++ server/databases/upgradedb.xml (revision bcdfc536ebeae6aa0c05246ba118aa5b16eead01)
@@ -0,0 +1,228 @@
+
+
+ %general-entities;
+]>
+
+
+
+
+
+ $Date$
+
+
+ Notes on Database Server Software
+
+$ 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.
+
+
+ If you like to avoid those situations like the one above finding
+ your database server software refusing to start, it is highly
+ recommended to read the following thoughts about how to upgrade
+ a DBMS (Database Management System) prior to actually doing the
+ upgrade.
+
+
+ Even if you are installing a DBMS the initial time, read it
+ anyway, it might give you a guideline on how to set up backup
+ and restore procedures (at least the strategy for building
+ them) which are sufficient for your needs and for the safety
+ of your data.
+
+
+
+ Upgrade database server packages
+
+
+ Usually, DB systems work on a bunch of files which holds
+ the database metadata and the data itself. Those files
+ are highly optimized in their internal structures for use
+ by the server software. When upgrading such a server software,
+ it can happen that the server software expects a slightly
+ different file format than it has been created by previous
+ versions. In the best case, the new software can act on the
+ old format as well - while not benefitting from newer formats
+ which might result in better performance or of other improvements.
+ It can also happen that the new server software will reformat
+ the data files automatically at the first start.
+
+
+
+ Unfortunatly, the most likly case is that the new server
+ software complains about invalid data file formats and exits.
+ When this happens and you have overwritten the installed server
+ software with the new one, you are kind of lost as you lsot the
+ software which can read the data files and the new software is
+ unwilling to do so.
+
+
+
+ Changes in data file formats come usually beside major
+ version changes. But there is no guaranty. Before upgrading
+ the server software, check the documentation if there are
+ changes which will require a data file reformat.
+
+
+
+ Of course, if you have databases with content which is not
+ easily to rebuild, it is always a good idea of creating backups
+ of the database from time to time. When going to upgrade the
+ server software, the time is quite good to run another backup.
+
+
+
+ Upgrade by backup and restore
+
+
+
+ A backup is meaningless if there is no verified process
+ to restore the data from this backup. When running a
+ database server, you should not only create backups but you
+ should also verify that the process you designed to fullfill
+ the restore task is working properly. When you encounter a
+ problem with the restore at the time you urgently have to
+ rely on the backup data, it is too late - your database are
+ in high danger at this moment.
+
+
+
+
+ In general, most (all?) 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 (here the restore tool). The other
+ way round (older resote tools can work with newer backup data)
+ is not defined and you should never
+ blindly assume that it would work. It might do, but usually
+ it doesn't.
+
+
+
+ The most easiest way to upgrade your database files is to
+
+
+
+
+ Create a full database backup using the old tools.
+ This step creates an offline copy of the database
+ files ready to be used for long term archiving, for desaster
+ recovery or just as a preparation for upgrade. This offline
+ backups consists of the full one-to-one copy of the current
+ database files or a backup of the files from a certain time
+ in history plus all journal data (that is Oracle(R)
+ terminology, its called "Continuous Archiving" or "write
+ ahead log (WAL)" in Postgresql) containing information about
+ changes made to the data content. The later are more quickly
+ to create if the DB software provides such kind of journal as
+ you only have to save the changes which are recorded in those
+ journals from the last backup upto now. The amount of data to
+ backup is much less than doing a full backup all the time.
+ In terms of upgrading a DB server software, it is
+ recommended to perform a full backup (which can be used for
+ subsequent incremental backups) but if the amount of data is
+ too big an incremental backup will be sufficient, too. Which
+ strategy is sufficient for you depends on the amount of data
+ stored in your DB (is it a few hundret of table rows or is it
+ hundrets of Terabyte. A full backup of the later one isn't done
+ quickly (and we assume that the underlaying system of such a DB
+ might not be a home brewed LFS anyhow).
+
+
+ Upgrade the server software
+ In this step, instructions to build the database
+ server software are executed just as they are shown in
+ subsequent sections talking about the DBMs like MariaDB or
+ Postgresql. This is building the software as
+ it is been done as usual in the BLFS book.
+
+
+ Restore the database by using the new tools.
+ To restore the data, the tools of the newly installed
+ server software is used. During the restore process, the new
+ tools will create and/or upgrade the data files in the
+ format the software expects them to be.
+
+
+
+
+ Since you have already a backup procedure in place (and you
+ have tested your restore procedure, don't you?), this way might
+ be the easiest way to upgrade as you are going to use well-known
+ processes to upgrade just as you allways do - at least in terms
+ of the backup and restore.
+
+
+
+
+
+ Upgrade by system tools
+
+
+ Some database systems (for instance Postgresql) provides
+ a tool which can reformat (=upgrade) the existing database
+ files to the new format. Since the upgrading tool has to
+ be used from the new server software (the old one cannot
+ know anything about a new file format), the old software
+ might be overwritten due to installation of the new software.
+
+
+
+ In case you have to restore a backup (may be because running
+ the upgrade tool failed) you have to reinstall the old version
+ to get back the access to your data.
+
+
+
+ Even though those tools might work one the actual database
+ files, it is still highly recommended to create a full backup
+ before running them. Any case of failure might result in a
+ serious damage of the database files.
+
+
+
+
+
+
+
+ Notes for specific DBMS
+
+
+ PostgreSQL
+
+
+
+ Upstream documentation for Backup & Restore:
+
+
+
+
+
+
+ MariaDB
+
+
+
+ Upstream documentation for Backup & Restore:
+
+
+
+
+
+
+
+