Docker-Pgsql

• Postgres Offical
• Postgres Docker
• Redrock Postgres 文档
• PGTune - calculate configuration for PostgreSQL based on the maximum performance for a given hardware configuration

1. Docker安装

# 创建Network
docker network create dev

# 创建数据卷
docker volume create pgsql_data;

# 创建文件夹
mkdir -p D:/docker/pgsql/{conf,data,logs}

# docker run -i --rm postgres cat /usr/share/postgresql/postgresql.conf.sample >  D:/docker/pgsql/conf/postgresql.conf

# 获取默认配置文件
docker run -d --name postgres_temp postgres:15.3 \
&& docker cp postgres_temp:/usr/share/postgresql/postgresql.conf.sample D:/docker/pgsql/conf/postgresql.conf \
&& docker stop postgres_temp && docker rm postgres_temp

# 运行容器
docker run -d \
  --publish 5432:5432 \
	--volume //d/docker/pgsql/data:/var/lib/postgresql/data \
	--volume //d/docker/pgsql/conf/postgresql.conf:/etc/postgresql/postgresql.conf:ro \
  --env PGDATA=/var/lib/postgresql/data \
  --env POSTGRES_PASSWORD=postgres \
  --net dev \
  --restart=no \
  --name postgres \
  postgres:15.3

docker exec -it -u root postgres /bin/bash

docker container restart postgres

• Account
  • postgres/postgres

2. Pgsql备份还原

一、Windows下备份和恢复

1. 备份命令

pg_dump -h localhost -U postgres test > D:\postgres.bak

1. pg_dump          是备份数据库指令；
2. localhost        是数据库的 ip 地址；
3. postgres         是数据库的用户名；
4. test             是数据库名；
5. >                意思是导出到指定目录；

2. 恢复命令

psql -h localhost -U postgres -d test < D:\postgres.bak

1. psql           是恢复数据库指令；
2. localhost      是要恢复的数据库的 ip 地址；
3. postgres       是数据库的用户名；
4. test           是数据库名；
5. <              意思是导出到指定目录；

二、Linux 下备份和恢复

1. 备份

pg_dump -h localhost -U postgres -O -x -c --inserts --if-exists --quote-all-identifiers -d databasename > databasename.bak

pg_dump -h localhost -U postgres -O -x -c --inserts --if-exists --quote-all-identifiers -d databasename -t tablename -f /usr/local/posgtes/postgres.sql

2. 恢复

psql -h localhost -U postgres -d databasename < databasename.bak

psql -h localhost -U postgres -d databasename -f /usr/local/posgtes/postgres.sql

root@pgsql# pg_dump --help
pg_dump dumps a database as a text file or to other formats.

Usage:
  pg_dump [OPTION]... [DBNAME]

General options:
  -f, --file=FILENAME          output file or directory name
  -F, --format=c|d|t|p         output file format (custom, directory, tar,
                               plain text (default))
  -j, --jobs=NUM               use this many parallel jobs to dump
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  -Z, --compress=0-9           compression level for compressed formats
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  --no-sync                    do not wait for changes to be written safely to disk
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -b, --blobs                  include large objects in dump
  -B, --no-blobs               exclude large objects in dump
  -c, --clean                  clean (drop) database objects before recreating    // 重建前先删除
  -C, --create                 include commands to create database in dump
  -e, --extension=PATTERN      dump the specified extension(s) only
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -n, --schema=PATTERN         dump the specified schema(s) only
  -N, --exclude-schema=PATTERN do NOT dump the specified schema(s)
  -O, --no-owner               skip restoration of object ownership in            // 跳过权限校验
                               plain-text format
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in plain-text format
  -t, --table=PATTERN          dump the specified table(s) only
  -T, --exclude-table=PATTERN  do NOT dump the specified table(s)
  -x, --no-privileges          do not dump privileges (grant/revoke)              // 不dump权限信息
  --binary-upgrade             for use by upgrade utilities only
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --enable-row-security        enable row security (dump only content user has
                               access to)
  --exclude-table-data=PATTERN do NOT dump data for the specified table(s)
  --extra-float-digits=NUM     override default setting for extra_float_digits
  --if-exists                  use IF EXISTS when dropping objects              // 删除表前添加IF EXISTS校验
  --include-foreign-data=PATTERN
                               include data of foreign tables on foreign
                               servers matching PATTERN
  --inserts                    dump data as INSERT commands, rather than COPY   // 以Insert语句的形式dump
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --no-table-access-method     do not dump table access methods
  --no-tablespaces             do not dump tablespace assignments
  --no-toast-compression       do not dump TOAST compression methods
  --no-unlogged-table-data     do not dump unlogged table data
  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands
  --quote-all-identifiers      quote all identifiers, even if not key words     // 将表名 字段名用引号包裹
  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts
  --section=SECTION            dump named section (pre-data, data, or post-data)
  --serializable-deferrable    wait until the dump can run without anomalies
  --snapshot=SNAPSHOT          use given snapshot for the dump
  --strict-names               require table and/or schema include patterns to
                               match at least one entity each
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=DBNAME      database to dump
  -h, --host=HOSTNAME      database server host or socket directory
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If no database name is supplied, then the PGDATABASE environment
variable value is used.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>

数据量较大时使用以下命令

备份

# 1. 备份主数据库（排除指定表的数据）
pg_dump -h localhost -U postgres -O -x -c --if-exists --quote-all-identifiers \
  -F d -j 4 \
  --exclude-table-data='base_access_log' \
  --exclude-table-data='base_integration_log' \
  -f /var/lib/postgresql/backup/dma_backup_$(date +%Y%m%d) \
  -d dma

