My first PostgreSQL

Hush Hush Hush…. after 3 long days i at last did it, setup vm , installed CentOS , configured Postgres 9.6-3 ( Redhat Linux edition).

So let me spit it when my head really hot with all the syntax… i promise its not a boring subject but yes its worth learning.

Step 1: Get the VM image any free VM available these are fine to install.Choose either 64 bit or 86 bit depends on your machine.

Step 2:  Spin up the O.S image and complete to end this will be pretty straight forward , even dumb linux guy like me able to do it. 🙂

Step 3: Once the base O.S installed get the below link for downloading  Postgresql software ( You need to learn swimming in ocean there 🙂 kidding ) i mean you need to be smart enough to know your O.S architecture and required PostgreSQL binaries suitable.

here is the trick there are multiple ways of downloading and configuring the database software.. Yum .. Curl … EnterpriseDB Manager download many more ways depends on O.S architecture.

my chosen best way is using “Curl” directly downloading from link here –

Yum installer ( if you choose to go by yum you will get the 9.2 version of postgresql link here https://www.postgresql.org/download/linux/redhat/ )

Curl installer ( if you choose to go by way of installing and configuration here is the Postgresql Link – https://yum.postgresql.org/repopackages.php

Step 4: Once you have the chosen path below is the way to install and configure.

4-1 . Do the Yum update for downloading latest O.S binaries this is mandate and follow the on screen dialogues , this is easy part.

4-2.  command line here

curl -O https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm

Verify the downloaded file location and name.

ls -lrta

once you have confirm the above downloaded file move to actual installation part.

4.3 run the below command to install the downloaded postgresql binaries..

rpm -ivh pgdg-redhate96-9.6-3.noarch.rpm

this will install the binaries perfect and then next step.

4.4 you need to install the downloaded binaries and configure

command here –

sudo yum postgresql96-libs postgresql96-server postgresql96-contrib

once the above required components are downloaded and installed and update at the same .. in between you will be asked to say Yes to proceed.

Check the installed using Curl -O version

You ned REBOOT the vm once installed all above steps.

4.5 Next part you need to verify the connection by making the actual connect to postgresql by below commands…

pgsql -h localhost ( to see the connectivity ) it may fail for with 5432 service check blah blah .. if this happens then you need to check pg_hba.conf file existed in /data directory, if configured properly . 

yes all the above strike is complete BS .. do not agree with any google finds you do cause you did really good till now and efforts don’t go waste.

all you need to connect to postgresql is to execute the below command to initiate the initdb file which will set the important paths and folders as well the actual hba file which has the configuration about the user logins as well connectivity access grants.

command to execute to do all the above blah blah blah … 🙂

sudo -U postgres pgsql postgres ( this is command trying to connect postgres database using postgres user login) this will end the connectivity error.

execute the below command to do all the magic required …

service postgresql-9.6 initdb ( this will set all the required folders and paths)

find the hba file using find -name “pg_hba*”

location will be “/var/lib/pgsql/9.6/data/pg_hba.conf ”

4.6 You need to edit the above configuration file for successful login for the first time…/

vi  /var/lib/pgsql/9.6/data/pg_hba.conf and edit as below

host all all ::1/128 trust

local all all  <blank> trust

host all all 0.0.0.0/0 trust

4.7 later then restart the service by below command

sudo service postgresql restart

4.8 now the show time doing the actually connectivity using postgres user id.

psql -U postgres psql

you will be in as a power user but now you need to reset the password using the below command

psql -U postgres -w then it will prompt to change the password …

later quit and connect in normal way…

thats it Wholllaaa….. you are in postgres now

POSTGRES=#

play with different commands you can refer the postgresql documentation …

Have a good day … and keep following the wiki for more updates …..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s