1

I want to perform a full vacuum and reindex on my database for my app hosted on Heroku.

I can't work out how to do it via the heroku command line remotely.

I can do it on my local Mac osx machine via the below commands in terminal...

psql database_name
>> vaccuum full;
>> \q

reindex database database_name

How can i perform a full vaccuum and reindex all my tables for my app on Heroku?

If possible I would like to do it without exporting the database.

Norto23
  • 2,249
  • 3
  • 23
  • 40

3 Answers3

2

Okay so it seems Heroku doesn't support this functionality unless you pay up. Looks like i'll have to pull the database, perform the actions and push it back upstream! Fun times.

Norto23
  • 2,249
  • 3
  • 23
  • 40
1

You can use the psql interactive terminal with Heroku. From Heroku PostgreSQL:

If you have PostgreSQL installed on your system, you can open a direct psql console to your remote db:

$ heroku pg:psql

Connecting to HEROKU_POSTGRESQL_RED... done
psql (9.1.3, server 9.1.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

rd2lk8ev3jt5j50=>

Community
  • 1
  • 1
Lukas Eklund
  • 6,068
  • 1
  • 32
  • 33
  • 1
    This is only if you're running one of the larger PostgreSQL plans (Ronin +) with Heroku that start at $200/mo. – coreyward May 04 '12 at 03:41
  • or the new Shared Postgres 9.1 that permits ingress https://postgres.heroku.com/blog/past/2012/4/26/heroku_postgres_development_plan/ – John Beynon May 04 '12 at 07:05
  • Thanks for the responses, I'm using the free database and it is a commercial application that's already in use so I cannot afford to use a beta version – Norto23 May 04 '12 at 08:17
1

You can also pass-in the parameters at the psql command-line, or from a batch file. The first statements gather necessary details for connecting to your database.

The final prompt asks for the constraint values, which will be used in the WHERE column IN() clause. Remember to single-quote if strings, and separate by comma:

@echo off
echo "Test for Passing Params to PGSQL"
SET server=localhost
SET /P server="Server [%server%]: "

SET database=amedatamodel
SET /P database="Database [%database%]: "

SET port=5432
SET /P port="Port [%port%]: "

SET username=postgres
SET /P username="Username [%username%]: "
    
"C:\Program Files\PostgreSQL\9.0\bin\psql.exe" -h %server% -U %username% -d %database% -p %port% -e -v -f cleanUp.sql

Now in your SQL code file, add the clean-up SQL, vacuum full (note the spelling). Save this as cleanUp.sql:

VACUUM FULL;

In Windows, save the whole file as a DOS BATch file (.bat), save the cleanUp.sql in the same directory, and launch the batch file. Thanks for Dave Page, of EnterpriseDB, for the original prompted script.

Also Norto, check out my other posting if you want to add parameters to your script, that can be evaluated in the SQL. Please vote it up.

Community
  • 1
  • 1
MAbraham1
  • 1,717
  • 4
  • 28
  • 45
  • I am using Postgresql and it's on a mac os. – Norto23 May 06 '12 at 23:12
  • Have you tried using an MS-DOS emulator such as Boxer? http://www.macupdate.com/app/mac/27440/boxer For running PSQL on the Mac OS, see the following post: http://dba.stackexchange.com/questions/3005/how-to-run-psql-on-mac-os-x – MAbraham1 May 07 '12 at 15:57
  • My problem is that I have the app and database stored on Heroku, and I want to run these commands via the Heroku command line interface remotely. – Norto23 May 07 '12 at 23:55