PostgreSQL upgrade
数据迁移
以下步骤在 screen 中执行。
此处数据迁移采用 Easy Dump shell 脚本工具,封装了 pg_dump 的 16 种 case,设置好相关参数后,一行命令即可。
同时给出对应的 pg_dump 命令以供参考。
以下列出几种常用 case (引自 Easy Dump 文档原文)。
Dump all schema and data
If you need to dump all the databases and users in one of the following cases, just use this easiest way to dump a PostgreSQL instance.
- The instance size is quite small
- You have got enough time to wait for the hours long dump
Easy Dump command
bash pg_dump.sh -v -M ALL
PostgreSQL pg_dump command
time "${PGBIN}"/pg_dumpall -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -s 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -e &>>"${lv_restore_log}"
Dump all tables of a database
In some cases you need to dump the users separately and then dump the database.
- The database size is quite small
- You’ve got enough time to wait for the hours long dump
- You are separating one database from a huge instance on which there are multiple databases or you just don’t need other databases
Easy Dump command
bash pg_dump.sh -v -M DB -d alvindb
PostgreSQL pg_dump command
time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}"
Dump all tables, specified tables are dumped in parallel
In some cases you need to dump a database and dump some of the tables in parallel.
- PostgreSQL database to be dumped contains one or more huge tables or time consuming tables
- You need to minimize the dump time to reduce the affect on the application
Easy Dump command
bash pg_dump.sh -v -M DB -d alvindb -T "public.tb_vacuum alvin.tb_alvindb_vacuum" -L -t 3
</