Create a Postgres database using a batch file with [template], [encoding], [owner] and .sql file

Question

I want to create a Postgres database using a batch file. Now the normal way to do this is:

"C: \ Program Files \ PostgreSQL \ 9.0 \ bin \ createdb.exe" -U Myadmin MydatAbseName

This script above creates a database with default database parameters. However, I want to create a database with the following parameters:

WITH OWNER = Myadmin TEMPLATE = template0 ENCODING = 'SQL_ASCII' TABLESPACE = pg_default LC_COLLATE = 'C' LC_CTYPE = 'C' CONNECTION LIMIT = -1; 

Tell us how to create a database with the above parameters using batch files.

Also let me know how to use the .sql file to do the same as on this command line:

 "C:\Program Files\PostgreSQL\9.0\bin\createdb.exe" -U Myadmin -f C:\createDB.sql; 

Answer 1

The createdb client program createdb not support all of these options.
Create the db_create.sql file:

 CREATE DATABASE MydatAbseName WITH OWNER myadmin TEMPLATE template0 ENCODING 'SQL_ASCII' TABLESPACE pg_default LC_COLLATE 'C' LC_CTYPE 'C' CONNECTION LIMIT -1; 

Name it:

 psql -U postgres postgres -f C:/path/to/db_create.sql 

The trick here is to connect to the default db postgres and create a new database. I am doing this with the default superuser named postgres in my example.
psql -f executes SQL commands in this file.

You can also just execute one command with psql -c (without file involvement):

 psql -U postgres postgres -c "CREATE DATABASE MydatAbseName WITH OWNER Myadmin EMPLATE template ENCODING 'SQL_ASCII' TABLESPACE pg_default LC_COLLATE 'C' LC_CTYPE C' CONNECTION LIMIT -1" 

Read more about creating a database in an excellent guide here and.
Learn more about psql .

---

On Windows, it looks something like this:

 "C:\Program Files\PostgreSQL\verson_number\bin\psql.exe" -U user -f C:/path/to/db_create.sql postgres 

The last "postgres" is the default db service name. If you want to use it in a batch file, you need to answer the password request or connect to the user who is allowed access without providing a password. The basics are in the chapters Password File and pg_hba.conf File Manuals. More details here:

• Run batch file with psql command without password