postgres

单机

启动

pg9.6

pg14.6

#!/bin/sh

port=5432
password=password
pg_version=9.6.8

data_dir=data-${port}
container_name=pg-${port}

mkdir -p ${data_dir}/ init-db-${port}/

# 新部署的机器这段需要注释掉
# cat << EOF > data-${port}/recovery.conf
# standby_mode = on  # 这个说明这台机器为从库
# primary_conninfo = 'application_name=standby01 host=192.168.xx.xx port=5432 user=postgres password=password'  # 主库地址
# recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据
# EOF
# mv -f ${data_dir}/recovery.conf ${data_dir}/recovery.conf.bak

chown -R 999:999 ${data_dir}/

docker stop ${container_name}
docker rm ${container_name}
docker run -d --name ${container_name} \
--restart=always \
--network=host \
-e POSTGRES_PASSWORD=${password} \
-v /etc/localtime:/etc/localtime:ro \
-v /etc/timezone:/etc/timezone:ro \
-v $(pwd)/${data_dir}:/var/lib/postgresql/data \
-v $(pwd)/init-db-${port}/:/docker-entrypoint-initdb.d/ \
muen/postgres:${pg_version} \
-c listen_addresses='*' \                                               # 允许所有网络地址连接
-c dynamic_shared_memory_type=posix \                                   # 使用 POSIX 共享内存
-c log_timezone='Asia/Shanghai' \                                       # 设置日志时区为 Asia/Shanghai
-c datestyle='iso, mdy' \                                               # 设置日期格式为 iso, mdy
-c timezone='Asia/Shanghai' \                                           # 设置时区为 Asia/Shanghai
-c lc_messages='en_US.utf8' \                                           # 设置消息本地化为 en_US.utf8
-c lc_monetary='en_US.utf8' \                                           # 设置货币本地化为 en_US.utf8
-c lc_numeric='en_US.utf8' \                                            # 设置数字本地化为 en_US.utf8
-c lc_time='en_US.utf8' \                                               # 设置时间本地化为 en_US.utf8
-c default_text_search_config='pg_catalog.english' \                    # 设置默认文本搜索配置为 pg_catalog.english
-c logging_collector=on \                                               # 开启日志收集器
-c log_filename='postgresql-%Y-%m-%d.log' \                             # 设置日志文件名格式为 postgresql-年-月-日.log
-c log_statement=all \                                                  # 记录所有 SQL 语句
-c log_min_duration_statement=1000 \                                    # 仅记录执行时间超过 1000 毫秒的 SQL 语句
-c shared_buffers=128MB \                                               # 分配 128MB 共享缓冲区
-c temp_buffers=8MB \                                                   # 分配 8MB 临时缓冲区
-c work_mem=4MB \                                                       # 每个工作线程分配 4MB 内存
-c maintenance_work_mem=64MB \                                          # 维护操作最多使用 64MB 内存
-c port=${port} \                                                          # 监听端口号为 5432
-c wal_level=logical \                                                  # 设置 WAL 日志级别为 logical
-c max_wal_senders=4 \                                                  # 允许的最大 WAL 发送者数量为 4
-c max_wal_size=1GB \                                                   # 设置最大 WAL 大小为 1GB
-c wal_sender_timeout=60s \                                             # 设置 WAL 发送者超时为 60 秒
-c max_connections=2000 \                                               # 允许的最大连接数为 2000
-c hot_standby=off \                                                    # 关闭热备模式
-c max_standby_streaming_delay=30s \                                    # 最大备用流延迟为 30 秒
-c wal_receiver_status_interval=10s \                                   # 设置 WAL 接收者状态检查间隔为 10 秒
-c hot_standby_feedback=on \                                            # 开启热备反馈
-c synchronous_standby_names='standby01' \                              # 同步备用服务器名称为 standby01
-c synchronous_commit=off \                                             # 关闭同步提交
-c commit_delay=0 \                                                     # 提交延迟为 0
-c commit_siblings=5 \                                                  # 提交兄弟数量为 5
-c archive_mode=on \                                                    # 开启归档模式
-c archive_command='DATE=`date +%Y%m%d`;DIR="/var/lib/postgresql/data/pg_xlog_backup/$DATE";(test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f' \                                                         # 设置归档命令
-c pgaudit.log_client=on \                                              # 开启客户端审计日志


# 添加复制权限
hba_file=${data_dir}/pg_hba.conf
while [ ! -f ${hba_file} ];do
    echo "等待初始化成功" && sleep 3
