Chapter 3. Using PostgreSQL

Procedure

1. Create a new database:

   $ createdb <db_name>

2. Verify that all users who own objects or have permissions on objects in the dumped database already exist. If such users do not exist, the restore fails to re-create the objects with the original ownership and permissions.

3. Run the psql utility to restore a text file dump created by the pg_dump utility:

   $ psql <db_name> < <dump_file>

   where <dump_file> is the output of the pg_dump command. To restore a non-text file dump, use the pg_restore utility instead:

   $ pg_restore <non-plain_text_file>

Procedure

1. Ensure that all users who own objects or were granted permissions on objects in the dumped databases already exist. If such users do not exist, the restore fails to re-create the objects with the original ownership and permissions.

2. Run the psql utility to restore a text file dump created by the pg_dumpall utility:

   $ psql < <dump_file>

   where <dump_file> is the output of the pg_dumpall command.

Procedure

1. Stop the postgresql service:

   # systemctl stop postgresql.service

2. Create a backup directory, for example:

   # mkdir -p /root/postgresql-backup/

3. Back up the /var/lib/pgsql/ directory:

   # cp -rp /var/lib/pgsql/ /root/postgresql-backup/

   The /var/lib/pgsql/ contains all essential files of the PostgreSQL database server, including configuration files, data files, and logs.

4. Start the postgresql service:

   # systemctl start postgresql.service

Procedure

1. Stop the postgresql service:

   # systemctl stop postgresql.service

2. Remove the current /var/lib/pgsql/ directory:

   # rm -rf /var/lib/pgsql/

3. Restore the data directory from your backup:

   # cp -rp /root/postgresql-backup/pgsql/ /var/lib/

4. Ensure the correct ownership of the /var/lib/pgsql/ directory:

   # chown -R postgres:postgres /var/lib/pgsql/

5. Restore the SELinux context of the /var/lib/pgsql/ directory:

   # restorecon -Rv /var/lib/pgsql/

6. Start the postgresql service:

   # systemctl start postgresql.service

Verification

1. Log in as the postgres user.

2. Connect to a database:

   $ psql <database>

3. Access data in the database:

   <database>=# SELECT * FROM <table>;

4. Disconnect from the PostgreSQL service:

   <database>=# \q

Procedure

1. In the /var/lib/pgsql/data/postgresql.conf file:

   1. Set the wal_level configuration parameter to replica or higher.
   2. Set the archive_mode parameter to on.

   3. Specify the shell command in the archive_command configuration parameter. You can use the cp command, another command, or a shell script.

      For example:

      archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'

      where the %p parameter specifies the relative path to the file to archive, and the %f parameter specifies the file name.

   This command copies archivable WAL segments to the /mnt/server/archivedir/ directory. After replacing the %p and %f parameters, the resulting command looks as follows:

   +

   test ! -f /mnt/server/archivedir/00000001000000A900000065 && cp pg_wal/00000001000000A900000065 /mnt/server/archivedir/00000001000000A900000065

   + A similar command is generated for each new file that is archived.

   +

   Note

   The archive command runs only after a WAL segment is completed. A server that generates little WAL traffic can have a substantial delay between the completion of a transaction and its safe recording in archive storage. To limit how old unarchived data can be, you can:

   • Set the archive_timeout parameter to force the server to switch to a new WAL segment file with a given frequency.
   • Use the pg_switch_wal parameter to force a segment switch to ensure that a transaction is archived immediately after it finishes.

2. Restart the postgresql service to enable the changes:

   # systemctl restart postgresql.service

3. Test your archive command and ensure it does not overwrite an existing file and that it returns a nonzero exit status if it fails.
4. To protect your data, ensure that the segment files are archived into a directory that does not have group or world read access.

Procedure

1. Use the pg_basebackup utility to perform the base backup.

   • To create a base backup as individual files (plain format):

     $ pg_basebackup -D <backup_directory> -Fp

     Replace backup_directory with your chosen backup location.

     If you use tablespaces and perform the base backup on the same host as the server, you must use the --tablespace-mapping option. Otherwise the backup fails upon an attempt to write the backup to the same location.

   • To create a base backup as a tar archive (tar and compressed format):

     $ pg_basebackup -D <backup_directory> -Ft -z

     Replace backup_directory with your chosen backup location.

     To restore such data, you must manually extract the files in the correct locations.

   To specify which database server pg_basebackup will contact, use the following command-line options:

   • The -h option to define the host.

     The default host is either the local host or a host specified by the PGHOST environment variable.

   • The -p option to define the port.

     The default port is indicated by the PGPORT environment variable or the compiled-in default.

2. After the base backup process completes, safely archive the database cluster copy and WAL segment files used during the backup, as specified in the backup history file.
3. Delete WAL segments numerically lower than the WAL segment files used in the base backup because these are older than the base backup and no longer needed for a restore.

Procedure

1. Stop the server:

   # systemctl stop postgresql.service

2. Copy the necessary data to a temporary location.

   Preferably, copy the whole cluster data directory and any tablespaces. Note that this requires enough free space on your system to hold two copies of your existing database.

   If you do not have enough space, save the contents of the cluster’s pg_wal directory, which can contain logs that were not archived before the system went down.

3. Remove all existing files and subdirectories under the cluster data directory and under the root directories of any tablespaces you are using.

4. Restore the database files from your base backup.

   Ensure that:

   • The files are restored with the correct ownership (the database system user, not root).
   • The files are restored with the correct permissions.
   • The symbolic links in the pg_tblspc/ subdirectory are restored correctly.

