Table of Contents
Introduction
PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley.
PostgreSQL Installation
To install PostgreSQL on your Ubuntu system, follow these steps.
1. Update system packages
First, ensure that your system packages are up-to-date. You can do this by running the following commands
sudo apt update
Using the following command to upgrade the installed packages
sudo apt -y upgrade
2. Install PostgreSQL on Ubuntu
You can install PostgreSQL on Ubuntu 20.04 without configuring the
Ubuntu repository. Use the following command to install PostgreSQL on Ubuntu
sudo apt install postgresql postgresql-client
This command installs both the PostgreSQL server and the client. During installation, you may be prompted to confirm the installation by pressing ‘y’.
After completing the installation of PostgreSQL, you will start, stop, and enable the PostgreSQL services using the following command
Check the postgres status after installation by using following command
sudo systemctl status postgresql.service
If the postgres service inactive start the service
sudo systemctl start postgresql.service
Following command enables PostgreSQL to start automatically at boot.
sudo systemctl enable postgresql.service
Setup PostgreSQL server
1. Set PostgreSQL user password
You can change or create the user password for PostgreSQL. Using the following command, you can change the default user password for PostgreSQL.
sudo passwd postgres
2. Access PostgreSQL shell
You can log in to PostgreSQL as a user to access the databases and working shell using the following command
3. Create a database and user roles
You can create new databases and users using the interactive Postgresql shell as follows.
psql -c "alter user postgres with password 'mypass123'"
This command changes the password for the postgres user to mypass123. The -c flag allows you to run a SQL command directly from the command line without entering the interactive psql shell.
Now create a new user and database using the following command:
createuser testuser
createdb testdb -O testuser
Change the role and password using the following command. The \q is used to quite from the shell.
psql -c "alter user testuser with password 'testpass123'"
Use the following command to list databases:
psql -l
PostgreSQL by default listens at the local interface which is 127.0.0.1. But, you can open its remote access by doing some changes in the configuration file. To access the configuration file of PostgreSQL, you will use the following command.
4. Remote connection[Optional]
To allow PostgreSQL to accept connections from remote clients, you'll need to modify the PostgreSQL configuration file. Open the configuration file using the following command.
sudo nano /etc/postgresql/12/main/postgresql.conf
Inside the file, find the line that starts with listen_addresses and change it to:
listen_addresses = '*'
listen_addresses = '*': This setting allows PostgreSQL to listen for connections on all network interfaces, not just 127.0.0.1. The * wildcard represents all IP addresses.
After making this change, save the file using ctrl+s and ctrl+x. Restart the PostgreSQL service for the changes to take effect.
sudo service postgresql restart
This command restarts the PostgreSQL service, applying the new configuration settings.
Conclusion
This tutorial has guided you through the steps to set up PostgreSQL on an Ubuntu server. PostgreSQL is a powerful and complex database management system with a rich set of features, including advanced space management for B-tree indexes, concurrent index rebuilding, and covering index creation.
While this guide covers the basic installation and configuration of PostgreSQL, there are many more advanced features and administrative tasks you can explore to fully utilise PostgreSQL's capabilities. We hope you found this guide useful in setting up your PostgreSQL environment
TRENDING OFFER
