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:

These icons link to social bookmarking sites where readers can share and discover new web pages.

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

2 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…