VectorLinux

Please login or register.

Login with username, password and session length
Advanced search  

News:

Visit our home page for VL info. For support and documentation, visit the Vector Linux Knowledge Center or search the Knowledge Center and this Forum using the search box above.

Author Topic: Just for Postgres  (Read 3143 times)

pekka

  • Member
  • *
  • Posts: 2
Just for Postgres
« on: December 16, 2014, 03:43:42 pm »

--------------
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;
« Last Edit: January 30, 2015, 01:56:14 pm by pekka »
Logged
systemd refugee