PostgreSQL 14 – Migrate from Oracle Linux 7 to 8

Today I’ve migrated 2 database clusters from Oracle Linux 7 to Oracle Linux 8 using backup/restore approach. First one has default PostgreSQL tablespaces and second (“mycluster”) has 2 user-defined tablespaces. Yes, if you are coming with Oracle ecosystem background you thread tablespaces very seriously as a lot of features associated with tablespaces could be used in multiple use cases. In PostgreSQL tablespace is not important database entity (but they could be useful if you have enable TDE encryption only for some database in cluster). More here

Worth of mentioning is tablespaces usage in backup/recovery migration approach. User-defined tablespaces are backed separately and not stored in “base” directory.

Their existence adds one step more for recovery process:

  • Uncompress tablespace-related archive to separated directory (as best practice don’t keep them in base directory)
  • Update tablespace_map file in uncompressed base folder for pointing to new locations

This step could be quite easily automated too using Ansible playbook, role or other automation software you are using. After restoring tablespace my tablespace_map file looks like below:

It worth to mentioned that “core” PostgreSQL doesn’t have something like backup repository included (like in Oracle controlfile or RMAN Catalog). Good news it is produced by pg_basebackup tool. For “mycluster” cluster it looks like below.

One important note you have to remember are extensions. If you have them on source and would like to have them on target – install them before restore (as most of them required installation on operating system level or PostgreSQL binaries) as in my case:

I’ve installed postgresql14-contrib package with yum and this helped me pg_stat_statements extension work correctly.

As we see existence of user tablespaces in PostgreSQL makes backup/recovery migration approach a bit more complicated, but this is not hard blocker or something which couldn’t be easily automated.