Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[pekka]

--------------
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-account

1.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.html

2.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;