Nov/0760
Install PostgreSQL on Ubuntu 7.10
If you are using the latest version of Ubuntu (8.04 - Hardy Heron), you might find these slightly updated instructions useful.
This quick walk-through are my notes for installing the PostgreSQL database server and the PgAdmin administration application on Ubuntu Linux, and also set up the server so it allows access to other PC’s on your network.
Before we move on, this guide was tested on the current release of Ubuntu Linux, (7.10 - Gutsy Gibbon) and PostgreSQL 8.2, but it should also be applicable to older versions (of Ubuntu and PostgreSQL) and other Debian based distros.
Right for the basic installation, at the command-line, enter the following commands (or search for the listed packages in synaptic if you prefer that way of working):
$ sudo apt-get install postgresql postgresql-client postgresql-contrib
$ sudo apt-get install pgadmin3
This installs the database server/client, some extra utility scripts and the pgAdmin GUI application for working with the database.
Now we need to reset the password for the ‘postgres’ admin account for the server, so we can use this for all of the system administration tasks. Type the following at the command-line (substitute in the password you want to use for your administrator account):
$ sudo su postgres -c psql template1
template1=# ALTER USER postgres WITH PASSWORD 'password';
template1=# \q
That alters the password for within the database, now we need to do the same for the unix user ‘postgres’:
$ sudo passwd -d postgres
$ sudo su postgres -c passwd
Now enter the same password that you used previously.
Then, from here on in we can use both pgAdmin and command-line access (as the postgres user) to run the database server. But before you jump into pgAdmin we should set-up the PostgreSQL admin pack that enables better logging and monitoring within pgAdmin. Run the following at the command-line:
$ sudo su postgres -c psql < /usr/share/postgresql/8.2/contrib/adminpack.sql
Finally, we need to open up the server so that we can access and use it remotely - unless you only want to access the database on the local machine. To do this, first, we need to edit the postgresql.conf file:
$ sudo gedit /etc/postgresql/8.2/main/postgresql.conf
Now, to edit a couple of lines in the ‘Connections and Authentication’ section…
Change the line:
#listen_addresses = 'localhost'
to
listen_addresses = '*'
and also change the line:
#password_encryption = on
to
password_encryption = on
Then save the file and close gedit.
Now for the final step, we must define who can access the server. This is all done using the pg_hba.conf file.1
$ sudo gedit /etc/postgresql/8.2/main/pg_hba.conf
Comment out, or delete the current contents of the file, then add this text to the bottom of the file:
# DO NOT DISABLE! # If you change this first entry you will need to make sure that the # database # super user can access the database using some other method. # Noninteractive # access to all databases is required during automatic maintenance # (autovacuum, daily cronjob, replication, and similar tasks). # # Database administrative login by UNIX sockets local all postgres ident sameuser # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 # Connections for all PCs on the subnet # # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD host all all [ip address] [subnet mask] md5
and in the last line, add in your subnet mask (i.e. 255.255.255.0) and the IP address of the machine that you would like to access your server (i.e. 138.250.192.115). However, if you would like to enable access to a range of IP addresses, just substitute the last number for a zero and all machines within that range will be allowed access (i.e. 138.250.192.0 would allow all machines with an IP address 138.250.192.x to use the database server).
That’s it, now all you have to do is restart the server:
$ sudo /etc/init.d/postgresql-8.2 restart
And all should be working.
-
The following advice can also be given to you (plus you don’t even need to figure out IP addresses and subnet masks) from the latest versions of pgAdmin (1.6.x). However, this is not the version that ships with Ubuntu, so i’ll leave these instructions here. ↩
4:50 am on November 24th, 2007
thank you very much for this wonderful guide …. god should send to earth more people like you.
4:35 pm on November 24th, 2007
yes true..thks..More of this pls
7:33 am on November 27th, 2007
Thanks for the nice guide! It saved my day.
5:39 am on November 29th, 2007
Very helpful howto, and saved a couple of discovery hours! Thanks much!
PS: I’d suggest adding howtos about phpPgAdmin and pgAdmin3.
6:01 am on November 29th, 2007
Hi akin, no problem.
With regards to the PS… have you checked the rest of the site out for info on phpPgAdmin…
As for howtos on pgAdmin3 - the install is described above, if you want info for how to use it there’s no better place than the manual.
1:51 am on December 4th, 2007
thanks very helpfull
2:24 pm on December 4th, 2007
many thanks
6:13 am on December 5th, 2007
thanks for the manual…. i carried out all the steps as listed in the manual.. my PGAdmin III is installed and i have created a new database called adempiere but i am not getting any databases or tables in it… can u plz help me in this regard..
1:57 pm on December 5th, 2007
Excellent guide. To the point, and actionable. Thank you my friend.
10:09 am on December 6th, 2007
I got problem :(. I make like How-to say. but i got error message when i try to connect from other computer (by pgAdmin3): Error connecting to the server: FATAL: password authentication failed for user “####” I’m sure, password is correct.
10:21 am on December 6th, 2007
Ok, my mistake. I’m not correctly create account, role, db. Now it working. Greats thanks for this How-To !!
11:07 am on December 6th, 2007
Have you tried creating a table with pgadmin in your database? By default you will not see any tables in your db - they are empty when you first create them…
What user are you logging into the database as - is it an admin user? If no, you won’t see any other databases (except your own) as you don’t have permission to see them…
3:53 pm on December 10th, 2007
Very helpful, clear and easy to follow. Many thanks. DBM
3:29 pm on December 13th, 2007
Rawk! Nice job and thanks.
-peace
1:49 am on December 14th, 2007
Thanks for the well constructed guide! well done.
5:23 pm on December 19th, 2007
Excellent! Thank you very much for this nice guide.
2:06 pm on December 27th, 2007
yeah, it works! thx
I’ve had problems few weeks ago with this on Ubuntu 7.04 using similar guide, finally I installed all stuff but it failed when attempting to create a database in pgadmin (i got ‘server doesnt listen’ and cannot get rid of it). But now it’s all good.
However I want to notice that the following line didn’t run on my Ubuntu:
$ sudo apt-get install postgresql postgresql-client postgresql-contrib
I had to add version in each of it at the end;
$ sudo apt-get install postgresql-8.2 postgresql-client-8.2 postgresql-contrib-8.2
Maybe it’s connected with my repos (default repositories in Polish Ubuntu).
2:42 pm on January 9th, 2008
Very nice howto. Keep up with the good job. ~Thanks
3:05 am on January 16th, 2008
hi!
thanks for this guide!!!
anyway - i’m still running into several problems with ubuntu 7.10 and pgsql 8.2 installed just as described above…
when altering the user via
ALTER USER postgres WITH PASSWORD ‘abcdef’;
i get an “ALTER ROLE” response. AFAIK i should get sth like “ALTER USER SUCCESSFULL”?!?
anyway - when trying to connect to the server via pgadmin, it wont work. when trying to connect via phppgadmin i get an “login not allowed”…
anyone knows some answer to this?
12:41 pm on January 16th, 2008
Nope, that’s completely normal - it’s the only output i’ve ever seen.
The phppgadmin problem is probably because you still have all of the extra security enabled - the ‘postgres’ account is not allowed to use phppgadmin by default.
As for the not being able to connect with pgadmin i’m not so sure - are you putting in the correct connection details? Is the database server on the same machine? Have you configured the pg_hba.conf file properly?
Hope some of this helps…
2:02 pm on January 16th, 2008
Thanks really for this quick guide. I’m migrating from Gentoo to Ubuntu, this saved me a lot of time.
7:07 am on January 18th, 2008
After this command:
template1=# ALTER USER postgres WITH PASSWORD 'mypassw';I get this errorERROR: syntax error at or near "template1". What is wrong?1:31 pm on January 18th, 2008
The command is
ALTER USER postgres WITH PASSWORD 'mypassw';- you don’t need to type thetemplate1=#, that is there to indicate that we are at the psql shell (in the ‘template1’ schema).3:47 pm on January 18th, 2008
daz, thank you, I am just a beginner in PostgreSQL. Now is OK.
3:47 pm on January 21st, 2008
I had trouble with the following; hopefully this will help someone.
here’s what would happen if I tried to connect as user ‘postgres:’
chris@chris-LT:~$ psql -U postgres psql: FATAL: Ident authentication failed for user "postgres"Even though the user was set up properly. I needed to be able to do this instead of doing an su to user ‘postgres’ - so I can connect with a Rails app running under user ‘chris’ for example.
Here’s how to fix it; edit pg_hba.conf: change where it says ‘local sameuser’ to ‘local map1’. This should be in 2 locations. Instead of checking that you are the user ‘postgres,’ psql will now check the map configuration ‘map1’ which we will now define:
edit pg_ident.conf; add the following entry (replace chris with your user name):
map1 chris postgresThat should do the trick.
12:01 pm on February 1st, 2008
Oh My Goodness! Thank You so much!
The info and man pages for postgres just weren’t cutting it for me; pgAdmin help wasn’t either. This is exactly what I needed to succeed in getting this stuff to work. If only there were a bunch of tutorials out there that would put things as simple and straightforward as you did. You have good things coming your way Daz for the help you have provided everyone. Thank you again.
12:02 am on February 23rd, 2008
Thanks for the step by step instruction. I could install, however as I am new to this whole universe of Linux and postgresql, just wondering how to proceed to create a database and start using it.
I tried
createdb myfirstdb
it fails with foll message, appreciate your help
root@sas-desktop:/home/shreyas# createdb myfirstdb Password: createdb: could not connect to database postgres: FATAL: password authentication failed for user "root"Thanks pekay
4:04 am on February 23rd, 2008
Hi pekay,
It looks like the problem is that you are logged in as the root user, the only user account that is set up by default on PostgreSQL is ‘postgres’ so you have to issue those commands as that user.
If you would like to use another account (other than ‘postgres’) you can create a new one (and therefore make and use databases under that account), or just stick with the postgres user.
Hope that helps.
5:14 pm on February 24th, 2008
Mil gracias por su ayuda.
Thanks very much.
9:53 am on February 26th, 2008
I was also getting this same type of error as chris.
chris@chris-LT:~$ psql -U postgres psql: FATAL: Ident authentication failed for user "postgres"I found the answer to be:
Password: createdb: could not connect to database postgres: FATAL: password authentication failed for user "douglas" douglas@frog:~$ sudo psql [sudo] password for douglas: Password: psql: FATAL: password authentication failed for user "root" douglas@frog:~$ sudo su postgres -c creatuser douglas douglas: creatuser: command not found douglas@frog:~$ sudo su postgres -c createuser douglas Enter name of role to add: douglas Shall the new role be a superuser? (y/n) y douglas@frog:~$ psql Password: psql: FATAL: password authentication failed for user "douglas" douglas@frog:~$ sudo su postgres -c psql Welcome to psql 8.3.0, the PostgreSQL interactive terminal.Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit
postgres=# ALTER USER douglas WITH PASSWORD '*******'; ALTER ROLE postgres=# \q douglas@frog:~$ sudo su postgres -c createdb douglas createdb: database creation failed: ERROR: database "postgres" already exists douglas@frog:~$ sudo su postgres -c createdb starsdatabase createdb: database creation failed: ERROR: database "postgres" already exists douglas@frog:~$ psql Password: psql: FATAL: database "douglas" does not exist douglas@frog:~$ sudo su postgres postgres@frog:/home/douglas$ createdb douglas postgres@frog:/home/douglas$ exit exit douglas@frog:~$ douglas@frog:~$ psql Password: Welcome to psql 8.3.0, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit
douglas=#
5:02 pm on March 4th, 2008
i am getting an error when i try to change the password for the postgres user account.
chris@chris-desktop:~$ sudo su postgres -c psql template1 psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?1:38 pm on March 7th, 2008
Thanks a lot. Now I have Store + VisualWorks
Bye
3:23 pm on March 13th, 2008
wow.. thanx.. I just needed to get postgres working and I couldn’t even figure out how to log in
now it’s all working..
5:40 am on April 1st, 2008
I went thro’ so many articles on the net abt how to install & configure PostgreSQL & I was totally lost & confused!! But this article THE BEST .. fantastic & simple .. step by step .. it worked!
5:03 am on April 13th, 2008
i’m a newbie in linux and ubuntu thank u for ur post!
11:15 am on May 5th, 2008
Thanks very much, works fine.
Just one thing: I needed to type “\g” after setting new password (alter user …). Don’t know if it’s a new stuff in 8.3 which i have, or it was my mistake or it’s just not written in this how-to…anyway I was wondering about this for a while, so hope that it can be useful for someone;)
ps: sorry for my english, I hope you’ll understand)
7:45 pm on May 22nd, 2008
Hi Daz!
I found your article very useful in our research. It possess simple and straightforward procedures in setting up the PostgreSQL database. In line with this, I was wondering if you would permit me to include this in our documentation papers.
Thank you and more power!
Regards
12:37 am on June 14th, 2008
This is a great guide indeed, thank you!
4:56 am on June 24th, 2008
Just dropping a line to say “Thank you!”
7:23 pm on June 24th, 2008
Thank you so much for this tutorial. It helped me a lot.
12:15 pm on July 7th, 2008
Nice guide, I wish I could find it earlier.
regards, tolahtoleh.com
5:12 am on July 12th, 2008
impressive guide thx
2:18 pm on August 19th, 2008
$ sudo su postgres -c psql < /usr/share/postgresql/8.2/contrib/adminpack.sql
after using this command i got this:
ERROR: could not access file “$libdir/adminpack”: No such file or directory ERROR: could not access file “$libdir/adminpack”: No such file or directory ERROR: function pg_catalog.pg_file_rename(text, text, text) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. CONTEXT: SQL function “pg_file_rename” ERROR: could not access file “$libdir/adminpack”: No such file or directory ERROR: could not access file “$libdir/adminpack”: No such file or directory CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION Is it ok to proceed ahead/whats the problem.
9:06 am on September 15th, 2008
Thanks for the tutorial. A semicolon (‘;’) is missing at the end of the ALTER command. Had me stuck for a while
Update the post
1:29 am on September 16th, 2008
Post updated.
12:51 pm on September 26th, 2008
thanks so much for this guide!!!
7:56 pm on October 1st, 2008
thank you!
3:04 pm on October 23rd, 2008
hey… many thanks buddy, this guide saved my day…
6:23 am on October 29th, 2008
After :$ sudo su postgres -c psql template1 I am getting :
psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket “/var/run/postgresql/.s.PGSQL.5432”?
How can I check if the server is running like it is supposed to? How can I start this server?
11:54 am on October 29th, 2008
Try this command:
ps aux | grep postgresYou should have an entry containing something like the following if PostgreSQL is up and running:
/usr/lib/postgresql/8.2/bin/postgres -DIf it’s not running, try this:
sudo /etc/init.d/postgresql-8.2 start1:53 am on November 18th, 2008
This is wonderful, and I think it should be add on wiki documentation in http://www.openerp.com
Thanks a lot.
7:38 pm on January 12th, 2009
Wow, thanks for putting this up, really useful.
11:57 pm on March 4th, 2009
Thanks!!!
7:57 am on April 10th, 2009
Great stuff!!! Been struggling with this setup for a while now, your guide was flawless.
10:49 am on June 24th, 2009
I tried this but there was a failure plz tell me how to install … itz urgent
Failed to fetch cdrom:[Ubuntu 7.10 Gutsy Gibbon - Release i386 (20071017)]/pool/main/p/postgresql-8.2/libpq5_8.2.5-1.1_i386.deb Hash Sum mismatch Failed to fetch cdrom:[Ubuntu 7.10 Gutsy Gibbon - Release i386 (20071017)]/pool/main/p/postgresql-common/postgresql-client-common_78_all.deb Hash Sum mismatch Failed to fetch cdrom:[Ubuntu 7.10 Gutsy Gibbon - Release i386 (20071017)]/pool/main/p/postgresql-8.2/postgresql-client-8.2_8.2.5-1.1_i386.deb Hash Sum mismatch Failed to fetch cdrom:[Ubuntu 7.10 Gutsy Gibbon - Release i386 (20071017)]/pool/main/p/postgresql-common/postgresql-common_78_all.deb Hash Sum mismatch Failed to fetch cdrom:[Ubuntu 7.10 Gutsy Gibbon - Release i386 (20071017)]/pool/main/p/postgresql-8.2/postgresql-8.2_8.2.5-1.1_i386.deb Hash Sum mismatch Failed to fetch cdrom:[Ubuntu 7.10 Gutsy Gibbon - Release i386 (20071017)]/pool/main/p/postgresql-8.2/postgresql_8.2.5-1.1_all.deb Hash Sum mismatch Failed to fetch cdrom:[Ubuntu 7.10 Gutsy Gibbon - Release i386 (20071017)]/pool/main/p/postgresql-8.2/postgresql-client_8.2.5-1.1_all.deb Hash Sum mismatch Failed to fetch cdrom:[Ubuntu 7.10 Gutsy Gibbon - Release i386 (20071017)]/pool/main/p/postgresql-8.2/postgresql-contrib-8.2_8.2.5-1.1_i386.deb Hash Sum mismatch Failed to fetch cdrom:[Ubuntu 7.10 Gutsy Gibbon - Release i386 (20071017)]/pool/main/p/postgresql-8.2/postgresql-contrib_8.2.5-1.1_all.deb Hash Sum mismatch E: Unable
9:32 pm on June 30th, 2009
@babbar It looks like your box is trying to find the deb files off of the Ubuntu cdrom. Open up synaptic and disable the use of the cdrom in the sources section, then try again.
Hope this helps, let me know how you get on.