To perform the steps that are demonstrated in this guide, you need the following components:
- A properly-configured Linux system. For testing, consider setting up a virtual Ubuntu machine using VirtualBox.
- Installed and configured PostgreSQL. Learn more about installing PostgreSQL on Ubuntu.
- Access to a non-root user with sudo privilege.
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:
- 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:
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:
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:
- 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:
With a privileged account, we can also create a database for someone else using the following command:
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:
Verify the action using the following command:
With various options, we can also fine-tune the createdb operations. Check out the following example:
- -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:
As the output suggests, it’s equivalent to the following query:
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:
Another way is to examine the “pg_database” system catalog:
Deleting a Database
To delete a database, run the following query:
Similar to createdb, PostgreSQL also comes with the dropdb command which we can run from the shell. Take a look at the following example:
- -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:
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:
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.
The command structure is as follows:
- -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.
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 or postgres: The unique protocol for PostgreSQL connection URI.
To connect to a database using the connection URI, use the following psql command:
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.