Postgres
install server
sudo apt-get install postgresql postgresql-contrib
install client
sudo apt-get install postgresql-client libpqxx-dev pgadmin3
configure server
As user postgres, run psql, connecting to database postgres
sudo -u postgres psql postgres
In the psql shell, set the postgres user’s password, and then press ctrl-d to exit the psql shell
\password postgres
create a new user, with an associated database
as user postgres, run createuser to create a new user dev
sudo -u postgres createuser -D -A -P dev
the options are:
-D: no database creation rights-A: no add user rights-P: ask for password
as user postgres, run createdb to create a new database devbd owned by user dev
sudo -u postgres createdb -O dev devdb
set the new user’s password
sudo -u postgres psql
\password dev
\password prod
install server instrumentation
as user postgres, create the adminpack extension
sudo -u postgres psql
CREATE EXTENSION adminpack;
change server authentication method
default authentication is peer. peer authentication obtains the client’s operating system user name from the kernel and uses it as the allowed database user name. This only works for local connections.
change to md5, which is password-based authentication, with the password sent over the connection as a md5 hash.
sudo vi /etc/postgresql/9.3/main/pg_hba.conf
change the line
# Database administrative login by Unix domain socket
local all postgres peer
to
# Database administrative login by Unix domain socket
local all postgres md5
get postgres to reload the config
sudo /etc/init.d/postgresql reload
