win10下docker启动postgres

桃扇骨 2023-02-13 08:48 49阅读 0赞

### 一、拉取镜像 ###

docker pull postgres:13

### 二、简单版启动容器 ###

**1. 执行命令启动容器**

docker run --name postgres -e POSTGRES_PASSWORD=1234 -p 5433:5432 -d postgres:13

**2. 打开navicat查看结果**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70][]

**从以上结果看出数据库连接成功 **

**3. 拷贝镜像默认配置到宿主机**

docker cp postgres:/var/lib/postgresql/data/postgresql.conf d:/usr/share/postgresql/postgresql.conf

![20200528095130787.png][]

**4. 查看配置文件，删除掉注释行后，镜像默认配置的结果如下**

listen_addresses = '*'
    					
    # port = 5432				# (change requires restart)
    max_connections = 100			# (change requires restart)
    
    shared_buffers = 128MB			# min 128kB
    
    dynamic_shared_memory_type = posix	# the default is the first option
    
    max_wal_size = 1GB
    min_wal_size = 80MB
    
    log_timezone = 'Etc/UTC'
    
    datestyle = 'iso, mdy'
    timezone = 'Etc/UTC'
    
    # These settings are initialized by initdb, but they can be changed.
    lc_messages = 'en_US.utf8'			# locale for system error message
    lc_monetary = 'en_US.utf8'			# locale for monetary formatting
    lc_numeric = 'en_US.utf8'			# locale for number formatting
    lc_time = 'en_US.utf8'				# locale for time formatting
    
    # default configuration for text search
    default_text_search_config = 'pg_catalog.english'

**5. 删除容器 **

docker rm -f postgres

### 三、复杂版启动容器 ###

**1. 在D:\\usr\\bin目录下创建docker-compose-postgres.yml文件**

version: '3.1'
    
    services:
      # 服务器名
      db:
        image: postgres:13
        container_name: postgres
        # restart: always
        # 从指定配置文件启动
        command: -c 'config_file=/etc/postgresql/postgresql.conf'
        volumes:
            - pgdata:/var/lib/postgresql/data
            - /d/usr/share/postgresql/log:/var/log/postgresql
            # # 配置文件默认路径 /var/lib/postgresql/data/postgresql.conf
            - /d/usr/share/postgresql/my-postgres.conf:/etc/postgresql/postgresql.conf
        environment:
          POSTGRES_USER: test
          POSTGRES_PASSWORD: test
          POSTGRES_DB: test
        ports:
          - 5433:5432
          
      adminer:
        image: adminer:4.7.7-standalone
        container_name: adminer
        # restart: always
        ports:
          - 8080:8080
    volumes:
      pgdata:
         external: true

**2. 在D:\\usr\\bin目录下创建postgres启动脚本postgres-up.sh**

#!/bin/bash
    
    docker-compose -f d:/usr/bin/docker-compose-postgres.yml up

**3. 在目录下D:\\usr\\share\\postgresql创建配置文件my-postgres.conf**

** 4. 手动创建数据卷**

docker volume create --name=pgdata

**5. 切换到postgres-up.sh脚本脚本所在目录，执行脚本命令**

cd d:/usr/bin
    ./postgres-up.sh

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70 1][]

**6. 查看结果 **

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70 2][]

**从以上结果可以看出配置成功**

### 四、日志配置 ###

**1. 配置文件my-postgres.conf新增内容**

logging_collector = on
    log_destination = 'stderr'
    log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
    log_directory = '/var/log/postgresql'
    log_statement = all	 #需设置跟踪所有语句，否则只能跟踪出错信息，有4种类型：none(默认), ddl, mod, all
    log_min_duration_statement = 5000	#milliseconds,记录执行5秒及以上的语句

**2. 重启容器**

./postgres-up.sh

**3. 执行错误查询语句生成日志**

# 正确表名为pg_stat_activity
    select * from pg_stat_activity1 where state<>'idle' and now()-query_start > interval '1 s' order by query_start

![20200528171707200.png][]

**4. 查看D:\\usr\\share\\postgresql\\log目录下日志文件内容**

2020-05-28 09:03:52.208 UTC [1] LOG:  starting PostgreSQL 13beta1 (Debian 13~beta1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
    2020-05-28 09:03:52.208 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
    2020-05-28 09:03:52.208 UTC [1] LOG:  listening on IPv6 address "::", port 5432
    2020-05-28 09:03:52.484 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
    2020-05-28 09:03:52.626 UTC [27] LOG:  database system was shut down at 2020-05-28 09:03:47 UTC
    2020-05-28 09:03:52.706 UTC [1] LOG:  database system is ready to accept connections
    2020-05-28 09:04:23.926 UTC [35] LOG:  statement: set client_encoding to 'UNICODE'
    2020-05-28 09:04:23.928 UTC [35] LOG:  statement: SELECT DISTINCT datlastsysoid FROM pg_database
    2020-05-28 09:04:23.930 UTC [35] LOG:  statement: SHOW datestyle
    2020-05-28 09:04:23.932 UTC [35] LOG:  statement: SELECT d.oid, d.datname AS databasename, d.datacl, d.datistemplate, d.datallowconn, pg_get_userbyid(d.datdba) AS databaseowner, d.datcollate, d.datctype, shobj_description(d.oid, 'pg_database') AS description, d.datconnlimit, t.spcname, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace = t.oid
    2020-05-28 09:04:26.018 UTC [35] LOG:  statement: SELECT DISTINCT datlastsysoid FROM pg_database
    2020-05-28 09:04:26.020 UTC [35] LOG:  statement: SHOW datestyle
    2020-05-28 09:04:26.022 UTC [35] LOG:  statement: SELECT d.oid, d.datname AS databasename, d.datacl, d.datistemplate, d.datallowconn, pg_get_userbyid(d.datdba) AS databaseowner, d.datcollate, d.datctype, shobj_description(d.oid, 'pg_database') AS description, d.datconnlimit, t.spcname, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname FROM pg_database d LEFT JOIN pg_tablespace t ON d.dattablespace = t.oid
    2020-05-28 09:04:29.066 UTC [35] LOG:  statement: select * from pg_stat_activity1 where state<>'idle' and now()-query_start > interval '1 s' order by query_start
    2020-05-28 09:04:29.066 UTC [35] ERROR:  relation "pg_stat_activity1" does not exist at character 15
    2020-05-28 09:04:29.066 UTC [35] STATEMENT:  select * from pg_stat_activity1 where state<>'idle' and now()-query_start > interval '1 s' order by query_start

![20200528171723847.png][]

**到此日志配置成功**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200528094644587.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz,size_16,color_FFFFFF,t_70
[20200528095130787.png]: https://img-blog.csdnimg.cn/20200528095130787.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200528162053799.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200528162156636.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pzeDE4MjczMTE3MDAz,size_16,color_FFFFFF,t_70
[20200528171707200.png]: https://img-blog.csdnimg.cn/20200528171707200.png
[20200528171723847.png]: https://img-blog.csdnimg.cn/20200528171723847.png