How to install PostgresDB on Ubuntu

"Lets Learn How to install PostgresDB on Ubuntu"

PostgreSQL is a complex open-source RDBMS with higher reliability than competitive products, as well as with possibility of extension and compliance with various standards. It supports most of the standard data types, a variety of transactions, and multi version concurrency control and hence is suitable for both simple applications and complex business applications. In general, PostgreSQL is an excellent option irrespective of whether it is used for developing a web application or dealing with a large database.

As you will learn here, let’s follow through the entire steps of installing PostgreSQL to Ubuntu, configuring it for local and remote connection, users, databases and more to make the system ready for production database server.

Updating Package Lists

When running Ubuntu it is wise to update the package lists so that the latest version of any software installed from the repositories is used. Run the following command to update:Run the following command to update:

sudo apt update

This command retrieves current list of packages and it enables one to install the most updated versions of the packages.

Installing PostgreSQL and Required Packages

PostgreSQL is present in the Ubuntu default repositories which therefore makes it easy to install. Use the following command to install PostgreSQL along with the postgresql-contrib package, which provides additional utilities and tools for managing the database:Use the following command to install PostgreSQL along with the postgresql-contrib package, which provides additional utilities and tools for managing the database:

sudo apt install postgresql postgresql-contrib

This command installs the server and additional tools, including for example pg_dump (used for backup purposes).

Verifying the Installation

After installation, verify that the PostgreSQL service is running:

sudo systemctl status postgresql

The output should show that PostgreSQL is running by showing active status. If it isn't running, you can start it manually with the command:If it isn't running, you can start it manually with the command:

sudo systemctl start postgr

You may also want to enable PostgreSQL to start automatically on boot:

sudo systemctl enable postgresql

Accessing the PostgreSQL Shell

Out of the box PostgreSQL sets up a system user called postgres who possesses the role of postgres for managing the PostgreSQL server. You have to log-in to this user in order to work with the PostgreSQL shell (psql).

Switch to the postgres user and enter the PostgreSQL shell:

sudo -i -u postgres
psql

When you are in the psql shell, you can then begin to type SQL commands and manipulate the database.

Basic Database Administration

The PostgreSQL allows the provision of creating roles or users and databases. Here's how to get started:

Creating a PostgreSQL User (Role)

To create a new user, you need to issue the CREATE USER SQL command, that allows the creation of new users and their relevant data. For instance, if you would like to set up a user called dbuser their password would be; password123

CREATE USER dbuser WITH PASSWORD 'password123';

However, the user created with this command has no privilegies to create databases as well as to perform other administrative operations. If one needs to make a user an administrator for the certain project, one can add the SUPERUSER attribute, though it is suggested not to do that for the security reasons or provide the user with the certain privileges, for instance, the ability to create databases.

To allow a user to create databases, modify the user with the following command:

ALTER USER dbuser CREATEDB

Creating a Database

When you have a user, then you have the possibilities to create a database that this user administers. The following command creates a new database called mydatabase:

CREATE DATABASE mydatabase;

Next, grant privileges to the user so they can fully manage the database:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO dbuser;

This ensures that the user dbuser has complete control over the mydatabase database.

Exiting the PostgreSQL Shell

When you are done managing the PostgreSQL databases and users, you can exit the PostgreSQL shell by typing:

\q

Managing PostgreSQL Users and Roles

PostgreSQL has a robust role-based access control system. Unlike some databases, PostgreSQL does not differentiate between users and roles; both are collectively referred to as "roles."

Creating Roles: You can create roles that don’t have login privileges but can be granted to other users or roles. This allows you to define roles that control permissions across various users.
```
CREATE ROLE read_access;
```
Granting Roles: Assign a role to a user:
```
GRANT read_access TO dbuser;
```

Roles can be used to manage complex permission structures for your databases.

Configuring PostgreSQL for Remote Access

By default, PostgreSQL is set to only accept connections that originate from the local machine (localhost). If you need remote access to your database—for example, if you are hosting PostgreSQL on a server and want to connect from a different machine—you'll need to modify the configuration files.

Modifying postgresql.conf

First, open the postgresql.conf file to allow PostgreSQL to listen for connections on IP addresses other than localhost:

sudo nano /etc/postgresql/12/main/postgresql.conf

In this file, look for the listen_addresses setting. By default, it is set to localhost:

#listen_addresses = 'localhost'

Uncomment the line and change it to:

listen_addresses = '*'

This allows PostgreSQL to accept connections from any IP address.

Modifying pg_hba.conf

Next, you need to tell PostgreSQL which IP addresses are allowed to connect by modifying the pg_hba.conf file. Open the file:

sudo nano /etc/postgresql/12/main/pg_hba.conf

At the end of the file, add the following line:

host    all             all             0.0.0.0/0               md

This line allows any IP address to connect, using password authentication (md5). If you want to restrict access to specific IP addresses, replace 0.0.0.0/0 with a specific IP address or range.

Restarting PostgreSQL

After making these changes, restart the PostgreSQL service to apply them:

sudo systemctl restart postgresq

Firewall Configuration (Optional)

If your server has a firewall (e.g., UFW on Ubuntu), you’ll need to open the PostgreSQL port (default is 5432) to allow external connections. Run the following command to allow traffic on port 5432:

sudo ufw allow 5432/tcp

Ensure that you restrict access to only trusted IP addresses to avoid exposing your database to the entire internet.

Connecting to PostgreSQL Remotely

Now that PostgreSQL is configured to accept remote connections, you can connect to your database from another machine using the psql command or a database GUI client like pgAdmin or DBeaver.

To connect from the terminal, use the following command:

psql -h your_server_ip -U dbuser -d mydatabase

Here

your_server_ip is the IP address of your server running PostgreSQL.
dbuser is the username you created.
mydatabase is the database you want to connect to.

Backing Up and Restoring Databases

PostgreSQL comes with built-in tools for backing up and restoring databases.

Backing Up a Database

To back up a database, use the pg_dump command. This creates a file that contains the SQL commands to recreate the database and its objects:

pg_dump mydatabase > mydatabase_backup.sql

Restoring a Database

To restore a database from a backup file, use the psql command:

psql -U dbuser -d mydatabase -f mydatabase_backup.sql

This command will restore the backup into the specified database.

Optimizing PostgreSQL for Production

For any application that is installed in the production environment, it is advisable to think about making PostgreSQL to perform even better. Here are some tips for improving PostgreSQL’s performance:Here are some tips for improving PostgreSQL’s performance:

Tuning Memory Settings: Pg is a superuser of Postgres obtained by typing postgres=# alter system set shared_buffers’ to ‘xx, where xx is the number obtained from effective cache size work_mem is also set through the same command by typing postgres=# alter system set work_mem’ to ‘xx; effective cache size is also set through this command postgres=# alter system set `effective_cache_size’ to ‘xx; checkpoint &cont Rol {::conf} used to Tunning in memory.
Indexing: Ensure that the optimum columns used in the WHERE clause are indexed so as to ensure quick search on the database.
Vacuuming: PostgreSQL, however, frees space using vacuuming and you can manually trigger ‘VACUUM’ to improve a database’s performance.