postgres
单机
启动
#!/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 postgresTip
如果这个数据库操作很多的话, 可以把启动参数的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拼接字符串
SELECT string_agg(yaml_text, '')
FROM (
SELECT
'- targets: ["' || local_host || ':9100"]' || E'\n' ||
' labels:' || E'\n' ||
' saltid: ' || id || E'\n' ||
' job_name: ' || id || E'\n' ||
' instance: ' || id || E'\n' ||
' comment: "' || COALESCE(note, '') || '"' || E'\n\n' AS yaml_text
FROM "server"
WHERE id LIKE 'lmb-cp%'
ORDER BY id
) AS t;