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!
[…] sites helped a lot in not only getting me started, but also in writing this post: Installing Postgresql on Debian; PostgreSQL Database Server Configuration; Tagged with: debian, postgresql « Manage […]
weiew,..
mangstab juragan,…
Thanks
Your example creates a database named “wordpress” but, ironically, WordPress doesn’t work with PostgreSQL… 🙂
Anyways, thanks…