|
|
This page will tell you the short and to the point stuff you need to know about using Postgres DB's
|
|
|
|
|
|
- [1. Install on Ubuntu 20.\*](#1-install-on-ubuntu-20)
|
|
|
- [Passwords](#passwords)
|
|
|
- [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)
|
|
|
- [2. Passwords](#2-passwords)
|
|
|
- [3. Connect to DB](#3-connect-to-db)
|
|
|
- [4. Backup restore](#4-backup-restore)
|
|
|
- [4.1. Daily backup](#41-daily-backup)
|
|
|
- [4.2. Restore](#42-restore)
|
|
|
|
|
|
|
|
|
# 1. Install on Ubuntu 20.*
|
... | ... | @@ -20,7 +20,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.
|
|
|
|
|
|
# Passwords
|
|
|
# 2. Passwords
|
|
|
|
|
|
There are many ways to use a password. For using DB commands from the command line (e.g. from scripts) the preferred way is to add credentials file `~/.pgpass` and limit rights to it. This is how you can do that on a container (which for us is the most common approach). This is the script for a container called `wyatt_local_db` where the DB runs on `localhost:5432`, with a DB and user called `wyatt`:
|
|
|
|
... | ... | @@ -28,7 +28,7 @@ There are many ways to use a password. For using DB commands from the command li |
|
|
docker exec -t wyatt_local_db bash -c 'echo "localhost:5432:wyatt:wyatt:somepasswd" > ~/.pgpass && chmod 0600 ~/.pgpass'
|
|
|
```
|
|
|
|
|
|
# 2. Connect to DB
|
|
|
# 3. Connect to DB
|
|
|
|
|
|
most often we do that from inside the container
|
|
|
|
... | ... | @@ -43,9 +43,9 @@ select * from sys_user; # executes that select query (or whatever query you like |
|
|
\q # to quit
|
|
|
```
|
|
|
|
|
|
# 3. Backup restore
|
|
|
# 4. Backup restore
|
|
|
|
|
|
## 3.1. Daily backup
|
|
|
## 4.1. Daily backup
|
|
|
|
|
|
From memory (so untested):
|
|
|
|
... | ... | @@ -108,7 +108,7 @@ aws_access_key_id = 6bd6bd1a6bb0464693d7b898b61acf21 |
|
|
aws_secret_access_key = 33c6dba1bd1346aba76238edbb980dfb
|
|
|
```
|
|
|
|
|
|
## 3.2. Restore
|
|
|
## 4.2. Restore
|
|
|
|
|
|
The restore can be done with command `pg_restore`. For instance
|
|
|
|
... | ... | |