PostgrepSQL - Foundation

http://postgresql.org

Install

Dependence:
libreadline
zlib

Ubuntu:
apt-get install libreadline6 libreadline6-dev
apt-get install zlib1g zlib1g-dev

Fedora:
yum install readline readline-devel
yum install zlib zlib-devel

./configure
make
sudo make install

(installed to /usr/local/pgsql)

Config

Create PostgreSQL daemon running OS user:

useradd postgres

set environment variables in .bash_profile for convenience when execute commands:

export PATH=$PATH:/usr/local/pgsql/bin
export PGDATA=/usr/local/pgsql/data

mkdir /usr/local/pgsql/data
chown postgres:postgres /usr/local/pgsql/data
su - postgres
initdb -D /usr/local/pgsql/data -W -A password

Change authentication method (optional):
update /usr/local/pgsql/data/pg_hba.config and change the method to password or md5

Start

View PostgreSQL daemon status:

pg_ctl status

postgres -D /usr/local/pgsql/data >logfile 2>&1 &

or

pg_ctl start -l logfile

auto start:
1. copy start-up script from source/contrib/start-scripts/linux to /etc/init.d/postgresql (try the “sudo /etc/init.d/postgresql start” firstly to make sure it works)
2. make it auto-start when OS boot up

Operations

Create database:

sudo -u postgres createdb mydb

psql [database name]
If you do not supply the database name then it will default to your PostgreSQL user account name
-U PostgreSQL account: If don’t specify -U, it will default to the same name
as your current OS account
mydb=>: means you are a database normal user
mydb=#: means you are a database superuser

Change default PostgreSQL user “postgres” password:

sudo -u postgres psql template1
ALTER USER postgres with encrypted password 'your_password';

Clients

pgAdmin: GUI based administration and management tool
phpPgAdmin Web based administration and management tool