How to Configure PostgreSQL in Linux

1. Basic Setup of PostgreSQL in Linux Systems
2. Create Usernames if Not Present Already in PostgreSQL Linux
3. Create ROLE and DATABASE in PSQL Shell
4. Use the PORT CONNECT Tool to Access Database
5. Different Methods to Configure PostgreSQL in Linux

This tutorial will teach us how to set up PostgreSQL in Linux. Our detailed setup will focus on Ubuntu or any other Linux distribution such as Fedora, Bodhi, PepperMint, BackBox etc.

Basic Setup of PostgreSQL in Linux Systems

1. Install PostgreSQL from the terminal.

For Ubuntu, Debian or Mint:

Command:

sudo apt-get install postgresql

For Arch Linux:

Command:

sudo pacman -S postgresql

You have to do the following in cases of YUM installations, such as Fedora, Red Hat, etc.

• Get the PostgreSQL version URL for CURL command here: URL.
• Once the URL is obtained, proceed to issue the CURL request to this URL to download Repository.

Command:

curl -O [url]

• Then, install the RPM.

Command:

rpm -ivh [rpm file name]

You can later also download packages if you desire for this distribution.

2. Connecting to the Database with User Postgres.

Let’s issue a command linking to our bin directory of the PostgreSQL installation.

Command:

cd BINPATH (most probably: /var/lib/postgresql/12/main)

Once inside the path, let’s run the SUDO -U command to connect to our database. Run the query as follows:

sudo -u postgres psql template1

TEMPLATE1 is the default database, but you can use any of the defined in the place of TEMPLATE1 in the query above. Once connected, you will have now entered the specified database.

3. Setting Password for Database POSTGRES.

Once inside the session, you will notice something like this:

postgres=# or template1=#

Meaning that you are now logged into the database session. Let’s set up the password for our root database POSTGRES by using the ALTER statement as follows:

ALTER USER postgres with encrypted password '[YOUR_PASSWORD]';

Once the password has been changed, the following output will be returned.

Output:

ALTER ROLE

Log out from your session using the \q command, and then log in again to see the desired changes.

4. Modifying PG_HBA.CONF for Connections

To modify any file inside Linux, you use VIM, a console editor to modify files. If you don’t have VIM installed, you can download and run the command below.

Command:

sudo apt install VIM;

With VIM, go ahead and edit the PG_HBA.CONF file in the directory. The MAIN directory will be prevented from being accessed by using the CD command.

Command:

sudo vim \var\lib\postgresql\12\main\pg_hba.conf

In case you see straight lines and no data inside, go ahead and append the following line to the file:

local all postgres md5

Then press ESC and type :wqa to EXIT from the editor.

5. Making a Database and Logging In as Postgres

To create a new database, use the following command:

sudo -u postgres createdb NEW_DB

To check it, use the command below to log in with a password.

psql - U postgres -W

Sometimes, even after entering the correct password for POSTGRES, it may return the following error.

psql: error: FATAL: Peer authentication failed for user 'postgres'

This is caused entirely due to not changing the PEER option for connections in PG_HBA.CONF.

That is all needed for running a PostgreSQL session in Linux. There are other ways and methods as well discussed below.

Create Usernames if Not Present Already in PostgreSQL Linux

In some versions, we CREATE a user by the following command.

Command:

sudo -u template1 createuser --superuser [user_name]

Once the user is created, we can log in simply without any password first. Use the ALTER statement to set the password later.

POSTGRES user works as the root user for the PostgreSQL installation on a system. It has to write access to underlying files and can be used for much higher security functions.

Create ROLE and DATABASE in PSQL Shell

Once logged in to the PSQL shell, you can use the following queries to CREATE a new database or user.

CREATE ROLE [user_name] LOGIN PASSWORD 'abc123';
CREATE DATABASE [database name] WITH OWNER = [user_name];

Use the PORT CONNECT Tool to Access Database

Another method that we can utilize to connect to a PostgreSQL session as a user on a different system is to use the following command.

$ psql -h [host_name] -d [database_name] -U [_username] -p [port_number]

This helps connect different users to our system. We can use this command to configure our user to run on a PostgreSQL session hosted on a server and then issue queries and work in that database.

You may need to specify the DATABASE to connect to along with specifying the USER you want to connect as:

psql -U [user_name] -d [db_name]

Different Methods to Configure PostgreSQL in Linux

Configuration is supposed to be easy and efficient. Sometimes, while trying to connect as a user to PSQL, we might need to always mention the PATH first, then form the connection.

This can be made simple with the use of the following:

export PATH=$PATH: [our_bin_directory_path]

This BIN_DIRECTORY is most of the time the var/lib/postgresql/.. or some similar folder created upon installation.

Once the PATH variable has been set up, it’s time to work on initializing the SERVICE for it to work. Most of the time, this is automatic, but to be on the safe side, we can go ahead and do something as follows:

//initialization
sudo service postgresql-[version_number] initdb

//starting
sudo service postgresql-[version_number] start

On FEDORA or YUM systems, the commands may be issued as follows:

sudo systemctl start postgresql-[version_number]
sudo systemctl enable postgresql-[version_number]

You can even install PGADMIN on Fedora or other systems to make your life easier.