# Postgre SQL from a container in 10 minutes
### Work in progress

Procedure for mac OS :) NOTE: you can use dockerhub, gcr or any other container image hub of your taste.

Use case:

Policy Security enforcement in your machine aka folder permissions errors :)

REquirements:

- dockerhub account and docker desktop CE edition installed
- you should be login in docker hub on your cli to be able to issue `docker pull` etc.
- PGadmin installed to have a nice GUI (not the Server from Installer)

## Procedure

docker-compose.yml

version: '3'

services:
  postgresql:
    image: docker.io/bitnami/postgresql:latest
    env_file:
      - database.env
    ports:
      - '5432:5432'
    volumes:
      - /Users/rcastaneda/000_DS_vray_magnimind/Postgresql-persistence:/bitnami/postgresql

volumes:
  postgresql_data:   
    driver: local

database.env

POSTGRES_USER=vray
POSTGRES_PASSWORD=password123
POSTGRES_DB=posgres_db

create a folde in which you will use for persistent data on postgreDB
for example:

`/Users/rcastaneda/000_DS_vray_magnimind/Postgresql-persistence`

then just adjust the path to this folder in the `docker-compose.yml` file, also custom the `database.env` with your own data.

the set of files for this quick recipe will looks like this:


issue the followind comand:

`docker-compose up`
`docker-compose up -d`

this will connect to docker hub and get the latest postgresql docker image from bitnami, I use bitnami cause they are some secure.

when you see something like this: 

you are good to open PGadmin:

enter your master password nad delete any server there.

then just rick-click on servers adn select `create` then `Server...`

add a fancy name to your connection and in the *Connection* tab enter the data corresponding to the`database.env` file, somehting like this :


click `Save` and enjoy.


----

# **Restore dvdrental database**

----
----
----

all you need is these files:

- inside you binding

- getdvdrentaldb.sh
- restoredb.sh
- whataever you run docker-compose

- database.env
- docker-compose.yml

check on file `docker-compose.yml` there will be 2 binidngs and one volume, the bindings are use to pass the init script to restore the dvd rental database and the other one to pass the tar file of dvd rentals to the docker container, finally you have a volume mount that will be use exclusively for persistent data.

### step 0

crerate teh routes check the file `docker-compose.yml` below to set them according your naming convention.

### step 1

this is to get the dvdrental.zip I create a simple script for that but either run it on your binding properly or just copy the .tar file to the route.

       │ File: getdvdrentaldb.sh
───────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1   │ #!/bin/sh
   2   │ export ARCH_NAME="dvdrental.zip"
   3   │ export RES_FILE="dvdrental.tar"
   4   │ export URL="https://www.postgresqltutorial.com/wp-content/uploads/2019/05/${ARCH_NAME}"
   5   │ wget -nv "${URL}" -O "${ARCH_NAME}" && unzip "${ARCH_NAME}" 
   6   │ 

### Step 2

copy the file `restoredb.sh` to the same path or route you create accoring to `docker-compose.yaml`

───────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
       │ File: restoredb.sh
───────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1 ~ │ #!/bin/bash
   2   │ set -e
   3 ~ │ export PGPASSWORD=$POSTGRES_PASSWORD;
   4 ~ │ pg_restore -U postgres -d "$POSTGRESQL_DATABASE" /tmp/dvdrental.tar

### step 3

set the variable as you want in file `database.env`

───────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
       │ File: database.env
───────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1 ~ │ POSTGRESQL_USERNAME=dvdrental
   2 ~ │ POSTGRESQL_PASSWORD=dvdrental
   3 ~ │ POSTGRESQL_DATABASE=dvdrental
   4   │ POSTGRESQL_POSTGRES_PASSWORD=password123

Note: `postgre` superuser will be there so we just set the password in order to be able ot execute as ownert of dvdrental database when restoring. check step 2

### step 4

run `docker-compose up` in same folder as you yml file this will get the bitnami dockerhub image, set the binding and the volume, fix permissions to be able to map them inside the container.

docker-compose.yml

───────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
       │ File: docker-compose.yml
───────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
   1 ~ │ version: '2'
   2   │ services:
   3   │   postgresql:
   4 ~ │     image: docker.io/bitnami/postgresql:14
   5   │     env_file:
   6   │       - database.env
   7   │     ports:
   8   │       - '5432:5432'
   9   │     volumes:
  10 ~ │       - '/Users/rcastaneda/000_DS_vray_magnimind/postgresqldata:/bitnami/postgresql'
  11 ~ │       - './db/restoredb.sh:/docker-entrypoint-initdb.d/restoredb.sh'
  12 ~ │       - './db/dvdrental.tar:/tmp/dvdrental.tar'
  13   │   fix-postgresql-permissions:
  14 ~ │     image: docker.io/bitnami/postgresql:14
  15   │     user: root
  16   │     command: chown -R 1001:1001 /bitnami/postgresql
  17   │     command: chown -R 1001:1001 /docker-entrypoint-initdb.d/restoredb.sh
  18 + │     command: chown -R 1001:1001 /tmp/dvdrental.tar
  19   │     volumes: 
  20 ~ │       - '/Users/rcastaneda/000_DS_vray_magnimind/postgresqldata:/bitnami/postgresql'
  21 ~ │       - './db/restoredb.sh:/docker-entrypoint-initdb.d/restoredb.sh'
  22 ~ │       - './db/dvdrental.tar:/tmp/dvdrental.tar'

