Relational databases play a crucial role in various applications and websites, providing a structured framework for data storage. These databases facilitate seamless data querying and accessibility, offering significant advantages.
When seeking an alternative to MySQL, PostgreSQL stands out as a popular choice utilized by numerous applications. As an open-source database, PostgreSQL boasts compatibility with a wide range of operating systems, including Unix-based systems.
This article guides you through the process of installing PostgreSQL on Ubuntu 18.04, leveraging Ubuntu's default repository with PostgreSQL packages. While the installation itself is straightforward, post-installation tasks involve creating an appropriate database and configuring user roles to ensure optimal functionality.
Steps to Install PostgreSQL on Ubuntu
Before commencing with these steps, ensure SSH access to your VPS.
Begin by updating the local package index using the apt command:
sudo apt update
Next, install PostgreSQL along with the contrib package, providing additional features:
sudo apt install postgresql postgresql-contrib
This command initiates the installation of PostgreSQL. Once installed, proceed with basic configuration.
Steps to Verify PostgreSQL on Ubuntu
Upon completing the PostgreSQL installation on Ubuntu, services will start automatically. Confirm the installation by connecting to the PostgreSQL database using psql, a command-line utility for interacting with the PostgreSQL server. To display the server version, use:
sudo -u postgres psql -c "SELECT version();"
To log in to PostgreSQL as a "postgres" user using psql, utilize the following command:
sudo su – postgres
To use PostgreSQL use this command:
psql
This command sequence provides access to the PostgreSQL instance. To exit, use:
\q
Role and User Creation
To create a new role, log in to the "postgres" account and access the Postgres console. Once in the console, create a new role with the following command:
createrole --interactive
You'll be prompted to name the role and specify if it should have superuser permissions.
Similarly, you can create a new user with the following command:
createuser –interactive
Alternatively, from the normal command prompt, use:
sudo -u postgres createuser –interactive
You'll be prompted to enter a username. The createuser command offers various options, which can be explored by using the createuser command itself.
This process helps you create a new user. Next, you'll learn about creating a database.
Database Creation
In PostgreSQL, it is conventionally expected that the role name aligns with the database name. If you created a user named "testPostgres," the role will automatically attempt to access a database named "testPostgres."
To create a database directly from the Postgres console, use the following command:
createdb testPostgres
Alternatively, without using the Postgres SQL, you can employ the command as displayed below:
sudo -u postgres created testPostgres
Steps to Validate the New Role
Begin by creating a Linux user with the same name as the Postgres database and role. This can be accomplished using a non-root account with sudo access. Create the new user in Linux with a command similar to the one shown below:
sudo adduser testPostgres
Replace "testPostgres" with the desired name. After creating the account, switch to it and connect to the database using the following command:
sudo -u testPostgres psql
To switch to another database, specify the database name with the command:
psql -d sampleUser
After logging in, check the current database connection with the command:
\conninfo
Postgres Basic DML Operation
PostgreSQL uses a syntax similar to other databases for fundamental operations such as table creation, deletion, and updates. Create a new table and describe its definition with the following command:
\d
To view the table without a sequence, use the command:
\dt
Wrapping Up
This tutorial has provided you with a foundational understanding of PostgreSQL. You've learned how to install PostgreSQL on Ubuntu 18.04 and establish the basic setup for creating users, roles, and databases. Use this knowledge to kickstart your journey with PostgreSQL on Ubuntu.