PostgreSQL (Postgres in short) is an open source, powerful, advanced, high performance and stable relational-document database system. It uses and enhances the SQL language coupled with a large number of features for secure data storage and management.
It is efficient, reliable, and scalable for handling large, complicated volumes of data and setting up enterprise-level and fault-tolerant environments, while ensuring high data integrity. Postgres is also highly extensible with features such as indexes comes with APIs so that you can develop your own solutions to solve your data storage challenges.
In this article, we will explain how to install PostgreSQL on an Ubuntu 18.04 server (also works on older Ubuntu releases) and learn some basic ways to use it.
How to Install PostgreSQL on Ubuntu
First, create a file /etc/apt/sources.list.d/pgdg.list which stores the repository configuration, then import the repository key to your system, update your system packages list and install Postgres package using following commands.
$ sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' $ sudo apt install wget ca-certificates $ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - $ sudo apt update $ sudo apt install postgresql-10 pgadmin4
Once postgres has been installed, the database service started automatically and you can confirm by typing following command.
$ sudo systemctl status postgresql.service
How to Use PostgreSQL Roles and Databases
In postgres, client authentication is controlled by the /etc/postgresql/10/main/pg_hba.conf configuration file. The default authentication method is “peer” for the database administrator, meaning it gets the client’s operating system user name from the operating system and checks if it matches the requested database user name to allow access, for local connections (as shown in the following screenshot).
During the installation process, a system user account called postgres was created without a password, this is also the default database administrator user name.
$ sudo vim /etc/postgresql/10/main/pg_hba.conf
In addition, under postgres database access permission management is performed via roles. A role can be considered as either a database user, or a group of database users, depending on how the role is set up.
The default role is also postgres. Importantly, database roles are conceptually fully unconnected to operating system users, but practically they may not be separate (for example when it comes to client authentication).
Importantly, roles can own database objects, and can assign privileges on those objects to other roles to control who has access to which objects. In addition, it is possible to grant membership in a role to another role.
To configure other roles to use encrypted passwords to manage databases assigned to them, apart from the default postgres role, you need to change the line to.
Then restart the postgresql service to apply the recent changes.
$ sudo systemctl restart postgresql
How to Use PostgreSQL on Ubuntu
Once everything setup, you can access the postgres system account with the following command, where the -i flag tells sudo to run the shell specified by the target user’s password database entry as a login shell.
$ sudo -i -u postgres $ psql #to launch the postgres shell program postgres=#
To access the postgres shell directly, without first accessing the postgres user account, run the following command.
$ sudo -i -u postgres psql
You can quit/exit the postgres by typing the following command.
postgres=# \q
Create PostgreSQL Database Roles
Create a new user role using the following command.
postgres=# CREATE ROLE tecmint;
To create a role with a LOGIN attribute, use the following command (roles with the LOGIN attribute can be considered the same as a database users).
postgres=#CREATE ROLE tecmint LOGIN; OR postgres=#CREATE USER name; #assumes login function by default
A role can also be created with a password, this is useful if you configured the client authentication method to ask users to supply an encrypted password when connecting to the database.
postgres=#CREATE ROLE tecmint PASSWORD 'passwd_here'
List Existing PostgreSQL Database Roles
To list the existing user roles, use any of these commands.
postgres=# \du #shows actual users OR postgres=# SELECT rolname FROM pg_roles;
Drop a PostgreSQL Database Role
To drop any existing user role use the DROP ROLE command as shown.
postgres=# DROP ROLE tecmint;
Create a PostgreSQL Database
Once you have created a role with a particular name (for instance tecmint user), you can create a database (with the same name as the role) which will be managed by that role as shown.
postgres=# CREATE DATABASE tecmint;
Now to manage the database tecmint, access the postgres shell as the tecmint role, provide your password as follows.
$ sudo -i -u tecmint psql
Create a PostgreSQL Table
Creating tables is so easy, we will create a test table called authors, which stores information about TecMint.com authors, as shown.
tecmint=>CREATE TABLE authors (
code char(5) NOT NULL,
name varchar(40) NOT NULL,
city varchar(40) NOT NULL
joined_on date NOT NULL,
PRIMARY KEY (code)
);
After creating a table, try to populate it with some data, as follows.
tecmint=> INSERT INTO authors VALUES(1,'Ravi Saive','Mumbai','2012-08-15');
To view the data stored in a table, you can run a SELECT command.
tecmint=> SELECT * FROM authors;
List PostgreSQL Database Tables
You can list all tables in the current database with the following command.
tecmint=>\dt
Delete/Drop a PostgreSQL Table
To delete a table in the current database, use the DROP command.
tecmint=> DROP TABLE authors;
List All PostgreSQL Databases
To list all databases, use any of the following commands.
tecmint=>SELECT datname FROM pg_database; OR tecmint=>\list #shows a detailed description OR tecmint=>\l
Delete/Drop a PostgreSQL Database
If you want to delete a database, use the DROP command, for example.
tecmint=>DROP DATABASE tecmint;
Switch to Another PostgreSQL Database
You can also switch from one database to another easily using the following command.
tecmint=>\connect database_name
For more information, refer to the PostgreSQL 10.4 Documentation.
That’s it for now! In this article, we have explained how to install and use PostgreSQL database management system on Ubuntu 18.04. You can send us your queries or thoughts in the comments.
