Chapter 4. Using PostgreSQL

Procedure

1. Create a new database:

   $ createdb <db_name>

2. Verify that all users who own objects or were granted permissions on objects in the dumped database already exist. If such users do not exist, the restore fails to recreate 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 recreate 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 is replaced by the relative path to the file to archive and the %f parameter is replaced by the file name.

      This command copies archivable WAL segments to the /mnt/server/archivedir/ directory. After replacing the %p and %f parameters, the executed 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 is executed only on completed WAL segments. 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 also use the --tablespace-mapping option, otherwise the backup will fail 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 is complete, safely archive the copy of the database cluster and the WAL segment files used during the backup, which are 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/, recreate 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 the recovery is terminated due to an external error, the server can be restarted and it will continue the recovery. When the recovery process is completed, 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 RHEL 9 system, install the postgresql-server and postgresql-upgrade packages:

   # dnf install postgresql-server postgresql-upgrade

   Optionally, if you used any PostgreSQL server modules on RHEL 8, install them also on the RHEL 9 system in two versions, compiled both against PostgreSQL 12 (installed as the postgresql-upgrade package) and the target version of PostgreSQL 13 (installed as the postgresql-server package). If you need to compile a third-party PostgreSQL server module, build it both against the postgresql-devel and postgresql-upgrade-devel packages.

2. Check the following items:

   • Basic configuration: On the RHEL 9 system, check whether your server uses the default /var/lib/pgsql/data directory and the database is correctly initialized and enabled. In addition, the data files must be stored in the same path as mentioned in the /usr/lib/systemd/system/postgresql.service file.
   • PostgreSQL servers: Your system can run multiple PostgreSQL servers. Ensure that the data directories for all these servers are handled independently.
   • PostgreSQL server modules: Ensure that the PostgreSQL server modules that you used on RHEL 8 are installed on your RHEL 9 system as well. Note that plugins are installed in the /usr/lib64/pgsql/ directory.

3. Ensure that the postgresql service is not running on either of the source and target systems at the time of copying data.

   # systemctl stop postgresql.service

4. Copy the database files from the source location to the /var/lib/pgsql/data/ directory on the RHEL 9 system.

5. Perform the upgrade process by running the following command as the PostgreSQL user:

   # postgresql-setup --upgrade

   This launches the pg_upgrade process in the background.

   In case of failure, postgresql-setup provides an informative error message.

6. Copy the prior configuration from /var/lib/pgsql/data-old to the new cluster.

   Note that the fast upgrade does not reuse the prior configuration in the newer data stack and the configuration is generated from scratch. If you want to combine the old and new configurations manually, use the *.conf files in the data directories.

7. Start the new PostgreSQL server:

   # systemctl start postgresql.service

8. Analyze the new database cluster.

   • For PostgreSQL 13:

     su postgres -c '~/analyze_new_cluster.sh'

   • For PostgreSQL 15 or later:

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

     Note

     You may need to use ALTER COLLATION name REFRESH VERSION, see the upstream documentation for details.

9. If you want the new PostgreSQL server to be automatically started on boot, run:

   # systemctl enable postgresql.service

Procedure

1. On your RHEL 8 system, start the PostgreSQL 10 server:

   # systemctl start postgresql.service

2. On the RHEL 8 system, dump all databases contents into the pgdump_file.sql file:

   su - postgres -c "pg_dumpall > ~/pgdump_file.sql"

3. Ensure that the databases were dumped correctly:

   su - postgres -c 'less "$HOME/pgdump_file.sql"'

   As a result, the path to the dumped sql file is displayed: /var/lib/pgsql/pgdump_file.sql.

4. On the RHEL 9 system, install the postgresql-server package:

   # dnf install postgresql-server

   Optionally, if you used any PostgreSQL server modules on RHEL 8, install them also on the RHEL 9 system. If you need to compile a third-party PostgreSQL server module, build it against the postgresql-devel package.

5. On the RHEL 9 system, initialize the data directory for the new PostgreSQL server:

   # postgresql-setup --initdb

6. On the RHEL 9 system, copy the pgdump_file.sql into the PostgreSQL home directory, and check that the file was copied correctly:

   su - postgres -c 'test -e "$HOME/pgdump_file.sql" && echo exists'

7. Copy the configuration files from the RHEL 8 system:

   su - postgres -c 'ls -1 $PGDATA/*.conf'

   The configuration files to be copied are:

   • /var/lib/pgsql/data/pg_hba.conf
   • /var/lib/pgsql/data/pg_ident.conf
   • /var/lib/pgsql/data/postgresql.conf

