April 14, 2008 · Server · Email This Post

Sponsored Link
PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others.

pgAdmin III is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world. The application may be used on Linux, FreeBSD, OpenSUSE, Solaris, Mac OSX and Windows platforms to manage PostgreSQL 7.3 and above running on any platform, as well as commercial and derived versions of PostgreSQL such as EnterpriseDB, Mammoth PostgreSQL, Bizgres and Greenplum database.

pgAdmin III is designed to answer the needs of all users, from writing simple SQL queries to developing complex databases. The graphical interface supports all PostgreSQL features and makes administration easy. The application also includes a syntax highlighting SQL editor, a server-side code editor, an SQL/batch/shell job scheduling agent, support for the Slony-I replication engine and much more. Server connection may be made using TCP/IP or Unix Domain Sockets (on *nix platforms), and may be SSL encrypted for security. No additional drivers are required to communicate with the database server.

Install Postgresql and pgadmin3 in Ubuntu

PostgreSQL 8.2 version will be installed in Ubuntu 7.10 (Gutsy Gibbon)

sudo apt-get install postgresql-8.2 postgresql-client-8.2 postgresql-contrib-8.2

sudo apt-get install pgadmin3

This will install the database server/client, some extra utility scripts and the pgAdmin GUI application for working with the database.

Configuring postgresql in Ubuntu

Now we need to reset the password for the ‘postgres’ admin account for the server

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.

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

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.

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

That's it you can start using postgresql in Ubuntu

Create a Database from command line

You can also use pgadmin3 for all postgresql related

To create a database with a user that have full rights on the database, use the following command

sudo -u postgres createuser -D -A -P mynewuser

sudo -u postgres createdb -O mynewuser mydatabase

Sponsored Link

Incoming search terms:

Related posts

