VectorLinux
December 18, 2014, 05:03:30 am *
Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
News: Visit our home page for VL info. To search the old message board go to http://vectorlinux.com/forum1. The first VL forum is temporarily offline until we can find a host for it. Thanks for your patience.
 
Now powered by KnowledgeDex.
   Home   Help Search Login Register  
Please support VectorLinux!
Pages: [1]
  Print  
Author Topic: Just for Postgres  (Read 67 times)
pekka
Member
*
Posts: 1


« on: December 16, 2014, 03:43:42 pm »

--------------
postgresql
--------------

1) Install postgresql & configure postgresql:
-------------------------------------------------------------------------------
1.1) Make folders:
   # 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 postgres user: nologin, sh=bash, home=/var/lib/pgsql

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) Without 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 as user mypguser to mypgdatabase:
   # su - mypguser
   $ psql mypgdatabase

5.2) Connect to database from shell:
   # psql -d mypgdatabase -U mypguser

5.3) Quit from the database
   postgres=# \q


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;

Connected users:
   postgres=# SELECT usesysid, usename FROM pg_stat_activity;

HBA.conf file in use:
   postgres0# SHOW hba_file;
« Last Edit: December 16, 2014, 06:05:10 pm by pekka » Logged

systemd refugee
Pages: [1]
  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2013, Simple Machines Valid XHTML 1.0! Valid CSS!