一种简单的PostgreSQL备份脚本（无须手动输入密码）

islandstar

参考了网上的文档，再结合自己的实际操作。给有需要的人，与之共勉。如有不当之处，还请指正。谢谢！！

因为PostgreSQL里没有加入密码选项，一般备份命令需要手动输入密码，所以会给自动备份带来一定的不便。这里我们使用 pg_dump 来备份，该命令一般存放在程序的安装位置，比如： /opt/PostgreSQL/8.4/bin/  下。

数据库帐号：test     数据库名：testdb    密码：123456  数据库地址：localhost   默认端口：5432

第一种方法：通过PostgreSQL的环境变量参数来实现保存密码。
在备份脚本执行前 设置一下 PGPASSWORD 参数，如：
cat pgsql_backup.sh

1. #!/bin/bash
   
2. export PGPASSWORD="123456"
   
3. /opt/PostgreSQL/8.4/bin/pg_dump  -U test testdb > /backup/pgsql.backup.`date +%F`.sql

复制代码

可以通过一些其它方式实现脚本自动输入密码。

第二种方法：通过Expect 来实现自动交互，帮助输入密码。
建立 pgsql_backup.sh 脚本。实现按日期备份
cat pgsql_backup.sh

1. #!/bin/bash
   
2. /opt/PostgreSQL/8.4/bin/pg_dump  -U test testdb > /backup/pgsql.backup.`date +%F`.sql

复制代码

建立 pgsql_expect.sh 脚本，通过执行pgsql_expect.sh脚本来调用pgsql_backup.sh实现后者自动输入密码。
cat pgsql_expect.sh

1. #!/usr/bin/expect
   
2. set passwd "123456"
   
3. spawn /root/pgsql_backup.sh;
   
4. expect "*Password:";
   
5. sleep 0.1;
   
6. send "$passwd\r";
   
7. sleep 2

复制代码

第三种方法：网上说是什么 ~/.pgpass 来保存密码。可试了很多次都不成功。郁闷。

附上官方 postgresql环境变量说明。
http://www.postgresql.org/docs/8.4/interactive/libpq-envars.html

The following environment variables can be used to select default connection parameter values, which will be used by PQconnectdb, PQsetdbLogin and PQsetdb if no value is directly specified by the calling code. These are useful to avoid hard-coding database connection information into simple client applications, for example.

    *

      PGHOST behaves the same as host connection parameter.
    *

      PGHOSTADDR behaves the same as hostaddr connection parameter. This can be set instead of or in addition to PGHOST to avoid DNS lookup overhead.
    *

      PGPORT behaves the same as port connection parameter.
    *

      PGDATABASE behaves the same as dbname connection parameter.
    *

      PGUSER behaves the same as user connection parameter. database.
    *

      PGPASSWORD behaves the same as password connection parameter. Use of this environment variable is not recommended for security reasons (some operating systems allow non-root users to see process environment variables via ps); instead consider using the ~/.pgpass file (see Section 30.14).
    *

      PGPASSFILE specifies the name of the password file to use for lookups. If not set, it defaults to ~/.pgpass (see Section 30.14).
    *

      PGSERVICE behaves the same as service connection parameter.
    *

      PGREALM sets the Kerberos realm to use with PostgreSQL, if it is different from the local realm. If PGREALM is set, libpq applications will attempt authentication with servers for this realm and use separate ticket files to avoid conflicts with local ticket files. This environment variable is only used if Kerberos authentication is selected by the server.
    *

      PGOPTIONS behaves the same as options connection parameter.
    *

      PGSSLMODE behaves the same as sslmode connection parameter.
    *

      PGREQUIRESSL behaves the same as requiressl connection parameter.
    *

      PGSSLCERT behaves the same as sslcert connection parameter.
    *

      PGSSLKEY behaves the same as sslkey connection parameter.
    *

      PGSSLROOTCERT behaves the same as sslrootcert connection parameter.
    *

      PGSSLCRL behaves the same as sslcrl connection parameter.
    *

      PGKRBSRVNAME behaves the same as krbsrvname connection parameter.
    *

      PGGSSLIB behaves the same as gsslib connection parameter.
    *

      PGCONNECT_TIMEOUT behaves the same as connect_timeout connection parameter.

The following environment variables can be used to specify default behavior for each PostgreSQL session. (See also the ALTER USER and ALTER DATABASE commands for ways to set default behavior on a per-user or per-database basis.)

    *

      PGDATESTYLE sets the default style of date/time representation. (Equivalent to SET datestyle TO ....)
    *

      PGTZ sets the default time zone. (Equivalent to SET timezone TO ....)
    *

      PGCLIENTENCODING sets the default client character set encoding. (Equivalent to SET client_encoding TO ....)
    *

      PGGEQO sets the default mode for the genetic query optimizer. (Equivalent to SET geqo TO ....)

Refer to the SQL command SET for information on correct values for these environment variables.

The following environment variables determine internal behavior of libpq; they override compiled-in defaults.

    *

      PGSYSCONFDIR sets the directory containing the pg_service.conf file.
    *

      PGLOCALEDIR sets the directory containing the locale files for message internationalization.

renxiao2003

不错。支持一下。我备份的时候因为密码找了半天最后使用的是EXPECT。

hmily36

我备份的都没用密码.......

zhujinzz

root用户下是不需要密码的:
[root@XXXX bin]# su postgres -c "/usr/local//pgsql/bin/pg_dump -Fc testdb" > /tmp/testdb.pgsql
[root@XXXX bin]# ls /tmp/
mapping-root  testdb.pgsql

islandstar

root用户下是不需要密码的:
[root@XXXX bin]# su postgres -c "/usr/local//pgsql/bin/pg_dump -Fc testdb ...
zhujinzz 发表于 2010-08-04 16:20

这个应该是设置了数据库允许 postgres帐号无密码的情况下才行吧。
或者是数据库认证方式不同。情况当然就不一样了。

prefect

第三种方法： 比如root 运行的脚本 一定呀 在root home目录中 建 .pgpass file 权限如下
-rw-------  1 root root     38 Nov 24  2009 .pgpass
内容如下：
localhost:5432:*:postgres:passwod
我验证可以这样用

congli

ls的可以,谢谢!!

unixkumao

学习