1. PostgreSQL Database Creation
PostgreSQL Database is an organized collection of objects stored in a structured format and accessed through the computer system. In PostgreSQL, the Database can be created by using the CREATE DATABASE psql command or using the createdb utility. A new database can created by cloning the system database template1 as well. To create a database in PostgreSQL, an individual should have superuser permission.
1.1 Create Database Using Psql
We can use the PostgreSQL psql command CREATE DATABASE to create a new database from the command line utility.
In the following example, we have used the psql utility to create a new database name CLOUDDUGGU_DB and assigned the owner to the POSTGRES user.
Syntax:
postgresql=# CREATE DATABASE DB_Name [ [ WITH ] [ OWNER [=] username ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] [ LOCALE [=] locale ] [ LC_COLLATE [=] lccollate ] [ LC_CTYPE [=] lcctype ] [ TABLESPACE [=] tablespacename ] [ ALLOW_CONNECTIONS [=] allowconn ] [ CONNECTION LIMIT [=] connlimit ] [ IS_TEMPLATE [=] istemplate ] ]
Command:
postgresql=# CREATE DATABASE CLOUDDUGGU_DB OWNER POSTGRES;
Output:
1.2 Drop Database Using Psql
To drop a database in PostgreSQL we can use the DROP DATABASE command. The Database which is connected currently can't be dropped.
In the below example we will use the Drop Database command to drop the CLOUDDUGGU_DB database.
Command:
postgresql=# DROP DATABASE CLOUDDUGGU_DB;
Output:
1.3 Create Database Using the createdb utility
We can create the PostgreSQL database using the createdb utility as well from the Unix command prompt.
In the following example, we are creating a database named cloudduggu_db using the createdb utility. We have supplied -h for hostname, -p for the post number 5432, and the -U is for the username(postgres)
Command:
cloudduggu@ubuntu: createdb -h localhost -p 5432 -U postgres cloudduggu_db
Output:
We can verify in the below output that the DB name cloudduggu_db is created.
If you are receiving this error createdb: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: then go to the /etc/postgresql/14/main and open the configuration file pg_hba.conf and change all Method to Trust and restart the PostgreSQL services using the command sudo service postgresql restart, post that runs above command it will run.
We can check the createdb utility command options using the createdb --help command. We can see the output in the below figure.
1.4 Drop Database Using the dropdb utility
To drop the PostgreSQL database, we can use the dropdb utility.
In the following section, we will use the dropdb utility to drop the cloudduggu_db.
Command:
cloudduggu@ubuntu: dropdb -h localhost -p 5432 -U postgres cloudduggu_db
Output:
We can verify in the below output that the DB name cloudduggu_db is deleted.
We can check more options for dropdb utility using the dropdb --help command as mentioned in the below figure.
