Create database from command line

Question

I am trying to create a database from command line. My OS is centos and postgres version is 10.9.

sudo -u postgres psql createdb test
Password for user test:

Why is it prompting me for the password?

Possible duplicate of [How to configure postgresql for the first time?](https://stackoverflow.com/questions/1471571/how-to-configure-postgresql-for-the-first-time) — Evan Carroll, May 20 '18 at 08:07

score 288 · Accepted Answer · edited Apr 03 '22 at 09:46

288

Change the user to postgres :

su - postgres

Create User for Postgres (in the shell and NOT with psql)

$ createuser testuser

Create Database (same)

$ createdb testdb

Acces the postgres Shell

psql ( enter the password for postgressql)

Provide the privileges to the postgres user

$ alter user testuser with encrypted password 'qwerty';
$ grant all privileges on database testdb to testuser;

edited Apr 03 '22 at 09:46

YakovL

7,557
12
62
102

answered Jun 04 '15 at 10:58

django-renjith

3,280
2
13
16

54

Important: createuser and createdb are run in the shell and *NOT* with psql. – Kim Kern Dec 07 '17 at 09:10
51

But if run with psql, the correct commands are: ```create user testuser; create database testdb;``` – kiril Jan 10 '18 at 05:15
If the name of user is 'user', Can it be run as "$ alter user user with encrypted password 'qwerty';" – u tyagi Nov 27 '21 at 16:51

score 124 · Answer 2 · answered Jul 13 '17 at 13:05

124

Try:

sudo -u postgres psql -c 'create database test;'

answered Jul 13 '17 at 13:05

tulsluper

1,688
1
13
8

15

do not forget to grant permissions as well: `sudo -u postgres psql -c 'grant all privileges on database test to username;'` – lukyer Sep 20 '17 at 14:45
2

On mac, it's just `psql -c 'create database test;'` – Joshua Swain Apr 19 '21 at 17:09
@JoshuaSwain That could be done if you have a postgresql user with the same name with your shell user with the privilege to create new database. – adipginting Jun 02 '22 at 04:40

score 61 · Answer 3 · edited Jun 18 '18 at 06:33

61

createdb is a command line utility which you can run from bash and not from psql. To create a database from psql, use the create database statement like so:

create database [databasename];

Note: be sure to always end your SQL statements with ;

edited Jun 18 '18 at 06:33

answered May 22 '17 at 12:20

Arif Usman

1,438
1
13
19

6

Plus one for the hint about ending with `;` – Jstuff Dec 27 '18 at 18:51
Just wanted to comment that Postgres makes UPPERCASE to lower. So `CREATE DATABASE FOO` would actually create database named `foo`. It took me like 5 minutes to figure out why "database doesn't exists". see: https://stackoverflow.com/questions/43111996/why-postgresql-does-not-like-uppercase-table-names – O-9 Apr 04 '19 at 11:21

score 46 · Answer 4 · answered Jun 18 '18 at 06:24

46

As some of the answers point out, createdb is a command line utility that could be used to create database.

Assuming you have a user named dbuser, the following command could be used to create a database and provide access to dbuser:

createdb -h localhost -p 5432 -U dbuser testdb

Replace localhost with your correct DB host name, 5432 with correct DB port, and testdb with the database name you want to create.

Now psql could be used to connect to this newly created database:

psql -h localhost -p 5432 -U dbuser -d testdb

Tested with createdb and psql versions 9.4.15.

answered Jun 18 '18 at 06:24

HelloWorld101

3,878
2
34
47

This was the right way to create a database on an instance I can't ssh to. All the attempts to run "CREATE DB" as -c param for remote psql utility assumed the existence of a database named like the user – Gili Garibi Apr 23 '20 at 09:08
This was the way that worked for me when using docker and wanting to add a 2nd database to the one that was added as part of the init of the postgres image: `docker exec -it postgres-container sh -c "createdb -h YOUR_HOST -p YOUR_PORT -U YOUR_USER YOUR_DATABASE"` – Reece Daniels Oct 23 '22 at 20:11

score 23 · Answer 5 · answered Dec 29 '17 at 19:17

As the default configuration of Postgres, a user called postgres is made and the user postgres has full super admin access to entire PostgreSQL instance running on your OS.

sudo -u postgres psql

The above command gets you the psql command line interface in admin mode.

Creating user

sudo -u postgres createuser <username>

Creating Database

 sudo -u postgres createdb <dbname>

NOTE: < > are not to be used while writing command, they are used just to signify the variables

score 10 · Answer 6 · answered Feb 03 '21 at 16:43

10

PostgreSQL Create Database - Steps to create database in Postgres.

Login to server using postgres user.
```
su - postgres
```
Connect to postgresql database.

bash-4.1$ psql
psql (12.1)
Type "help" for help.
postgres=#

Execute below command to create database.

CREATE DATABASE database_name;

Check for detailed information below: https://orahow.com/postgresql-create-database/

answered Feb 03 '21 at 16:43

santosh tiwary

598
6
4

2

Please notice the `;` character in the end of the line! It is important that you type it! Verify that the db was indeed created by using `\l` – Kots Sep 16 '21 at 09:52

score 3 · Answer 7 · edited Aug 15 '19 at 20:59

3

PGPORT=5432
PGHOST="my.database.domain.com"
PGUSER="postgres"
PGDB="mydb"
createdb -h $PGHOST -p $PGPORT -U $PGUSER $PGDB

edited Aug 15 '19 at 20:59

Dharman

30,962
25
85
135

answered Aug 12 '19 at 16:39

James Wierzba

16,176
14
79
120

If you change 'PGDB' to 'PGDATABASE' and export them (or prefix them to the command), then you don't need to pass these to the command, it'll read them from the environment. – Connor Bell Nov 26 '20 at 13:21

score 3 · Answer 8 · answered Mar 11 '21 at 15:33

3

With a single command line:

su -c "createuser dbuser;createdb -h localhost -p 5432 -E UTF8 -O dbuser dbname;" - postgres

answered Mar 11 '21 at 15:33

Anthares

31
2

score 3 · Answer 9 · answered Dec 07 '22 at 19:25

It is pretty simple but sometimes I find the answers tricky.
[For windows users]
Open Windows cmd

psql -U <username>
Once connected to psql, enter the following command to create a new database:
CREATE DATABASE <database_name>;
To verify that the database has been created, you can run the \l command to list all available databases. Your new database should be listed in the output.
------[Additional]------
You can now connect to the new database using the \c command followed by the database name, like this:
\c <database_name>
You can now run SQL commands on the new database to create tables, insert data, and so on.
Note: Make sure to replace <username>, <database_name> with your actual Postgres username and database name.

notice not to forget the ; at the end. – Amitk Jan 12 '23 at 11:00 — Amitk, Jan 12 '23 at 11:00

score 0 · Answer 10 · answered Mar 19 '22 at 05:09

If you are using pgAdmin:

In query editor you can try like this :

CREATE DATABASE <databasename>
    WITH 
    OWNER = <dbowner>
    ENCODING = <encoding>
    CONNECTION LIMIT = <numberofsimulaneousconnections>;

an example snippet :

CREATE DATABASE twitterdb
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    CONNECTION LIMIT = -1;

Create database from command line

10 Answers10

Linked