PostgreSQL is an open source relational database. To work with the postgres database we should be connected to the database using a client. Client can be any piece of software that might be an API server that is put together, or a utility program designed to connect to a postgres database. Moreover, when we connect a client to a database we can write certain SQL queries. SQL is a kind of programming language for database operations such as retrieve, update, and delete. Additionally, SQL is one way that we can interact with the postgres database. The advantages of using PostgreSQL system is, it is easy to use, and it provides reliability and flexibility and is also freely available.
This blog will provide an insight on Initialization and an understanding of the basic settings of the PostgreSQL database
Before installing postgresql first we want to update and upgrade the APT which can be done using the following code:
sudo apt update
sudo apt upgrade
Next we can download and install the postgreSQL package using the code:
sudo apt-get install postgresql
We want to configure postgreSQL for our first time usage, for that set a root user and a root password to access postgreSQL.
Then first log into the postgres account with the following command.
sudo su - postgres
Further then open a postgreSQL prompt with :
Psql
If we are connected to PostgreSQL and we can see the details of the current connection by using the following command:
\conninfo
Now we can reset the root password for the user postgres, type the command:
ALTER USER postgres PASSWORD ‘new_password’;
Hence we need to enter into postgreSQL for that we can use the command:
psql -U postgres -h localhost
After that enter the password at the required section and then it will connect to the postgres shell. “\l” command is used to list all the databases.
In addition, we can exit the interactive PostgreSQL session by ‘\q’ and we can exit from the postgres user by using the command ‘exit’.
Create new role: We can create new role by using the command:
sudo -u postgres createuser --interactive
Create new database: We can create appropriate databases using the command createdb as described below:
sudo -u postgres createdb db_name
In addition, we can use PgAdmin as a Graphical User Interface for postgreSQL. The following commands are for installing pgAdmin:
Create repository configuration file:
You can create repository configuration file using the following command:
curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
Install pgAdmin4 using the code as mentioned below:
sudo apt install pgadmin4
To configure web mode, run the command:
sudo /usr/pgadmin4/bin/setup-web.sh
We can select pgAdmin from the ubuntu dashboard and it will be open in the browser. Further then we can access our database using pgAdmin 4. From the following figures we can see how to configure the database connection.
After saving the connection we can access the connection and will be able to see the list of databases and inside the databases we get all tables of the corresponding database. These are some of the Initialization and basic settings of the PostgreSQL database