In this article We are going to perform How to install Postgresql on Ubuntu 22.04 LTS and how we can use postgresql, create database, how to create user in postgresql and we will see file location in postgresql database.
Table of Contents
What is PostgreSQL ?
PostgreSQL is an open-source, object-relational database management system (ORDBMS) that emphasizes extensibility and robustness. It is commonly referred to as “Postgres” and is known for its reliability, scalability, and extensive features.
PostgreSQL supports various platforms, including Windows, macOS, and Linux, and it can be used for a wide range of applications, from small-scale projects to large-scale enterprise systems. It is highly regarded for its ability to handle complex workloads and large amounts of data.
Key features of PostgreSQL include:
- Data integrity: PostgreSQL enforces the ACID (Atomicity, Consistency, Isolation, Durability) properties to ensure data integrity and reliability.
- Extensibility: It allows users to define custom data types, operators, and functions, making it highly extensible and adaptable to specific needs.
- SQL support: PostgreSQL supports the SQL standard and offers a rich set of SQL features, including complex queries, triggers, and views.
- Concurrency control: It provides concurrent access to the database, allowing multiple users to read and write data simultaneously while maintaining consistency.
- Replication and high availability: PostgreSQL supports various replication methods, allowing for data replication across multiple servers to enhance availability and fault tolerance.
- Full-text search: It includes advanced full-text search capabilities, enabling efficient searching and indexing of text data.
- Geospatial support: PostgreSQL has robust support for geospatial data types and provides functions for performing spatial operations and queries.
- Security: It offers various security features such as access control mechanisms, SSL encryption, and authentication methods to protect data.
PostgreSQL is widely used in both commercial and open-source projects and has a vibrant community that contributes to its development and maintenance. Its open-source nature allows users to modify and enhance its functionality, making it a flexible and powerful choice for database management.
How to Install Postgresql on Ubuntu 22.04 LTS ?
Step #1:Enable PostgreSQL Package Repository
Postgresql package is not available in the default package repository, so enable its official package repository using create the file repository configuration
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
Import the repository signing key using following command
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Update all system packages
sudo apt-get update
Step #2:To install postgresql on Ubuntu 22.04 LTS
Install latest version of postgresql
sudo apt-get -y install postgresql
if you want to install specific version the use ‘postgresql-12’.
After the installation completed the enable and start the postgresql service using following command
sudo systemctl enable postgresql
sudo systemctl start postgresql
also you check the status of postgresql using below command
sudo systemctl status postgresql
Output:
To check postgresql installed version
psql –-version
Output:
We have covered Install PostgreSQL on Ubuntu 22.04 LTS.
Step #3:To connect postgresql account using command line in Ubuntu
- Directly access postgresql account
sudo -u postgres psql
To exit from postgresql using
\q
Output:
2. Access using default user account
sudo -i -u postgres
To connect postgresql
psql
To exit from postgresql using
\q
Output:
Step #4:To set the password in PostgreSQL
By default, we can connect to the Postgresql server without using any password. In this step we will see how to set the password.
ALTER USER postgres PASSWORD ‘enter the password’;
To check password has been set successfully, for that exit from your current session and try again to log in.
\q
psql -h localhost -U postgres
Output:
The password has been set successfully.
Step #5:To create new database in PostgreSQL
For create new database use below query
create database db_name;
To create table inside the newly created database using below query
create table t_name(add data types);
To insert data in the table
insert into t_name (add data);
To see the the table or information in that table
select * from t_name;
Step #6:To create users and check the list of users in PostgreSQL
See the list of users using below command
\du
Now let’s create new user
create user devops with password ‘dev@123’;
now again you can see list of users
\du
Output:
Step #7: file location in PostgreSQL
If you want to find configuration file information using
show config_file;
authentication file location
show hba_file;
to find large or data directory
show data_directory;
to find log directory
show log_directory;
Conclusion:
In this article we have covered how to install Postgresql on 22.04 LTS and how we can use postgresql, create database, how to create user and see file location.
Related Articles:
How to Install Apache Kafka on Ubuntu 22.04 LTS
Reference: