Postgresql的备份与恢复

原创
2019/12/29 11:02
阅读数 144

Postgresql

背景

身为一枚主要做后端的程序员,跟数据库打交道是必不可少的,虽然数据库由专门的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>.
  1. 参数解析

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

    -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,这个会安全一点,但是时间会延长,不建议使用

  2. 联接选项

    数据库连接参数是必填参数,参数与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
  1. 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 2. #### 恢复 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

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部