CODEWITHSUNDEEP
databasepostgresqlcommand-linecommandpsql
Juan Reina Pascual
Juan Reina Pascual

Reputation: 4588

Create database from command line in PostgreSQL

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?

Upvotes: 266

Views: 567074

Answers (11)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

For example, first, you can create the user(role) john with the password banana as shown below. *By default, PostgreSQL needs a password so you should set PASSWORD and you can omit WITH which is optional and you must use '' for PASSWORD instead of "" otherwise there is error:

CREATE ROLE john WITH LOGIN PASSWORD 'banana';

Then, you can create apple database with the owner john as shown below. *If you omit OWNER, the current user(role) is the database owner:

CREATE DATABASE apple OWNER john;

In addition, if you want to drop the user(role) john, you need to remove the owner john from apple database by replacing it with other (e.g., david), then drop the user(role) john as shown below otherwise there is error. *My answer explains how to drop a user(role) more properly:

ALTER DATABASE apple OWNER TO david;
DROP ROLE john;

Or:

REASSIGN OWNED BY john TO david;  
DROP ROLE john;

In addition again, you can drop the database as shown below:

DROP DATABASE apple;

Upvotes: 4

MANAN
MANAN

Reputation: 813

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.

Upvotes: 4

django-renjith
django-renjith

Reputation: 4000

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;

Upvotes: 359

Vaibhav Kadam
Vaibhav Kadam

Reputation: 699

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;

Upvotes: 0

Anthares
Anthares

Reputation: 51

With a single command line:

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

Upvotes: 5

santosh tiwary
santosh tiwary

Reputation: 708

PostgreSQL Create Database - Steps to create database in Postgres.

  1. Login to server using postgres user.
    su - postgres
  2. Connect to postgresql database.
bash-4.1$ psql
psql (12.1)
Type "help" for help.
postgres=#
  1. Execute below command to create database.
CREATE DATABASE database_name;

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

Upvotes: 11

James Wierzba
James Wierzba

Reputation: 17508

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

Upvotes: 3

Arif Usman
Arif Usman

Reputation: 1728

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 ;

Upvotes: 88

HelloWorld101
HelloWorld101

Reputation: 4366

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.

Upvotes: 57

Gaurav Sachdeva
Gaurav Sachdeva

Reputation: 893

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

Upvotes: 24

tulsluper
tulsluper

Reputation: 1977

Try:

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

Upvotes: 151

Related Questions