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   all    md5

(example for user postgres from subnet 10.0.x.x

host   all   postgres    md5


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

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

(change your version respectively, for example postgresql-9.5
$ service postgresql-9.5 restart
That’s it.



For Redmond (Windoze) users:

1/ Download the installer package


Either 8.4 or 9.0 should be fine. The last versions I tested was 8.4, 9.4, 9.5, FYI .

(Another EXE installer from Big



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 )

Bonus: to connect remote Postgres DB via SSH Tunnel, consider using SSH key:


Besides pgAdmin3, you can use other GUI SQL clients such as SQLectron or HeidiSQL.




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

4 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';

    In general it’s easier to create the database with same name of login role.

    CREATE DATABASE qmduc_test OWNER=qmduc_test;
    GRANT ALL PRIVILEGES ON DATABASE qmduc_test TO qmduc_test;
    CREATE SCHEMA IF NOT EXISTS qmduc_test_app AUTHORIZATION qmduc_test;

    (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?currentSchema=public )
    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