How to Create and Manage PostgreSQL Databases

PostgreSQL is a free, open-source and one of the most popular database management systems. A relational database management system uses databases and tables to structure and organize data. Creating and managing the PostgreSQL database is important for any database administrator. Therefore, it is essential for any system administrator to know how to create, list and delete databases and tables using the command-line interface.

This guide will show you how to create and manage a PostgreSQL database, user and tables.

Requirements
Connect to PostgreSQL Shell
Create a User in PostgreSQL
Create a Database in PostgreSQL
Create a Table in PostgreSQL
Conclusion

Requirements

A server running Ubuntu 20.04 with PostgreSQL installed.
A root password is set up on your server.

Connect to PostgreSQL Shell

There are two ways to access the Postgres shell.

First, switch to the Postgres user with the following command:

su - postgres

Next, access the PostgreSQL prompt using the following command:

psql

Output:

psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)) Type "help" for help. postgres=#

You can exit from the PostgreSQL with the following command:

postgres=# \q

You can also access the PostgreSQL prompt without switching the Postgres user.

You can do it with the following command:

sudo -u postgres psql

Output:

psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)) Type "help" for help. postgres=#

Create a User in PostgreSQL

To create a new user, you will need to connect to the PostgreSQL shell.

sudo -u postgres psql

Once connected, create a new user named user1 and set a password with the following command:

postgres=# CREATE USER user1 WITH PASSWORD 'password';

Now, see the attribute of the newly created user with the following command:

\du

Output:

List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} user1 | | {}

You can add Superuser attribute to user1 with the following command:

postgres=# ALTER USER user1 WITH SUPERUSER;

Now, check the attribute again:

postgres=# \du

Output:

List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} user1 | Superuser | {}

If you want to change the password of the user1, run the following command:

postgres=# ALTER USER user1 WITH PASSWORD 'newpassword';

To delete a user, run the following command:

postgres=# DROP USER user1;

Create a Database in PostgreSQL

First, login to the PostgreSQL shell with the following command:

sudo -u postgres psql

Once login, check your connection information with the following command:

postgres=# \conninfo

Output:

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Next, create a new database named userdb with the following command:

postgres=# CREATE DATABASE userdb;

To list all databases, run the following command:

postgres=# \list

Output:

List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres userdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (4 rows)

Grant all privileges on userdb to user1, run the following command:

postgres=# grant all privileges on database userdb to user1;

To delete a database, run the following command:

postgres=# DROP DATABASE userdb;

Create a Table in PostgreSQL

First, connect to the PostgreSQL with the following command:

sudo -u postgres psql

Once connected, connect to the database where you want to create a table:

postgres=# \c userdb

Output:

You are now connected to database "userdb" as user "postgres".

Next, create a table named usertable with the following command:

userdb=# CREATE TABLE usertable ( code char(5) NOT NULL, name varchar(40) NOT NULL, city varchar(40) NOT NULL, joined_on date NOT NULL, PRIMARY KEY (code));

To check the relation of table, run the following command:

userdb=# \d

Output:

List of relations Schema | Name | Type | Owner --------+-----------+-------+---------- public | usertable | table | postgres (1 row)

Next, insert some data in the usertable with the following command:

userdb=# INSERT INTO usertable VALUES(1,'Hitesh Jethva','India','2021-04-30');

To check the data in the usertable, run the following command:

userdb=# SELECT * FROM usertable;

Output:

code | name | city | joined_on -------+---------------+-------+------------ 1 | Hitesh Jethva | India | 2021-04-30 (1 row)

To list all tables in the current database, run the following command:

userdb=# \dt

Output:

List of relations Schema | Name | Type | Owner --------+-----------+-------+---------- public | usertable | table | postgres (1 row)

To delete a table, run the following command:

userdb=# DROP TABLE usertable;

Conclusion

The above guide taught you how to create and manage databases, users and tables in PostgreSQL. We hope this will help you to perform day-to-day database operations.

Can we create multiple databases in PostgreSQL?

Multiple databases can be managed by one Postgre server process simultaneously. Every database is kept on the server as a distinct set of files in its directory under the data directory.