How to create a new database in PostgreSQL 10 in Windows from batch file?

Question

I am creating a batch file to create a new database in PostgreSQL 10. I used the following code,

echo off
cd "C:\Program Files (x86)\PostgreSQL\10\bin"
psql "dbname=myDB host=localhost user=postgres password=postgres port=5432"
pause

But I get this error,

psql: FATAL: database "myDB" does not exist

I understand this is not the command to create a new database. I also tried the following code,

echo off
cd "C:\Program Files (x86)\PostgreSQL\10\bin"
psql "createdb myDB passfile %APPDATA%\postgresql\pgpass.conf"
pause

This is based on the information in this link. The "pgpass.conf" file has the follwoing text,

localhost:5432:myDB:postgres:postgres

But it asks to enter password. I entered the default password "postgres". But this was the output,

psql: FATAL: password authentication failed for user "User1"

The objective here is to create new database from the batch file without asking for password. How to achieve this?

Surely createdb is also available on Windows? postgresql.org/docs/current/static/app-createdb.html — Colin 't Hart, May 30, 2018 at 11:16
createdb is a command line program, not a SQL statement. psql is used to run SQL statements, not to execute a command line program. You need to decide if you want to run a SQL statement or a command line program to do that. — a_horse_with_no_name, May 30, 2018 at 11:34
I already tried the method mentioned in the links. As mentioned in the question, the objective here is to create new database from the batch file without asking for password. How to achieve this? — Codename K, May 31, 2018 at 21:02
I think your usage of passfile is wrong: postgresql.org/docs/current/static/… Also, you will need -U postgres (or user=postgres, depending on which form of connection parameters you are using) in your createdb command. I'd suggest taking one step at a time, carefully reading some introductory material and the error messages. In this case you could notice that User1 is not the username you want. — dezso, Jun 19, 2018 at 13:27

JediCate · Accepted Answer · 2018-12-12 11:48:24Z

2

Create a bat file with the following content and execute it:

c:\path\to\postgresql\bin\psql.exe -f C:\path\to\db_create.sql  postgresql://user:password@localhost:port/postgres

This is what worked for me.

answered Dec 12, 2018 at 11:48

JediCate

1362 bronze badges

Add a comment |

Paul White · Accepted Answer · 2021-05-20 20:51:11Z

1

Other way to work for me is:

1.- Create one test.bat file with this content:

@set PGPASSFILE=D:\bash\Test02.conf
@"C:\Program Files\PostgreSQL\12\bin\"createdb  -U postgres PEDIDOS-2052021141847 

# all in one line

2.- Create one test.conf file with this content:

*:*:*:postgres:1234
# host:port:database:user:password

3.- Execute .bat file

Paul White♦

78.5k28 gold badges393 silver badges616 bronze badges

answered May 20, 2021 at 20:45

Juan Murillo

113 bronze badges

Add a comment |

2 Answers 2