- Basic Setup of PostgreSQL in Linux Systems
- Create Usernames if Not Present Already in PostgreSQL Linux
DATABASEin PSQL Shell
PORT CONNECTTool to Access Database
- 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
- Install PostgreSQL from the terminal.
For Ubuntu, Debian or Mint:
sudo apt-get install postgresql
For Arch Linux:
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
CURLcommand here: URL.
- Once the
URLis obtained, proceed to issue the
CURLrequest to this
URLto download Repository.
curl -O [url]
- Then, install the RPM.
rpm -ivh [rpm file name]
You can later also download packages if you desire for this distribution.
- Connecting to the Database with User Postgres.
Let’s issue a command linking to our bin directory of the PostgreSQL installation.
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.
- Setting Password for Database
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.
Log out from your session using the
\q command, and then log in again to see the desired changes.
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.
sudo apt install VIM;
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
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
ESC and type
EXIT from the editor.
- 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
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.
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.
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];
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]
BIN_DIRECTORY is most of the time the
var/lib/postgresql/.. or some similar folder created upon installation.
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
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.