As stated in Part 1, pgAgent is going away, so I'm focussing on setting up pg_timetable as similar to pgAgent that I can. For this second part, I'm going to go over how to configure pg_timetable as a service on Linux.

A heads up, i really loved the pgAgent UI in pgAdmin so I'm working on one for pg_timetable which is mostly patterned after the pgAgent one. You can see the pull request work in progress pg_timetable UI for pgAdmin. I'm in the middle of cleaning up some loose ends before it is ready for commit.

Create account to run pg_timetable jobs

If you had created a pgagent account, you can just reuse that one especially since you'll want it to run the same jobs. If you didn't here are the basic steps. For my purposes I'm going to call the account pgtt. I'm also just going to create the account logged in as root, if you prefer running under an account, then just put sudo on all your commands.

sudo -i
#leave out the -r if you just want it to be a regular account you can log in as
useradd -r pgtt

You will also want to create a PostgreSQL account for this user, or you could just use postgres account, but I like a separate account even if I make it superuser for auditing. Replace superhardpassword with a password of your own choosing.

su postgres
psql #now in psql shell
CREATE ROLE pgtt 
    WITH LOGIN 
        SUPERUSER PASSWORD 'superhardpassword';
\q
exit

I like to keep my passwords in pgpass file, but you could store it as an environment variable too. To create pgpass, do the below.

su pgtt
cat << 'EOF' > ~/.pgpass
*:*:*:pgtt:superhardpassword
EOF
chmod 0600 ~/.pgpass

Setting up pg_timetable on Linux

For this I tested with Debian 13 but should work just fine if you are running Ubuntu or Redhat based. For other systems you might need to change things a bit to suit your variant of Linux. Binaries are available for pg_timetable for Linux, Darwin, and Windows with each release here pg_timetable releases

For the purpose of this discussion, I'm going to be using v6.3.0, which is the latest at the time of this writing. There are two flavors of binaries. There is the raw binary and there are installer packages. You need to click the Show All 17 Assets to see all of them. There are installers for Yum and Debian based systems as well as the binaries. For other OS it just has the executables for the agent. For the sake of same instruction for all, I'm going to go with the binaries so I don't have to use different commands to install. Also pay careful attention you download the right binary for your OS/CPU Architecture on an x86_64, i386, or ARM because for Linux you'll see all of them. Alternatively you can use the docker package if you have docker installed.

If per chance your OS/Architecture is not covered or you want to test a not yet released version, you can compile your own by compiling with Go compiler which you can get from https://go.dev/dl/ or from your package manager.

Install and configure as a service

# Note this is for amd/intel chip run x64 Linux
#make sure to use _arm if you have an arm or i386 if you are running 32-bit
wget https://github.com/cybertec-postgresql/pg_timetable/releases/download/v6.3.0/pg_timetable_Linux_x86_64.tar.gz

# extract
tar -xvf pg_timetable_Linux_x86_64.tar.gz
# copy to local system
cp pg_timetable_Linux_x86_64/pg_timetable /usr/local/bin/
# make executable
chmod +x /usr/local/bin/pg_timetable

mkdir -p /etc/pg_timetable
mkdir -p  /var/log/pg_timetable
chown pgtt:pgtt /var/log/pg_timetable
mkdir -p /etc/pg_timetable
mkdir -p  /var/log/pg_timetable
chown pgtt:pgtt /var/log/pg_timetable

Most people just store pgagent in postgres. You can use a different database if you want to. For migration from pgagent to pg_timetable, it's easiest to install pg_timetable in same database as you had pgagent. pg_timetable has a pgAgent to pg_timetable migration SQL script which you can customize for your needs that will copy your pgagent jobs to pg_timetable tables. pg_timetable tables and functions will live in a schema called timetable.

cat << EOF > /etc/pg_timetable/pg_timetable.conf
PGDATABASE=postgres
PGUSER=postgres
PGHOST=localhost
PGPORT=5432

# leave out password if you created ~/.pgpass
PGPASSWORD="superhardpassword"
#if you don't want to use your hostname for runner
# hardcode the anme
CLIENTNAME=${HOSTNAME}
LOGFILE=/var/log/pg_timetable/pg_timetable.log
LOGLEVEL=2
EOF

cat << 'EOF' | sudo tee /etc/systemd/system/pg_timetable.service
[Unit]
Description=pg_timetable Advanced PostgreSQL Job Scheduler
After=network.target postgresql.service

[Service]
User=pgtt
Group=pgtt
Type=simple
# Location of the configuration file
EnvironmentFile=/etc/pg_timetable/pg_timetable.conf
ExecStart=/usr/local/bin/pg_timetable --clientname=${CLIENTNAME} --log-file=${LOGFILE}
Restart=always
RestartSec=10

[Install]
WantedBy=multi-user.target
EOF

Unlike pgAgent, there is no CREATE EXTENSION command you need to do to create pg_timetable tables, views, and functions. These are created for you on the startup of the first agent. If you'd prefer to create them manually on first go you can run pg_timetable --init --clientname=${HOSTNAME}. Check out the help with pg_timetable --help

sudo systemctl daemon-reload
sudo systemctl enable pg_timetable
sudo systemctl start pg_timetable
# confirm it's working
sudo systemctl status pg_timetable

If everything is working, you should see a new schema in your postgres database called timetable. If it fails most likely you have a permission issue or password issue.

Hopefully these instructions work for you. In the next article I'll cover how to setup pg_timetable agent as a service under windows.