--------------
postgresql
--------------
1) Install postgresql & configure postgresql:-------------------------------------------------------------------------------
1.1) Make folders if they do not exist:
# mkdir /var/lib/pgsql/ # postgres home folder
# mkdir /var/lib/pgsql/data # data folder
# mkdir /etc/pgsql/*version* # config files folder for testing
1.2) Create system postgres user:
Using Webmin, use parameters: user=postgres, home=/var/lib/pgsql, shell=bash, password='No login allowed'
Postgres user need to be able to excute some commands, and need a working shell like bash.
But postgres is a system user, and we want to disable login for postgres user without setting shell=nologin.
Thus, if you are using terminal, disable login for postgres user:
# passwd -l postgres
Ref:
http://unix.stackexchange.com/questions/19333/disable-a-users-login-without-disabling-the-account1.3) Move configfiles to /etc/pgsql/*version*
(config files may appare in /var/lib/pgsql depending on distribution)
Debian move files to /etc/pgsql/*version* but slackware usually use vanilla install.
When using vanilla install, no files and folders are needed in /etc..
Vanilla install use /var/lib/pgsql for all files
1.4) Export environment variables, add these lines to .bash_profile in /var/lib/pgsql
export PGLIB=/usr/lib/pgsql
export PGDATA=/var/lib/pgsql/data
You may want these files in /var/lib/pgsql:
.bash_history, .bash_logout, .bash_profile, .psql_history
2) Initialize PostgreSQL:-------------------------------------------------------------------------------
More settings check:
http://www.postgresql.org/docs/current/static/locale.html2.1) With default (=POSIX = C) settings:
# su - postgres -c "initdb --pgdata=/var/lib/pgsql/data"
2.2) With character encoding & locale:
# su - postgres -c "initdb --encoding=UTF8 --locale=en_US.UTF8 --pgdata=/var/lib/pgsql/data"
3) Configure postgres superuser:-------------------------------------------------------------------------------
3.1) Change user & log on to postgresql:
# su - postgres
$ pgsql
3.2) Set new postgres password:
postgres=# ALTER USER postgres WITH PASSWORD 'newpassword';
postgres=# \password postgres
4) Configure mydatabaseuser:-------------------------------------------------------------------------------
4.1) Create a new database user and a database using postgres-shell:
postgres=# CREATE USER mypguser WITH PASSWORD 'mypguserpassword';
4.2) Allow new db-user to create databases:
postgres=# ALTER USER mypguser WITH CREATEDB;
4.3) Create new database using mypguser:
postgres=# CREATE DATABASE mypgdatabase OWNER mypguser;
5) Using postgresql with mypguser:-------------------------------------------------------------------------------
5.1) Connect to database from shell:
# psql -d mypgdatabase -U mypguser
5.2) Quit from the database
postgres=# \q
6) Information 1-liners:-------------------------------------------------------------------------------
Connected users:
postgres=# SELECT usesysid, usename FROM pg_stat_activity;
Monitor connections:
postgres=# SELECT * FROM pg_user;
postgres=# SELECT * FROM pg_stat_activity;
postgres=# SELECT * FROM pg_stat_activity WHERE datname='clients';
postgres=# SELECT * FROM pg_stat_database;
HBA.conf file in use:
postgres0# SHOW hba_file;