Installing PostgreSQL on Debian

Description

This tutorial is about howto installing PostgreSQL on Debian Server.

Installing PostgreSQL

apt-get update
apt-get install pgsql

Create Language

Example plpgsql

su postgres
createlang plpgsql template1
exit

Change authentication method

We need to edit file pg_hba.conf to change authentification method for accessing PostgreSQL database.

cp /etc/postgresql/pg_hba.conf /etc/postgresql/pg_hba.confbak
vi  /etc/postgresql/pg_hba.conf

Find this section

# TYPE  DATABASE  USER  IP-ADDRESS  IP-MASK  METHOD
# Database administrative login by UNIX sockets
local  all  postgres        ident sameuser
#
# All other connections by UNIX sockets
local  all  all        ident sameuser
#
# All IPv4 connections from localhost
host  all  all  127.0.0.1  255.255.255.255  ident sameuser
#
# All IPv6 localhost connections
host  all  all  ::1  ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff  ident sameuser
host  all  all    ::ffff:127.0.0.1/128  ident sameuser
#
# reject all other connection attempts
host  all  all  0.0.0.0  0.0.0.0  reject

Edit that section to

# TYPE  DATABASE  USER  IP-ADDRESS  IP-MASK  METHOD
# Database administrative login by UNIX sockets
local  all  postgres        ident sameuser
#
# All other connections by UNIX sockets
local  all  all        password
#
# All IPv4 connections from localhost
host  all  all  127.0.0.1  255.255.255.255  password
#
# All IPv6 localhost connections
host  all  all  ::1  ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff  password
host  all  all    ::ffff:127.0.0.1/128  password
#
# reject all other connection attempts
host  all  all  0.0.0.0  0.0.0.0  reject

Restart PostgreSQL Server

/etc/init.d/postgresql restart

Create a New Database

Example wordpress

su postgres
createdb -T template1 wordpress
exit

Create a New User

Example: User supriyadisw with password cak3p

su postgres
createuser supriyadisw -P
Enter password for new user: cak3p [enter]
Enter it again: cak3p [enter]
Shall the new user be allowed to create databases? (y/n) y [enter]
Shall the new user be allowed to create more new users? (y/n) n [enter]
CREATE USER
exit

Login to PostgreSQL

pgsql -U supriyadisw wordpress [enter]
Password: cak3p [enter]

Good Luck

Share and Enjoy:

June 5th, 2006 8 Comments Debian, Linux, Tutorial

8 Responses to “Installing PostgreSQL on Debian”

Feed for this Entry Trackback Address

1 milisdad on Jul 7th, 2006 said:

aku kok konek pake pgadmin3 ra iso yoo
padahal wis manut karo tutorialmu loo??
2 supriyadisw on Jul 7th, 2006 said:

Keluar pesan error apa mas? Tulis aja Soale aku juga pernah gak konek pakai pgadmin3. Kali aja sama masalahnya…
3 Paul on Aug 7th, 2008 said:

Thanks you helped me a lot
4 Omar on Aug 17th, 2008 said:

probably one of the clearest turoails out there.. thanks a lot!
5 angus on Feb 23rd, 2009 said:

weiew,..
\mangstab juragan,…
6 fernando filipuzzi on Mar 1st, 2009 said:

Thanks
7 Alan on May 20th, 2009 said:

Your example creates a database named “wordpress” but, ironically, Wordpress doesn’t work with PostgreSQL…

Anyways, thanks…