Table Of Contents

For those folks on windows who want to do http gets and posts directly from your PostgreSQL server, we've made binaries for the http extension for PostgreSQL Windows.

These are designed to work with PostgreSQL EDB windows distributions.

If you have PostGIS already installed, many of these files you will also already have since things like the libcurl and PCRE are also packaged with PostGIS.

http extension binaries for PostgreSQL 19, 18, 17, 16, 15, 14, 13, 12 11, 10, 9.6, 9.5, and 9.4 windows 64-bit downloads

PostgreSQL 17-19 64-bit are 1.7.1

PostgreSQL 16 and 15 are currently 1.6.1

PostgreSQL 10-14 64-bit are 1.5.0, other versions are older http versions.

The curl library for http is built with SSL support and utilizes the ssleasy.dll packaged with the EDB installs.

• PostgreSQL 19 w64 http (zip) 7z version
• PostgreSQL 18 w64 http (zip) 7z version
• PostgreSQL 17 w64 http (zip) 7z version
• PostgreSQL 16 w64 http (zip) 7z version
• PostgreSQL 15 w64 http (zip) 7z version
• PostgreSQL 14 w64 http (zip) 7z version
• PostgreSQL 13 w64 http (zip) 7z version
• PostgreSQL 12 w64 http (zip) 7z version
• PostgreSQL 11 w64 http (zip) 7z version
• PostgreSQL 10 w64 http (zip) 7z version
• PostgreSQL 9.6 w64 http (zip) 7z version
• PostgreSQL 9.5 w64 http (zip) 7z version
• PostgreSQL 9.4 w64 http (zip) 7z version

http extension binaries for PostgreSQL 10, 9.6, 9.5, and 9.4 windows 32-bit downloads

• PostgreSQL 10 w32 http (zip) 7z version
• PostgreSQL 9.6 w32 http (zip) 7z version
• PostgreSQL 9.5 w32 http (zip) 7z version
• PostgreSQL 9.4 w32 http (zip) 7z version

http quick primer

To enable in a database after having installed the binaries.

CREATE EXTENSION http;

Do a basic get

SELECT h.content, h.content_type, hkv.value As dt
FROM http_get('http://postgis.net/') AS h 
    LEFT JOIN LATERAL (SELECT *  
    FROM unnest(h.headers) 
        WHERE field =  'Date') AS hkv ON true;

Check out more examples at: https://github.com/pramsey/pgsql-http and https://github.com/pramsey/pgsql-openai.

http version 1.7 and up support setting any CURL settings via GUC variables. These can be set at the session, database, or system level. This is particularly useful if for some reason your paths aren't set to ssl bundle or you are using a self-signed cert.

Older versions of http extension prior to 1.7, you'd use the http_set_curlopt which would last at most for the length of your session.

SELECT http_set_curlopt('CURLOPT_SSL_VERIFYPEER', '0');

SET http.CURLOPT_SSL_VERIFYPEER = '0';
SELECT h.content, h.content_type, hkv.value As dt
FROM http_get('https://postgis.net/') AS h 
    LEFT JOIN LATERAL (SELECT *  
    FROM unnest(h.headers) 
        WHERE field =  'Date') AS hkv ON true;

pgAgent has been my go to scheduling solution for quite some time. Sadly in 6 months it will be completely retired and the pgAgent UI in pgAdmin will be gone. The main reasons I liked pgAgent were:

• Cross Platform: I have a lot of windows and linux customers, so this was important.
• Nice UI in pgAdmin, so I could do all work with PostgreSQL and schedule things at the same time as well as check status of jobs.
• The database backend is PostgreSQL, my favorite database
• Supports Multiple Agents with varying OS.
• Supports jobs having many ordered steps

pg_timetable has all the above features I mentioned about pgAgent except for the nice UI in pgAdmin. It does have a rudimentary UI here pg_timetable_gui which I haven't tried out.

Another scheduler I've been using and liking is pg_cron. pg_cron is a much simpler scheduler agent and is largely patterned after Linux cron except it can only do database jobs. When all you want is something simple to schedule SQL it hits the spot nicely. But it has no agents, the database is pretty much the agent. I was avoiding pg_cron in the past cause it was unsupported on windows and I needed something that would work both on Linux and Windows. So I submitted a patch to get it to work under windows, compiling with mingw64. I've only tested compiling against mingw64, but use it with the windows PostgreSQL VC++ compiled servers fine. There was another pull request added to make pg_cron compile under Windows VC++ which hasn't been accepted yet. Anyway in a later article I'll compare the two and also provide binaries for pg_cron on windows if there isn't already one by the time I get to it.

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.