Create database from command line

Question

I´m trying to create a data base from command line. My OS is centos and postgres version is 10.9.

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

Why asking me by user?

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 216 · Accepted Answer · edited Apr 03 '22 at 09:46

216

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

6,451
11
52
82

answered Jun 04 '15 at 10:58

django-renjith

2,552
2
12
16

39

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

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 98 · Answer 2 · answered Jul 13 '17 at 13:05

98

Try:

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

answered Jul 13 '17 at 13:05

tulsluper

1,408
12
7

11

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
1

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 43 · Answer 3 · edited Jun 18 '18 at 06:33

43

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

a_horse_with_no_name

497,550
91
775
843

answered May 22 '17 at 12:20

Arif Usman

1,228
1
12
19

4

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 35 · Answer 4 · answered Jun 18 '18 at 06:24

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.

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

score 17 · 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 4 · Answer 6 · answered Feb 03 '21 at 16:43

4

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

329
3
1

1

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

26,923
21
73
125

answered Aug 12 '19 at 16:39

James Wierzba

14,715
12
69
110

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 2 · Answer 8 · answered Mar 11 '21 at 15:33

2

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

21
2

score 0 · Answer 9 · 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

9 Answers9

Linked