# 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/``.
----
Sign in with Wallet
Connect another wallet