# 2. 备份排除表的结构（不包含数据）
pg_dump -h localhost -U postgres -s -t 'base_access_log' -t 'base_integration_log' \
  -f /var/lib/postgresql/backup/dma_excluded_tables_schema.sql \
  -d dma

# 3. 压缩
cd /var/lib/postgresql/backup
tar -zcvf dma_backup_$(date +%Y%m%d).tar.gz dma_backup_$(date +%Y%m%d)

# 4. 发送
scp dma_backup_$(date +%Y%m%d).tar.gz root@localhost:/var/lib/postgresql/backup
scp dma_excluded_tables_schema.sql root@localhost:/var/lib/postgresql/backup

恢复

# 1. 解压
cd /var/lib/postgresql/backup
tar -zxvf dma_backup_$(date +%Y%m%d).tar.gz

# 2. 先恢复主数据库（包含排除表的空结构）
pg_restore -h localhost -U postgres -c --if-exists -j 4 \
  -d dma \
  /var/lib/postgresql/backup/dma_backup_$(date +%Y%m%d)

# 3. 单独恢复排除表的结构（确保表结构存在）
psql -h localhost -U postgres -d dma \
  -f /var/lib/postgresql/backup/dma_excluded_tables_schema.sql
  

脚本

#!/bin/bash
BACKUP_DATE=$(date +%Y%m%d)
BACKUP_DIR="/var/lib/postgresql/backup/dma_backup_${BACKUP_DATE}"
DB_NAME="dma"
EXCLUDED_TABLES=("base_access_log" "base_integration_log")

# 创建备份目录
mkdir -p $BACKUP_DIR

# 1. 备份主数据库（排除指定表数据）
echo "备份主数据库（排除表数据）..."
pg_dump -h localhost -U postgres -O -x -c --if-exists --quote-all-identifiers \
  -F d -j 4 \
  --exclude-table-data='base_access_log' \
  --exclude-table-data='base_integration_log' \
  -f $BACKUP_DIR/main_backup \
  -d $DB_NAME

# 2. 单独备份排除表的结构
echo "备份排除表的结构..."
for table in "${EXCLUDED_TABLES[@]}"; do
  echo "备份表结构: $table"
  pg_dump -h localhost -U postgres -s -t "$table" \
    -f "$BACKUP_DIR/${table}_schema.sql" \
    -d $DB_NAME
done

# 3. 创建恢复脚本
cat > "$BACKUP_DIR/restore.sh" << EOF
#!/bin/bash
echo "恢复主数据库..."
pg_restore -h \$1 -U postgres -c --if-exists -j 4 -d $DB_NAME main_backup

echo "恢复排除表的结构..."
for table in ${EXCLUDED_TABLES[@]}; do
  echo "恢复表结构: \$table"
  psql -h \$1 -U postgres -d $DB_NAME -f "\${table}_schema.sql"
done

echo "数据迁移完成！"
EOF

chmod +x "$BACKUP_DIR/restore.sh"

echo "备份完成！在目标服务器运行: ./restore.sh <B服务器IP>"

SQL方式备份和恢复

这里我们用到的工具是 pg_dump 和 pg_dumpall 这种方式可以在数据库正在使用的时候进行完整一致的备份，并不阻塞其它用户对数据库的访问。 它会产生一个脚本文件，里面包含备份开始时，已创建的各种数据库对象的 SQL 语句和每个表中的数据。 可以使用数据库提供的工具 pg_dumpall 和 pg_dump 来进行备份。 pg_dump 只备份数据库集群中的某个数据库的数据，它不会导出角色和表空间相关的信息，因为这些信息是整个数据库集群共用的，不属于某个单独的数据库。 pg_dumpall，对集簇中的每个数据库调用 pg_dump 来完成该工作,还会还转储对所有数据库公用的全局对象（ pg_dump 不保存这些对象）。 目前这包括适数据库用户和组、表空间以及适合所有数据库的访问权限等属性。

例如，在我的计算机上，可使用如下命令对名为 dbname 的数据库进行备份：

pg_dump –h 127.0.0.1 -p 5432 -U postgres -c -C –f dbname.sql dbname

使用如下命令可对全部 pg 数据库进行备份：

pg_dumpall –h 127.0.0.1 –p 5432 -U postgres –c -C –f db_bak.sql

恢复方式很简单。执行恢复命令即可：

psql –h 127.0.0.1 -p 5432 -U postgres –f db_bak.sql

3. 常用命令

1. 命令行操作

# 登录数据库
psql --help
psql -h localhost -p 5432 -U postgres -W -d postgres 
psql --host=localhost --port=5432 --username=postgres --password --dbname=postgres 

# 创建用户并授权
CREATE USER light WITH PASSWORD 'light';
GRANT ALL PRIVILEGES ON DATABASE test TO light;
ALTER TABLE test.test_table OWNER TO light;

2. 连接管理

-- 查询当前连接数
SELECT * FROM pg_stat_activity;

-- 查看过期连接
SELECT * FROM pg_stat_activity WHERE state = 'idle';

--删除连接，括号里传pid
SELECT pg_terminate_backend(151);

-- 查看最大连接数
SHOW max_connections;

-- 修改最大连接数，需要superuser权限
ALTER system SET max_connections = 1000;

3. 数据库操作

-- 查看数据库列表
SELECT datname FROM pg_database;

-- 查看数据表列表
SELECT tablename FROM pg_tables WHERE schemaname='public';

-- 切换到某个数据库
\c db_name;

-- 执行数据库脚本
\i /path/to/scripts.sql

-- 查询当前数据库下的所有数据表
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE';

-- 删除数据库
DROP DATABASE IF EXISTS "db_name";

-- 创建数据库
CREATE DATABASE "db_name"
    WITH OWNER = postgres
    TEMPLATE = template0
    ENCODING = 'UTF8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;
GRANT ALL ON DATABASE "db_name"  TO postgres;