5. Remove any files present in the pg_wal/ subdirectory.

   These files resulted from the base backup and are therefore obsolete. If you did not archive pg_wal/, re-create it with proper permissions.

6. Copy any unarchived WAL segment files that you saved in step 2 into pg_wal/.

7. Create the recovery.conf recovery command file in the cluster data directory and specify the shell command in the restore_command configuration parameter. You can use the cp command, another command, or a shell script. For example:

   restore_command = 'cp /mnt/server/archivedir/%f "%p"'

8. Start the server:

   # systemctl start postgresql.service

   The server enters the recovery mode and proceeds to read through the archived WAL files that it needs.

   If an external error terminates the recovery process, you can restart the server to continue the recovery. When the recovery process completes, the server renames recovery.conf to recovery.done. This prevents the server from accidentally re-entering the recovery mode after it starts normal database operations.

9. Check the contents of the database to verify that the database has recovered into the required state.

   If the database has not recovered into the required state, return to step 1. If the database has recovered into the required state, allow the users to connect by restoring the client authentication configuration in the pg_hba.conf file.

Procedure

1. On the host with the existing PostgreSQL instance that you want to migrate:

   1. Export all databases to the /var/lib/pgsql/pgdump_file.sql file:

      # su - postgres -c "pg_dumpall > /var/lib/pgsql/pgdump_file.sql"

   2. Check the exported file:

      # su - postgres -c 'less "/var/lib/pgsql/pgdump_file.sql"'

   3. Copy the database dump that you created in an earlier step and the PostgreSQL configuration files to the RHEL 10 host, for example:

      # scp /var/lib/pgsql/pgdump_file.sql \
             /var/lib/pgsql/data/pg_hba.conf \
             /var/lib/pgsql/data/pg_ident.conf \
             /var/lib/pgsql/data/postgresql.conf \
             <user>@<rhel_10_host>:/tmp/

2. On the RHEL 10 host:

   1. Install the postgresql-server package:

      # dnf install postgresql-server

   2. Initialize the /var/lib/pgsql/data/ directory:

      # postgresql-setup --initdb

   3. Move the copied configuration files to the /var/lib/pgsql/data/ directory:

      # mv /tmp/pg_hba.conf \
           /tmp/pg_ident.conf \
           /tmp/postgresql.conf \
           /var/lib/pgsql/data/

   4. Ensure a correct ownership of the content in the /var/lib/pgsql/data/ directory:

      # chown -R postgres:postgres /var/lib/pgsql/data/

   5. Restore the SELinux context on /var/lib/pgsql/data/:

      # restorecon -Rv /var/lib/pgsql/data/

   6. Enable and start the postgresql service:

      # systemctl enable --now postgresql.service

   7. Import the data as the postgres user:

      # su - postgres -c 'psql -f /tmp/pgdump_file.sql postgres'

   8. Verify your databases and ensure that your applications that use the PostgreSQL server work as expected.

Procedure

1. On the host with the existing PostgreSQL instance that you want to migrate:

   1. Stop the postgresql service:

      # systemctl stop postgresql.service

   2. Change into the /var/lib/pgsql/ directory, and back up the data subdirectory:

      # cd /var/lib/pgsql/
      # tar -zcf ~/pgdata.bak.tar.gz data/

   3. Copy the ~/pgdata.bak.tar.gz archive to the RHEL 10 host, for example:

      # scp ~/pgdata.bak.tar.gz <user>@<rhel_10_host>:/tmp/

2. On the RHEL 10 host:

   1. Install the required packages:

      # dnf install postgresql-server postgresql-upgrade

      The postgresql-upgrade package provides a PostgreSQL 13 server which is required during the migration.

   2. If you use third party PostgreSQL server modules, build them against both the postgresql-devel and postgresql-upgrade-devel packages, and install them.

   3. Ensure that the postgresql service is stopped:

      # systemctl stop postgresql.service

   4. Change into the /var/lib/pgsql/ directory, and extract the backed up data directory from the previous host:

      # cd /var/lib/pgsql/
      # tar -zxf /tmp/pgdata.bak.tar.gz

   5. Optional: Remove the /tmp/pgdata.bak.tar.gz archive:

      # rm /tmp/pgdata.bak.tar.gz

   6. Perform the upgrade process:

      # postgresql-setup --upgrade

      The postgresql-setup shell script renames the /var/lib/pgsql/data/ directory to /var/lib/pgsql/data-old/ and uses the pg_upgrade utility to migrate the databases to a re-created /var/lib/pgsql/data/ directory.

      Important

      The pg_upgrade utility migrates only the databases and not the configuration files. After the migration, /var/lib/pgsql/data/ contains only the default .conf files. If you, previously, had custom configuration files, copy them from the /var/lib/pgsql/data-old/ directory and ensure that they are compatible with the new PostgreSQL version.

   7. Enable and start the postgresql service:

      # systemctl enable --now postgresql.service

   8. Clean up and analyze all databases:

      # su postgres -c 'vacuumdb --all --analyze-in-stages'

   9. Verify your databases and ensure that your applications that use the PostgreSQL server work as expected.

   10. Optional: Remove the /var/lib/pgsql/data-old/ directory which contains the databases and configuration file from before the migration.

       # rm -r /var/lib/pgsql/data-old/

   11. Optional: Remove the postgresql-upgrade package:

       # dnf remove postgresql-upgrade