gongdear

gongdear的技术博客

欢迎大家参观我的博客
  menu
103 文章
89355 浏览
4 当前访客
ღゝ◡╹)ノ❤️

Postgresql数据目录迁移

几点说明:
1 需要迁移的场景一般发生在服务器/var分区空间不够用,需要将数据库数据迁移到其他物理分区,如home目录下
2 专门用来装数据库的服务器尽量在安装系统时定制最大物理分区为/var
3 此种方式迁移后,若执行yum update/upgrade 命令更新时更新了postgresql那么所有的配置都会恢复原始值,请注意这一点

先停止postgesql服务
systemctl stop postgresql-9.6
vim /usr/lib/systemd/system/postgresql-9.6.service

It's not recommended to modify this file in-place, because it will be

overwritten during package upgrades. If you want to customize, the

best way is to create a file "/etc/systemd/system/postgresql-10.service",

containing

.include /lib/systemd/system/postgresql-10.service

...make your changes here...

For more info about custom unit files, see

http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F

Note: changing PGDATA will typically require adjusting SELinux

configuration as well.

Note: do not use a PGDATA pathname containing spaces, or you will

break postgresql-setup.

[Unit]
Description=PostgreSQL 10 database server
Documentation=https://www.postgresql.org/docs/10/static/
After=syslog.target
After=network.target

[Service]
Type=notify
User=postgres
Group=postgres

Note: avoid inserting whitespace in these Environment= lines, or you may

break postgresql-setup.

Location of database directory

Environment=PGDATA=/home/hadoop/pgsql/pgsql/10/data/

Where to send early-startup messages from the server (before the logging

options of postgresql.conf take effect)

This is normally controlled by the global default set by systemd

StandardOutput=syslog

Disable OOM kill on the postmaster

OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0

ExecStartPre=/usr/pgsql-10/bin/postgresql-10-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-10/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT

Do not set any timeout value, so that systemd will not kill postmaster

during crash recovery.

TimeoutSec=0

[Install]
WantedBy=multi-user.target

将配置文件中的 Environment变量的值改为目标目录/home/gongdear/pgsql

将原始数据复制到新目录
cp -rf /var/lib/postgresql/9.6/ /home/gongdear/pgsql
给权限:
chown -R postgres:postgres /home/gongdear/pgsql
chmod 700 /home/gongdear/pgsql

systemctl daemon-reload

systemctl start postgresql-9.6
测试没问题后可删除原始数据文件,删除前建议先备份。

宝剑锋从磨砺出,梅花香自苦寒来.