Create Postgres database using batch file with [template],[encoding],[owner] and a .sql file
The client program createdb
does not support all those options.
Create a file db_create.sql
:
CREATE DATABASE MydatAbseName
WITH OWNER myadmin
TEMPLATE template0
ENCODING 'SQL_ASCII'
TABLESPACE pg_default
LC_COLLATE 'C'
LC_CTYPE 'C'
CONNECTION LIMIT -1;
Call it:
psql -U postgres postgres -f C:/path/to/db_create.sql
The trick here is to connect to the default maintenance db "postgres" and create the new database from there. I do it with the default superuser named "postgres" in my example.
psql -f
executes the SQL commands in the given file.
You could also just execute a single command with psql -c
(no file involved):
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"
More on creating a database in the fine manual here and here.
More on 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 name of the default maintenance db. If you want to use it in a batch file you have to answer a password prompt or connect with a user that is allowed access without providing a password. Basics in chapters The Password File and The pg_hba.conf File of the manual. More here:
PresleyDias
By Day : MEAN Stack developer! By Night : Digging into Delphi, building Raspberry Pi stuff, working on WordPress website! If you want to know more about what I do, then check by blog Slaay
Updated on July 05, 2022Comments
-
PresleyDias almost 2 years
I want to create a Postgres database using a batch file. Now the normal way of doing this is the following:
"C:\Program Files\PostgreSQL\9.0\bin\createdb.exe" -U Myadmin MydatAbseName
This script above creates a database with the default database parameters. However, I want to create a database with the following parameters, as follows:
WITH OWNER = Myadmin TEMPLATE = template0 ENCODING = 'SQL_ASCII' TABLESPACE = pg_default LC_COLLATE = 'C' LC_CTYPE = 'C' CONNECTION LIMIT = -1;
Please tell me how to create a database with the above parameters using Batch files.
Also let me know how to use a .sql file to do the same, like this command-line:
"C:\Program Files\PostgreSQL\9.0\bin\createdb.exe" -U Myadmin -f C:\createDB.sql;