PostgreSQL or Postgres is an open-source, free relational database management system with great extensibility and SQL compliance. It is also a commonly used Relational Database Management System (RDMS) for handling massive and complicated data, particularly in large companies.
It is a multi-platform database server that can be installed on Linux, Windows, and macOS, and it has extensive support for programming languages like Python, Java, Perl, Ruby, Go, C, and C#. The main features are:
- Easily expandable
- Supports time-series data types
- Efficient and cost-effective
- Scalable and supports concurrency
- Supports non-relational and relational data types
PostgreSQL has a wide range of features that can be used by administrators to create fault-tolerant systems and maintain data integrity, as well as by developers to create apps and end users to manage their data regardless of the size of the dataset. PostgreSQL is not only open source and free, but it is also very extensible. You may create new functions and specify your own data types.
PostgreSQL makes an effort to follow the SQL standard where doing so does not conflict with established functionality or potentially results in poor architectural choices. Most of the SQL standard's essential capabilities are supported, albeit occasionally with slightly different syntax or functionality. Ongoing progress in this direction can be anticipated.
PostgreSQL's primary goal is to manage a range of tasks, from simple technologies to online services or the data warehouse with multiple concurrent users. It supports both SQL for relational queries and JSON for non-relational queries. Many different businesses across many different industries, including financial services, information technology, government, and media and communications, employ PostgreSQL databases, which offer enterprise-class database solutions.
Here, you can learn how to install and connect to PostgreSQL on Ubuntu 22.04.
Some of the critical PostgreSQL configurations are:
Default PostgreSQL port 5432 The default user will be "postgres" Important Configuration files are located in: /etc/postgresql/postgresql.conf /etc/postgresql/pg_hba.conf Default database postgres Default data directory /var/lib/postgresql/
- Ubuntu 22.04 server that has been pre-configured.
- A user with sudo/root access.
- A basic firewall.
Install PostgreSQL on Ubuntu
Step 1: Update Your Linux System
Postgres packages are available in Ubuntu's default repositories; therefore, you can install them by using the APT packaging system. To update the package lists, connect to your Ubuntu 22.04 server and run the following command.
sudo apt update
Step 2: PostgreSQL Installation
You can now install the Postgres package using the following methods.
Method 1: From APT Repository
This is the most common method used and will install the latest version of PostgreSQL.
sudo apt-get -y install postgresql
Method 2: From Local Ubuntu Repository
The -contrib package adds some extra utilities and functionality.
sudo apt install postgresql postgresql-contrib -y
Now check the version of PostgreSQL installed by running the following command.
The output below indicates that you have installed PostgreSQL version 14.7.
You can also use the below command to find the version directly.
sudo -u postgres psql -c "SELECT VERSION();"
Step 3: Check Running Status of PostgreSQL
Once installed, you can check the status of the PostgreSQL daemon using the following commands.
sudo systemctl status postgresql
If the PostgreSQL service is up and running, the output would be:
PostgreSQL listens to TCP port 5342 by default, and this can be verified using the following command.
ss -pnltue | grep postgresql
Connect to PostgreSQL
Method 1: Via SQL Shell
You can now connect and interact with the PostgreSQL shell by creating a test user Postgres. You can log in to the PostgreSQL shell using the test user and access the shell using the following commands.
sudo su - postgres
To print the connection details, run the below command.
PostgreSQL servers come pre-configured with postgres, template0, and template1 databases. You may use the subsequent command to list the databases.
Postgres will act as the default database before creating any new database. The databases template0 and template1 are only skeleton databases. These must not be changed or removed.
Method 2: Via pgAdmin Tool
The pgAdmin tool helps to connect to the PostgreSQL database server via an in-built graphical user interface. Here, you are using pgadmin4 to connect to the database.
Step 1: Launch the Application
You need to install pgadmin4 on your system and open the application. The following window will be opened once you click on the pgAdmin application.
Step 2: Create a New Server
Navigate to CreateServer by right-clicking the server's node. Choose the server name on the next window options and click on the connection tab.
Step 3: Connection
You can now enter the hostname and password for the Postgres user and then click on the Save button.
Step 4: Server Details
Click on the server node and expand the new server. You can see the default database Postgres.
Step 5: Using the Query Tool
Click on Tools, and then click Query Tool.
Now enter the below command in the Query Editor and click on the Execute button.
The output will show the current version of PostgreSQL being used.
Creating a New PostgreSQL Database and User
You can use the below commands to create a new database and user in PostgreSQL.
CREATE DATABASE testdb; CREATE USER sapta WITH ENCRYPTED PASSWORD 'Sapta@123'; GRANT ALL PRIVILEGES ON DATABASE testdb TO sapta;
Password Protecting PostgreSQL Admin User
When you install PostgreSQL, a new user, Postgres, is created. This account will be assigned the default Postgres role, which does not require any password for authentication. This can pose security issues. Hence, to secure the user account and prevent unauthorized access, you can assign a password for this user account.
The following command can be used to assign a password for the PostgreSQL user.
ALTER USER postgres PASSWORD ‘postgres@123’;
Once done, exit the shell by using the below command.
Enabling Remote Connections to PostgreSQL Server
PostgreSQL, by default, only accepts connections from localhost or the system on which it was installed.
If you want to connect from remote locations, the remote connection feature needs to be enabled. For this you need to make changes to the PostgreSQL configuration file located at /etc/postgresql/<version>/main/ directory.
In this case, the file would be at /etc/postgresql/14/main/postgresql.conf.
Under the Connection and Authentication section, edit the listen address field to ‘*’, which will allow remote connections from all IPs.
Save the changes and exit.
Now, to allow IPv4 addresses, you can edit /etc/postgresql/14/main/pg_hba.conf.
Under IPv4 local connections, you can allow global connections or specific IP addresses.
Save the changes and restart the PostgreSQL service for the changes to be effective.
systemctl restart postgresql
If there is a firewall running, enable port 5342.
sudo ufw allow 5342/tcp sudo ufw reload
Now you can verify the remote connectivity using the following command.
psql -h 192.168.153.134 -U postgres
Here, 192.168.153.134 is the server IP.
How to Uninstall PostgreSQL Database on Ubuntu 22.04
You can un-install PostgreSQL using the following command.
sudo apt remove postgresql postgresql-contrib
You have now set up PostgreSQL on Ubuntu 22.04 server and learned how to connect to the database, create new databases, enable remote connections, etc. Consider adopting PostgreSQL if you want an effective and stable database management system. Liquid Web offers a dedicated server platform that you can use to try these installation steps to configure your own database server.
Our Sales and Support teams are available 24 hours by phone or e-mail to assist.