Enjoy the good lift everyday!
Postgresql的备份与恢复 | PyGo²

Postgresql的备份与恢复

背景

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


命令介绍

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

  1. pg_dump详解

    执行pg_dump –help,英文内容如下:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    $ 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,这个会安全一点,但是时间会延长,不建议使用

  1. 联接选项

    数据库连接参数是必填参数,参数与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用户:

    1
    2
    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

  • 本文作者:mingliang.gao【一个爱老婆的python程序猿。。。。。。】
  • 本文链接: https://gis90.github.io/articles/17366/
  • 版权声明: 本博客所有文章欢迎转载,转载请注明出处!
觉得有帮助,请喝个咖啡
0%