How to Create and Manage PostgreSQL Databases

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

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

# 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

In the above guide, you learned how to create and manage databases, users and tables in PostgreSQL. I hope this will help you to perform day-to-day database operations.

SnapShooter Learning Center

How to Create and Manage PostgreSQL Databases