Simple procedure to install PostgreSQL (stable: 8.4) on Ubuntu Linux (tested on 8.04 LTS, 10.04 LTS, 11.04) :
$ sudo apt-get install postgresql postgresql-contrib
(the postgresql-contrib is optional, it’s just some additional facilities for Postgres)
$ sudo apt-get install pgadmin3 postgresql-client
$ 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 192.168.1.0/24 md5
(example for user postgres from subnet 10.0.x.x
host all postgres 10.0.0.0/16 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
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 :-) .
Done ! ( use the guideline at your own risk :D )
Bonus: to connect remote Postgres DB via SSH Tunnel, consider using SSH key: