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 
