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. ↩
thank you very much for this wonderful guide …. god should send to earth more people like you.
yes true..thks..More of this pls
Thanks for the nice guide! It saved my day.
Very helpful howto, and saved a couple of discovery hours! Thanks much!
PS: I’d suggest adding howtos about phpPgAdmin and pgAdmin3.
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.
thanks very helpfull
many thanks
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..
thanks in Advance
Excellent guide. To the point, and actionable. Thank you my friend.
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.
Ok, my mistake. I’m not correctly create account, role, db. Now it working.
Greats thanks for this How-To !!
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…
Very helpful, clear and easy to follow. Many thanks.
DBM
Rawk! Nice job and thanks.
-peace
Thanks for the well constructed guide! well done.
Excellent! Thank you very much for this nice guide.
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).
Very nice howto. Keep up with the good job.
~Thanks
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?
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…
Thanks really for this quick guide. I’m migrating from Gentoo to Ubuntu, this saved me a lot of time.
After this command:
template1=# ALTER USER postgres WITH PASSWORD 'mypassw';I get this errorERROR: syntax error at or near "template1". What is wrong?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).daz, thank you, I am just a beginner in PostgreSQL. Now is OK.
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 postgrespsql: 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.
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.
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 myfirstdbPassword:
createdb: could not connect to database postgres: FATAL: password authentication failed for user "root"
Thanks
pekay
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.
Mil gracias por su ayuda.
Thanks very much.
I was also getting this same type of error as chris.
chris@chris-LT:~$ psql -U postgrespsql: 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=#
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 template1psql: 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"?
Thanks a lot.
Now I have Store + VisualWorks
Bye
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..
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!
i’m a newbie in linux and ubuntu
thank u for ur post!
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)
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
This is a great guide indeed, thank you!
Just dropping a line to say “Thank you!”
Thank you so much for this tutorial. It helped me a lot.
Nice guide,
I wish I could find it earlier.
regards,
tolahtoleh.com
impressive guide
thx
$ 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.
Thanks for the tutorial.
A semicolon (‘;’) is missing at the end of the ALTER command.
Had me stuck for a while
Update the post
Post updated.
thanks so much for this guide!!!
thank you!
hey… many thanks buddy, this guide saved my day…
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?
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 startThis is wonderful, and I think it should be add on wiki documentation in http://www.openerp.com
Thanks a lot.