Restore PostgreSQL Database

tasks/common/restore_db.yml imports a PostgreSQL database dump restored from a Restic snapshot, recreating the database from scratch first. Service-specific restore tasks restore their Restic snapshot into a temporary directory, then supply the dump location and database connection details to this shared task.

Process

The task:

• Validates the supplied dump path, client certificate directory, database name, application credentials, host, port, and maintenance password.
• Fails immediately if the dump file is not present at the supplied path.
• Generates a client certificate (CN = the application database user, signed by the internal Root CA), owned by root, in the supplied certificate directory.
• Generates a temporary internal-Root-CA client certificate, owned by root, for the PostgreSQL maintenance user.
• Waits for the database server over verify-full TLS, then recreates the application login role (so restore works even against a host where the role doesn't already exist), and drops and recreates the application database so it is guaranteed empty before restore.
• Removes the temporary maintenance certificate files.
• Waits for the database server again, this time authenticating as the application user over its own client certificate, then imports the dump with community.postgresql.postgresql_db.
• Removes the dump file and clears the task facts after the import completes.

Input variables

Each calling service supplies these variables to tasks/common/restore_db.yml:

Variable	Type	Description
cs_common_restore_db_dump_path	string	Path to the restored dump file.
cs_common_restore_db_client_cert_dir	string	Directory the application's client certificate is written into.
cs_common_restore_db_database	string	Application database name.
cs_common_restore_db_user	string	Application database login user.
cs_common_restore_db_password	string	Application database login password.
cs_common_restore_db_host	string	PostgreSQL host address.
cs_common_restore_db_port	int	PostgreSQL port.
cs_common_restore_db_maintenance_password	string	PostgreSQL maintenance user's password.

The calling task must have already gathered facts (ansible.builtin.setup) in the same play: the client certificate's subject_alt_name uses the first address in ansible_facts.all_ipv4_addresses that the valid_remote_ip_only filter keeps.

Prerequisites

The application host must already have the PostgreSQL client installed, see System Patching.