How To Create and Manage PostgreSQL Databases
PostgreSQL is a powerful and versatile free and open-source database management system that is widely used in the industry. It is a high-performance, object-relational database management system that uses databases and tables to structure and organize data. As a database administrator, it is important to have a deep understanding of how to create and manage a PostgreSQL database.
In this guide, you will learn how to create, list, and delete databases and tables using the command-line interface. Additionally, you will gain insights into how to manage users, permissions, and data types in PostgreSQL. By the end of this guide, you will have a solid foundation in PostgreSQL and be able to apply your knowledge to real-world scenarios.
Requirements
- A server running Ubuntu 20.04 with PostgreSQL installed.
- A root password set up on your server.
Connect to PostgreSQL Shell
There are two ways to access the Postgres shell:
- 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 conclusion, we hope that this guide has provided you with the necessary knowledge to create and manage databases, users, and tables in PostgreSQL. As a free and open-source database management system, PostgreSQL is widely used in the industry due to its reliability and scalability.
By following the steps outlined in this guide, you can confidently create, list, and delete databases and tables using the command-line interface. In addition, you have learned how to create a new user, set a password, grant privileges, and delete a user in PostgreSQL.
Furthermore, this guide has shown you how to connect to the PostgreSQL shell and database, create a new database, list all databases, and delete a database. You have also learned how to create a table, insert data, list all tables, and delete a table in PostgreSQL.
We believe that this guide will help you perform day-to-day database operations and assist you in your journey to becoming a proficient PostgreSQL user.