done
if [ -f ${hba_file} ] && [ $(cat ${hba_file} |egrep "^host"|grep replication|grep -c postgres) -eq 0 ];then
    # 重新加载配置
    sleep 3
    echo "host replication postgres all md5" >> ${hba_file} && docker exec -i ${container_name} su -c 'psql -c "select pg_reload_conf();"' postgres
fi

集群

在主节点中dump数据

docker run -i --net host --rm -e PGPASSWORD=password -v $(pwd)/cache/postgresql_data/:/tmp/postgresql_data/ muen/postgres:9.6.8 \
    pg_basebackup -v --format t --gzip --progress --write-recovery-conf --xlog --pgdata=/tmp/postgresql_data --host xx.xx.xx.xx --port 5432 --username postgres

启动从节点

需要修改synchronous_commit、synchronous_standby_names、hot_standby配置

用户操作

查询用户

SELECT usename FROM pg_catalog.pg_user;

只读用户

创建一个用户名为readonly_user的密码为password的只读test库的用户

CREATE ROLE readonly_user WITH LOGIN PASSWORD 'password';
GRANT CONNECT ON DATABASE test TO readonly_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES FOR USER readonly_user GRANT SELECT, USAGE ON SEQUENCES TO readonly_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES FOR ROLE readonly_user GRANT EXECUTE ON FUNCTIONS TO readonly_user;

TIP

如果是在创建只读用户后创建表，那只读用户是没有这个表的只读权限的，需要授权

GRANT SELECT ON table TO readonly_user;

修改密码

ALTER ROLE postgres WITH PASSWORD 'password1';

创建基础备份前准备

创建用户

create role rep nosuperuser replication login connection limit 32 encrypted password '123456';

创建用户库

create database repdb;

用户与用户库关联

grant all privileges on database repdb to rep;

修改 pg_hba.conf 添加权限

host replication rep 0.0.0.0/0 md5

-- 重载配置
SELECT pg_reload_conf();

创建基础备份

创建备份目录

mkdir -p backdb && cd backdb

包含wal日志的备份

docker run -i --net host --rm -e PGPASSWORD=password -v $(pwd)/.cache/postgresql_data/:/tmp/postgresql_data/ muen/postgres:9.6.8 \
    pg_basebackup -v --format t --gzip --progress --write-recovery-conf --xlog --pgdata=/tmp/postgresql_data --host xx.xx.xx.xx --port 5432 --username postgres

TIP

如果这个数据库操作很多的话, 可以把启动参数的wal_keep_segments加大到例如2048

恢复

把xlog恢复

