Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have wrote a script to REINDEX many indexes in a database. Here is one of them:

echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE}
psql -U ${USERNAME} -h ${HOSTNAME} -d ${DBNAME} -c "REINDEX INDEX scm_main.unq_vbvdata_vehicle;"
if [[ ${?} -eq 0 ]]; then
    echo "reindex for unq_vbvdata_vehicle finished at: `date "+%F %T"`" >> ${LOG_FILE}
else
    echo "reindex for unq_vbvdata_vehicle failed" >> ${LOG_FILE}
    exit 1
fi

The problem is I can not run this script in standalone mode. psql is prompting password every time it runs. There is also two limitations:

  1. I can not create a user on database with no password.

  2. Because REINDEX locks tables, I should use sleep <num> between each REINDEX.

Is there any automatic solution?

share|improve this question

1 Answer 1

up vote 13 down vote accepted

You have three choices regarding the password prompt:

  1. set the PGPASSWORD environment variable. For details see the manual: http://www.postgresql.org/docs/current/static/libpq-envars.html
  2. use a .pgpass file to store the password. For details see the manual: http://www.postgresql.org/docs/current/static/libpq-pgpass.html
  3. use "trust authentication" for that specific user: http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-TRUST
share|improve this answer
1  
or 3. 'trust' the user in pg_hba.conf? –  Jack Douglas Mar 10 '12 at 9:08
    
@JackDouglas: thanks, I added that. –  a_horse_with_no_name Mar 10 '12 at 9:11
    
Since PostgreSQL 9.1 there is also the peer authentication method for local connections. Currently only for Linux, BSD, OS X or Solaris (not Windows). –  Erwin Brandstetter Mar 11 '12 at 2:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.