1. Postgres

docker run \
	--name pg \
	-p 5432:5432 \
	--privileged=true \
	--net network-common \
	--shm-size 1G \
  	-e POSTGRES_PASSWORD=pg-cipher \
	-e PGDATA=/var/lib/postgresql/data/pgdata \
	-v /elf/postgress/data:/var/lib/postgresql/data \
	-itd postgres:16.2

2. Postgres Vector

docker run \
	--name pgv \
	-p 5432:5432 \
	--privileged=true \
	--net network-common \
	--shm-size 1G \
  	-e POSTGRES_PASSWORD=pg-cipher \
	-e PGDATA=/var/lib/postgresql/data/pgdata \
	-v /elf/postgress/data:/var/lib/postgresql/data \
	-itd pgvector/pgvector:0.6.2-pg16
  • 直接修改Postgres镜像名即可:
    postgres:16.2修改为pgvector/pgvector:0.6.2-pg16

  • 对原有Postgres,可进入每个Postgres实例,安装Postgres Vector:
    apt install postgresql-16-pgvector,并重启postgres容器;

  • 也可自定义Dockerfile或使用docker-compose.yaml;

# Shared Memory Limit:

--shm-size=1g
# Enable Assertion
PG_CFLAGS="-DUSE_ASSERT_CHECKING"

# Enable Benchmarking
PG_CFLAGS="-DIVFFLAT_BENCH"

# Show Memory Usage
PG_CFLAGS="-DHNSW_MEMORY -DIVFFLAT_MEMORY"

# Retrieveet k-means Metric
PG_CFLAGS="-DIVFFLAT_KMEANS_DEBUG"

3. Navicat

  • Navicat连接Postgres出现datlastsysoid不存在:Postgres15从表中
    已删除datlastsysoid字段,Navicat16.1之前的版本会出现查询错误;

  • 备份Navicat安装目录下libcc.dll,在任何十六进制
    编辑器打开此文件,或使用 https://hexed.it 打开;

  • 搜索 SELECT DISTINCT datlastsysoid
    将其替换为 SELECT DISTINCT dattablespace
    按Ctrl + S保存并下载到本地,替换原文件后可顺利连接;

4. Postgres Admin

mkdir -p /data/docker/pgadmin/{data,logs}

mkdir -p /elf/PgAdmin/{data,log} && \
	chown  -R 5050:5050 /elf/PgAdmin
docker run \
	--name pga \
	-p 5050:80 \
	--privileged=true \
	--net network-common \
	--shm-size 1G \
  	-e PGADMIN_DEFAULT_EMAIL=2032047511@qq.com \
	-e PGADMIN_DEFAULT_PASSWORD=admin \
	-v /elf/PgAdmin/data:/var/lib/pgadmin \
	-v /elf/PgAdmin/log:/var/log/pgadmin \
	-itd dpage/pgadmin4:8.4
  • Postgres Admin连接Postgres同样有问题,宿主机修改:

  • vim /elf/postgress/data/pgdata/pg_hba.conf,

  • 在IPv4和IPv6 Local Connection之间添加行:

  • host all all 0.0.0.0/0 md5

# vim /elf/postgress/data/pgdata/postgresql.conf

  • 修改host和端口:

# listen_addresses = '127.0.0.1'
listen_addresses = '*'

# port = 5432
port = 5432
  • 重启Postgres:docker restart pgv

Entry Memo

Host

192.168.0.123

Port

5432

Database

postgres

Username

postgres

Password

pg-cipher