#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Austin,TX
    Posts
    62
    Rep Power
    0

    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?
  2. #2
  3. #3
  4. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Austin,TX
    Posts
    62
    Rep Power
    0
    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
  5. #4
  6. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  7. #5
  8. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Austin,TX
    Posts
    62
    Rep Power
    0
    okay... getting somewhere but now i get the error from the console:

    fe_sendauth: No password supplied.


    any ideas?

    -S
  9. #6
  10. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    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?
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  11. #7
  12. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Austin,TX
    Posts
    62
    Rep Power
    0
    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?
  13. #8
  14. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    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".
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  15. #9
  16. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2001
    Location
    Austin,TX
    Posts
    62
    Rep Power
    0
    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/rpm2htm...p?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!
    Attached Files
  17. #10
  18. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Cool, I'm going to check it out .
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo