Tuesday, September 11, 2012

pgAgent Stumbling Blocks

In addition to doing scheduled backups from Postgres, we've also needed to load nightly dumps sent via scp to our database server's filesystem.  I finally got pgAgent working, but it wasn't without much consternation.  If you need to do this sort of thing on Windows, I'd recommend using Task Scheduler and a batch file if pgdump will work (though it likely won't if the file is coming from Oracle), or Task Scheduler with pgsql if it won't.  If you need to use pgAgent, here are some stumbling blocks that I found:

  • add pgpass.conf to C:\Users\postgres\AppData\Roaming\postgresql
    • should be localhost:5432:*:postgres:PASSWORD 
    • (note, this is not under the default user, it's the "postgres" user on your system) 
  • may also need the following in C:\Program Files (x86)\PostgreSQL\9.0\data\pg_hba.conf (but don't think so) 
    • local all all trust
    • host all all XXX.XXX.XXX.XXX/32 trust 
  • reload configuration *running as admin* 
  • install pgAgent via application/stackbuilder (*run as admin*) 
  • make your steps ignore error, for some reason they error even if they complete successfully and stall subsequent steps