PostgreSQL is a powerful and reliable object-relational database system. It’s a great alternative for MySQL. It is as easy to set up, performs better and offers far more features.
1. Make sure you already have install python-software-properties
$sudo apt-get install python-software-properties
2.Add PPA repository to my Ubuntu.
$sudo add-apt-repository ppa:pitti/postgresql
3. After adding PPA, update your system apt:
$sudo apt-get update
4.Finally install postgresql-9.1:
$sudo apt-get install postgresql
if you having any error, make sure you already install libpq-dev.The libpq-dev package is for compiling wrappers/clients against libpq.
$sudo apt-get install postgresql-9.1 libpq-dev
5.Now check it out installation is successful or…..
$ locate postgresql
6. If done!!!
Check the install version.
$psql -V
7.Now let’s take look to postgres console
$su postgres
7(a) Setup Root User ‘posrgres’
$sudo passwd postgres
give the postgres user a (unix) password,Now we can switch to the user postgres using command
$ su postgres
8. That’s it for postgers installation. If you want to access postgres from remote, Mean localhost. You need to perform some addition setting to postgres conf file.
First we edit the file postgresql.conf:
$ cd /etc/postgresql/9.1/main
List all file in main folder
$ ls -la
total 80
drwxr-xr-x 2 postgres postgres 4096 2012-05-17 18:05 .
drwxr-xr-x 3 postgres postgres 4096 2012-04-07 12:44 ..
-rw-r–r– 1 postgres postgres 316 2012-04-07 12:44 environment
-rw-r–r– 1 postgres postgres 143 2012-04-07 12:44 pg_ctl.conf
-rw-r—– 1 postgres postgres 4653 2012-05-17 18:05 pg_hba.conf
-rw-r—– 1 postgres postgres 4649 2012-04-09 19:29 pg_hba.conf.bak.original
-rw-r—– 1 postgres postgres 1636 2012-04-07 12:44 pg_ident.conf
-rw-r–r– 1 postgres postgres 19235 2012-04-07 12:44 postgresql.conf
-rw-r–r– 1 postgres postgres 19235 2012-04-09 19:29 postgresql.conf.bak.original
-rw-r–r– 1 postgres postgres 378 2012-04-07 12:44 start.conf
I have already back of config file, you need to make back up for further need
$ cp pg_hba.conf pg_hba.conf.bak.original $ cp postgresql.conf postgresql.conf.bak.original
9. Make changes to pg_hba.conf (authetification methods).
host all all 127.0.0.1/32 trust # md5 -> trust
10.You make change in config file, So once restart you postgres
$sudo /etc/init.d/postgresql restart *Restarting PostgreSQL 9.0 database server
I want to make database application based on web, how can I connect php and postgres on ubuntu ? thanks
Frankly I never use PostgreSQL with php, I used postgres as ROR development. but hope this link will help you. http://www.techrepublic.com/blog/howdoi/how-do-i-use-php-with-postgresql/110
Errata: I believe that point number 9 should read
“9. Make changes to pg_hba.conf”
instead of
“9.Make changes to pg_hba.config”
This is a nice and concise set of instructions for installing postgreSQL. I found them quite useful. Thanks.
Thank you, I updated it. and I am glad you found it worth
good information. thanks for posting.http://www.hpbrasil.org
Hi Ennya, You most welcome. 🙂
thanks man!! worked pefectly… 🙂
Do I have any idea about a “phpmyadmin” like tool for postgresql, which I can use for the postgresql installation I made using the instructions from this post ?
i hope this link help you. http://www.techrepublic.com/blog/howdoi/how-do-i-use-php-with-postgresql/110
if you are missing /etc/postgresql/9.1/main folder and postgre won’t start, after step 5 do
pg_createcluster 9.1 main –start
Great work Pravin….all of your posts including this one are very informative and provide real help to rails community!
Thanks for your time.
help me please, my folder /etc/postgresql does not exists, help me!
many Thanks its work successfully 🙂
And when at step 7 you are asked for a password…? Is there a default? If not, how do you set it? Without those small pieces of information this is useless.
Right, Just now updated post. Thanks for comment. 🙂
I want to add a new user to postgres with the sentence: createuser openerp70. But I have a bucle saying: Please answer “y” or “n”, and I cant do anything. What happens? I need to add a new user to do the connection to the database.
Thanks!!
hey, you are missing space. Command should be-> CREATE USER username
CREATE USER openerp70
https://railskey.wordpress.com/2012/04/13/postgres-commands/
May above link helps you.
Make changes to pg_hba.conf (authetification methods).
how to do it?
hey, follow below steps.
1. cd /etc/postgresql/9.1/main
2. cp pg_hba.conf pg_hba.conf.bak.original
3. cp postgresql.conf postgresql.conf.bak.original
4. sudo gedit pg_hba.conf OR sudo vi pg_hba.conf OR sudo nano pg_hba.conf
will open the file, modify as per mention.
I hope it helps you, let me know if you have any further issues.
thx u for answering my question ,after i tried i gt [sudo] password for postgres:
postgres is not in the sudoers file. This incident will be reported.
postgres is not in the sudoers file.<—how to solved it?
Have you done below step?
7(a) Setup Root User ‘posrgres’
$sudo passwd postgres
give the postgres user a (unix) password,Now we can switch to the user postgres using command
$ su postgres
ya,i ady done the step…
hey, it’s sort OR issues?
Thanks .
Thanks a million!
many thanks, I get a problem with the step 10
sudo /etc/init.d/postgresql start
I have the message
* Starting PostgreSQL 9.1 database server * The PostgreSQL server failed to start. Please check the log output:
FATAL: syntax error in file “/etc/postgresql/9.1/main/postgresql.conf” line 60, near token “.1”
I think there is problem with your new configuration file. Step 9th required for access from local host. Once restore original configuration file and try to access postgresql console. if you are not able to access may be problem with the installation. Let me know progress.
Not so far I have found new cool tool to work with postgresql on linux – Valentina Studio. Its free edition can do things more than many commercial tools!!
I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview
Thanks for the details about pg_hba.conf. it seemed logical to change md5 to something else, but still no result. After PQconnectStart gives a great connection, every time I call PQexec returns a NULL pointer. Where can I look?