DROP Postgres SCHEMA or TABLE using Windows Batch File

Question

1. I want to perform DROP and CREATE SCHEMA in PostgreSQL using Batch file. I have PostgresSQL installed on many machines without any interface like PgAdmin.

2. I am looking for a simple solution to let Technicians perform resetting of database and restore the default backup at production site. Batch file seems to be a good solution.

3. I already figure out command to restore backup using Batch file. The missing item is the drop/create SCHEMA (As i need to create new SCHEMA before restoring the default back up).

4. I tried following command to DROP / CREATE SCHEMA. However it only works when used via PgAdmin or if i type manually under postgres=# prompt on CMD.

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO public;

1. Using the above code in batch file generates following Error.

'drop' is not recognized as an internal or external command, operable program or batch file.

Any suggestions, please let me know. Thanks for help in advance.

Answer 1

Put the statements you want to run into a (text) file e.g. drop.sql

Then in your batch file use:

psql -U <your_username_here> -d <your_database_here> -f drop.sql

psql will prompt you for a password, see Run a PostgreSQL .sql file using command line arguments for possible options to overcome that.