Again depending on your taste you can name the folders prerly for me here is the list of them:
- ./db
- /Users/rcastaneda/000_DS_vray_magnimind/postgresqldata

Note remember to set permissions to shell scripts the old `chmod +x file.sh`

If everything is ok you will get the engine up and running, the dvd rental db restore in teh container and the persistent data in your local machine, that means that everytime you reboot your dabases will preserve.

Note you can do `Ctrl+C` to stop the container next time you need it try to use `docker-compose up -d` to start the container and the `docker-compose down` to stop it.

once running just open PGadmin and will have a portable postgres in you local machine to pl4y :).



if you feel courious on this or just for save time here are some commands :

. docker ps -a :to see all process aka containers
. docker images : to see al images on local container registry aka you local machine
. docker rmi -f postgres:14 :to remove force way a docker image from the local registry
. docker container pruner : to purge all stop containers and reclaim storage
. docker stop $(docker ps -a -q) : to stop all containers that are "exit state"
. docker inspect : to chec confid details on container

. dive container_name : to se the layers of building on this container

to see mounts on container:

`docker ps -a`

 CONTAINER ID   IMAGE                   COMMAND                  CREATED          STATUS                      PORTS                    NAMES
89f4af8eb40f   bitnami/postgresql:14   "/opt/bitnami/script…"   34 minutes ago   Exited (0) 34 minutes ago                            sql_stuff-fix-postgresql-permissions-1
a7553c90f600   bitnami/postgresql:14   "/opt/bitnami/script…"   34 minutes ago   Up 34 minutes               0.0.0.0:5432->5432/tcp   sql_stuff-postgresql-1

then

`docker inspect -f '{{ json .Mounts }}' a7553c90f600 | python -m json.tool`

 [
    {
        "Type": "volume",
        "Name": "63fb76b139cf10c2539a2e9c6e395a72d57c7ccd7fa1baa08cbaabbf4a09dfe1",
        "Source": "/var/lib/docker/volumes/63fb76b139cf10c2539a2e9c6e395a72d57c7ccd7fa1baa08cbaabbf4a09dfe1/_data",
        "Destination": "/docker-entrypoint-preinitdb.d",
        "Driver": "local",
        "Mode": "",
        "RW": true,
        "Propagation": ""
    },
    {
        "Type": "bind",
        "Source": "/Users/rcastaneda/000_DS_vray_magnimind/SQL_stuff/db/dvdrental.tar",
        "Destination": "/tmp/dvdrental.tar",
        "Mode": "rw",
        "RW": true,
        "Propagation": "rprivate"
    },
    {
        "Type": "bind",
        "Source": "/Users/rcastaneda/000_DS_vray_magnimind/postgresqldata",
        "Destination": "/bitnami/postgresql",
        "Mode": "rw",
        "RW": true,
        "Propagation": "rprivate"
    },
    {
        "Type": "bind",
        "Source": "/Users/rcastaneda/000_DS_vray_magnimind/SQL_stuff/db/restoredb.sh",
        "Destination": "/docker-entrypoint-initdb.d/restoredb.sh",
        "Mode": "rw",
        "RW": true,
        "Propagation": "rprivate"
    },
    {
        "Type": "volume",
        "Name": "71a1180cbc4ae500c2dbb7fb07ad0198f6cc1cf729a0e3b87389796de854732f",
        "Source": "/var/lib/docker/volumes/71a1180cbc4ae500c2dbb7fb07ad0198f6cc1cf729a0e3b87389796de854732f/_data",
        "Destination": "/docker-entrypoint-initdb.d",
        "Driver": "local",
        "Mode": "",
        "RW": true,
        "Propagation": ""
    }
]

# Restore any DB on docker container bitnami Postgres

the easy way is as follows although can be automated thru script or something else:

#### Step 1
run `docker ps` from there take the CONTAINER ID other folks tend to work with name but I rather prefer the ID.

check the volumes of this container refer to previous steps if you liek to set a new one for this restore.

just note that `a7553c90f600` i s my postgres cntrn id.
this will get you this example output:

![](https://i.imgur.com/JUWV97G.png)

I just select `"bitnami/postgresql"` volume.

#### Step 2
run 
`docker cp my_db_dump_file a7553c90f600:/bitnami/postgresql`

#### Step 3
then run 
`docker exec -it a7553c90f600 /bin/bash`

move to the file location if you prefer to:
`cd bitnami/postgresql/`

#### Step 4
On PGadmin jsut create a db with name that will be used to restore from dump just name it and leave postgres as owner.(refer to steps previous in this doc)

#### Step 5.a
run the command
`pg_restore -U postgres -d temp_parch_and_posey ./parch_and_posey_db_archivedump`
 you will be ask for password so this is postgres password
 ![](https://i.imgur.com/nhKgT9z.png)

#### Step 5.b
The alternative to this for example if you are using postgres image instead of bitnami iamge is to run the command 
`docker exec pg_restore -U postgres -d temp_parch_and_posey ./parch_and_posey_db_archivedump`
just note that you need to set the env variable with teh postgres password to excute this.

![](https://i.imgur.com/ZWjHJ4v.png)


I found this [gist](https://gist.github.com/natrod/25977493a460afe76d0c6c8f4a6d7f5d) with many good things related to docker commands hope your find useful.
 
 
 PD will try to create a repo on this someday :) ...