Sunday, December 2, 2018

Beginners Guide To Install Postgres Ubuntu.

Nowadays most applications use PostgreSQL as their database than Mysql. PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions. Therefore if you are planning to have a career in Software Engineering knowledge of PostgreSQL comes in handy. So today lets discuss Install Postgres Ubuntu. Reason for this post is there are few thing beginners should know when he starts using Postgres.
The easiest way to install Postgres in Ubuntu is run below command.
sudo apt install postgresql
This will install the latest PostgreSQL version which is currently postgresql-10.
But sometimes you might need an older version which compatible with your application.
For such an event you can use below procedure to achieve that.
For the tutorial, let’s install postgresql-9.6 for that run below commands in terminal.
sudo add-apt-repository "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main"
 
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
 
sudo apt-get update
 
sudo apt-get install postgresql-9.6


Now you have installed Postgres in Ubuntu and you can confirm the version using below command.
psql -V

If you can see a version number you installed, then Postgres successfully installed.
Now, let’s start Postgres using below command.

sudo service postgresql start


Now your server should be running. You can check that using below command.
sudo service postgresql status

Now how to access the psql using terminal?
When Postgres is being installed, it creates a user called postgres.This user is created as Ubuntu System user not in Postgres as Mysql would do. You can see it using the below command.
cat /etc/passwd

In end of file you can see a line starts with postgres. The x after postgres means there is no password setted for it.
Reason for bring this user matter is You need to log into psql using that user. this user is not activated and you should not activate is using passwd command. Otherwise it will leave security vulnerbility if you used weak password like postgres
Let's see how to use psql without activating postgres user.

Type below command to become postgres user without password. (For this you need root privilages)

sudo -i -u postgres


now type psql to enter Postgres terminal. After that type below command to change default user password.

\password postgres


Please remember that password. because it is needed when you are going to connect the Postgres through your application. If you want to exit terminal, then type \q and enter. Now you are in terminal as postgres user. use exit to log out. If you want to access psql terminal use below commad.
sudo -u postgres psql

If you don't want to use psql terminal you can use GUI client call pgamin
(https://www.pgadmin.org/download/) Now you you know basics of Install Postgres Ubuntu and get development start up :D


Export Postgres Database In Ubuntu.

As a Postgres beginner, I was confused when I went export Postgres database in Ubuntu. Because If you installed Postgres as https://passioned91.blogspot.com/2018/12/beginners-guide-to-install-postgres.html You didn’t set a password for user postgres because it not a real Ubuntu user.


First of all, let’s check basic of how to Export Postgres database in Ubuntu. There is a standalone application call pg_dump. And also this, not a psql command, this should run is bash. To use this we have to use authentication for a postgres user.
I usually use
sudo -u postgres psql
to log into psql, so I don’t have to give a password for postgres user. So How can I give authentication to pg_dump. 
First of all, you have to change user into postgres without password?
sudo -i -u postgres

Now you can see your username is changed is bash.
let’s say you have database name school, so you command should be like below
pg_dump -U postgres school school.sql;
Now is the tricky part ðŸ˜€ Where is the school.sql file? Why don’t we ask it from bash?
type pwd in the terminal, then you will see something like this
/var/lib/postgresql 
Go there and you will see you SQL dump ðŸ˜€
Happy coding guys.