PostgreSQL, often referred to as Postgres is a powerful, open-source relational database management system (RDBMS) that can handle complex queries and large volumes of data. Its versatility allows it to handle a wide range of workloads, from small-scale applications to large, enterprise-level systems. It is widely used for web development, data analysis, and business applications. In this Article, explores how to install PostgreSQL on Ubuntu, a popular Linux distribution, and how to perform some basic operations with it.
Uses of PostgreSQL:
PostgreSQL, an open-source relational database system, is extensively utilized for its robustness and adaptability. It ensures data accuracy and reliability, making it a go-to choice for applications requiring steadfast performance. With scalability, support for custom data types, and advanced concurrency control, PostgreSQL caters to a broad spectrum of project needs. Its commitment to SQL standards and collaborative open-source community further solidify its position as a reliable database solution for both small-scale projects and enterprise
Data Integrity and Reliability:
- PostgreSQL ensures data integrity through its support for ACID (Atomicity, Consistency, Isolation, Durability) properties. This makes it suitable for applications where data accuracy and reliability are critical.
Scalability:
- With support for both horizontal and vertical scaling, PostgreSQL can efficiently handle growing datasets and increased workloads. This scalability is essential for applications experiencing rapid expansion.
Extensibility and Customization:
- PostgreSQL allows users to define their data types, operators, and functions, providing a high degree of customization. This extensibility makes it adaptable to specific project requirements.
Advanced Data Types:
- Apart from standard data types, PostgreSQL offers advanced types like arrays, hstore (key-value pairs), JSON, and more. This makes it well-suited for projects requiring diverse data representations.
Concurrency Control:
- PostgreSQL employs advanced concurrency control mechanisms, allowing multiple users to access and modify data simultaneously without compromising consistency.
How to Install PostgreSQL on Ubuntu
There are two main ways to install PostgreSQL on Ubuntu: from the official APT repository or from the local Ubuntu repository. The APT repository contains the latest version of PostgreSQL, while the local Ubuntu repository may have an older version. We recommend using the APT repository for the most up-to-date features and security patches.
Installation From APT Repository
To install PostgreSQL from the APT repository, you need to add the PostgreSQL repository to your system’s sources list. To do this, open a terminal and run the following commands:
sudo apt-get install wget ca-certificates
wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –
sudo sh -c ‘echo “deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main” >> /etc/apt/sources.list.d/pgdg.list’
Next, update your system’s package list and install PostgreSQL:
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib
This will install PostgreSQL along with some additional modules and utilities.
Installation Using Local Ubuntu Repository
Alternatively, you can install PostgreSQL from the local Ubuntu repository using the following command:
sudo apt-get install postgresql
However, this may not install the latest version of PostgreSQL, so you may miss out on some features and bug fixes.
How to Check the PostgreSQL Status
After installing PostgreSQL, you can check its status using the following command:
sudo systemctl status PostgreSQL
This will show you whether PostgreSQL is running or not, and some other information such as its process ID and memory usage.
Connect to PostgreSQL
By default, PostgreSQL creates a user account called postgres that has full access to the database system. To connect to PostgreSQL as this user, you need to switch to the postgres system user and then use the psql command-line tool. To do this, run the following commands:
sudo su – postgres
psql
This will open the psql interactive shell, where you can run SQL commands and queries. You should see something like this:
psql (13.1 (Ubuntu 13.1-1.pgdg20.04+1))
Type “help” for help.
postgres=#
To exit psql, type \q and press Enter.
Change User Password
To change the password of the postgres user, you can use the \password command in psql. For example:
postgres=# \password
Enter new password:
Enter it again:
postgres=#
Alternatively, you can use the ALTER USER SQL statement. For example:
postgres=# ALTER USER postgres WITH PASSWORD ‘new_password’;
ALTER ROLE
postgres=#
Create a User on PostgreSQL
To create a new user on PostgreSQL, you can use the CREATE USER SQL statement. For example, to create a user named alice with password ‘secret’, run the following command in psql:
postgres=# CREATE USER alice WITH PASSWORD ‘secret’;
CREATE ROLE
postgres=#
You can also specify other options for the user, such as whether they can create databases or roles, or whether they are superusers. For more details, see the CREATE USER documentation.
Create a Database on PostgreSQL
To create a new database on PostgreSQL, you can use the CREATE DATABASE SQL statement. For example, to create a database named testdb owned by alice, run the following command in psql:
postgres=# CREATE DATABASE testdb OWNER alice;
CREATE DATABASE
postgres=#
You can also specify other options for the database, such as its encoding, collation, or tablespace. For more details, see the CREATE DATABASE documentation.
Configure the PostgreSQL Server
To configure the PostgreSQL server, you need to edit its configuration files, which are located in /etc/postgresql/<version>/main/. The most important files are:
– postgresql.conf: This file contains various settings for the server, such as its port number, logging options, memory usage, and performance tuning.
– pg_hba.conf: This file controls how clients can connect to the server, based on their IP address, username, password, and database name.
– pg_ident.conf: This file maps system users to database users for authentication purposes.
After editing any of these files, you need to restart the PostgreSQL service for the changes to take effect. To do this, run the following command:
sudo systemctl restart postgresql
Set up a Remote Database Connection
If you want to access your PostgreSQL database from another machine, you need to configure some settings on the server side. First, you need to edit the pg_hba.conf file, which controls the client authentication. You can find this file in the /etc/postgresql/13/main directory (the 13 may vary depending on your PostgreSQL version). To edit this file, run:
- sudo nano /etc/postgresql/13/main/pg_hba.conf
You need to add a line like this at the end of the file:
- host dbname username ip_address/32 md5
This line allows the user username to connect to the database dbname from the IP address ip_address using md5 password encryption. You can use a CIDR notation to specify a range of IP addresses, such as 192.168.1.0/24. You can also use * to match any database, user, or IP address.
After saving and closing the file, you need to reload the PostgreSQL service for the changes to take effect:
- sudo systemctl reload postgresql
You also need to edit the postgresql.conf file, which controls the server parameters. You can find this file in the same directory as pg_hba.conf. To edit this file, run:
- sudo nano /etc/postgresql/13/main/postgresql.conf
You need to find and uncomment (or add) the following line:
- listen_addresses = ‘*’
This line tells PostgreSQL to listen on all network interfaces. You can also specify a list of IP addresses separated by commas, such as ‘localhost,192.168.1.100’.
After saving and closing the file, you need to restart the PostgreSQL service for the changes to take effect:
- sudo systemctl restart postgresql
You can now connect to your PostgreSQL database from another machine using a tool like psql or pgAdmin.
Install pgAdmin
pgAdmin is a web-based graphical tool for managing PostgreSQL databases. It allows you to create, modify, and delete databases, tables, views, functions, triggers, and more. To install pgAdmin on Ubuntu, you need to run the following commands in a terminal:
- Add the pgAdmin repository: sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
- Add the pgAdmin repository: sudo sh -c ‘echo “deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main” > /etc/apt/sources.list.d/pgadmin4.list && apt update’
- Install pgAdmin: sudo apt install pgadmin4
You can then access pgAdmin from your web browser by visiting http://localhost/pgadmin4 or http://ip_address/pgadmin4. You need to create an account and log in with your email and password. You can then add your PostgreSQL server by clicking on Add New Server in the dashboard. You need to provide the server name, host name or IP address, port number (usually 5432), username, and password of your PostgreSQL server.
If you encounter any problems while installing or configuring PostgreSQL on Ubuntu, you can check the following sources for help:
- The official PostgreSQL documentation: https://www.postgresql.org/docs/
- The official pgAdmin documentation: https://www.pgadmin.org/docs/
- The Ubuntu community help wiki: https://help.ubuntu.com/community/PostgreSQL
- The Stack Overflow website: https://stackoverflow.com/questions/tagged/postgresql