Postgresql的备份与恢复

Postgresql pg_dump 备份 恢复 迁移

背景

身为一枚主要做后端的程序员，跟数据库打交道是必不可少的，虽然数据库由专门的dba进行负责，技多不压身。于是，把日常对postgresql数据库的日常操作比较频繁的进行记录下来，一是自己找的时候方便，另外也与大家做个分享交流。

---

命令介绍

除去写相关的sql工作，工作上用到比较多的就是数据库的备份与恢复、导表，今天先介绍一下数据库的备份与恢复。
pg_dump是一个用于备份的命令工具，即使当前数据库正在使用，也能够生成一致性的备份文件，生成sql文件或其他格式文件，且不会阻塞其他用户访问数据库(包括读、写)，下面就详细介绍一下pg_dump。

1. pg_dump详解

   执行pg_dump –help，英文内容如下：

   $ pg_dump --help
   
   pg_dump dumps a database as a text file or to other formats.
   
   Usage:
     pg_dump [OPTION]... [DBNAME]
   
   General options:
     -f, --file=FILENAME          output file or directory name
     -F, --format=c|d|t|p         output file format (custom, directory, tar,
                                  plain text (default))
     -j, --jobs=NUM               use this many parallel jobs to dump
     -v, --verbose                verbose mode
     -V, --version                output version information, then exit
     -Z, --compress=0-9           compression level for compressed formats
     --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
     --no-sync                    do not wait for changes to be written safely to disk
     -?, --help                   show this help, then exit
   
   Options controlling the output content:
     -a, --data-only              dump only the data, not the schema
     -b, --blobs                  include large objects in dump
     -B, --no-blobs               exclude large objects in dump
     -c, --clean                  clean (drop) database objects before recreating
     -C, --create                 include commands to create database in dump
     -E, --encoding=ENCODING      dump the data in encoding ENCODING
     -n, --schema=SCHEMA          dump the named schema(s) only
     -N, --exclude-schema=SCHEMA  do NOT dump the named schema(s)
     -o, --oids                   include OIDs in dump
     -O, --no-owner               skip restoration of object ownership in
                                  plain-text format
     -s, --schema-only            dump only the schema, no data
     -S, --superuser=NAME         superuser user name to use in plain-text format
     -t, --table=TABLE            dump the named table(s) only
     -T, --exclude-table=TABLE    do NOT dump the named table(s)
     -x, --no-privileges          do not dump privileges (grant/revoke)
     --binary-upgrade             for use by upgrade utilities only
     --column-inserts             dump data as INSERT commands with column names
     --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
     --disable-triggers           disable triggers during data-only restore
     --enable-row-security        enable row security (dump only content user has
                                  access to)
     --exclude-table-data=TABLE   do NOT dump data for the named table(s)
     --if-exists                  use IF EXISTS when dropping objects
     --inserts                    dump data as INSERT commands, rather than COPY
     --load-via-partition-root    load partitions via the root table
     --no-comments                do not dump comments
     --no-publications            do not dump publications
     --no-security-labels         do not dump security label assignments
     --no-subscriptions           do not dump subscriptions
     --no-synchronized-snapshots  do not use synchronized snapshots in parallel jobs
     --no-tablespaces             do not dump tablespace assignments
     --no-unlogged-table-data     do not dump unlogged table data
     --quote-all-identifiers      quote all identifiers, even if not key words
     --section=SECTION            dump named section (pre-data, data, or post-data)
     --serializable-deferrable    wait until the dump can run without anomalies
     --snapshot=SNAPSHOT          use given snapshot for the dump
     --strict-names               require table and/or schema include patterns to
                                  match at least one entity each
     --use-set-session-authorization
                                  use SET SESSION AUTHORIZATION commands instead of
                                  ALTER OWNER commands to set ownership
   
   Connection options:
     -d, --dbname=DBNAME      database to dump
     -h, --host=HOSTNAME      database server host or socket directory
     -p, --port=PORT          database server port number
     -U, --username=NAME      connect as specified database user
     -w, --no-password        never prompt for password
     -W, --password           force password prompt (should happen automatically)
     --role=ROLENAME          do SET ROLE before dump
   
   If no database name is supplied, then the PGDATABASE environment
   variable value is used.
   
   Report bugs to <pgsql-bugs@postgresql.org>.

2. 参数解析

   这里介绍一下高频&&常用的参数说明，如果想深挖，建议查看官网进行查看，文章最后会给出官网链接。

   -f

   输出文件或目录名

   -F && –format=c|d|t|p

   输出文件格式 (定制、目录、tar)、明文 (默认值))

   -j, –jobs=NUM

   多任务并行

   -Z, –compress=0-9

   被压缩格式的压缩级别

   -a, –data-only

   只转储数据,不包括模式

   -c, –clean

   在重新创建之前，先删除数据库对象，默认这个参数是False，如果不进行删除，可以使用drop进行手动删除已存在的数据库

   -C, –create

   备份文件中有create database数据库的sql语句，默认是进行create

   -E, –encoding=ENCODING

   转储以ENCODING形式编码的数据，默认是数据库编码，不常用

   -s, –schema-only

   只备份数据库策略, 不包括数据

   -t, –table=TABLE

   只备份指定数据库指定名称的表

   –inserts

   以inserts命令，而不是copy命令的形式转储数据，pg_dump默认是copy模式

   –column-inserts

   以带有列名的INSERT命令形式转储数据

   -T, –exclude-table=TABLE

   备份的数据中将排除指定名称的表

   –exclude-table-data=TABLE

   不转储指定名称的表中的数据

   –if-exists

   当删除对象时使用IF EXISTS，这个会安全一点，但是时间会延长，不建议使用

3. 联接选项

   数据库连接参数是必填参数，参数与psql一致。

   -d, –dbname=DBNAME

   数据库

   -h, –host=主机名

   数据库服务器的主机名或IP

   -p, –port=端口号

   数据库的端口号，默认5432

   -U, –username=名字

   用户名

   -w, –no-password

   永远不提示输入口令

   -W, –password

   强制口令提示 (自动)

备份

登陆数据库服务器／远程连接的服务器，使用命令pg_dump，具体参数请查看上面相关详细介绍，下面列出几种常见的情景：

data + schema

导出整个指定的数据库。

pg_dump -h XXXX -p XXXX -U XXXX 数据库名称 > ~/数据库.sql

pg_dump -h XXXX -p XXXX -U XXXX -d XXXX -F t > /home/q/XXXX.tar

schema

导出整个指定数据库的策略。

pg_dump -h XXXX -p XXXX -U XXXX -s 数据库名称 > ~/数据库.sql

data

导出整个指定数据库的表数据。

pg_dump -h XXXX -p XXXX -U XXXX -a 数据库名称 > ~/数据库.sql

table

导出整个指定数据库的指定表。

pg_dump -h XXXX -p XXXX -U XXXX -t 表名 数据库名称 > ~/表.sql

exclude table

导出整个指定数据库的指定表，并排除掉指定的表。

pg_dump -h XXXX -p XXXX -U XXXX -T 数据库名称 > ~/数据库.sql

pg_dump -h XXXX -p XXXX -U XXXX –exclude-table-data 表名 数据库名称 > ~/数据库.sql

迁移数据

在这里介绍3种传输方式：

• python -m SimpleHTTPServer 8888

• scp

• nc

命令的具体不做详细介绍。

恢复

1. 切换用户

   登陆到需要进行恢复的数据库服务器，切换postgres用户：

   sudo -i
   su postgres

2. psql

   执行psql，进行客户端。
   新建数据库专属用户
   create user 用户名 with password ‘密码’;
   新建数据库
   create database 数据库名 owner 用户名;
   分配权限
   grant all privileges on database 数据库 to 用户;
   退出psql，执行：psql 数据库名 <数据库.sql
   加访问权限，执行vim pg_hba.conf 加入：host 数据库 用户 all md5

3. 恢复

   psql -U XXXX -d XXXX -f XXXX.sql

问题集

1 数据库无hstore数据类型
解决：
create extension hstore;
Select pg_terminate_backend(pid) from pg_stat_activity where datname = ‘数据库名称’

2 aborting because of server version mismatch

服务端 & 客户端 版本不一致导致。
解决：

方案一

降级服务端pg版本 <= 客户端
卸载old版本，更新新版本 >= 服务端版本

方案二

版本共存
1.sudo yumdownloader postgresql
2.yum install perl-ExtUtils-Embed
3.sudo yum install uuid
4.sudo yum install libxslt
5.rpm -ivh postgresql rpm包
6.在 /export 软连接/home/q/export下建一个目录 pg100_data ，用户和组都给 postgres
7./opt/pg10/bin/pg_ctl -D /export/pg100_data initdb
8.postgresql.conf 复制到 /export/pg100_data 目录中
9./opt/pg10/bin/pg_ctl -D /export/pg100_data start

学习参考

pg_dump官网：https://www.postgresql.org/docs/9.2/app-pgdump.html