docker - PostgreSQL 컨테이너 실행
PostgreSQL도 공식 이미지가 있군요. ^^
postgres - Docker Official Images
; https://hub.docker.com/_/postgres
c:\temp> docker pull postgres
c:\temp> docker run --name postgres1 -p 5432:5432 -e POSTGRES_PASSWORD=test_1234 -d postgres
마찬가지로 데이터베이스 변경에 대한 유지를 위해 볼륨 매핑을 추가할 수 있고,
c:\temp> docker run --name postgres1 -p 5432:5432 -v postgresql:/var/lib/postgresql/data -e POSTGRES_PASSWORD=test_1234 -d postgres
DB 성격상 매번 자동으로 올라오게 설정할 것입니다.
c:\temp> docker run --name postgres1 -p 5432:5432 -v postgresql:/var/lib/postgresql/data --restart=always -e POSTGRES_PASSWORD=test_1234 -d postgres
"
DockerDesktopVM의 파일 시스템 접근"에 따라 매핑된 볼륨은 다음과 같이 _data가 구성됩니다.
/ # ls host/var/lib/docker/volumes/postgresql/_data
PG_VERSION pg_dynshmem pg_multixact pg_snapshots pg_tblspc postgresql.auto.conf
base pg_hba.conf pg_notify pg_stat pg_twophase postgresql.conf
global pg_ident.conf pg_replslot pg_stat_tmp pg_wal postmaster.opts
pg_commit_ts pg_logical pg_serial pg_subtrans pg_xact postmaster.pid
이후 구성은
닷넷에서 접근해보는 PostgreSQL 글과 같이 GUI 도구를 이용하거나,
pgAdmin 4 - Download
; https://www.pgadmin.org/download/
컨테이너 내부로 들어가 SQL 쿼리 실행을 위한 psql 명령행 프로그램을 찾아,
c:\temp> docker exec -it postgres1 /bin/bash
/# find / -name psql
/usr/lib/postgresql/12/bin/psql
/usr/bin/psql
...[생략]...
실행할 수도 있습니다.
/# /usr/bin/psql --username=postgres
psql (12.2 (Debian 12.2-2.pgdg100+1))
Type "help" for help.
postgres=# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# \connect postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \dt
Did not find any relations.
postgres=# SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;
table_name
------------
(0 rows)
참고로, PostgreSQL 컨테이너의 초기화는 기본적으로 postgres 사용자로 생성하므로 root가 없어 그냥 실행하면 다음과 같은 오류가 발생합니다.
# /usr/bin/psql
psql: error: could not connect to server: FATAL: role "root" does not exist
그리고 아래는 정상적으로 실행된 경우의 로그 출력입니다.
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... okperforming post-bootstrap initialization ... ok
syncing data to disk ... initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
ok
Success. You can now start the database server using:
pg_ctl -D /var/lib/postgresql/data -l logfile start
waiting for server to start....2020-03-10 00:01:30.405 UTC [46] LOG: starting PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-03-10 00:01:30.472 UTC [46] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-03-10 00:01:30.788 UTC [47] LOG: database system was shut down at 2020-03-10 00:01:29 UTC
2020-03-10 00:01:30.847 UTC [46] LOG: database system is ready to accept connections
done
server started
/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
2020-03-10 00:01:30.942 UTC [46] LOG: received fast shutdown request
waiting for server to shut down....2020-03-10 00:01:30.995 UTC [46] LOG: aborting any active transactions
2020-03-10 00:01:30.998 UTC [46] LOG: background worker "logical replication launcher" (PID 53) exited with exit code 1
2020-03-10 00:01:30.998 UTC [48] LOG: shutting down
2020-03-10 00:01:31.451 UTC [46] LOG: database system is shut down
done
server stopped
PostgreSQL init process complete; ready for start up.
2020-03-10 00:01:31.605 UTC [1] LOG: starting PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-03-10 00:01:31.605 UTC [1] LOG: listening on IPv4 address "0.0.0.0", port 5432
2020-03-10 00:01:31.606 UTC [1] LOG: listening on IPv6 address "::", port 5432
2020-03-10 00:01:31.747 UTC [1] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-03-10 00:01:31.907 UTC [55] LOG: database system was shut down at 2020-03-10 00:01:31 UTC
2020-03-10 00:01:31.958 UTC [1] LOG: database system is ready to accept connections
만약 container 실행 시 이런 오류가 발생한다면?
$ docker run --name DB-PostgreSQL --restart=always -p 5432:5432 -v PostgreSQL:/var/lib/postgresql/data -e POSTGRES_PASSWORD=test1234 postgres
Error: in 18+, these Docker images are configured to store database data in a
format which is compatible with "pg_ctlcluster" (specifically, using
major-version-specific directory names). This better reflects how
PostgreSQL itself works, and how upgrades are to be performed.
See also https://github.com/docker-library/postgres/pull/1259
Counter to that, there appears to be PostgreSQL data in:
/var/lib/postgresql/data (unused mount/volume)
This is usually the result of upgrading the Docker image without
upgrading the underlying database using "pg_upgrade" (which requires both
versions).
The suggested container configuration for 18+ is to place a single mount
at /var/lib/postgresql which will then place PostgreSQL data in a
subdirectory, allowing usage of "pg_upgrade --link" without mount point
boundary issues.
See https://github.com/docker-library/postgres/issues/37 for a (long)
discussion around this process, and suggestions for how to do so.
기존에 연결해 두었던 볼륨의 DB 파일들이 새로운 컨테이너의 postgre DB 서버와 호환이 안 되기 때문입니다. 이런 경우, 1) 완전히 새롭게 볼륨을 지정하든가, 2) 아니면 기존 DB 파일을 생성했던 PostgreSQL 컨테이너 버전을 명확하게 태그해 생성해야 합니다.
[이 글에 대해서 여러분들과 의견을 공유하고 싶습니다. 틀리거나 미흡한 부분 또는 의문 사항이 있으시면 언제든 댓글 남겨주십시오.]