---
# System prepended metadata

title: Docker - Compose - PostgreSQL
tags: [Docker, PostgreSQL, Compose]

---

# Docker - Compose - PostgreSQL

###### tags: `Docker` `Compose` `PostgreSQL`

# Reference

* [Docker Image for postgres](https://hub.docker.com/_/postgres)
* [Exploring PostgreSQL with Docker](https://markheath.net/post/exploring-postgresql-with-docker)



---

我想知的是, 是否可以指定 

environment 
port #, db user/ password
data 
log

等等的, 充其是 db 檔案的存放位置
這樣我可以從外部把 db 備份檔載回來, 倒入
或直接建立新的, 然後馬上使用。

---

# Exercise 

## Date 

* 2021/02/08

使用 Docker Compose 的好處是不用安裝 Postgres, 且也不用像這篇文章所說，
[Setting up a remote Postgres database server on Ubuntu 18.04](https://blog.logrocket.com/setting-up-a-remote-postgres-database-server-on-ubuntu-18-04/)
要去改 ``/etc/postgresql/10/main/postgresql.conf`` 組態檔,
就可以直接從 Windows 連上 VM 中 Docker 中的 Postgres



---

## 1. yml file:

* ``elite_lin_postgresql2_docker-compose.yml``:
```
version: "3"
services:
  database:
    image: postgres:13 # use latest official postgres version
    environment:
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data/      

volumes:
  postgres_data: # named volumes can be managed easier using docker-compose
    driver: local
    driver_opts:
      o: bind
      type: none
      device: /home/elite_lin/Docker/yaml_defs/postgresql/
```

### 1.1 Run:

```
$ docker-compose -f elite_lin_postgresql2_docker-compose.yml config
services:
  database:
    environment:
      POSTGRES_PASSWORD: postgres
    image: postgres:13
    ports:
    - published: 5432
      target: 5432
    volumes:
    - postgres_data:/var/lib/postgresql/data:rw
version: '3'
volumes:
  postgres_data:
    driver: local
    driver_opts:
      device: /home/elite_lin/Docker/yaml_defs/postgresql
      o: bind
      type: none

$ docker-compose -f elite_lin_postgresql2_docker-compose.yml up -d
Building with native build. Learn about native build in Compose here: https://docs.docker.com/go/compose-native-build/
Creating volume "yaml_defs_postgres_data" with local driver
Creating yaml_defs_database_1 ... done

$ docker-compose -f elite_lin_postgresql2_docker-compose.yml ps
        Name                      Command              State           Ports         
-------------------------------------------------------------------------------------
yaml_defs_database_1   docker-entrypoint.sh postgres   Up      0.0.0.0:5432->5432/tcp

$ docker volume inspect yaml_defs_postgres_data 
[
    {
        "CreatedAt": "2021-02-08T17:17:50+08:00",
        "Driver": "local",
        "Labels": {
            "com.docker.compose.project": "yaml_defs",
            "com.docker.compose.version": "1.28.2",
            "com.docker.compose.volume": "postgres_data"
        },
        "Mountpoint": "/var/lib/docker/volumes/yaml_defs_postgres_data/_data",
        "Name": "yaml_defs_postgres_data",
        "Options": {
            "device": "/home/elite_lin/Docker/yaml_defs/postgresql",
            "o": "bind",
            "type": "none"
        },
        "Scope": "local"
    }
]

```

### 1.2 Stop and remove:

```
docker-compose -f elite_lin_postgresql2_docker-compose.yml down

Stopping yaml_defs_database_1 ... done
Removing yaml_defs_database_1 ... done
Removing network yaml_defs_default
```

### Trouble shooting

#### 1.
```
docker-compose -f postgresql_docker-compose.yml up -d
Creating volume "docker_postgres_data" with local driver
Creating docker_database_1 ... error

ERROR: for docker_database_1  Cannot create container for service database: 
failed to mount local volume: 
mount /home/elite_lin/docker/yaml_defs/postgresql:/var/lib/docker/volumes/docker_postgres_data/_data, 
flags: 0x1000: no such file or directory

ERROR: for database  Cannot create container for service database: 
failed to mount local volume: mount /home/elite_lin/docker/yaml_defs/postgresql:/var/lib/docker/volumes/docker_postgres_data/_data, 
flags: 0x1000: no such file or directory
ERROR: Encountered errors while bringing up the project.
```

小心路徑打錯!!

#### 2.

```
docker-compose -f elite_lin_postgresql_1.yml up -d
Building with native build. Learn about native build in Compose here: https://docs.docker.com/go/compose-native-build/
Creating network "yaml_defs_default" with the default driver
Creating yaml_defs_database_1 ... 
Creating yaml_defs_database_1 ... error

ERROR: for yaml_defs_database_1  Cannot start service database: driver failed programming external connectivity on endpoint yaml_defs_database_1 (ec1db138a4b75a201c541166ffc2e063402de151c7d3790c493e5977f80b556a): Error starting userland proxy: listen tcp4 0.0.0.0:5432: bind: address already in use

ERROR: for database  Cannot start service database: driver failed programming external connectivity on endpoint yaml_defs_database_1 (ec1db138a4b75a201c541166ffc2e063402de151c7d3790c493e5977f80b556a): Error starting userland proxy: listen tcp4 0.0.0.0:5432: bind: address already in use
ERROR: Encountered errors while bringing up the project.
```

忘了我的 VM 原本就有跑 PostgreSql...

#### [Docker & Postgres: Failed to bind tcp 0.0.0.0:5432 address already in use](https://stackoverflow.com/questions/38249434/docker-postgres-failed-to-bind-tcp-0-0-0-05432-address-already-in-use)

I have postgres installed locally, but I stopped it and running
```
pg_ctl status
```

returns
>pg_ctl: no server running

I've ran the following to check what's running on 5432
```
lsof -i tcp:5432
```

and
```
netstat -anp tcp | grep 5432
```
and nothing is running on the port.


Answer:
If ``lsof -i :5432`` doesn't show you any output, 
you can use ``sudo ss -lptn 'sport = :5432'`` to see what process is bound to the port.

Proceed further with ``kill <pid>``


---

## 2. 連上 postgresql container:

### 2.1 方法1:

format:
```
docker exec -it [container_name] psql -U [postgres_user] [database_name]
```

my case:
```
docker exec -it yaml_defs_database_1 psql -U postgres
postgres-# 
```

```
docker exec -it yaml_defs_database_1 psql -U postgres gfi_sso_db1
postgres-# 
```

### 2.2 方法 2:

format:
```
docker exec -it [container_name] bash
```

my case:
```
docker exec -it yaml_defs_database_1 bash
root@4b9d251e6b09:/#
```


### 2.3 方法 3:

format:
```
docker-compose run database bash
```

my case:
```
docker-compose -f elite_lin_postgresql1_docker-compose.yml run database bash
Creating yaml_defs_database_run ... done
root@4b9d251e6b09:/#
```

### 2.4 方法 2/ 3 進入 database:

format - connect to default database ``postgres``:

```
/usr/bin/psql -U postgres
postgres-# 
```

---

format: connect to specific database:

```
/usr/bin/psql -U [database_name]
```

my case:

```
/usr/bin/psql -U gfi_sso_db1
gfi_sso_db1=#
```

## 3. restore from backup_file:

### 3.1 建立 DB user

```
CREATE USER goglobaldev WITH PASSWORD 'gfi8158456';
ALTER ROLE goglobaldev SET client_encoding TO 'utf8';
ALTER ROLE goglobaldev SET default_transaction_isolation TO 'read committed';
ALTER ROLE goglobaldev SET timezone TO 'UTC';
```

### 3.2 建立 DB

```
CREATE DATABASE gfi_sso_db1;
```

### 3.3 先將 backup_file 從 host 複製到 container 中

format:
```
docker cp /hostfile  (container_id):/(to_the_place_you_want_the_file_to_be)
```

my case:
```
docker cp /home/elite_lin/AWS/gfi_sso_db1-2021-02-08 yaml_defs_database_1:/home/
```

### 3.4 執行以下指令：

```
root@00e2cb544a51:/# /usr/bin/psql gfi_sso_db1 < /home/gfi_sso_db1-2021-02-08
psql: error: FATAL:  role "root" does not exist
root@00e2cb544a51:/# /usr/bin/psql -U postgres gfi_sso_db1 < /home/gfi_sso_db1-2021-02-08
The input is a PostgreSQL custom-format dump.
Use the pg_restore command-line client to restore this dump to a database.
```

```
root@00e2cb544a51:/home# pg_restore -U postgres --dbname=gfi_sso_db1 --no-owner --no-acl --verbose /home/gfi_sso_db1-2021-02-08
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "public"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;


pg_restore: creating COMMENT "SCHEMA public"
pg_restore: creating EXTENSION "plpgsql"
pg_restore: creating COMMENT "EXTENSION plpgsql"
pg_restore: creating TABLE "public.app1_appsettingbackup"
pg_restore: creating SEQUENCE "public.app1_appsettingbackup_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.app1_appsettingbackup_id_seq"
pg_restore: creating TABLE "public.app1_dongleinfo"
pg_restore: creating SEQUENCE "public.app1_dongleinfo_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.app1_dongleinfo_id_seq"
pg_restore: creating TABLE "public.app1_dongleprofile"
pg_restore: creating SEQUENCE "public.app1_dongleprofile_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.app1_dongleprofile_id_seq"
pg_restore: creating TABLE "public.app1_groupfile"
pg_restore: creating SEQUENCE "public.app1_groupfile_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.app1_groupfile_id_seq"
pg_restore: creating TABLE "public.app1_profilebackup"
pg_restore: creating SEQUENCE "public.app1_profilebackup_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.app1_profilebackup_id_seq"
pg_restore: creating TABLE "public.app1_programblue"
pg_restore: creating SEQUENCE "public.app1_programblue_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.app1_programblue_id_seq"
pg_restore: creating TABLE "public.app1_programrefugium"
pg_restore: creating SEQUENCE "public.app1_programrefugium_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.app1_programrefugium_id_seq"
pg_restore: creating TABLE "public.app1_programsun"
pg_restore: creating SEQUENCE "public.app1_programsun_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.app1_programsun_id_seq"
pg_restore: creating TABLE "public.app1_programzeroten"
pg_restore: creating SEQUENCE "public.app1_programzeroten_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.app1_programzeroten_id_seq"
pg_restore: creating TABLE "public.app1_requestlog"
pg_restore: creating SEQUENCE "public.app1_requestlog_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.app1_requestlog_id_seq"
pg_restore: creating TABLE "public.app1_userfilebackup"
pg_restore: creating SEQUENCE "public.app1_userfilebackup_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.app1_userfilebackup_id_seq"
pg_restore: creating TABLE "public.auth_group"
pg_restore: creating SEQUENCE "public.auth_group_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.auth_group_id_seq"
pg_restore: creating TABLE "public.auth_group_permissions"
pg_restore: creating SEQUENCE "public.auth_group_permissions_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.auth_group_permissions_id_seq"
pg_restore: creating TABLE "public.auth_permission"
pg_restore: creating SEQUENCE "public.auth_permission_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.auth_permission_id_seq"
pg_restore: creating TABLE "public.auth_user"
pg_restore: creating TABLE "public.auth_user_groups"
pg_restore: creating SEQUENCE "public.auth_user_groups_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.auth_user_groups_id_seq"
pg_restore: creating SEQUENCE "public.auth_user_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.auth_user_id_seq"
pg_restore: creating TABLE "public.auth_user_user_permissions"
pg_restore: creating SEQUENCE "public.auth_user_user_permissions_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.auth_user_user_permissions_id_seq"
pg_restore: creating TABLE "public.authtoken_token"
pg_restore: creating TABLE "public.django_admin_log"
pg_restore: creating SEQUENCE "public.django_admin_log_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.django_admin_log_id_seq"
pg_restore: creating TABLE "public.django_content_type"
pg_restore: creating SEQUENCE "public.django_content_type_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.django_content_type_id_seq"
pg_restore: creating TABLE "public.django_migrations"
pg_restore: creating SEQUENCE "public.django_migrations_id_seq"
pg_restore: creating SEQUENCE OWNED BY "public.django_migrations_id_seq"
pg_restore: creating TABLE "public.django_session"
pg_restore: creating DEFAULT "public.app1_appsettingbackup id"
pg_restore: creating DEFAULT "public.app1_dongleinfo id"
pg_restore: creating DEFAULT "public.app1_dongleprofile id"
pg_restore: creating DEFAULT "public.app1_groupfile id"
pg_restore: creating DEFAULT "public.app1_profilebackup id"
pg_restore: creating DEFAULT "public.app1_programblue id"
pg_restore: creating DEFAULT "public.app1_programrefugium id"
pg_restore: creating DEFAULT "public.app1_programsun id"
pg_restore: creating DEFAULT "public.app1_programzeroten id"
pg_restore: creating DEFAULT "public.app1_requestlog id"
pg_restore: creating DEFAULT "public.app1_userfilebackup id"
pg_restore: creating DEFAULT "public.auth_group id"
pg_restore: creating DEFAULT "public.auth_group_permissions id"
pg_restore: creating DEFAULT "public.auth_permission id"
pg_restore: creating DEFAULT "public.auth_user id"
pg_restore: creating DEFAULT "public.auth_user_groups id"
pg_restore: creating DEFAULT "public.auth_user_user_permissions id"
pg_restore: creating DEFAULT "public.django_admin_log id"
pg_restore: creating DEFAULT "public.django_content_type id"
pg_restore: creating DEFAULT "public.django_migrations id"
pg_restore: processing data for table "public.app1_appsettingbackup"
pg_restore: executing SEQUENCE SET app1_appsettingbackup_id_seq
pg_restore: processing data for table "public.app1_dongleinfo"
pg_restore: executing SEQUENCE SET app1_dongleinfo_id_seq
pg_restore: processing data for table "public.app1_dongleprofile"
pg_restore: executing SEQUENCE SET app1_dongleprofile_id_seq
pg_restore: processing data for table "public.app1_groupfile"
pg_restore: executing SEQUENCE SET app1_groupfile_id_seq
pg_restore: processing data for table "public.app1_profilebackup"
pg_restore: executing SEQUENCE SET app1_profilebackup_id_seq
pg_restore: processing data for table "public.app1_programblue"
pg_restore: executing SEQUENCE SET app1_programblue_id_seq
pg_restore: processing data for table "public.app1_programrefugium"
pg_restore: executing SEQUENCE SET app1_programrefugium_id_seq
pg_restore: processing data for table "public.app1_programsun"
pg_restore: executing SEQUENCE SET app1_programsun_id_seq
pg_restore: processing data for table "public.app1_programzeroten"
pg_restore: executing SEQUENCE SET app1_programzeroten_id_seq
pg_restore: processing data for table "public.app1_requestlog"
pg_restore: executing SEQUENCE SET app1_requestlog_id_seq
pg_restore: processing data for table "public.app1_userfilebackup"
pg_restore: executing SEQUENCE SET app1_userfilebackup_id_seq
pg_restore: processing data for table "public.auth_group"
pg_restore: executing SEQUENCE SET auth_group_id_seq
pg_restore: processing data for table "public.auth_group_permissions"
pg_restore: executing SEQUENCE SET auth_group_permissions_id_seq
pg_restore: processing data for table "public.auth_permission"
pg_restore: executing SEQUENCE SET auth_permission_id_seq
pg_restore: processing data for table "public.auth_user"
pg_restore: processing data for table "public.auth_user_groups"
pg_restore: executing SEQUENCE SET auth_user_groups_id_seq
pg_restore: executing SEQUENCE SET auth_user_id_seq
pg_restore: processing data for table "public.auth_user_user_permissions"
pg_restore: executing SEQUENCE SET auth_user_user_permissions_id_seq
pg_restore: processing data for table "public.authtoken_token"
pg_restore: processing data for table "public.django_admin_log"
pg_restore: executing SEQUENCE SET django_admin_log_id_seq
pg_restore: processing data for table "public.django_content_type"
pg_restore: executing SEQUENCE SET django_content_type_id_seq
pg_restore: processing data for table "public.django_migrations"
pg_restore: executing SEQUENCE SET django_migrations_id_seq
pg_restore: processing data for table "public.django_session"
pg_restore: creating CONSTRAINT "public.app1_appsettingbackup app1_appsettingbackup_pkey"
pg_restore: creating CONSTRAINT "public.app1_dongleinfo app1_dongleinfo_id_sn_8584b389_uniq"
pg_restore: creating CONSTRAINT "public.app1_dongleinfo app1_dongleinfo_pkey"
pg_restore: creating CONSTRAINT "public.app1_dongleinfo app1_dongleinfo_sn_key"
pg_restore: creating CONSTRAINT "public.app1_dongleprofile app1_dongleprofile_pkey"
pg_restore: creating CONSTRAINT "public.app1_dongleprofile app1_dongleprofile_user_id_dongle_info_id_1e5b425f_uniq"
pg_restore: creating CONSTRAINT "public.app1_groupfile app1_groupfile_pkey"
pg_restore: creating CONSTRAINT "public.app1_profilebackup app1_profilebackup_pkey"
pg_restore: creating CONSTRAINT "public.app1_programblue app1_programblue_pkey"
pg_restore: creating CONSTRAINT "public.app1_programrefugium app1_programrefugium_pkey"
pg_restore: creating CONSTRAINT "public.app1_programsun app1_programsun_pkey"
pg_restore: creating CONSTRAINT "public.app1_programzeroten app1_programzeroten_pkey"
pg_restore: creating CONSTRAINT "public.app1_requestlog app1_requestlog_pkey"
pg_restore: creating CONSTRAINT "public.app1_userfilebackup app1_userfilebackup_pkey"
pg_restore: creating CONSTRAINT "public.auth_group auth_group_name_key"
pg_restore: creating CONSTRAINT "public.auth_group_permissions auth_group_permissions_group_id_permission_id_0cd325b0_uniq"
pg_restore: creating CONSTRAINT "public.auth_group_permissions auth_group_permissions_pkey"
pg_restore: creating CONSTRAINT "public.auth_group auth_group_pkey"
pg_restore: creating CONSTRAINT "public.auth_permission auth_permission_content_type_id_codename_01ab375a_uniq"
pg_restore: creating CONSTRAINT "public.auth_permission auth_permission_pkey"
pg_restore: creating CONSTRAINT "public.auth_user_groups auth_user_groups_pkey"
pg_restore: creating CONSTRAINT "public.auth_user_groups auth_user_groups_user_id_group_id_94350c0c_uniq"
pg_restore: creating CONSTRAINT "public.auth_user auth_user_id_social_id_f76d6998_uniq"
pg_restore: creating CONSTRAINT "public.auth_user auth_user_pkey"
pg_restore: creating CONSTRAINT "public.auth_user auth_user_social_id_key"
pg_restore: creating CONSTRAINT "public.auth_user_user_permissions auth_user_user_permissions_pkey"
pg_restore: creating CONSTRAINT "public.auth_user_user_permissions auth_user_user_permissions_user_id_permission_id_14a6b632_uniq"
pg_restore: creating CONSTRAINT "public.authtoken_token authtoken_token_pkey"
pg_restore: creating CONSTRAINT "public.authtoken_token authtoken_token_user_id_key"
pg_restore: creating CONSTRAINT "public.django_admin_log django_admin_log_pkey"
pg_restore: creating CONSTRAINT "public.django_content_type django_content_type_app_label_model_76bd3d3b_uniq"
pg_restore: creating CONSTRAINT "public.django_content_type django_content_type_pkey"
pg_restore: creating CONSTRAINT "public.django_migrations django_migrations_pkey"
pg_restore: creating CONSTRAINT "public.django_session django_session_pkey"
pg_restore: creating INDEX "public.app1_appsettingbackup_dongle_profile_id_7029ea6e"
pg_restore: creating INDEX "public.app1_appsettingbackup_group_file_id_c9f57f43"
pg_restore: creating INDEX "public.app1_appsettingbackup_program_blue_id_1e203984"
pg_restore: creating INDEX "public.app1_appsettingbackup_program_refugium_id_04ed3334"
pg_restore: creating INDEX "public.app1_appsettingbackup_program_sun_id_84a675d7"
pg_restore: creating INDEX "public.app1_appsettingbackup_program_zero_ten_id_13aef6af"
pg_restore: creating INDEX "public.app1_dongleinfo_sn_23884794_like"
pg_restore: creating INDEX "public.app1_dongleprofile_dongle_info_id_31bb322b"
pg_restore: creating INDEX "public.app1_dongleprofile_dongle_info_id_31bb322b_like"
pg_restore: creating INDEX "public.app1_dongleprofile_user_id_2501f2e3"
pg_restore: creating INDEX "public.app1_profilebackup_dongle_profile_id_907d902a"
pg_restore: creating INDEX "public.app1_profilebackup_group_file_id_d001e5ff"
pg_restore: creating INDEX "public.app1_profilebackup_program_blue_id_7107497d"
pg_restore: creating INDEX "public.app1_profilebackup_program_refugium_id_29fa9557"
pg_restore: creating INDEX "public.app1_profilebackup_program_sun_id_f975d411"
pg_restore: creating INDEX "public.app1_profilebackup_program_zero_ten_id_b81e5481"
pg_restore: creating INDEX "public.app1_requestlog_user_id_4eab611a"
pg_restore: creating INDEX "public.app1_userfilebackup_group_file_id_dcf22778"
pg_restore: creating INDEX "public.app1_userfilebackup_program_blue_id_c7121ef5"
pg_restore: creating INDEX "public.app1_userfilebackup_program_refugium_id_c3ba3283"
pg_restore: creating INDEX "public.app1_userfilebackup_program_sun_id_79c2474f"
pg_restore: creating INDEX "public.app1_userfilebackup_program_zero_ten_id_6344a9ec"
pg_restore: creating INDEX "public.app1_userfilebackup_user_id_24d4c825"
pg_restore: creating INDEX "public.auth_group_name_a6ea08ec_like"
pg_restore: creating INDEX "public.auth_group_permissions_group_id_b120cbf9"
pg_restore: creating INDEX "public.auth_group_permissions_permission_id_84c5c92e"
pg_restore: creating INDEX "public.auth_permission_content_type_id_2f476e4b"
pg_restore: creating INDEX "public.auth_user_groups_group_id_97559544"
pg_restore: creating INDEX "public.auth_user_groups_user_id_6a12ed8b"
pg_restore: creating INDEX "public.auth_user_social_id_167687b0_like"
pg_restore: creating INDEX "public.auth_user_user_permissions_permission_id_1fbb5f2c"
pg_restore: creating INDEX "public.auth_user_user_permissions_user_id_a95ead1b"
pg_restore: creating INDEX "public.authtoken_token_key_10f0b77e_like"
pg_restore: creating INDEX "public.django_admin_log_content_type_id_c4bce8eb"
pg_restore: creating INDEX "public.django_admin_log_user_id_c564eba6"
pg_restore: creating INDEX "public.django_session_expire_date_a5c62663"
pg_restore: creating INDEX "public.django_session_session_key_c0390e0f_like"
pg_restore: creating FK CONSTRAINT "public.app1_appsettingbackup app1_appsettingbacku_dongle_profile_id_7029ea6e_fk_app1_dong"
pg_restore: creating FK CONSTRAINT "public.app1_appsettingbackup app1_appsettingbacku_group_file_id_c9f57f43_fk_app1_grou"
pg_restore: creating FK CONSTRAINT "public.app1_appsettingbackup app1_appsettingbacku_program_blue_id_1e203984_fk_app1_prog"
pg_restore: creating FK CONSTRAINT "public.app1_appsettingbackup app1_appsettingbacku_program_refugium_id_04ed3334_fk_app1_prog"
pg_restore: creating FK CONSTRAINT "public.app1_appsettingbackup app1_appsettingbacku_program_sun_id_84a675d7_fk_app1_prog"
pg_restore: creating FK CONSTRAINT "public.app1_appsettingbackup app1_appsettingbacku_program_zero_ten_id_13aef6af_fk_app1_prog"
pg_restore: creating FK CONSTRAINT "public.app1_dongleprofile app1_dongleprofile_dongle_info_id_31bb322b_fk_app1_dong"
pg_restore: creating FK CONSTRAINT "public.app1_dongleprofile app1_dongleprofile_user_id_2501f2e3_fk_auth_user_id"
pg_restore: creating FK CONSTRAINT "public.app1_profilebackup app1_profilebackup_dongle_profile_id_907d902a_fk_app1_dong"
pg_restore: creating FK CONSTRAINT "public.app1_profilebackup app1_profilebackup_group_file_id_d001e5ff_fk_app1_groupfile_id"
pg_restore: creating FK CONSTRAINT "public.app1_profilebackup app1_profilebackup_program_blue_id_7107497d_fk_app1_prog"
pg_restore: creating FK CONSTRAINT "public.app1_profilebackup app1_profilebackup_program_refugium_id_29fa9557_fk_app1_prog"
pg_restore: creating FK CONSTRAINT "public.app1_profilebackup app1_profilebackup_program_sun_id_f975d411_fk_app1_prog"
pg_restore: creating FK CONSTRAINT "public.app1_profilebackup app1_profilebackup_program_zero_ten_id_b81e5481_fk_app1_prog"
pg_restore: creating FK CONSTRAINT "public.app1_requestlog app1_requestlog_user_id_4eab611a_fk_auth_user_id"
pg_restore: creating FK CONSTRAINT "public.app1_userfilebackup app1_userfilebackup_group_file_id_dcf22778_fk_app1_groupfile_id"
pg_restore: creating FK CONSTRAINT "public.app1_userfilebackup app1_userfilebackup_program_blue_id_c7121ef5_fk_app1_prog"
pg_restore: creating FK CONSTRAINT "public.app1_userfilebackup app1_userfilebackup_program_refugium_id_c3ba3283_fk_app1_prog"
pg_restore: creating FK CONSTRAINT "public.app1_userfilebackup app1_userfilebackup_program_sun_id_79c2474f_fk_app1_prog"
pg_restore: creating FK CONSTRAINT "public.app1_userfilebackup app1_userfilebackup_program_zero_ten_id_6344a9ec_fk_app1_prog"
pg_restore: creating FK CONSTRAINT "public.app1_userfilebackup app1_userfilebackup_user_id_24d4c825_fk_auth_user_id"
pg_restore: creating FK CONSTRAINT "public.auth_group_permissions auth_group_permissio_permission_id_84c5c92e_fk_auth_perm"
pg_restore: creating FK CONSTRAINT "public.auth_group_permissions auth_group_permissions_group_id_b120cbf9_fk_auth_group_id"
pg_restore: creating FK CONSTRAINT "public.auth_permission auth_permission_content_type_id_2f476e4b_fk_django_co"
pg_restore: creating FK CONSTRAINT "public.auth_user_groups auth_user_groups_group_id_97559544_fk_auth_group_id"
pg_restore: creating FK CONSTRAINT "public.auth_user_groups auth_user_groups_user_id_6a12ed8b_fk_auth_user_id"
pg_restore: creating FK CONSTRAINT "public.auth_user_user_permissions auth_user_user_permi_permission_id_1fbb5f2c_fk_auth_perm"
pg_restore: creating FK CONSTRAINT "public.auth_user_user_permissions auth_user_user_permissions_user_id_a95ead1b_fk_auth_user_id"
pg_restore: creating FK CONSTRAINT "public.authtoken_token authtoken_token_user_id_35299eff_fk_auth_user_id"
pg_restore: creating FK CONSTRAINT "public.django_admin_log django_admin_log_content_type_id_c4bce8eb_fk_django_co"
pg_restore: creating FK CONSTRAINT "public.django_admin_log django_admin_log_user_id_c564eba6_fk_auth_user_id"
pg_restore: warning: errors ignored on restore: 1

```

### 3.5 Tables 權限設定:

```
/usr/bin/psql -U postgres gfi_sso_db1

GRANT ALL PRIVILEGES ON TABLE auth_user TO goglobaldev;

GRANT ALL PRIVILEGES ON TABLE app1_profilebackup TO goglobaldev;
GRANT ALL PRIVILEGES ON TABLE app1_dongleprofile TO goglobaldev;
GRANT ALL PRIVILEGES ON TABLE app1_dongleinfo TO goglobaldev;

GRANT ALL PRIVILEGES ON TABLE app1_groupfile TO goglobaldev;
GRANT ALL PRIVILEGES ON TABLE app1_programblue TO goglobaldev;
GRANT ALL PRIVILEGES ON TABLE app1_programsun TO goglobaldev;
GRANT ALL PRIVILEGES ON TABLE app1_programrefugium TO goglobaldev;
GRANT ALL PRIVILEGES ON TABLE app1_programzeroten TO goglobaldev;
```

---

補充

My case

### 

``elite_lin_postgresql_docker-compose.yml``:
```
version: "3"
services:
  database:
    image: postgres:13 # use latest official postgres version
    ports:
      - "5432:5432"
```


```
docker-compose -f elite_lin_postgresql2_docker-compose.yml ps
        Name                      Command              State    Ports
---------------------------------------------------------------------
yaml_defs_database_1   docker-entrypoint.sh postgres   Exit 1        
elite_lin@elite-lin-VirtualBox:~/Docker/yaml_defs$ docker-compose -f elite_lin_postgresql2_docker-compose.yml logs -f
Attaching to yaml_defs_database_1
database_1  | Error: Database is uninitialized and superuser password is not specified.
database_1  |        You must specify POSTGRES_PASSWORD to a non-empty value for the
database_1  |        superuser. For example, "-e POSTGRES_PASSWORD=password" on "docker run".
database_1  | 
database_1  |        You may also use "POSTGRES_HOST_AUTH_METHOD=trust" to allow all
database_1  |        connections without a password. This is *not* recommended.
database_1  | 
database_1  |        See PostgreSQL documentation about "trust":
database_1  |        https://www.postgresql.org/docs/current/auth-trust.html
```

``elite_lin_postgresql_docker-compose.yml``:
```
version: "3"
services:
  database:
    image: postgres:13 # use latest official postgres version
    environment:
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
```

```
version: "3"
services:
  database:
    image: postgres:13 # use latest official postgres version
    environment:
      POSTGRES_PASSWORD: postgres
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data/      

volumes:
  postgres_data: # named volumes can be managed easier using docker-compose

```

```
$ docker-compose -f elite_lin_postgresql2_docker-compose.yml config
services:
  database:
    environment:
      POSTGRES_PASSWORD: postgres
    image: postgres:13
    ports:
    - published: 5432
      target: 5432
    volumes:
    - postgres_data:/var/lib/postgresql/data:rw
version: '3'
volumes:
  postgres_data: {}

$ docker-compose -f elite_lin_postgresql2_docker-compose.yml up -d
Building with native build. Learn about native build in Compose here: https://docs.docker.com/go/compose-native-build/
Creating network "yaml_defs_default" with the default driver
Creating volume "yaml_defs_postgres_data" with default driver
Creating yaml_defs_database_1 ... done

$ docker volume ls
DRIVER    VOLUME NAME
local     18a7f00fb351c7a03ebf3037eca9ab103758be25ead197f966a5c455b19ca5ae
local     752d515d2e88f701cff4ae462870d1f90bf76dd1239f590916a3909cfadea0e4
local     d4fcc6819b2feaa9e3ed861d18c2956e3ed5b6b1f15d16689f176aa305cb1561
local     e5b8905f1d45d5093c98cf592086464ca84afa32d34898abc994ff9cb0149c06
local     fbcc62a084c92f9872eea850e77575b3848822553bf434be9f72259a3824eb27
local     yaml_defs_postgres_data

$ docker volume inspect yaml_defs_postgres_data 
[
    {
        "CreatedAt": "2021-02-08T16:55:05+08:00",
        "Driver": "local",
        "Labels": {
            "com.docker.compose.project": "yaml_defs",
            "com.docker.compose.version": "1.28.2",
            "com.docker.compose.volume": "postgres_data"
        },
        "Mountpoint": "/var/lib/docker/volumes/yaml_defs_postgres_data/_data",
        "Name": "yaml_defs_postgres_data",
        "Options": null,
        "Scope": "local"
    }
]

```

資料保存在 host 底下的 "/var/lib/docker/volumes/yaml_defs_postgres_data/_data"



---

# [Getting Started with PostgreSQL using Docker-Compose](https://medium.com/analytics-vidhya/getting-started-with-postgresql-using-docker-compose-34d6b808c47c)

## Front Matter
The code for this example containerized PostgreSQL database can be found here:
* [zhao-lin-li/postgresql-with-docker-compose](https://github.com/zhao-lin-li/postgresql-with-docker-compose)

We will use the Docker Official Image of PostgreSQL found here:
* (https://hub.docker.com/_/postgres)

## Configure Docker-Compose

We will configure Docker-Compose to use PostgreSQL by creating this ``docker-compose.yml``:

```
# docker-compose.yml
version: '3'
services:
  database:
    image: "postgres" # use latest official postgres version
    env_file:
      - database.env # configure postgres
    volumes:
      - database-data:/var/lib/postgresql/data/ # persist data even if container shuts down
volumes:
  database-data: # named volumes can be managed easier using docker-compose
```

### ``database.env``:

The ``database.env`` file looks like this:
```
# database.env
POSTGRES_USER=unicorn_user
POSTGRES_PASSWORD=magical_password
POSTGRES_DB=rainbow_database
```

## Log In to PostgreSQL

### Start the Database

Run docker-compose up to bring up the database.
```
docker-compose up                             

Creating volume "postgresql-with-docker-compose_database-data" with default driver
Pulling database (postgres:)...
latest: Pulling from library/postgres
8d691f585fa8: Pull complete
...                                                                             
6283090fa09d: Pull complete
Digest: sha256:a4a944788084a92bcaff6180833428f17cceb610e43c828b3a42345b33a608a7                                                                                                                                     
Status: Downloaded newer image for postgres:latest                                                        
Creating postgresql-with-docker-compose_database_1 ... done                                  
Attaching to postgresql-with-docker-compose_database_1                                                                                                                                                              
database_1  | The files belonging to this database system will be owned by user "postgres".                                                                                                                         
database_1  | This user must also own the server process.
...
database_1  | 2019-11-17 20:33:02.208 UTC [1] LOG:  database system is ready to accept connections
```

Docker-Compose will only download the image the first time you run this command.

### Connect to the Database

There are multiple ways of __connecting to the database container__. 
In this example, we will __drop into the database container and 
__use the psql client software that is already installed in the database container__.

drop into the container shell:
```
docker-compose run database bash 

database# psql --host=database --username=unicorn_user --dbname=rainbow_database
Password for user unicorn_user: 
psql (12.0 (Debian 12.0-2.pgdg100+1))
Type "help" for help.
rainbow_database=#

```

When prompted to the password, enter the password we configured in our 
``docker-compose.yml``, e.g. ``magical_password``.


### Create a Table

We can start interacting with the database by first creating a database table.

```
rainbow_database=# \d # verify table does already not exist
Did not find any relations.
rainbow_database=# CREATE TABLE color_table(name TEXT);
CREATE TABLE
rainbow_database=# \d # verify table is created
              List of relations
 Schema |    Name     | Type  |    Owner     
--------+-------------+-------+--------------
 public | color_table | table | unicorn_user
(1 row)
```

### Add and Read Data

```
rainbow_database=# SELECT * FROM color_table; -- verify record does not already exist
 name 
------
(0 rows)
rainbow_database=# INSERT INTO color_table VALUES ('pink'); -- be sure to use single quotes
INSERT 0 1
rainbow_database=# SELECT * FROM color_table; -- verify record is created
 name 
------
 pink
(1 row)
```

We can now add data in to the table. And then read data back from the table.

## Conclusion

Congratulations! You have successfully used a PostgreSQL database 
inside a container with Docker-Compose.

## Notes

It is worth noting some nuances with persisting data with containers.

### Using Named Volumes

In our example, we used ``named volumes``. 
Docker-Compose helps us manage __creating and destroying these volumes__. 
These volumes __allow the data to persist even if we destroy the containers__.

```
# docker-compose.yml
services:
  database:
    ...
    volumes:
      - database-data:/var/lib/postgresql/data/
volumes:
  database-data:
```

To tell Docker-Compose to __destroy the volume and its data__, 
you need to issue ``docker-compose down --volumes``


### Without Volumes

If we removed these volume configurations, 
each time we destroy our container, any data we stored will be gone. 
Create some data and give it a try.

建議使用 volumes, 不然資料就會消失

### Using Host Volumes

If we used the following configuration:

```
# docker-compose.yml
services:
  database:
    ...
    volumes:
      - ./host-folder/:/var/lib/postgresql/data/
```

The __data will be stored on the host computer__. 
To delete this data and start a fresh new database, 
you will have to manually remove the data files 
from the host computer with something like ``rm -rf ./host-folder/``.



----