8. On the RHEL 9 system, start the new PostgreSQL server:

   # systemctl start postgresql.service

9. On the RHEL 9 system, import data from the dumped sql file:

   su - postgres -c 'psql -f ~/pgdump_file.sql postgres'

Procedure

1. Store your sensitive variables in an encrypted file:

   1. Create the vault:

      $ ansible-vault create ~/vault.yml
      New Vault password: <vault_password>
      Confirm New Vault password: <vault_password>

   2. After the ansible-vault create command opens an editor, enter the sensitive data in the <key>: <value> format:

      pwd: <password>

   3. Save the changes, and close the editor. Ansible encrypts the data in the vault.

2. Create a playbook file, for example, ~/playbook.yml, with the following content:

   ---
   - name: Installing and configuring PostgreSQL
     hosts: managed-node-01.example.com
     vars_files:
       - ~/vault.yml
     tasks:
       - name: Create directory for TLS certificate and key
         ansible.builtin.file:
           path: /etc/postgresql/
           state: directory
           mode: 755
   
       - name: Copy CA certificate
         ansible.builtin.copy:
           src: "~/{{ inventory_hostname }}.crt"
           dest: "/etc/postgresql/server.crt"
   
       - name: Copy private key
         ansible.builtin.copy:
           src: "~/{{ inventory_hostname }}.key"
           dest: "/etc/postgresql/server.key"
           mode: 0600
   
       - name: PostgreSQL with an existing private key and certificate
         ansible.builtin.include_role:
           name: redhat.rhel_system_roles.postgresql
         vars:
           postgresql_version: "16"
           postgresql_password: "{{ pwd }}"
           postgresql_ssl_enable: true
           postgresql_cert_name: "/etc/postgresql/server"
           postgresql_server_conf:
             listen_addresses: "'*'"
             password_encryption: scram-sha-256
           postgresql_pg_hba_conf:
             - type: local
               database: all
               user: all
               auth_method: scram-sha-256
             - type: hostssl
               database: all
               user: all
               address: '127.0.0.1/32'
               auth_method: scram-sha-256
             - type: hostssl
               database: all
               user: all
               address: '::1/128'
               auth_method: scram-sha-256
             - type: hostssl
               database: all
               user: all
               address: '192.0.2.0/24'
               auth_method: scram-sha-256
   
   
       - name: Open the PostgresQL port in firewalld
         ansible.builtin.include_role:
           name: redhat.rhel_system_roles.firewall
         vars:
           firewall:
             - service: postgresql
               state: enabled

   The settings specified in the example playbook include the following:

   postgresql_version: <version>

   Sets the version of PostgreSQL to install. The version you can set depends on the PostgreSQL versions that are available in Red Hat Enterprise Linux running on the managed node.

   You cannot upgrade or downgrade PostgreSQL by changing the postgresql_version variable and running the playbook again.

   postgresql_password: <password>

   Sets the password of the postgres database superuser.

   You cannot change the password by changing the postgresql_password variable and running the playbook again.

   postgresql_cert_name: <private_key_and_certificate_file>

   Defines the path and base name of both the certificate and private key on the managed node without .crt and key suffixes. During the PostgreSQL configuration, the role creates symbolic links in the /var/lib/pgsql/data/ directory that refer to these files.

   The certificate and private key must exist locally on the managed node. You can use tasks with the ansible.builtin.copy module to transfer the files from the control node to the managed node, as shown in the playbook.

   postgresql_server_conf: <list_of_settings>

   Defines postgresql.conf settings the role should set. The role adds these settings to the /etc/postgresql/system-roles.conf file and includes this file at the end of /var/lib/pgsql/data/postgresql.conf. Consequently, settings from the postgresql_server_conf variable override settings in /var/lib/pgsql/data/postgresql.conf.

   Re-running the playbook with different settings in postgresql_server_conf overwrites the /etc/postgresql/system-roles.conf file with the new settings.

   postgresql_pg_hba_conf: <list_of_authentication_entries>

   Configures client authentication entries in the /var/lib/pgsql/data/pg_hba.conf file. For details, see the PostgreSQL documentation.

   The example allows the following connections to PostgreSQL:

   • Unencrypted connections by using local UNIX domain sockets.
   • TLS-encrypted connections to the IPv4 and IPv6 localhost addresses.
   • TLS-encrypted connections from the 192.0.2.0/24 subnet. Note that access from remote addresses is only possible if you also configure the listen_addresses setting in the postgresql_server_conf variable appropriately.

   Re-running the playbook with different settings in postgresql_pg_hba_conf overwrites the /var/lib/pgsql/data/pg_hba.conf file with the new settings.

   For details about all variables used in the playbook, see the /usr/share/ansible/roles/rhel-system-roles.postgresql/README.md file on the control node.

