Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am trying to execute a few postgres commands from a bash file.

I am trying to drop a schema by writing the following line

DROP SCHEMA "xxx" IF EXISTS

I have two issues here

  1. I am not sure how to specify the db from which the schema should be deleted

  2. I am getting the following error message -- "DROP: command not found"

Can anyone help me with this issue?

share|improve this question
add comment

2 Answers

You cannot execute SQL statements directly from a bash script. Use psql to send the command to the database server. If you have a single (or a few) SQL statements to execute you can do it like this

psql -d YOUR_DB_NAME -c "YOUR_SQL_STATEMENT"

Make sure that your SQL statement does not include quotes or escape them appropriately. If you have many SQL statements in a file you can execute all of them like this

psql -d YOUR_DB_NAME -f FILENAME

In the above YOUR_DB_NAME should be replaced with your database name, YOUR_SQL_STATEMENT with the SQL statement you want to execute and FILENAME is the name of the file with SQL statements to be executed.

If you need to specify extra parameters like username, check the command's usage message displayed with psql -?.

Also, note that your syntax for DROP SCHEMA is incorrect. It should be

DROP SCHEMA IF EXISTS schema_name;

See also this documentation.

share|improve this answer
    
Hi, Thankyou for the reply.I tried the below lines of code in a test.sh file pg_dump $dbname > testdb.backup pg_dump $dbname -s -N 'public' >schema_backup.out #Drop the user defined schema's psql -d $dbname -c "DROP_SCHEMA aaa IF EXISTS;" psql -d $dbname -c "DROP_SCHEMA bbb IF EXISTS;" #restore the schema to restore the appropriate permissions cat schema_backup.out | psql testdb I am still getting the following error -- ERROR: schema "aaa" already exists –  Trip Jan 3 '12 at 23:31
    
Can you please help me how to fix this issue –  Trip Jan 3 '12 at 23:38
    
"DROP: command not found" is gone and it looks your SQL actually makes it to the db server. However, your SQL is wrong. See this: postgresql.org/docs/9.0/static/sql-dropschema.html –  Adam Zalcman Jan 3 '12 at 23:41
    
It worked :-) Finally .......... after a day of struggle. Thanku for the help –  Trip Jan 4 '12 at 0:02
    
@Trip Glad to see it helped. Added the description of the last issue to the solution. –  Adam Zalcman Jan 4 '12 at 0:17
add comment

sure Adam Zalcman

Here is the final solution

dbname="xxxx"

Make a copy of the full db

pg_dump $dbname > mydb.backup

backup the schema's that we want to

pg_dump $dbname -s -N 'public' >test.out

Drop the user defined schema's

psql -d $dbname -c "DROP SCHEMA IF EXISTS aaa CASCADE" psql -d $dbname -c "DROP SCHEMA IF EXISTS bbb CASCADE"

restore the schema to restore the appropriate permissions

cat test.out | psql $dbname

And it worked :-)

share|improve this answer
add comment

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.