|
|
|
THIS PAGE IS WORK IN PROGRES
|
|
|
|
This page will tell you the short and to the point stuff you need to know about using Postgres DB's
|
|
|
|
|
|
|
|
# Install on Ubuntu 20.*
|
|
|
|
- [1. Install on Ubuntu 20.\*](#1-install-on-ubuntu-20)
|
|
|
|
- [2. Connect to DB](#2-connect-to-db)
|
|
|
|
- [3. Backup restore](#3-backup-restore)
|
|
|
|
- [3.1. Daily backup](#31-daily-backup)
|
|
|
|
- [3.2. Restore](#32-restore)
|
|
|
|
|
|
|
|
|
|
|
|
# 1. Install on Ubuntu 20.*
|
|
|
|
|
|
|
|
```bash
|
|
|
|
sudo apt update
|
| ... | ... | @@ -12,7 +19,7 @@ Check status: `sudo systemctl status postgresql.service` |
|
|
|
|
|
|
|
See [this tutorial](https://www.digitalocean.com/community/tutorials/how-to-install-postgresql-on-ubuntu-20-04-quickstart) for more info.
|
|
|
|
|
|
|
|
# Connect to DB
|
|
|
|
# 2. Connect to DB
|
|
|
|
|
|
|
|
```bash
|
|
|
|
sudo -u postgres psql # to open psql (the interactive command line interface to the DB)
|
| ... | ... | @@ -22,9 +29,9 @@ sudo -u postgres psql # to open psql (the interactive command line interface to |
|
|
|
|
|
|
|
```
|
|
|
|
|
|
|
|
# Backup restore
|
|
|
|
# 3. Backup restore
|
|
|
|
|
|
|
|
## Daily backup
|
|
|
|
## 3.1. Daily backup
|
|
|
|
|
|
|
|
From memory (so untested):
|
|
|
|
|
| ... | ... | @@ -87,8 +94,7 @@ aws_access_key_id = 6bd6bd1a6bb0464693d7b898b61acf21 |
|
|
|
aws_secret_access_key = 33c6dba1bd1346aba76238edbb980dfb
|
|
|
|
```
|
|
|
|
|
|
|
|
|
|
|
|
## Restore
|
|
|
|
## 3.2. Restore
|
|
|
|
|
|
|
|
The restore can be done with command `pg_restore`. For instance
|
|
|
|
|
| ... | ... | @@ -96,10 +102,13 @@ The restore can be done with command `pg_restore`. For instance |
|
|
|
pg_restore dewilde_backup_Monday
|
|
|
|
```
|
|
|
|
|
|
|
|
A more professional version of this is the following script that will get the backup from S3 cloud storage based on a date. Please adjust exported variables first before running this script.
|
|
|
|
A more professional version of this is the following script that will get the backup from S3 cloud storage based on a date. But first:
|
|
|
|
- adjust exported variables first and
|
|
|
|
- stop the application before running this script.
|
|
|
|
|
|
|
|
```bash
|
|
|
|
echo Example usage: $0 acc dm 20240122
|
|
|
|
echo Example usage: $0 acc local 20240122
|
|
|
|
MSYS_NO_PATHCONV=1 # in Git Bash do not convert paths to Windows equivalents
|
|
|
|
SOURCE_ENV=$1 # e.g. acc
|
|
|
|
TARGET_ENV=$2 # e.g. tst
|
|
|
|
DATE=$3 # e.g. 20240122
|
| ... | ... | @@ -108,6 +117,10 @@ mkdir -p /tmp/backup-restore |
|
|
|
cd /tmp/backup-restore
|
|
|
|
aws $AWS_OPTS s3 cp s3://backups/wyatt_${SOURCE_ENV}_db/wyatt_${SOURCE_ENV}_db_${DATE}.zip .
|
|
|
|
unzip wyatt_${SOURCE_ENV}_db_${DATE}.zip # enter password
|
|
|
|
docker cp backups/wyatt_${SOURCE_ENV}_db_${DATE}.db wyatt_${TARGET_ENV}_db://tmp/wyatt.db
|
|
|
|
docker exec -t wyatt_${TARGET_ENV}_db pg_restore --verbose -Fc --clean -U wyatt -d wyatt /tmp/wyatt.db
|
|
|
|
docker cp backups/wyatt_${SOURCE_ENV}_db_${DATE}.db wyatt_${TARGET_ENV}_db:/tmp/wyatt.db
|
|
|
|
docker exec -t wyatt_${TARGET_ENV}_db psql -U wyatt -d wyatt -c "DROP SCHEMA IF EXISTS public CASCADE;"
|
|
|
|
docker exec -t wyatt_${TARGET_ENV}_db psql -U wyatt -d wyatt -c "CREATE SCHEMA IF NOT EXISTS public;"
|
|
|
|
docker exec -t wyatt_${TARGET_ENV}_db pg_restore --verbose --create -Fc -U wyatt -d wyatt /tmp/wyatt.db
|
|
|
|
```
|
|
|
|
|
|
|
|
Afterwards you can safely start the application again. All should work now on the target environment but with a restored DB from that environment and that date. |
|
|
\ No newline at end of file |
