automatic backups with postgresql

I'm trying to discover a way to perform automatic backups of postgresql databases.

the issue is that when you execute the pg_dump command from the console, it prompts you for a username and password. because of this *simple* reason, you can't run this as a cronned bash script.

so i'm looking to see how php could be utilized in a standalone script that can "insert" values for the username and password dynamically...

any ideas?

Try reading this: http://forums.devshed.com/showthrea...&highlight=cron

unfortunately this is too basic for what I am trying to do...

pg_dump requires that you enter the username and password *after* you issue the command...

-S

I have never been prompted for a password from pg_dump, as long as I am executing the dump as the 'postgres' user. This should apply to whatever your master postgreSQL user is.

So, if you do as suggested in the other post, and tell cron to su to the correct user, you should have no problem. PostgreSQL inherits its permissions directly from the OS, unlike MySQL.

If this doesn't work, please post exactly what you are trying to do.

On the other hand, yes, it is possible to automate the console response as if entering actual keystrokes. This is probably best done in a Perl script, and then you can just run the Perl script as a cron job.

okay... getting somewhere but now i get the error from the console:

fe_sendauth: No password supplied.


any ideas?

-S

Hmm... what authentication method have you specified in the pg_hba.conf file?

I believe the line "local all" is a default, which means local users who have already been authenticated by the Unix system are allowed in. If you see instead "local all password", then that might be the explanation for your problem, meaning that passwords are required, even for valid users.

Is this a shared server, where local users might pose a security risk?

hi rycamor,

it's not a shared server BUT it is running phpPgAdmin which is effectively a "local" entrance into the server. and you're correct in stating that in the pg_hba.conf file it says "local all password"

is there a way to denote that user postgres can be the only user that doesn't need one?

Oops, I made a small mistake: the default is "local all trust", not just "local all" (I don't think that would work at all). "local all trust" means the same thing I intended in my post above, which is that validated local Unix users are "trusted".

I don't think there is a way to specify only one user, but you can specify only one database. The format for local users in the conf file is "local DBNAME AUTHTYPE [AUTH_ARGUMENT]".

But, if it is not a shared server, there is no real reason not to just open it up with "local all trust".

well, for those of you who are interested in learning how to perform automatic backups, i was successful in my research in finding out how to do this. procedure is briefly outlined below. Email me for more information.

It assumes you are running Red Hat 7.1

1. Chances are that you already have tcl installed on your machine. Download and install the "expect" rpm from the resource below:

http://www.rpmfind.net/linux/rpm2ht...hp?query=expect

rpm -ivh ****.rpm

2. Download the attached dbbackup.zip file and extract the dbbackup.exp file from it. Change it's permissions to execute

chmod +x dbbackup.exp

3. run it in the following form

./dbbackup.exp [username] [password] [databasename]

where you substitute the values of username, password, and databasename.

and voila! everything should work.

Key things to consider

* This doesn't go over the security issues of the above command. I'm working on another php-driven version that dynamically grabs the password of a database if stored *in* a database itself.

* This is meant to be used in conjunction with cron jobs. (That was the whole point anyway.)

Have fun!

Cool, I'm going to check it out .