Could I script my monthly postgres maintenance?

Question

I have to perform a monthly maintenance to a postgres database.
I puTTy into the system, navigate to the database and then run 3 commands on 40 different tables:

CLUSTER [table1] USING [primarykey];
ANALYZE [table1];
REINDEX TABLE [table1];

I have to wait for each command to finish executing before I can run the next one (i.e. CLUSTER, -wait up to a few minutes-, ANALYZE -wait-, REINDEX -wait-, )

It's very simple to do but it takes around 30-45 minutes of me just copying and pasting 120 lines, one line at a time... is there any way to automate this process?
I have zero experience with scripting and I know very little about postgreSQL.

My question is somewhat unique because I cannot install anything in the postgreSQL database. I want to have this script localized on my computer and then be able to run it when it's time for the maintenance.

Possible duplicate of [Event Scheduler in PostgreSQL?](http://stackoverflow.com/questions/6839469/event-scheduler-in-postgresql) — Marc B, Apr 29 '16 at 19:44
@PeterEisentraut I have no idea what that is but I'm about to google it! from your statement, I assume I can do this with cron — p0tato, Apr 29 '16 at 20:16

score 0 · Answer 1 · answered Apr 30 '16 at 19:25

Clustering automatically reindexes the table. There is no reason to reindex the table immediately after you cluster it.
Do you actually need to do this stuff? Do you have evidence that your tables are in need of clustering? Or you just assuming they do because of something you read off the internet referring to a decade-old version of PostgreSQL written by someone who didn't know what they were talking about in the first place? It is possible you really would benefit from this. It is even more possible you wouldn't, and it is just a waste of time.
If you know nothing about scripting, then you need to learn something about scripting. You should probably tag your post as being about scripting, in whichever shell/language you would like to use.

At the core, all you have to do is write a series of commands to be executed from the command line, and shove them into a text file. The easiest way is probably to install psql on your local computer, if it is not already there.

psql -c 'cluster foobar' -h thehost.example.com
psql -c 'analyze foobar' -h thehost.example.com

You might need to do some configuration to make this connection work with whatever authentication method you have in place, but without knowing which authentication method that is I can't comment further.

If the cluster for some reason fails, there is little reason to proceed to try to analyze it. (But there is also little harm in doing so). If you want to fine tune this situation, there are a variety of ways to do it, depending on which shell you are writing your script for, and what you want it to do.

thanks for that first tip, I no longer reindex. In regards to your second point, I don't know if this is 100% necessary but it is recommended by the support team of the system. Would this automation be doable with puTTy? — p0tato, Aug 26 '16 at 19:12

Could I script my monthly postgres maintenance?

1 Answers1