PgAdmin

pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.

Simple running

$ docker run -p 5050:80 -e "[email protected]" -e "PGADMIN_DEFAULT_PASSWORD=a12345678" -d  dpage/pgadmin4

docker-compose example

version: '3.3'

services:
  dbPostgres:
    image: postgres:10
    restart: always
#    volumes:
#      - $DATABASE:/var/lib/postgresql/data
    ports:
      - 5433:5432
    environment:
      POSTGRES_USER: username
      POSTGRES_PASSWORD: 6090

  pgadmin:
    image: dpage/pgadmin4
    restart: always
    depends_on:
      - dbPostgres
#    volumes:
#      - $BACKUP:/var/lib/pgadmin
    ports:
      - 9090:80
    environment:
      - "[email protected]"
      - "PGADMIN_DEFAULT_PASSWORD=0000"

Provisioning connections docker-compose example

Postgresql 서버의 연결정보를 저장해 놓는 방법을 docker-compose로 구현. Password File 파일 및 servers.json 파일을 구현해야 한다.

version: '3.8'

services:
  postgres:
    container_name: answer-pg
    image: postgres:13
    restart: always
    volumes:
      - postgres_data:/var/lib/postgresql/data
    ports:
      - 5432:5432
    environment:
      - TZ=Asia/Seoul
      - POSTGRES_USER=${POSTGRES_USER:-recc}
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD:-recc1234}
    networks:
      - postgres_net

  pgadmin:
    container_name: answer-pgadmin
    image: dpage/pgadmin4:latest
    restart: always
    depends_on:
      - postgres
    volumes:
      - pgadmin_data:/var/lib/pgadmin
    ports:
      - 9090:80
    environment:
      - TZ=Asia/Seoul
      - PGADMIN_DEFAULT_EMAIL=${PGADMIN_DEFAULT_EMAIL:-recc@localhost}
      - PGADMIN_DEFAULT_PASSWORD=${PGADMIN_DEFAULT_PASSWORD:-recc1234}
      - PGADMIN_SERVER_JSON_FILE=/tmp/servers.json
    networks:
      - postgres_net
    entrypoint:
      - sh
      - -c
      - |
        mkdir -p /var/lib/pgadmin/storage/recc_localhost &&
        cat << EOF > /var/lib/pgadmin/storage/recc_localhost/pgpassfile &&
        answer-pg:5432:*:${POSTGRES_USER:-recc}:${POSTGRES_PASSWORD:-recc1234}
        EOF
        chmod 600 /var/lib/pgadmin/storage/recc_localhost/pgpassfile &&
        cat << EOF > /tmp/servers.json &&
        {
            "Servers": {
                "1": {
                    "Name": "answer-postgres",
                    "Group": "Servers",
                    "Host": "answer-pg",
                    "Port": 5432,
                    "MaintenanceDB": "postgres",
                    "Username": "${POSTGRES_USER:-recc}",
                    "PassFile": "/pgpassfile",
                    "SSLMode": "prefer",
                    "SSLCert": "<STORAGE_DIR>/.postgresql/postgresql.crt",
                    "SSLKey": "<STORAGE_DIR>/.postgresql/postgresql.key",
                    "SSLCompression": 0,
                    "Timeout": 10,
                    "UseSSHTunnel": 0,
                    "TunnelPort": "22",
                    "TunnelAuthentication": 0
                }
            }
        }
        EOF
        /entrypoint.sh

volumes:
  postgres_data:
  pgadmin_data:

networks:
  postgres_net:

servers.json파일은 다음 명령으로 추출할 수 있다.

## docker container 내부 기준, 사용 방법:
cd /pgadmin4
python setup.py --dump-servers /tmp/servers.json --user recc@localhost

pgpassfile파일은 다음의 포맷으로 작성해야 한다.

hostname:port:database:username:password

Environment Variables

Container Deployment — pgAdmin 4 4.25 documentation

PGADMIN_DEFAULT_EMAIL: This is the email address used when setting up the initial administrator account to login to pgAdmin. This variable is required and must be set at launch time.

PGADMIN_DEFAULT_PASSWORD: This is the password used when setting up the initial administrator account to login to pgAdmin. This variable is required and must be set at launch time.

Troubleshooting

Default email/password error in Dockerfile

Dockerfile을 설정했지만 초기 EMAIL 및 암호가 정상적으로 작동하지 않을 수 있다:

version: '3.3'
services:
  pgadmin:
    image: dpage/pgadmin4
    environment:
      PGADMIN_DEFAULT_EMAIL: [email protected]
      PGADMIN_DEFAULT_PASSWORD: 0000

위와 같이 하면 안되고 아래와 같이 environment 내용을 변경해야 한다.

version: '3.3'
services:
  pgadmin:
    image: dpage/pgadmin4
    environment:
      - "[email protected]"
      - "PGADMIN_DEFAULT_PASSWORD=0000"

no password supplied

데이터베이스 접속시 다음과 같은 에러가 출력될 수 있다.

fe_sendauth: no password supplied

최초 접속시 다음 옵션을 유지해라.

"SSLMode": "prefer",
"SSLCert": "<STORAGE_DIR>/.postgresql/postgresql.crt",
"SSLKey": "<STORAGE_DIR>/.postgresql/postgresql.key",
"SSLCompression": 0,

Favorite site

pgAdmin web site