Thread: Database backup

Database backup

From

"Panchal, Ritesh"

Date:

12 February 2003, 18:00:18

Hi,
I am trying to automate my backups using pg_dump. However, I am not able to
pass the password to the command. Has anybody sucessfully done automated
backup thru scripts?

Thank you for your comments and suggestions.

Ritesh.

Re: Database backup

From

Bruce Momjian

Date:

12 February 2003, 19:27:22

7.3.2 has .pgpass where you can place the password.

---------------------------------------------------------------------------

Panchal, Ritesh wrote:
> Hi,
> I am trying to automate my backups using pg_dump. However, I am not able to
> pass the password to the command. Has anybody sucessfully done automated
> backup thru scripts?
>
> Thank you for your comments and suggestions.
>
> Ritesh.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Database backup

From

Preston

Date:

12 February 2003, 19:41:06

Hi all,

> 7.3.2 has .pgpass where you can place the password.

That's a nice feature - haven't read up much on the 7.3 series as yet. It will
certainly remove my reliance on kludgy expect scripts to automate backups and
refreshes...

> Panchal, Ritesh wrote:
> > Hi,
> > I am trying to automate my backups using pg_dump. However, I am not
> > able to pass the password to the command. Has anybody sucessfully done
> > automated backup thru scripts?
> >
> > Thank you for your comments and suggestions.

As above - I've used "expect" in the past with success. Stored in a secure
directory that only postgres will have access to (with of course the exception
of root...) I have a "dump_prodDB" script which looks like the following:

========================== expect script ===============================
#!/usr/bin/expect

spawn pg_dump prodDB -f /var/lib/pgsql/tmp/prodDB.dump -U backup_user
set timeout -1

expect "Password:"
send "s3cr3tpa55w0rd\r"
expect eof
========================== expect script ===============================

(Where "s3cr3tpa55w0rd" is the appropriate password string or md5 password for
the backup user...)

This is in turn called by the primary backup script, which backs up all the
databases on the system but for those databases requiring passwords invokes the
appropriate script. Since I have only one database requiring a password I didn't
bother to make the script generic by database name but that's relatively easy.

The script that calls this then does a bzip2 on the database backup. This is
called as part of a Legato NetWorker savepnpc process, which then writes it off
to tape as part of the filesystem backup...

As I said, it's a kludge. But it works for what I need it to do :)

Cheers,

-Preston.

--
Oops.

Re: Database backup

From

dima

Date:

13 February 2003, 06:54:23

> I am trying to automate my backups using pg_dump. However, I am not able to
> pass the password to the command. Has anybody sucessfully done automated
> backup thru scripts?
export PGPASSWORD=password && pg_dump ... && export PGPASSWORD=""

Re: Database backup

From

Robert Treat

Date:

13 February 2003, 13:19:29

Seems this should be added to the FAQ...

Robert Treat

On Wed, 2003-02-12 at 16:27, Bruce Momjian wrote:
>
> 7.3.2 has .pgpass where you can place the password.
>
> ---------------------------------------------------------------------------
>
> Panchal, Ritesh wrote:
> > Hi,
> > I am trying to automate my backups using pg_dump. However, I am not able to
> > pass the password to the command. Has anybody sucessfully done automated
> > backup thru scripts?
> >
> > Thank you for your comments and suggestions.
> >
> > Ritesh.
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: Database backup

From

Oliver Elphick

Date:

13 February 2003, 15:58:03

On Thu, 2003-02-13 at 08:53, dima wrote:
> > I am trying to automate my backups using pg_dump. However, I am not able to
> > pass the password to the command. Has anybody sucessfully done automated
> > backup thru scripts?
> export PGPASSWORD=password && pg_dump ... && export PGPASSWORD=""

Presumably the second export is to conceal PGPASSWORD; it would not be
executed if pg_dump were to fail, because && means execute the following
pipeline only if the previous command succeeds. This should read:

  export PGPASSWORD=password && pg_dump ... ; export PGPASSWORD=""

Better than this is to use ~/.pgpass, which is a new feature in 7.3.
That would not expose the administrator's password to being scanned in
the process table.  Use of ~/.pgpass is described in the libpq manual,
section 1.11

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "The earth is the LORD'S, and the fullness thereof; the
      world, and they that dwell therein."
                                   Psalms 24:1