1. 下載最新發(fā)布版

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

• 通過官方鏡像創(chuàng)建容器

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基礎(chǔ)包

由于官方鏡像沒有 vim ping ip 等基礎(chǔ)命令 ,這里進(jìn)行手動(dòng)安裝

docker exec -it mypg121 bash

cat /etc/os-release

當(dāng)前的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安裝報(bào)錯(cuò),缺依賴包

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參數(shù)

配置環(huán)境變量

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,修改如下參數(shù):

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,"條用時(shí):",end-start,"秒")

cur.close()


myzmac:pg myz$ python3 demo2.py 
寫入 [(10000,)] 條用時(shí): 10.807251930236816 秒
myzmac:pg myz$ 

FYI:

PostgreSQL官網(wǎng)
https://www.postgresql.org