How to fire VACUUM command using shell script in p

Question

Here is my shell script

    #!/bin/bash
    psql --host=127.0.0.1 --port=5432 --dbname=SIEM --username=dbauser
    vacuumdb --analyze --verbose --table 'vuln' SIEM

but its not working fine and gives error as:

   linux-lxh4:/home/gaurav # ./script.sh 
   psql (9.2.5)
   Type "help" for help.

   SIEM=# \q
   vacuumdb: could not connect to database root: FATAL:  Peer authentication failed for user "root"

Edit1:I used this code :

   psql --host=127.0.0.1 --port=5432 --dbname=SIEM --username=dbauser
   VACUUM FULL VERBOSE vuln 

And here is error:

   ./script.sh: line 4: VACUUM: command not found

Answer 1

No need to connect to Postgres using psql if you're running vacuumdb later. Instead use something like the following:

  vacuumdb --host=127.0.0.1 --port=5432 --dbname=SIEM --username=dbauser --analyze --verbose --table 'vuln' 

(alternatively as mentioned in another answer, you can use the VACUUM SQL command after connecting using psql. The syntax is different and does not use "--xxxx" options)

Answer 2

The bash script that we are using here in OLA to vacuum reportsdb.

                     #!/bin/sh
                     dbname="reportsdb_new"
                     username="postgres"
                     psql $dbname $username << EOF
                     vacuum FULL VERBOSE ANALYZE ;
                     reindex database reportsdb_new ;
                     EOF

Answer 3

From Postgres VACUUM documentation

the administrative command is called vacuum not vacuumdb.

I don't have a psql here but it should be

#!/bin/bash
psql --host=127.0.0.1 --port=5432 --dbname=SIEM --username=dbauser
-c 'VACUUM VERBOSE ANALYZE vuln'