1. 下载最新发布版

docker pull postgres

或去hub选择需要版本:
https://hub.docker.com/_/postgres?tab=tags

myzmac:~ myz$ docker images postgres
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
postgres            latest              ec5d6d5f5b34        4 weeks ago         394MB

• 通过官方镜像创建容器

mkdir -p ~/Docker/pg/data && cd ~/Docker/pg/

docker run -e TZ="Asia/Shanghai" --name mypg121 -h mypg121  -v "$PWD/data":/var/lib/postgresql/data -m 512m   -p 54321:5432 -e POSTGRES_PASSWORD=welcome1 -d postgres

myzmac:pg myz$ docker ps -a
CONTAINER ID        IMAGE                       COMMAND                  CREATED             STATUS                     PORTS                     NAMES
f64106742acd        postgres                    "docker-entrypoint.s…"   4 seconds ago       Up 3 seconds               0.0.0.0:54321->5432/tcp   mypg121

2. 配置PG

2.1 容器安装OS基础包

由于官方镜像没有 vim ping ip 等基础命令 ,这里进行手动安装

docker exec -it mypg121 bash

cat /etc/os-release

当前的os 为Debian 使用apt-get 安装

root@mypg121:/# apt-get install vim
Reading package lists... Done
Building dependency tree       
Reading state information... Done
E: Unable to locate package vim

由于源的问题不能安装,更新apt源, 自带的源太慢了,这里改为163的源

mv  /etc/apt/sources.list /etc/apt/sources.list.orig
echo "deb http://mirrors.163.com/debian/ stretch main non-free contrib " >> /etc/apt/sources.list 
echo "deb http://mirrors.163.com/debian/ stretch-updates main non-free contrib " >> /etc/apt/sources.list
echo "deb http://mirrors.163.com/debian/ stretch-backports main non-free contrib " >> /etc/apt/sources.list
echo "deb-src http://mirrors.163.com/debian/ stretch main non-free contrib " >> /etc/apt/sources.list
echo "deb-src http://mirrors.163.com/debian/ stretch-updates main non-free contrib" >> /etc/apt/sources.list
echo "deb-src http://mirrors.163.com/debian/ stretch-backports main non-free contrib" >> /etc/apt/sources.list
echo "deb http://mirrors.163.com/debian-security/ stretch/updates main non-free contrib" >> /etc/apt/sources.list
echo "deb-src http://mirrors.163.com/debian-security/ stretch/updates main non-free contrib  " >> /etc/apt/sources.list

需要update更新下

apt-get update
apt-get install vim

vim安装报错,缺依赖包

root@mypg121:/# apt-get install vim
Reading package lists... Done
Building dependency tree       
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
 vim : Depends: libtinfo5 (>= 6) but it is not going to be installed
E: Unable to correct problems, you have held broken packages.

安装libtinfo5依赖包

apt-get -y install libtinfo5
apt-get -y install vim

视需要安装其他工具包

apt-get install -y  lrzsz iproute  net-tools  iputils-ping  lsof  unzip zip tmux

2.2 配置pg参数

配置环境变量

su - postgres
vi .profile
export PGHOME=/usr/lib/postgresql/12
export PGDATA=/var/lib/postgresql/data
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PATH:$PGHOME/bin/
export LANG=en_US.utf8
alias pgstart='pg_ctl -D $PGDATA -l $PGDATA/postgres.log start'
alias pgstopk='pg_ctl kill INT `head -1 $PGDATA/postmaster.pid`'
alias pgstop='pg_ctl stop -mf'

source .profile

配置pg_hba.conf

vi $PGDATA/pg_hba.conf

image.png

配置postgresql.conf,修改如下参数:

vi $PGDATA/postgresql.conf

listen_addresses='*'
shared_buffers=256MB    #memory 1/2
max_connections=200
archive_mode = on
log_min_duration_statement = 5s

重启pg

pgstop
docker start mypg121

su - postgres
createuser -Pw jason
createdb pgdb -O jason

psql pgdb jason
pgdb=> create table tbl_tst (id int);
CREATE TABLE
pgdb=> insert into tbl_tst select generate_series(1,10000);
INSERT 0 10000
pgdb=> \q

3. 外部访问PG

3.1 通过navicat等工具

image.png
image.png

3.2 通过python操作Pg

pip3 install --upgrade pip
pip3 install psycopg2-binary


#cat demo.py
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
__title__ = 'pg_demo'
__author__ = 'Jason Ma'
__mtime__ = '2020/2/3'
__version__= ' '
"""

import psycopg2
import time

conn = psycopg2.connect(database="pgdb", user="jason", password="oracle", host="1.1.1.2", port="54321")
cur = conn.cursor()
cur.execute("drop  table if exists  tbl_tst1;")
cur.execute("create table tbl_tst1(id int,str text);")

start = time.time()
for i in range(10000):
    #cur.execute("insert into tbl_tst1 select generate_series(1,100), md5(random()::text)")
    sql = """insert into tbl_tst1  (id,str) values (%s,%s) returning  id"""
    var = (i,"nm_"+str(i))
    cur.execute(sql, var)
conn.commit()
end = time.time()

sql = "select count(1) from tbl_tst1"
cur.execute(sql)
cnt = cur.fetchall()

print ("写入",cnt,"条用时:",end-start,"秒")

cur.close()


myzmac:pg myz$ python3 demo2.py 
写入 [(10000,)] 条用时: 10.807251930236816 秒
myzmac:pg myz$ 

FYI:

PostgreSQL官网
https://www.postgresql.org