Setting up PostgreSQL

Edit online

You need to install the PostgreSQL database as a prerequisite for IBM® Guardium ® Quantum Safe. It is important to setup, configure and administer the database by following the best practices to help ensure optimal performance and usage of the database in the context of Guardium Quantum Safe.

About this task

PostgreSQL is a powerful and reliable database system that supports the business operations. The plan focuses on five key areas such as infrastructure and storage, security, backup and restore, and monitoring. This plan creates a system that protects data, minimizes downtime, and scales with needs. Each section is designed to help ensure that the data is secure, accessible, and efficiently managed.

Important: PostgreSQL database is not packaged along with Guardium Quantum Safe. You need to install and configure it separately as a prerequisite for Guardium Quantum Safe installation.

Note: The commands provided in the following sections are intended for indicative purposes only. Modify them as necessary to align with the specific version, installation path, operating system, and environment of Guardium Quantum Safe installation.

Building a Strong Foundation: Infrastructure and storage

Edit online

You need to create a robust environment for storing and organizing data, much like setting up an efficient warehouse.

Procedure

Set up infrastructure.

Set up dedicated servers or cloud-based systems to run PostgreSQL, helping ensure that they have enough power (CPU, memory, and storage) to handle data needs. This step is like choosing the right size and location for a warehouse to store inventory.
Define archival and storage policies.
Partitioning organizes data into smaller, manageable pieces based on a key (for example, date). This supports archival policies by allowing older data to be moved to cost-effective storage (for example, slower HDDs or cloud storage) while the recent data is kept on fast SSDs.
Archival policy implementation example:
- Retention: Keep orders for 7 years.
- Archiving: Move partitions older than 2 years to ts_archive on slower storage.
- Purging: Drop partitions older than 7 years.
```
ALTER TABLE orders_2023 SET TABLESPACE ts_archive;
```
Manage tablespaces.

Data is organized into separate tablespaces like different sections in a warehouse to improve performance. For example, frequently accessed customer data is stored on faster drives. This step helps ensure quick access to critical information and efficient use of storage resources.
Set up environment.
1. Consider an Ubuntu server for installation.
```
sudo apt update 
sudo apt install postgresql postgresql-contrib 
```
2. Configure the server resource.
  - For information on sizing guidance, see Sizing guidance.
  - Check disk space.
```
df -hP
```
3. Initialize the database cluster.
```
sudo -u postgres <your initdb complete path> -D /var/lib/postgresql/data
```
  For example,
```
sudo -u postgres /usr/lib/postgresql/16/bin/initdb -D /var/lib/postgresql/data
```
4. Start and enable the PostgreSQL service.
```
sudo systemctl start postgresql 
sudo systemctl enable postgresql 
```
5. Define archival policies. Enable Write-Ahead Logging (WAL) archiving for point-in-time recovery (PITR).
```
wal_level = replica 

archive_mode = on 

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

archive_timeout = 3600 
```
6. Add or modify postgresql.conf. Provide the path as per the version.
```
sudo nano /etc/postgresql/14/main/postgresql.conf
```
7. User Private SSH key: Enter the private SSH key details for the user account.
8. Run the following command as a Postgres user and not as root user.
```
psql -U postgres
```
9. Create the directory /pgdata/tablespaces/ts_index and give permission to Postgres user.
10. Run the following commands to manage tablespaces.
```
CREATE TABLESPACE ts_index LOCATION '/pgdata/tablespaces/ts_index'; 
CREATE TABLESPACE ts_large LOCATION '/pgdata/tablespaces/ts_large'; 
```

Protecting the data: Security

Edit online

Security is a priority, like locking the doors and setting up cameras in a warehouse to protect valuable assets.

Procedure

