Install PostgreSQL Database

Simple procedure to install PostgreSQL (stable: 8.4) on Ubuntu Linux (tested on 8.04 LTS, 10.04 LTS, 11.04) :

1/ Install the DBMS :

$ sudo apt-get install postgresql postgresql-contrib

(the postgresql-contrib is optional, it’s just some additional facilities for Postgres)


2/ Install the GUI client (pgadmin or pg-client) to manage :

$ sudo apt-get install pgadmin3 postgresql-client


3/ Change password for user ‘postgres’ :

$ sudo su postgres -c psql
postgres=# ALTER USER postgres WITH PASSWORD ‘password’;
postgres=# \q

4/ Enable access from outside localhost (if needed) :

Edit the postgresql.conf or pg_hba.conf (in /etc/postgresql/8.4/main/ or $PG_HOME/data/ ) :
+ enable listening TCP/IP by adding this line to postgresql.conf (Postgres version 8.x)
(Postgres 7.x and prior versions, use this instead: tcpip_socket=true )
+ add following line into pg_hba.conf to pick the corresponding IPv4 subnet to be allowed:

host all postgres md5

To make sure the change is applied, restart the deamon :

$ sudo /etc/init.d/postgresql-8.4 restart

That’s it.



For Redmond (Windoze) users:

1/ Download the installer package (MSI)

Either 8.4 or 9.0 should be fine. The last version I tested was, FYI .


2/ Run the installer (administrator priviledge is preferred)

It is a wizard process, i.e. step-by-step configuration with respective instructions. You can leave it as default configuration, however I recommend to install to a directory other than “C:\Program Files\” , for example “D:\USR\PostgreSQL\8.4\” .

If your Windoze is one of the 64-bit versions, the “C:\Program Files (x64)\” is strongly NOT recommended, cuz it has several bugs related to user permissions and modifications.


3/ The password created in the wizard process

is used for both the Windoze service and default user ‘postgres’ of the DBMS. The client tools (pgclient and pgadmin3) are also bundled in the MSI package. To change the password of postgres user, the easy way is use the pgadmin3 (right-click user properties), but to change the password of Windoze service user, use this command:

C:\> net user postgres newpassword

4/ Now you can also edit the files postgresql.conf or pg_hba.conf 

to make your PostgreSQL connectable from outside of localhost (similar to the Ubuntu guide) .

The Pgadmin3 in Windoze can locate those files via its menu , so you don’t have to browse the Postgres ‘data’ folder to edit them :-) .

PostgreSQL pgadmin3 (Duc Quoc)

Done ! ( use the guideline at your own risk :D )




A coder, husband and brother...
This entry was posted in Coding, Marketing. Bookmark the permalink.

3 Responses to Install PostgreSQL Database

  1. Pingback: SVN best practices | DucQuoc's Blog

  2. It’s recommended to use UTF-8 encoding for the database, although the locale selected when installed may be “default locale”.

    CREATE ROLE duc_test WITH LOGIN PASSWORD ‘changeit’ VALID UNTIL ‘infinity’;

    (The create database SQL can be alternated with the createdb command-line in ‘bin’ folder:
    $ createdb -E UNICODE qmduc

    JDBC driver: org.postgresql.Driver
    JDBC url: jdbc:postgresql://<host>:<port>/<databaseName>
    (example: jdbc:postgresql://localhost:5432/qmduc?ssl=true )
    Datasource Name in some AppServers: java:comp/env/jdbc/qmduc

    JDBC driver: Maven dependency like below

    <version>9.4-1201-jdbc41</version><!-- Java6: jdbc4, Java7: jdbc41, Java8: jdbc42 -->

  3. To reload the change, one should restart the service (Linux command in the article, or the Windows service of Postgres).

    Alternative: PG 9.x supports reloading the configuration by the SQL

    SELECT pg_reload_conf();

    This is recommended because no need to restart the service, and the PG DB can run without interruption/interference.



    Another alternative: the pg_ctl executable can also :

    pg_ctl reload

Leave a Reply

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

You are commenting using your 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