# 从旧库复制到新库
cp -R ../data-5433/pg_xlog/* pg_xlog/
cp -R ../data-5433/pg_xlog_backup/* pg_xlog_backup/

# 从每天的备份中恢复
tar -zxvf 10.0.18.2_5433.tar.gz

修改recovery.conf

restore_command = 'cp -r /var/lib/postgresql/data/pg_xlog_backup/20240123/* /var/lib/postgresql/data/pg_xlog/'
recovery_target_time ='2024-01-23 20:00:38.042165+08'

另外的方式

设置还原点的xid 还原顺序由前往后（区间 xid[1,607]）

recovery_target_xid = '607' 
#recovery_target = 'immediate'

#recovery_target_inclusive = true #设置 target #recovery_target_timeline = 'latest' 在恢复过程中，用户可以通过使用recovery.conf文件来指定恢复的各个参数，如下：归档恢复设置 restore_command：用于获取一个已归档段的XLOG日志文件的命令 archive_cleanup_command：清除不在需要的XLOG日志文件的命令 recovery_end_command：归档恢复结束后执行的命令恢复目标设置（默认情况下，数据库将会一直恢复到 WAL 日志的末尾）

recovery_target = ’immediate’：在从一个在线备份中恢复时，这意味着备份结束的那个点 recovery_target_name (string)：这个参数指定(pg_create_restore_point()所创建)的已命名的恢复点,将恢复到该恢复点 recovery_target_time (timestamp)：这个参数指定恢复到的时间戳 recovery_target_xid (string)：这个参数指定恢复到的事务 ID recovery_target_inclusive (boolean)：指定是否在指定的恢复目标之后停止(true)，或者在恢复目标之前停止 (false)；适用于recovery_target_time或者recovery_target_xid被指定的情况；这个设置分别控制事务是否有准确的目标提交时间或 ID 是否将被包括在该恢复中；默认值为 true recovery_target_timeline (string)：指定恢复到一个特定的时间线 recovery_target_action (enum)：指定在达到恢复目标时服务器应该立刻采取的动作，包括pause（暂停）、promote（接受连接）、shutdown（停止服务器），其中pause为默认动作

recovery_target_name使用

#该参数用于还原备份点

select pg_create_restore_point('20220221'); -- 创建还原点 recovery_target_name='20220221' -- 添加还原点配置

pg12以上备份与恢复

备份

docker run -i --net host --rm -e PGPASSWORD=password -v $(pwd)/postgresql_data/:/tmp/postgresql_data/ postgres:14.6-alpine \
    pg_basebackup -v --format t --gzip --progress --write-recovery-conf -X none --pgdata=/tmp/postgresql_data --host 192.168.1.1 --port 5432 --username postgres

恢复

解压后删除standby.signal，然后把postgresql.auto.conf清空

TIP

如果一直报错主检查点记录无效，可以尝试：pg_resetwal 会把目录里面其他的wal日志清掉，按照数据库原来的数据重新生成，会丢失当前wal日志部分的数据

docker run -it --net host --privileged=true --rm --entrypoint=/bin/bash -e PGPASSWORD="password" -v $(pwd)/data-5432:/var/lib/postgresql/data postgres:14.6
su - postgres
/usr/lib/postgresql/14/bin/pg_resetwal -f -D /var/lib/postgresql/data/pgdata/

数据迁移

查询pg结果导出csv

docker run -i --rm -e PGPASSWORD=password \
    -v ./:/tmp/ \
    postgres:9.6.8 \
    psql -U postgres -p 5432 -h 192.168.1.1 -d vsadmin -c "\COPY (SELECT * FROM public.test) TO '/tmp/test.csv' With CSV DELIMITER ','"

:::tip

HEADER: 增加一行字段名
QUOTE '\"': 字段包裹双引号 :::

导入csv

docker run -i --rm -e PGPASSWORD=password \
    -v ./:/tmp/ \
    postgres:9.6.8 \
    psql -U postgres -p 5432 -h 192.168.1.1 -d test -c "\COPY public.test FROM '/tmp/merged.csv' WITH CSV DELIMITER ','"

执行sql文件

docker run -i --net host --rm -e PGPASSWORD=password -v ./:/tmp muen/postgres:9.6.8 \
    psql -h xx.xx.xx.xx -p 5432 -U postgres -d mydatabase -f /tmp/init.sql

小型库的迁移

#!/bin/bash

source_host=192.168.1.1
source_port=5432
source_user=postgres
source_password=password
image="postgres:9.6.8"
database_name_list="
test1
test2
"
for database_name in ${database_name_list}; do
  docker run --rm --name pgbackup-${source_host}-${source_port} \
  --net host \
  -v /etc/timezone:/etc/timezone:ro \
  -v /etc/localtime:/etc/localtime:ro \
  -e PGPASSWORD=${source_password} ${image} \
  pg_dump -d ${database_name} -h ${source_host} -p ${source_port} -U ${source_user} -F p | gzip > ${database_name}.sql.gz
done



# 导入
docker exec -it pg-5432 psql -U postgres -d test1 -f /tmp/test1.sql

查看占用大小

查看数据库的总大小

SELECT pg_size_pretty(pg_database_size('your_database_name')) AS size;

查看所有数据库的大小

SELECT datname AS database_name,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

查看特定表的大小

SELECT pg_size_pretty(pg_relation_size('your_table_name')) AS table_size;

查看表及其索引的总大小

SELECT pg_size_pretty(pg_total_relation_size('your_table_name')) AS total_size;

查看数据库中所有表的大小

SELECT relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

技巧

初始化sql

在docker映射阶段把sql文件映射到/docker-entrypoint-initdb.d中

-v $(pwd)/init-db/:/docker-entrypoint-initdb.d/

然后在sql文件中最前面加上以下sql就可以完成建库的时候初始化sql

以下是test库的初始化

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';

ALTER DATABASE test OWNER TO postgres;

\connect test

#postgres

#单机

#启动

#集群

#在主节点中dump数据

#启动从节点

#用户操作

#查询用户

#只读用户

#修改密码

#创建基础备份前准备

#创建用户

#创建用户库

#用户与用户库关联

#修改 pg_hba.conf 添加权限

#创建基础备份

#创建备份目录

#包含wal日志的备份

#恢复

#把xlog恢复

#修改recovery.conf

#另外的方式

#设置还原点的xid 还原顺序由前往后 （区间 xid[1,607]）

#recovery_target_name使用

#pg12以上备份与恢复

#备份

#恢复

#数据迁移

#查询pg结果导出csv

#导入csv

#执行sql文件

#小型库的迁移

#查看占用大小

#查看数据库的总大小

#查看所有数据库的大小

#查看特定表的大小

#查看表及其索引的总大小

#查看数据库中所有表的大小

#技巧

#初始化sql