Transparent Data Encryption (TDE)
- Overview: TDE encrypts all data files, including temporary files, at rest. It uses a single key to encrypt the entire database cluster, with decryption that occurs transparently when data is read from disk.
- Implementation: Native TDE is not fully integrated into PostgreSQL but is available through third-party fixes, such as CYBERTEC TDE. These fixes use AES encryption at the block level.
- Configuration: Requires initializing the database with encryption enabled and providing the encryption key through a 'custom key setup' command.
Other encryption options
- Column-level encryption: Use the pgcrypto module to encrypt specific columns. This method requires application-level key management and displays decrypted data briefly on the server. Regular key rotation limits the exposure window if a key is compromised. For example, if a key is leaked, only data encrypted with that key is at risk, and frequent rotation reduces the attack surface.
Considerations
- Performance: Encryption, especially TDE, can increase processor usage, so test performance impacts in your environment.

Set up security parameters.

Mount the partition assuming that the FS path does exist.

mkdir /mnt/encrypted  
sudo mount <path to your mount point> /mnt/encrypted

For example,

mkdir /mnt/encrypted  
sudo mount /dev/mapper/map_point /mnt/encrypted

Stop PostgreSQL.
```
sudo systemctl stop postgresql
```

Move data directory.

sudo mv /var/lib/postgresql /mnt/encrypted/postgresql

Start PostgreSQL.
```
sudo systemctl start postgresql
```

Safeguarding the data: Backup and restore

Edit online

Backups are like keeping copies of important documents in a fireproof safe to recover quickly if something goes wrong.

Procedure

Define a backup and restore policy.
Backup involves copying the actual files that PostgreSQL uses to store database data, including the entire data directory (for example, PGDATA) and, optionally, Write-Ahead Log (WAL) files. This method is essential for fast restoration and supports Point-in-Time Recovery (PITR), making it suitable for large databases and disaster recovery scenarios.
Point-in-Time Recovery (PITR): Definition and process.
- Point-in-Time Recovery (PITR) is a recovery method that allows restoring a PostgreSQL database to a specific point in time, not just to the last full backup. This method is crucial for minimizing data loss during failures, such as accidental data deletions or system crashes. PITR relies on a combination of a physical backup (base backup) and continuously archived Write-Ahead Log (WAL) files.
- PITR requires enabling WAL archiving in the postgresql.conf file.
```
wal_level = replica 
archive_mode = on 
archive_command = 'cp %p /path/to/wal_archive/%f' 
```
PostgreSQL backup automation and monitoring guide.
The following guide provides a step-by-step process to automate backups and implement monitoring for a PostgreSQL database by using native PostgreSQL tools (pg_dump) and standard Linux utilities (cron, gzip, mail). It avoids third-party tools, helping ensure simplicity and compatibility. The guide includes commands, example scripts, and monitoring strategies.
1. Create the backup script. Create a script to automate pg_dump, compress the output, and handle errors. Save it as /usr/local/bin/pg_backup.sh.
```
#!/bin/bash
```
2. Configure the database.
```
DB_NAME="postgres" 

BACKUP_DIR="/backups/postgresql" 

TIMESTAMP=$(date +"%Y%m%d_%H%M%S") 

BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.sql.gz" 

PGUSER="postgres" 

PGHOST="localhost" 

PGPORT="5432" 

EMAIL_ADMIN="admin@example.com"
```
3. Create a backup directory if it does not exist.
```
mkdir -p ${BACKUP_DIR}
```
4. Delete backups older than 7 days.
```
find ${BACKUP_DIR} -type f -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete 
```
5. Schedule backups with Cron.
```
0 2 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1
```

Keeping an eye on the performance: Monitoring

Edit online

Monitoring is like having a dashboard that shows the health of the database in real time. This helps to spot and fix issues before they cause problems.

Procedure

Monitor Write-Ahead Logs (WAL).
WAL logs record every change to the database, like a transaction ledger. Monitor these logs to help ensure data integrity. This method helps to detect issues, such as unexpected changes, early on.
Monitor slow queries.
Analyze frequent or slow queries by using pg_stat statements.
```
SELECT query,  calls, total_exec_time / 1000 AS total_seconds, 

 mean_exec_time AS avg_ms FROM pg_stat_statements 

WHERE mean_exec_time > 100 ORDER BY total_exec_time DESC 

LIMIT 10; 
```
Action
- Optimize slow queries by adding indexes (for example, CREATE INDEX idx_orders_amount ON orders(amount);).
- Adjust work_mem for complex queries when sorts or spills occur.