Skip to main content

Installing Postgresql on Linux

Before we begin, it's important to note that this tutorial assumes you have a Linux system up and running. If you're not there yet, take a step back and get your Linux environment set up.

Step 1: Update Your System​

First thing's first, we need to make sure your system is up-to-date. Open up a terminal and type the following commands:

sudo apt-get update
sudo apt-get upgrade

These commands will update the package lists for upgrades and new package installations.

Step 2: Install PostgreSQL​

Now let's install PostgreSQL. PostgreSQL is available in Ubuntu's default repositories, so we can install it using the apt package system.

sudo apt-get install postgresql postgresql-contrib

The postgresql-contrib package includes additional utilities and functionality for PostgreSQL.

Step 3: Using PostgreSQL Roles and Databases​

By default, PostgreSQL uses a concept called "roles" to handle authentication and authorization. After installation, PostgreSQL creates a 'postgres' superuser role and a 'postgres' database.

Switch over to the 'postgres' account on your server by typing:

sudo -i -u postgres

You can access a PostgreSQL prompt by typing:

psql

Exit out of the PostgreSQL prompt by typing:

\q

Step 4: Create a New Role​

To create a new role, you can type:

sudo -u postgres createuser --interactive

The script will prompt you with some questions. You can specify the name of the role and whether it has superuser permissions.

Step 5: Create a New Database​

You can create a new database using the createdb command like this:

sudo -u postgres createdb test

In this example, 'test' is the name of the new database.

Step 6: Open a Postgres Prompt with the New Role​

To interact with the new database, you can open up a PostgreSQL prompt with the new role by typing:

sudo -u test psql

At this point, you are free to interact with your PostgreSQL instance. You can create tables, insert data, and run queries.

Step 7: Enable Remote Access to PostgreSQL server​

By default, the PostgreSQL server only allows connections from localhost. If you want to enable remote access, you need to modify the pg_hba.conf and postgresql.conf files.

Find the postgresql.conf file and edit the line that specifies listen_addresses:

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

Change this line to the following:

listen_addresses = '*'

Then, find the pg_hba.conf file and add the following line to the bottom:

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

Add this line to the file:

host    all             all             0.0.0.0/0               md5

Finally, restart the PostgreSQL service for the changes to take effect:

sudo service postgresql restart

And that's it! You have successfully installed PostgreSQL on your Linux server and are now able to begin exploring this powerful database system. Happy querying!