一种简单的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.

tnbsd007

学习了。。。

sstiger

Mark 一下，日后细看

handbags02

We sell discount Designer Handbags of all top brands like GUCCI Handbags, louis vuitton handbag, Coach handbags,replica designer handbags, designer purses and accessories etc. discount Designer Handbags