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 😀

  • aku kok konek pake pgadmin3 ra iso yoo
    padahal wis manut karo tutorialmu loo??

  • Keluar pesan error apa mas? Tulis aja 😀 Soale aku juga pernah gak konek pakai pgadmin3. Kali aja sama masalahnya… 😉

  • Thanks 🙂 you helped me a lot 🙂

  • probably one of the clearest turoails out there.. thanks a lot!

  • Pingback: Postgresql 8.3 on Debian Lenny « Code Ghar()

  • weiew,..
    mangstab juragan,…

  • fernando filipuzzi

    Thanks

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

    Anyways, thanks…