PostgreSQL

Create a Database in PostgreSQL Using the createdb Command

2 years ago
by Sidratul Muntaha
In this guide, we will learn about creating a database in PostgreSQL using the createdb command.

Prerequisites:

To perform the steps that are demonstrated in this guide, you need the following components:

PostgreSQL Databases

PostgreSQL is a free and open-source SQL-compliant object-relational database system. It can work with both relational (SQL) and non-relational (JSON) querying. Learn more about the features of PostgreSQL.

In PostgreSQL, the data hierarchy is as follows:

  • cluster
  • database
  • schema
  • table (or other objects; for example, function)

Any PostgreSQL instance can accept multiple client connections. The client must specify the database name in the connection request. Only one database per connection is allowed. However, a client can open multiple connections to the server, connecting to one or more databases simultaneously.

Creating a Database in PostgreSQL

1. Opening a Connection to the Server

To create a new database, connect to the PostgreSQL server first:

$ sudo -i -u postgres
$ psql

Note that database creation is a restricted operation. Only users with sufficient privilege are allowed to perform the action.

2. Listing the Current Databases
Run the following query in psql to print the list of databases on the server:

$ \list

During installation, PostgreSQL creates the first database of the server which is “postgres”. Two additional databases are also created:

  • template1: Whenever creating any new database within the cluster, “template1” is cloned.
  • template0: It serves as a pristine copy of the original content of “template1”.

Do not create objects within “template1” unless you want them to be a part of every newly created database. If “template1” is modified, “template0” can be cloned to create a new database without any site-local additions.

3. Creating a New Database
To create a new database, run the following query in psql:

$ CREATE DATABASE <db_name>;

Here:

  • The current role is automatically assumed as the owner of the new database.
  • The owner has the privilege to change the owner to a different role.

Check the list of databases if the action is successful using the following command:

$ \list

With a privileged account, we can also create a database for someone else using the following command:

$ CREATE DATABASE <db_name> OWNER <role>;

The Createdb Command

In the previous method, we had to go through multiple steps to create a database:

  • Connect to the PostgreSQL server using psql.
  • Run the queries to create a new database.

To streamline the process, PostgreSQL comes with the createdb command. It basically acts as a wrapper for these actions. We can directly run the createdb command from the shell.

1. Creating a Database Using Createdb
To create a database using the default database server, use the following command:

$ createdb <db_name>

Verify the action using the following command:

$ psql -c "\list"

With various options, we can also fine-tune the createdb operations. Check out the following example:

$ createdb -h <host> -p <port> -T <template> -e --username=<username> --password --maintenance-db=<maintenance_db_name> <db_name>

Here:

  • -h: This parameter specifies the PostgreSQL server location (IP address or domain name).
  • -p: The port to connect to the server.
  • -T: The template to use when creating the new database. It can be template0, template1, or any other database.
  • -e: Echoes the equivalent query.
  • –username: The username to connect to the server.
  • –password: Forces the createdb command to prompt for a password before connecting to the server. In most cases, it’s not required since createdb automatically prompts for a password if the server requires it. However, it spends a connection attempt in figuring it out.
  • –maintenance-db: The database to connect to when creating the new database. If not specified, postgres is assumed by default. If postgres doesn’t exist, “template1” is assumed.

Time to put it in action. Run the following createdb command:

$ createdb -h localhost -p 5432 -T template0 -e --username=postgres test_db

As the output suggests, it’s equivalent to the following query:

$ CREATE DATABASE test_db TEMPLATE template0;

Additional Database Management

In this section, let’s take a look at other database management operations.

Listing the Databases

There are a couple of ways to list the databases that are stored in the server. We already demonstrated one method in the previous sections:

$ \list

Another way is to examine the “pg_database” system catalog:

$ SELECT datname FROM pg_database;

Deleting a Database

To delete a database, run the following query:

$ DROP DATABASE <db_name>;

Similar to createdb, PostgreSQL also comes with the dropdb command which we can run from the shell. Take a look at the following example:

$ dropdb -h <host> -p <port> --username=<username> --password -e <db_name>

Here:

  • -h: The PostgreSQL server to connect to.
  • -p: The port of the PostgreSQL server to connect to.
  • -e: Echoes the equivalent query.

Note that the user must have sufficient privilege to delete a database.

Changing a Database Ownership

The owner of a database can perform any action on the database, including deleting the database. By default, the user that creates the database is assigned as the owner. However, we can reassign the ownership to a different user.

To change the owner of the database, run the following query in psql:

$ ALTER DATABASE <db_name> OWNER TO <new_owner>;

This, however, won’t change the ownership of the objects within the database (including tables). In such cases, we have to use a different query. Connect to the target database and run the following query:

$ REASSIGN OWNED BY <old_owner> TO <new_owner>;

While convenient, this query comes with a few caveats:

  • When connecting to postgres (database), it can change the ownership of multiple databases at once.
  • Don’t use this query when the original owner is postgres as it could corrupt the entire DB instance.

Bonus: Running the Queries from the Shell

So far, we run the queries from the PostgreSQL console. What if you want to incorporate some database functionalities in your scripts? The createdb and dropdb commands can only do specific operations.

To solve this, we can use psql as a conduit. Besides the standard interactive shell, psql can also run the queries on the fly.

Method 1:

The command structure is as follows:

$ psql -h <host> -p <port> -U <username> -d <database> -c <query>

Here:

  • -h: The address of the PostgreSQL server.
  • -p: The port to connect to (default value is 5432).
  • -U: The user to connect to as.
  • -d: The database to connect to.
  • -c: The query to execute.

Method 2:

PostgreSQL comes with another interesting feature: connection URI. It’s a clever way of neatly encoding all the connection parameters. The structure of a connection URI is as follows:

$ postgresql://<username>:<password>@<host>:<port>/<db_name>

Here:

  • postgresql or postgres: The unique protocol for PostgreSQL connection URI.

To connect to a database using the connection URI, use the following psql command:

$ psql -d <connection_uri> -c <query>

Conclusion

We learned about various ways of creating databases in PostgreSQL. We demonstrated how to create a database using the CREATE DATABASE query. We also showcased the database creation using the createdb command. In addition, we also glossed over some other important database management actions like deleting databases and changing the owner.

Interested in learning more about PostgreSQL? Check out the PostgreSQL sub-category that contains numerous guides on various features; for example: functions, regular expressions, tables, and much more.

About the author

Sidratul Muntaha

Student of CSE. I love Linux and playing with tech and gadgets. I use both Ubuntu and Linux Mint.

View all posts