Gremoire

How to Install PostgreSQL on Ubuntu ​

Installing PostgreSQL on Ubuntu is a straightforward process. Follow these steps to get PostgreSQL up and running on your Ubuntu server.

1. Update Your Package Index ​

Before installing PostgreSQL, ensure that your package index is updated:

bash
sudo apt-get update

2. Install PostgreSQL ​

Install PostgreSQL using the package manager:

bash
sudo apt-get install postgresql postgresql-contrib
  • postgresql: The core PostgreSQL database server.
  • postgresql-contrib: Additional utilities and extensions that are useful for PostgreSQL.

3. Verify the Installation ​

After installation, you can verify that PostgreSQL is running by checking its status:

bash
sudo systemctl status postgresql

You should see an output indicating that PostgreSQL is active and running.

4. Accessing the PostgreSQL Command Line ​

To start using PostgreSQL, switch to the postgres user and access the PostgreSQL command line interface:

bash
sudo -i -u postgres
psql

Once inside the psql prompt, you can start executing SQL commands.

5. Creating a New PostgreSQL Role and Database ​

  • Create a new role (user):
sql
CREATE USER your_username WITH PASSWORD 'your_password';
  • Create a new database:
sql
CREATE DATABASE your_database;
  • Grant privileges to the user on the database:
sql
GRANT ALL PRIVILEGES ON DATABASE your_database TO your_username;
  • Exit the psql prompt by typing:
sql
\q

6. Configuring Remote Access (Optional) ​

By default, PostgreSQL only allows connections from the localhost. To enable remote access:

  1. Edit the PostgreSQL configuration file:
bash
sudo nano /etc/postgresql/14/main/postgresql.conf

(Replace 14 with your PostgreSQL version if different)

  1. Find the listen_addresses line and change it to:
bash
listen_addresses = '*'
  1. Allow remote connections:

Edit the pg_hba.conf file:

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

Add the following line at the end of the file to allow connections from any IP address:

bash
host    all             all             0.0.0.0/0               md5
  1. Restart PostgreSQL to apply the changes:
bash
sudo systemctl restart postgresql

7. Connecting to PostgreSQL ​

You can now connect to PostgreSQL from your application or using a tool like psql:

bash
psql -U your_username -d your_database -h localhost -W

Replace localhost with the IP address of the PostgreSQL server if connecting remotely.

By following these steps, you should have PostgreSQL installed and running on your Ubuntu server.