# Backing up PostgreSQL databases using cron jobs

> Schedule automatic PostgreSQL database backups with pg_dump, a .pgpass file and a cron job.

Source: https://helpcenter.ruachost.com/developers/databases/backing-up-postgresql-databases-using-cron-jobs/

> **PostgreSQL is available on Managed VPS only:**
Ruachost shared hosting supports MySQL (MariaDB) databases, not PostgreSQL. To use PostgreSQL, choose a [Managed VPS](https://ruachost.com/vps).

Automated backups are essential for protecting your data. On **ruachost.com**, you can schedule PostgreSQL backups using **cron jobs**, ensuring your databases are regularly saved without manual intervention.

## Why Use Cron Jobs for Backups?

- Automates routine backups.
- Reduces risk of data loss.
- Ensures recovery points are always available.
- Saves time compared to manual exports.

## Steps to Set Up Automated Backups

### Step 1: Configure `.pgpass` File

Cron jobs run without user interaction, so you must store database credentials securely in a `.pgpass` file.

1. Log in via SSH.
2. Create a file named `.pgpass` in your home directory:

   ```bash
   nano ~/.pgpass
   ```
3. Add the following line (replace placeholders with actual values):

   ```
   hostname:port:database:username:password
   ```

   Example:

   ```
   localhost:5432:mydb:dbuser:dbpassword
   ```
4. Set file permissions:

   ```bash
   chmod 600 ~/.pgpass
   ```

### Step 2: Create Backup Command

Use `pg_dump` to export the database.

```bash
/usr/bin/pg_dump --no-password -U dbuser dbname > /home/username/backups/mydb_backup.pgsql
```

- Replace `dbuser`, `dbname`, and `username` with your actual values.
- Ensure the backup path exists (`/home/username/backups/`).

### Step 3: Schedule Cron Job

1. Open the crontab editor:

   ```bash
   crontab -e
   ```
2. Add a line to schedule the backup. Example: run daily at 2 AM:

   ```
   0 2 * * * /usr/bin/pg_dump --no-password -U dbuser dbname > /home/username/backups/mydb_backup.pgsql
   ```
3. Save and exit.

## Example Cron Expressions

- ### Daily at 2 AM

  ```
  0 2 * * * ...
  ```
- ### Every Sunday at midnight

  ```
  0 0 * * 0 ...
  ```
- ### Every 6 hours

```
0 */6 * * * ...
```

> **Important Notes:**
- Always test the backup command manually before scheduling.
- Use `.pgpass` to avoid password prompts.
- Store backups in a secure location with limited access.
- Consider rotating backups (e.g., keep 7 daily backups, 4 weekly backups).
- For shared hosting, paths must begin with `${HOME}` or `/home/username`.