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.