|
This page will tell you the short and to the point stuff you need to know about using Postgres DB's
|
|
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)
|
|
- [1. Install on Ubuntu 20.\*](#1-install-on-ubuntu-20)
|
|
|
|
- [Passwords](#passwords)
|
|
- [2. Connect to DB](#2-connect-to-db)
|
|
- [2. Connect to DB](#2-connect-to-db)
|
|
- [3. Backup restore](#3-backup-restore)
|
|
- [3. Backup restore](#3-backup-restore)
|
|
- [3.1. Daily backup](#31-daily-backup)
|
|
- [3.1. Daily backup](#31-daily-backup)
|
... | @@ -19,14 +20,27 @@ Check status: `sudo systemctl status postgresql.service` |
... | @@ -19,14 +20,27 @@ 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.
|
|
See [this tutorial](https://www.digitalocean.com/community/tutorials/how-to-install-postgresql-on-ubuntu-20-04-quickstart) for more info.
|
|
|
|
|
|
# 2. Connect to DB
|
|
# 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`:
|
|
|
|
|
|
```bash
|
|
```bash
|
|
sudo -u postgres psql # to open psql (the interactive command line interface to the DB)
|
|
docker exec -t wyatt_local_db bash -c 'echo "localhost:5432:wyatt:wyatt:somepasswd" > ~/.pgpass && chmod 0600 ~/.pgpass'
|
|
\l # to list all DB's
|
|
```
|
|
\c <name of DB> # to connect to one DB
|
|
|
|
\d # to describe all in that DB
|
|
|
|
|
|
|
|
|
|
# 2. Connect to DB
|
|
|
|
|
|
|
|
most often we do that from inside the container
|
|
|
|
|
|
|
|
```bash
|
|
|
|
docker ps # get a listing of all containers on this machine
|
|
|
|
docker exec -it wyatt_local_db bash # open an interactive terminal to the DB container called wyatt_local_db
|
|
|
|
psql -U wyatt # to open psql as user wyatt to interact with the DB (the interactive command line interface to the DB)
|
|
|
|
\l # lists all DB's
|
|
|
|
\c <name of DB> # connects to DB
|
|
|
|
\d # describes all in that DB
|
|
|
|
select * from sys_user; # executes that select query (or whatever query you like)
|
|
|
|
\q # to quit
|
|
```
|
|
```
|
|
|
|
|
|
# 3. Backup restore
|
|
# 3. Backup restore
|
... | @@ -86,7 +100,7 @@ echo Upload to S3... |
... | @@ -86,7 +100,7 @@ echo Upload to S3... |
|
aws $AWS_OPTS s3 cp $ZIPFILE s3://backups/$CONTAINER/$NAME.zip
|
|
aws $AWS_OPTS s3 cp $ZIPFILE s3://backups/$CONTAINER/$NAME.zip
|
|
```
|
|
```
|
|
|
|
|
|
Note that S3 requires credentials that can be stored in a file called `/home/devops/.aws/credentials/`. Here is an example content of that file (with fake keys of course):
|
|
Note that S3 requires credentials that can be stored in a file called `~/.aws/credentials/`. Here is an example content of that file (with fake keys of course):
|
|
|
|
|
|
```properties
|
|
```properties
|
|
[default]
|
|
[default]
|
... | | ... | |