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 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]
Author Topic: Just for Postgres  (Read 67 times)
Posts: 1

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


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:

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]
Jump to:  

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