PostgreSQL, automate REINDEX, Windows environment

Question

I have a couple of PostgreSQL tables (9.1) which are inserted to / deleted from often. Over time they suffer from index bloating even though the autovacuum is configured and runs regularly.

I'm thinking about automating the REINDEX on these tables. There will be no one to physically access the database as the software will be installed at the client site and is literally supposed to run for years.

I keep reading about "cron jobs" but I'm missing some guide or tutorial on how to best set it up, specifically in Windows environment.

Could someone please point me to the right direction? Other suggestions are welcome, but the main requirement is that it does not require any manual actions.

EEAA · Accepted Answer · 2013-03-06T02:42:33.133

Windows has their built-in Task Scheduler which, while somewhat limited in functionality, might work for you. If you need something with a bit more configurability, I've had good luck with Visual Cron. That said, in your situation, I'd want as little unknown/un-tested/third-party software on the box as possible, so if you can make the native Task Scheduler work, that would likely be your best option.

Now, with that out of the way, I'd be scared out of my pants if someone asked me to put together a system that could run, completely hands-off for years. How will you handle OS patches, hardware issues, unexpected crashed processes, etc.? You had better, at the very least, ensure that someone from your staff will be able to go on-site every so often to do a sanity check on the server, apply patches, etc.. Is there no way for you to gain remote access to the server for these purposes?

Thanks. Well, the system will not be running quite hands-off and there is an engineer that can fix issues, but we're limited in terms of what he can do. Would take too long to explain, it's about processes and regulations most of the time ... I think eventually we'll do it through pgagent as it turns out there are already a couple of scheduled tasks running this way (cleaning up log tables etc.) — Evgeny, Mar 06 '13 at 22:21

score 0 · Answer 2 · answered Mar 07 '13 at 21:31

Here is my take on it: - make sure pgAgent is installed - I spent some time searching, but could not find a user interface for pgAgent on Windows for Postgres 9.1. Apparently, some existed in earlier versions - but for this I had to write some SQL manually

SET search_path = pgagent;

INSERT INTO pga_jobclass VALUES (6, 'Scheduled Tasks');

INSERT INTO pga_job VALUES (5, 6, 'TableReindex', 'Reindex tables', '', true, 
    '2013-03-07 10:00:00.000+11', --date created
    '2013-03-07 10:00:00.000+11', --date changed
    NULL, NULL, NULL);

INSERT INTO pga_schedule VALUES (3, 5, 'TableReindexSchedule', 'Reindex tables', 
    true, --enabled
    '2013-03-07 10:00:00.000+11', --start date
    NULL, --end (never)
    '{t,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}', --minutes: 't' for run on the first minute of an hour
    '{t,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}', --hours: 't' to run at 3 AM
    '{f,f,f,f,f,f,f}', -- weekdays: don't care, all false
    '{t,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}', -- monthdays: 't' to run on the first day
    '{t,t,t,t,t,t,t,t,t,t,t,t}'); -- months: all true to run on the first day on each month

INSERT INTO pga_jobstep VALUES (5, 5, 'TableReindexInfo', '', true, 's', 'REINDEX TABLE mytable1;REINDEX TABLE mytable2;', '', '@@DATABASE_NAME@@', 'f', NULL);

As a check, the jobnextrun is set to '2013-04-01 03:00:00+11' in pga_job table after running the script.

PostgreSQL, automate REINDEX, Windows environment

2 Answers2