CODEWITHSUNDEEP
macospostgresqljob-schedulingpgadminpgagent
Pamela Cook - LightBe Corp
Pamela Cook - LightBe Corp

Reputation: 4052

How Can I Schedule A PostgreSQL Command Normally Executed from the Terminal?

I'm using the PostgreSQL database in my Ruby on Rails applications. I manually create backups of my databases using the following command.

pg_dumpall -c --inserts -f ~/Desktop/pgdumpall_`date +%Y%m%d_%H%M%S`.sql

I would like to schedule this command to execute every few hours.

I have done a lot of web searching, including here. I found documentation about PgAgent. I currently use the latest version of PgAdmin which the documentation says can be used to maintain PgAgent.

I just downloaded the latest version. When I read the installation instructions in README I did not understand much of what to do other than to download/install the missing applications. I have included them below.

This document describes the compilation of pgAgent, a job scheduler for 
PostgreSQL.

pgAgent is managed using pgAdmin (http://www.pgadmin.org). The pgAdmin 
documentation contains details of the setup and use of pgAgent with your
PostgreSQL system. The latest build of the documentation can be found at
http://www.pgadmin.org/docs/dev/pgagent.html.

Building pgAgent
----------------

You will need:

- A C/C++ compiler, such as GCC or Microsoft Visual C++ on Windows.
- CMake 2.6 (from www.cmake.org)
- A wxWidgets 2.8.x installation, configured per the requirements for
  pgAdmin:
  http://git.postgresql.org/gitweb/?p=pgadmin3.git;a=blob_plain;f=INSTALL;hb=HEAD
- A PostgreSQL 8.3 or higher installation

1) Unpack the pgAgent source code
2) Create a build directory in which the code will be built.
3) Run ccmake from the build directory (on Windows, use the CMake graphical
   interface). By default, ccmake will generate Unix Makefiles - consult the 
   documentation if you wish to generate other types of output:

$ ccmake /path/to/pgagent

4) If required, press 'c' to generate a default configuration:

CMAKE_BUILD_TYPE                 Release
CMAKE_INSTALL_PREFIX             /usr/local
CMAKE_OSX_ARCHITECTURES          ppc;i386
CMAKE_OSX_SYSROOT                /Developer/SDKs/MacOSX10.5.sdk
PostgreSQL_CONFIG_EXECUTABLE     /usr/local/pgsql/bin/pg_config
wxWidgets_CONFIG_EXECUTABLE      /usr/local/bin/wx-config
wxWidgets_USE_DEBUG              OFF
wxWidgets_USE_STATIC             ON
wxWidgets_USE_UNICODE            ON
wxWidgets_wxrc_EXECUTABLE        /usr/bin/wxrc

5) Use the ccmake interface to adjust any settings as required. When configured
   as required, press 'c' to re-configure (if required) and 'g' to generate the 
   build files and exit.

6) Run 'make' to build pgAgent on Mac or Unix, or open the generated project
   files in VC++ on Windows and build the solution in the desired configuration.

I already had GCC from the command line tools in Xcode. I have installed the CMake app. I'm running PostgreSQL 9.4. I have downloaded wxMac but I don't know what to do with it. The instructions say that it will work with OS X 10.6 and later but the documentation is only for Snow Leopard. I cannot install this unless someone that is using this on Mac computers running Lion or later.

Most of what I'm finding for PgAgent is for Windows users. Is there other software or a process that is easier to implement on a Mac (Intel) that will schedule the execution of my PostgreSQL command?

Upvotes: 0

Views: 1665

Answers (2)

Pamela Cook - LightBe Corp
Pamela Cook - LightBe Corp

Reputation: 4052

I did research on launchd to see how to implement my job. Some stated that it was hard to do repeat executions of jobs using it. However I was able to find an app LaunchD Task Scheduler in the Mac App Store. It costs $1.99. Well worth the price. Other than a learning curve on creating a shell script I was able to get up my job up and running in about 30 minutes. People that know LaunchD will probably get things set up in no time. It's very easy to use. The customer service is excellent.

Upvotes: 0

metronom72
metronom72

Reputation: 171

Did you tried to use Cron or any other solution for autotasks? I don't know about OSX, but in linux

Crontab

0 22 * * * /var/lib/pgsql/backups/backup.sh database_name
0 0 * * 0 /var/lib/pgsql/backups/backup.sh

Backup.sh

BACKUP_DIRECTORY="/var/lib/pgsql/backups"
CURRENT_DATE=$(date "+%Y%m%d")

if [ -z "$1" ]
then
  pg_dumpall | gzip - > $BACKUP_DIRECTORY/pg_dumpall_$CURRENT_DATE.sql.gz
else
  pg_dump $1 | gzip - > $BACKUP_DIRECTORY/$1_$CURRENT_DATE.sql.gz
fi

Upvotes: 1

Related Questions