27 Comments to “Howto setup Database Server With postgresql and pgadmin3”

  1. yes, i really do want to know, thanks says:

    Why should postgresql be used instead of mysql? Isnt mysql the default database in the server package thingie in ubuntu anyway?

    [Reply]

  2. admin says:

    mainly related to performance.If you install LAMP server mysql is default package.If you install basic installation without LAMP option you can install any database.

    Mostly now a days Postgresql is used for webdevelopment

    [Reply]

  3. Christ says:

    PostgreSQL is a more feature-rich database than mysql, more robust and more reliable. Mysql is used for pure speed and not much else. Both are perfectly acceptable choices, and will fulfill most people’s needs nicely. the only reason mysql has such a large share of the web development market is because the mysql client was once included in PHP, the leading language used for development of web applications.

    [Reply]

  4. Mick says:

    Licensing is one good reason to use it over MySQL. PostgreSQL has a BSD license, which may mean a lot to some users wanting to sell software. MySQL has also started making moves to become more commercial, limiting some features to corporate versions. There could be some number of developers jumping ship to support PostgreSQL in the near future, so it might finally be their turn in the sun, from all I’ve seen it is a solid system and should be pretty deserving of a bit more attention.

    [Reply]

  5. jj says:

    In configuration section, after “template1=# \q” I received the following response

    postgres=# template1=# ALTER USER postgres WITH PASSWORD ‘jack’;

    ERROR: syntax error at or near “template1″

    LINE 1: template1=# ALTER USER postgres WITH PASSWORD ‘jack’;
    postgres=# template1=# \q

    Not sure if I was to literally use the word “password”, I substituted my own password ‘jack’

    Did I screw up?

    [Reply]

  6. jj says:

    There should be a hash (#)before “DO NOT DISABLE!”

    [Reply]

  7. jmvbxx says:

    Where is the default database installed? thx

    [Reply]

  8. spicedreams says:

    postgres=# template1=# ALTER USER postgres WITH PASSWORD ‘jack’;

    ERROR: syntax error at or near “template1?

    This is because template1=# is the prompt you’re supposed to see, not part of the command. In fact you’re seeing the prompt postgres=#, indicating that you’re logged in to the database ‘postgres’ rather than ‘template1′ that you should be. That in turn is because the earlier command listed above: sudo su postgres -c psql template1 is wrong- it should read sudo su postgres -c “psql -d template1″, which will connect you to database ‘template1′ as needed.

    The typographical `quotes’ around the password in the instructions are wrong too- you need to replace them with simple ‘quotes’ or you will get other errors.

    That said, I have been using this to help me set up my own postgresql server and it has helped me. Is it strictly true that the unix password for the user ‘postgresql’ needs to match the database server’s password for the user of the same name? I think I have got away in the past without making them match.

    [Reply]

  9. spicedreams says:

    in fact watch out for the ‘typographical quotes’ issue throughout. This WordPress web server software takes all your nice simple " and ' quotes as required for these commands, and replaces them with dumb “typographical” ‘quotes’ which don’t work. It’s probably a setting in the wordpress configuration- it needs to be turned off please!

    [Reply]

  10. chappejw says:

    Thanks for the great quick tutorial. Don’t forget to read user’s comments and update/fix your typos… ;)

    [Reply]

  11. se7s says:

    Thank you so much .. u just saved my day with this great tutorial

    [Reply]

  12. OldManRiver says:

    All,

    Followed this HOWTO and got the the restart part and get:

    root@u-main-svr:/etc/postgresql/8.2/main# sudo /etc/init.d/postgresql-8.2 restart
    * Restarting PostgreSQL 8.2 database server * Error: pid file is invalid, please manually kill the stale server process.
    [fail]

    Tried a stop and start instead but no luck

    What do I need to do to get around this problem?

    OMR

    [Reply]

  13. OldManRiver says:

    All,

    I see why I get no restart when I run the following this is all I get:

    # find /* -name postg*
    /etc/postgresql/8.2/main/postgresql.conf

    so system contains no control script in the “/etc/init.d” dir.

    OMR

    [Reply]

  14. OldManRiver says:

    All,

    Take that back, was not in root dir.

    OMR

    [Reply]

  15. Witi says:

    Thank you for the nice tutorial! I think i will give Postgres a try!

    [Reply]

  16. Piotrek says:

    Very handy jump start
    Thanx !

    [Reply]

  17. Cesar Scheck says:

    Thank you, buddy!!!
    I was looking for a code to change the admin’s password and I found your article.

    After coding, I had to stop and start the service to make this thing works,
    Thanks once!
    Best regards!

    [Reply]

  18. Pat says:

    I Tried to execute the command but im getting this WARNING…did i do something wrong?

    petrus@petrus-laptop:~$ sudo gedit /etc/postgresql/8.2/main/postgresql.conf

    (gedit:8166): Gtk-WARNING **: cannot open display:

    [Reply]

  19. Alan Ha;e says:

    Isn’t there some text missing at the end of the section on” set-up the PostgreSQL admin pack” ? WHAT do you run at the command line to set this up?

    Many thanks for the tutorial.

    [Reply]

  20. BtcheBtch says:

    I got all the info needed,
    Thank you so much !

    [Reply]

  21. Simon says:

    Thanks for the tutorial. I used it to get connected…

    [Reply]

  22. Gontrand says:

    Some commands missing for admin pack installation I think as Alan Ha;e already mention…

    [Reply]

  23. Gontrand says:

    Not sure, but maybe this command :

    $ sudo su postgres -c psql < /usr/share/postgresql/8.2/contrib/adminpack.sql

    At the end of this post :

    http://moojix.wordpress.com/2008/06/24/install-postgresql-82-under-ubuntu-with-adminpack/

    [Reply]

  24. RezvieFN says:

    Postgres is a good choice.

    Follow this step to install latest Postgres:
    - go to “Synaptic Package Manager” then search “postgresql” only, then click Apply
    - while you are on “Synaptic Package Manager” search “pgadmin3″ only, then click Apply

    then follow the other steps:
    ———————————–
    Configuring postgresql in Ubuntu

    Now we need to reset the password for the ‘postgres’ admin account for the server

    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.

    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

    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.

    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

    That’s it you can start using postgresql in Ubuntu

    [Reply]

  25. iceman says:

    pg_hba.conf settings for Maverick Meerkat

    #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
    # 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 [ipaddress] [subnet mask] md5

    [Reply]

  26. prathvish says:

    hi am new to the postgres world .

    i need help .
    we are planning to set up a postgres server on linux which will share database of a system running on mac and linux. i need the steps and procedures i should follow. would be grateful if i get info and procedure to goabot the same .
    thanks in advance

    [Reply]

  27. prathvish says:

    the software is cent os and there will be three machines . one with the centos postgres and other with a centos resolve and a mac resolve with whom the linux resolve has to share the projects data.

    [Reply]

Leave a Reply