postgresSQL 逻辑备份

PostgreSQL自带一个客户端pgAdmin，里面有个备份，恢复选项，也能对数据库进行备份 恢复（还原），但最近发现数据库慢慢庞大的时候，经常出错，备份的文件过程中出错的几率那是相当大，手动调节灰常有限。所以一直寻找完美的备份恢复方案。其实PostgreSQL内置不少的工具，寻找的备份恢复方案就在其中：pg_dump，psql。这两个指令 在数据库的安装目录下，比如我自己本地安装的，路径形如：C:\Program Files\PostgreSQL\9.5\；然后进入到bin文件夹，会看到不少的exe文件，这就是PostgreSQL内置的工具了。里面会找到 pg_dump.exe，psql.exe两个文件。

root@ubuntu:~# 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
  -?, --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
  -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
  --no-security-labels         do not dump security label assignments
  --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
  --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>.

用法：

备份数据库，指令如下：

pg_dump -h $hostname -p $port -U $username -d $dbname -w -b -v -f /backup/pgsql/pgsql_$dbname_$today.sql

指令解释：如上命令，pg_dump 是备份数据库指令，$hostname是数据库的ip地址（必须保证数据库允许外部访问的权限哦~），当然本地的数据库ip写 localhost；$username 是数据库的用户名；$dbname 是数据库名。> 意思是导出到/backup/pgsql/pgsql_$dbname_$today.sql文件里，如果没有写路径，单单写pgsql_$dbname_$today.sql文件名，那么备份文件会保存在/usr/lib/postgresql/9.5/bin文件夹里。

 

大家注意到了pg_dump后面没有跟密码参数，上面备份命令没有输入密码。

#切换至pgsql用户
su - postgres
#家目录下创建.pgpass文件
vi .pgpass
$hostname:$port:$dbname:$username:$password
#赋权
chmod 600 .pgpass

注：$hostname无论是主机名还是IP，必须要存在于/etc/hosts中且一致。

报错信息“pg_dump: [archiver (db)] connection to database "rebate" failed: fe_sendauth: no password supplied”

#编辑.bashrc文件
vi ~/.bashrc
#pgsql passwd
export PGPASSWORD="$password"

执行完上述步骤后可免密备份。

备份脚本如下

#!/bin/bash
export today=`date +%Y%m%d`
export EXP_FILE=/backup/$dbname/pgsql-$dbname_$today.gz
pg_dump -h $hostname -p $port -U $username -d $dbname -w -b -v -f /backup/$dbname/pgsql_$dbname_$today.sql
nohup /bin/gzip /backup/$dbname/pgsql-$dbname_$today.sql $EXP_FILE &
find /backup/$dbname -mtime +15 -name 'pgsql-$dbname_*.gz' -exec rm -rf {} \;
find /backup/$dbname -mtime -1 -name '*.sql' -exec rm -rf {} \;