postgresSQL 逻辑备份

本文主要是介绍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 exitOptions 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 inplain-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 hasaccess 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-authorizationuse SET SESSION AUTHORIZATION commands instead ofALTER OWNER commands to set ownershipConnection 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 dumpIf 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 {} \;