PGSQL Migration step with (pg_dump & pg_restore)

prefect

介绍一下使用pg_dump与pg_restore来迁移数据时的基本步骤。

1. 首先当然是准备目标环境,修改数据库参数。

2. 更改目标环境的配置（如

archive_mode = off

log_min_duration_statement = -1

log_statement = 'none'

checkpoint_segments = 256

checkpoint_timeout = 1h

checkpoint_completion_target = 0.1

autovacuum = off)

原则是尽量减少导入数据的开销,加快导入速度.

2. 停止业务，或者理解为停止对源库的任何更改操作，保持数据一致。（这里有个特例，如果数据只有插入，并且有PK，PK不会被更改与删除时，也可以继续此类业务，迁移完将增量数据补到目标库即可）

3. 备份源数据库

4. 还原目标数据

5. 修正异常

6. analyze目标数据库

7. 修改参数，重启目标数据。

前面的那几个参数改成OLTP适应的参数。

8. 停止源库，切换业务到目标数据库

9.（如果有增量则同步增量，同步完analyze目标数据库）

10. 调整表空间.

以下是一个普通的配置

listen_addresses = '*'

port = 1921

max_connections = 2400

superuser_reserved_connections = 13

unix_socket_permissions = 0700

password_encryption = on

shared_buffers = 2048MB

maintenance_work_mem = 2048MB

max_stack_depth = 8MB

max_files_per_process = 10000

vacuum_cost_delay = 10ms

effective_io_concurrency = 6

wal_sync_method = open_sync

wal_buffers = 16384kB

checkpoint_segments = 32

checkpoint_timeout = 5min

checkpoint_completion_target = 0.5

random_page_cost = 2.0

effective_cache_size = 35000MB

default_statistics_target = 300

constraint_exclusion = partition

logging_collector = on

log_directory = '/var/applog/pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

log_truncate_on_rotation = on

log_rotation_age = 1d

log_rotation_size = 50MB

log_min_duration_statement = 1000ms

log_checkpoints = on

log_line_prefix = '%t %u@%d (PID:%p SID:%c) %r '

log_lock_waits = on

log_statement = 'ddl'

stats_temp_directory = '/database/pgdata/digoal/pg_stat_tmp'

autovacuum = on

log_autovacuum_min_duration = 10000

autovacuum_max_workers = 13

autovacuum_naptime = 1min

default_tablespace = 'tbs_default'

temp_tablespaces = 'tbs_temp'

datestyle = 'iso, mdy'

lc_messages = 'C'

lc_monetary = 'C'

lc_numeric = 'C'

lc_time = 'C'

default_text_search_config = 'pg_catalog.english'

deadlock_timeout = 1s