Yaya za ka yi jiyya da sake dawo da PostgreSQL?

Question

Accepted Answer

PostgreSQL yana ba da hanyoyi da yawa na **jiyya** — jiyyoyi masu hankali (`pg_dump`) da jiyyoyi jiki (tushen jiyya + WAL don **sake dawo zuwa lokaci daidai**). Dabarar jiyya-da-sake-dawo mai ƙarfi tana da mahalaci wajen karewa daga riba data, kuma a maimaitawa, **dole ne a gwada jiyyoyi**.

## Jiyyoyi masu hankali — pg_dump

```bash
# pg_dump — dumps a database as SQL (or a custom archive) — portable, flexible
pg_dump -U user -d mydb -F c -f backup.dump      # custom format (compressed, flexible restore)
pg_dump -U user -d mydb > backup.sql              # plain SQL
pg_dumpall > all.sql                              # ALL databases + roles

# restore
pg_restore -U user -d mydb backup.dump            # from custom format
psql -U user -d mydb -f backup.sql                # from plain SQL
```

`pg_dump` yana aiki da jiyya masu hankali (SQL ko archive) na database — da iya yin aiki (ana iya sake dawo zuwa nau'i/sabar daban), mai kyau don ƙarami databases, ɓimawa, da jiyyoyi da aka zaɓa. Amma ɗin snapshot na lokaci daidai ne (babu ci gaba recovery).

## Jiyyoyi jiki + Sake dawo zuwa lokaci daidai (PITR)

```text
For larger/critical databases: a BASE BACKUP (a copy of the data files) + continuous
archiving of the WAL (write-ahead log) → enables POINT-IN-TIME RECOVERY:
  ✓ Restore the base backup, then REPLAY WAL up to ANY point in time
  ✓ Recover to JUST BEFORE a disaster (e.g. an accidental DROP TABLE at 2:47pm)
→ Tools: pg_basebackup, WAL archiving, and tools like pgBackRest / Barman automate this.
```

**PITR** yana da ƙarfi: ta hanyar aji WAL ci gaba, za ka iya sake dawo zuwa kowane lokaci — ba kawai jiyya ta ƙarshe ba — wanda ya zama dole wajen rage riba data daga bala'i.

## Abubuwan mahimman dabarar jiyya

```text
✓ Regular, AUTOMATED backups (scheduled, not manual/forgotten)
✓ Store backups OFF the database server (and offsite / cloud) — survive hardware failure
✓ Retention policy (keep daily/weekly/monthly as needed)
✓ Combine: periodic base backups + continuous WAL for PITR (critical data)
✓ Backups from a REPLICA to avoid loading the primary
```

## Mulkin mafi mahimma: GWADA jiyyoyin ka

```text
⚠️ A backup you've never restored is NOT a reliable backup.
UNTESTED backups fail when you need them most (corrupt, incomplete, wrong process).
→ Regularly TEST restores to verify they actually work and you know the procedure.
```

## Me ya sa ya mahalaci

Jiyya da sake dawo shi ne **mahimmi** ilimi wajen aiki da PostgreSQL da sauki — karewa daga riba data (daga gazawar kayan aiki, lalacewar data, kashe-kashe, bala'i) shi ne ɗaya daga cikin abubuwan mafi mahimma wajen kowa da shugaban database na samar da aiki, saboda haka fahimtar dabarar jiyya tana da mahalaci ilimi matakin babbar matakin.

Fahimtar hanyoyi tana da mahalaci: **`pg_dump`** (jiyyoyi masu hankali — SQL/archive dumps da iya yin aiki, mai kyau don ƙarami databases, ɓimawa, da jiyyoyi da aka zaɓa/nau'i, amma snapshots na lokaci daidai ne kawai) da **jiyyoyi jiki tare da Sake dawo zuwa lokaci daidai (PITR)** (tushen jiyya + aji WAL ci gaba, ba da damar sake dawo zuwa *kowane* lokaci — wanda yana da ƙarfi wajen rage riba data, kamar sake dawo kafin wasu kashe-kashe `DROP TABLE`, dole ne ga mahimmanci data).

Sanin **abubuwan mahimman dabarar** (jiyyoyi na yau-yau na atomatik, ajiyar su waje-sabar da waje-kasuwa, manufofi ajiyar, haɗa tushen jiyya tare da WAL don PITR akan mahimmanci data, jiyya daga masu daurepan) ilimi ne na mahimman aiki.

Mafi mahimmi, fahimtar cewa **dole ne a gwada jiyyoyi** — jiyya da ba a gwada ba ba ta da amincin gida kuma sau da yawa ta kasa daidai sa'ad da aka buƙata (lalacewar data, cikakken data ba komai, saurin sake dawo da sauƙi) — mahimmin abin da an cire sau-sau: giggida gwada sake dawo shine abin da zai sa dabarar jiyya ta zama da amincin gida.

Tun data riba za ta iya zama mahimmanci (da sake dawo kawai ya yiwu tare da jiyyoyi da ke aiki), kuma tun dabarar maidari (abubuwan jiyya da ta dace, PITR don rage riba ga mafi karai, ajiyar waje-kasuwa, kuma a maimaitawa *gwada* sake dawo) shine abin da zai kareba data na ƙanƙali, fahimtar jiyya da sake dawo da PostgreSQL shine mahimmi matakin babbar matakin ilimi wajen aiki da amincin database — mahimmanci mai gida inda bambancin dabarar da aka gwada da ɗaya da ba a gwada ba za ta iya zama bambanci tsakanin sake dawo daga bala'i da riba data da sukuwa, wanda ya sa shi dole-dole, mahimmanci mahimmanci gida wajen kowa da ke aiki da production databases.