How to Install PostgreSQL Server on Ubuntu 22.04.3 LTS

Follow this comprehensive guide to install and set up PostgreSQL on Ubuntu 22.04.3 LTS.

Step 1: Update the Package List

First, ensure your package list is up-to-date:

sudo apt update

Step 2: Install PostgreSQL

Install PostgreSQL and the associated packages using the following command:

sudo apt install postgresql postgresql-contrib

This will install the PostgreSQL server and some additional tools and extensions.

Step 3: Start and Enable PostgreSQL Service

After the installation, PostgreSQL service should start automatically. You can check its status with:

sudo systemctl status postgresql

If it is not running, start the PostgreSQL service with:

sudo systemctl start postgresql

To ensure PostgreSQL starts on boot, enable the service with:

sudo systemctl enable postgresql

Step 4: Switch to the PostgreSQL User

PostgreSQL creates a default user called postgres. Switch to this user to start using PostgreSQL:

sudo -i -u postgres

Step 5: Access the PostgreSQL Command Line

Now you can access the PostgreSQL command line interface (psql) by typing:

psql

You should see the PostgreSQL prompt like this:

postgres=#

Step 6: Create a New Database User (Optional)

If you want to create a new database user, you can do so with the following SQL commands inside the psql interface:

CREATE USER myuser WITH PASSWORD 'mypassword';

Step 7: Create a New Database (Optional)

Create a new database and grant all privileges on it to your new user:

CREATE DATABASE mydatabase;
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Step 8: Exit the psql Interface

To exit the psql interface, type:

\q

Step 9: Adjusting Firewall (if applicable)

If you are planning to access your PostgreSQL server from another machine, you might need to adjust your firewall settings to allow connections on PostgreSQL's default port (5432). For example, to allow access from any IP address:

sudo ufw allow 5432/tcp

Step 10: Configure PostgreSQL to Allow Remote Connections (Optional)

To allow remote connections, you need to modify the PostgreSQL configuration files:

Edit postgresql.conf to listen on all IP addresses:

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

Find the line with listen_addresses and change it to:

listen_addresses = '*'

Edit pg_hba.conf to allow connections from any IP address:

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

Add the following line at the end of the file:

host    all             all             0.0.0.0/0               md5

Restart PostgreSQL to apply the changes:

sudo systemctl restart postgresql

Now, your PostgreSQL server should be ready to use, both locally and remotely.