3. Validate the playbook syntax:

   $ ansible-playbook --ask-vault-pass --syntax-check ~/playbook.yml

   Note that this command only validates the syntax and does not protect against a wrong but valid configuration.

4. Run the playbook:

   $ ansible-playbook --ask-vault-pass ~/playbook.yml

Procedure

1. Store your sensitive variables in an encrypted file:

   1. Create the vault:

      $ ansible-vault create ~/vault.yml
      New Vault password: <vault_password>
      Confirm New Vault password: <vault_password>

   2. After the ansible-vault create command opens an editor, enter the sensitive data in the <key>: <value> format:

      pwd: <password>

   3. Save the changes, and close the editor. Ansible encrypts the data in the vault.

2. Create a playbook file, for example, ~/playbook.yml, with the following content:

   ---
   - name: Installing and configuring PostgreSQL
     hosts: managed-node-01.example.com
     vars_files:
       - ~/vault.yml
     tasks:
       - name: PostgreSQL with certificates issued by IdM
         ansible.builtin.include_role:
           name: redhat.rhel_system_roles.postgresql
         vars:
           postgresql_version: "16"
           postgresql_password: "{{ pwd }}"
           postgresql_ssl_enable: true
           postgresql_certificates:
             - name: postgresql_cert
               dns: "{{ inventory_hostname }}"
               ca: ipa
               principal: "postgresql/{{ inventory_hostname }}@EXAMPLE.COM"
           postgresql_server_conf:
             listen_addresses: "'*'"
             password_encryption: scram-sha-256
           postgresql_pg_hba_conf:
             - type: local
               database: all
               user: all
               auth_method: scram-sha-256
             - type: hostssl
               database: all
               user: all
               address: '127.0.0.1/32'
               auth_method: scram-sha-256
             - type: hostssl
               database: all
               user: all
               address: '::1/128'
               auth_method: scram-sha-256
             - type: hostssl
               database: all
               user: all
               address: '192.0.2.0/24'
               auth_method: scram-sha-256
   
   
       - name: Open the PostgresQL port in firewalld
         ansible.builtin.include_role:
           name: redhat.rhel_system_roles.firewall
         vars:
           firewall:
             - service: postgresql
               state: enabled

   The settings specified in the example playbook include the following:

   postgresql_version: <version>

   Sets the version of PostgreSQL to install. The version you can set depends on the PostgreSQL versions that are available in Red Hat Enterprise Linux running on the managed node.

   You cannot upgrade or downgrade PostgreSQL by changing the postgresql_version variable and running the playbook again.

   postgresql_password: <password>

   Sets the password of the postgres database superuser.

   You cannot change the password by changing the postgresql_password variable and running the playbook again.

   postgresql_certificates: <certificate_role_settings>

   A list of YAML dictionaries with settings for the certificate role.

   postgresql_server_conf: <list_of_settings>

   Defines postgresql.conf settings you want the role to set. The role adds these settings to the /etc/postgresql/system-roles.conf file and includes this file at the end of /var/lib/pgsql/data/postgresql.conf. Consequently, settings from the postgresql_server_conf variable override settings in /var/lib/pgsql/data/postgresql.conf.

   Re-running the playbook with different settings in postgresql_server_conf overwrites the /etc/postgresql/system-roles.conf file with the new settings.

   postgresql_pg_hba_conf: <list_of_authentication_entries>

   Configures client authentication entries in the /var/lib/pgsql/data/pg_hba.conf file. For details, see the PostgreSQL documentation.

   The example allows the following connections to PostgreSQL:

   • Unencrypted connections by using local UNIX domain sockets.
   • TLS-encrypted connections to the IPv4 and IPv6 localhost addresses.
   • TLS-encrypted connections from the 192.0.2.0/24 subnet. Note that access from remote addresses is only possible if you also configure the listen_addresses setting in the postgresql_server_conf variable appropriately.

   Re-running the playbook with different settings in postgresql_pg_hba_conf overwrites the /var/lib/pgsql/data/pg_hba.conf file with the new settings.

   For details about all variables used in the playbook, see the /usr/share/ansible/roles/rhel-system-roles.postgresql/README.md file on the control node.

3. Validate the playbook syntax:

   $ ansible-playbook --ask-vault-pass --syntax-check ~/playbook.yml

   Note that this command only validates the syntax and does not protect against a wrong but valid configuration.

4. Run the playbook:

   $ ansible-playbook --ask-vault-pass ~/playbook.yml