How to set Home and Environment variables in PostgreSQL

 ==============SETTING HOME PATH & ENV PATH=====================

Once you installed PostgreSQL successfully, follow below procedure to set home and environment variables

[root@postgres Desktop]# vi /etc/passwd

postgres:x:26:26:PostgreSQL Server:/opt/PostgreSQL/10:/bin/bash

:wq

================================================

[root@postgres Desktop]# cd /opt/PostgreSQL/10/

[root@postgres 10]# cat pg_env.sh 

#!/bin/sh

# The script sets environment variables helpful for PostgreSQL

export PATH=/opt/PostgreSQL/10/bin:$PATH

export PGDATA=/opt/PostgreSQL/10/data

export PGDATABASE=postgres

export PGUSER=postgres

export PGPORT=5432

export PGLOCALEDIR=/opt/PostgreSQL/10/share/locale

export MANPATH=$MANPATH:/opt/PostgreSQL/10/share/man

=======================================================

[root@postgres 10]# cp /root/.bash_profile .

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

        . ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH=/opt/PostgreSQL/10/bin:$PATH

export PGDATA=/opt/PostgreSQL/10/data

export PGDATABASE=postgres

export PGUSER=postgres

export PGPORT=5432

export PGLOCALEDIR=/opt/PostgreSQL/10/share/locale

export MANPATH=$MANPATH:/opt/PostgreSQL/10/share/man

:wq

==========================================================

[root@postgres 10]# cd /opt/

[root@postgres opt]# chown postgres:postgres PostgreSQL/ -R

[root@postgres opt]# su - postgres

-bash-4.1$ pwd

/opt/PostgreSQL/10

-bash-4.1$ 

-bash-4.1$ 

-bash-4.1$ psql --version

psql (PostgreSQL) 10.9

-bash-4.1$ psql -p 5432 -U postgres -d postgres ------> connecting to postgresql user

Password for user postgres: 

psql.bin (10.9)

Type "help" for help.

postgres=# show data_directory;

     data_directory      

-------------------------

 /opt/PostgreSQL/10/data

(1 row)

postgres=# select current_user;

 current_user 

--------------

 postgres

(1 row)

postgres=# select datname from pg_database;

  datname  

-----------

 postgres

 template1

 template0

(3 rows)

postgres=# 

=================================CREATE CLUSTER====================

How to Create Cluster ???

[root@postgres Desktop]# cd /u01/

[root@postgres u01]# mkdir proddb

[root@postgres u01]# chown postgres:postgres proddb/ -R

[root@postgres u01]# su - postgres

-bash-4.1$ initdb -D /u01/proddb/

-bash-4.1$ cd /u01/proddb/

-bash-4.1$ vi postgresql.conf

port=5433

:wq

-bash-4.1$ pg_ctl -D /u01/proddb/ start --------

-bash-4.1$ ps -ef | grep postgres

root     17715 17705  0 13:51 pts/0    00:00:00 su - postgres

postgres 17716 17715  0 13:51 pts/0    00:00:00 -bash

postgres 17783     1  0 13:53 pts/1    00:00:00 /opt/PostgreSQL/10/bin/postgres -D ../data

postgres 17784 17783  0 13:53 ?        00:00:00 postgres: logger process                  

postgres 17786 17783  0 13:53 ?        00:00:00 postgres: checkpointer process            

postgres 17787 17783  0 13:53 ?        00:00:00 postgres: writer process                  

postgres 17788 17783  0 13:53 ?        00:00:00 postgres: wal writer process              

postgres 17789 17783  0 13:53 ?        00:00:00 postgres: autovacuum launcher process     

postgres 17790 17783  0 13:53 ?        00:00:00 postgres: stats collector process         

postgres 17791 17783  0 13:53 ?        00:00:00 postgres: bgworker: logical replication launcher   

root     17832 17741  0 13:59 pts/1    00:00:00 su - postgres

postgres 17833 17832  0 13:59 pts/1    00:00:00 -bash

postgres 17890     1  0 14:01 pts/1    00:00:00 /opt/PostgreSQL/10/bin/postgres -D /u01/proddb

postgres 17892 17890  0 14:01 ?        00:00:00 postgres: checkpointer process                     

postgres 17893 17890  0 14:01 ?        00:00:00 postgres: writer process                           

postgres 17894 17890  0 14:01 ?        00:00:00 postgres: wal writer process                       

postgres 17895 17890  0 14:01 ?        00:00:00 postgres: autovacuum launcher process              

postgres 17896 17890  0 14:01 ?        00:00:00 postgres: stats collector process                  

postgres 17897 17890  0 14:01 ?        00:00:00 postgres: bgworker: logical replication launcher   

-bash-4.1$ psql -p 5433 -U postgres -d postgres

psql.bin (10.9)

Type "help" for help.

postgres=# show data_directory;

  data_directory  

------------------

 /u01/proddb

(1 row)

postgres=# show port;

 port 

------

 5433

(1 row)

postgres=# select datname from pg_database;

  datname  

-----------

 postgres

 template1

 template0

(3 rows)

postgres=# 

postgres=# create table emp(name char);

CREATE TABLE

postgres=# insert into emp values('s');

INSERT 0 1

postgres=# \dt

        List of relations

 Schema | Name | Type  |  Owner   

--------+------+-------+----------

 public | emp  | table | postgres

(1 row)

postgres=# \dt+

                      List of relations

 Schema | Name | Type  |  Owner   |    Size    | Description 

--------+------+-------+----------+------------+-------------

 public | emp  | table | postgres | 8192 bytes | 

(1 row)

=======================PAGE LAYOUT========================

postgres=# create table page(id int);

CREATE TABLE

postgres=# insert into page values(1);

INSERT 0 1

postgres=# \dt+

                      List of relations

 Schema | Name | Type  |  Owner   |    Size    | Description 

--------+------+-------+----------+------------+-------------

 public | emp  | table | postgres | 8192 bytes | 

 public | page | table | postgres | 8192 bytes | 

(2 rows)

postgres=# select ctid,* from page;

 ctid  | id 

-------+----

 (0,1) |  1

(1 row)

postgres=# insert into page values(2);

INSERT 0 1

postgres=# select ctid,* from page;

 ctid  | id 

-------+----

 (0,1) |  1

 (0,2) |  2

(2 rows)

postgres=# analyze page;

ANALYZE

postgres=# select relpages,reltuples from pg_class where relname='page';

 relpages | reltuples 

----------+-----------

        1 |         2

(1 row)

postgres=# insert into page values(generate_series(3,100));

INSERT 0 98

postgres=# \dt+

                      List of relations

 Schema | Name | Type  |  Owner   |    Size    | Description 

--------+------+-------+----------+------------+-------------

 public | emp  | table | postgres | 8192 bytes | 

 public | page | table | postgres | 8192 bytes | 

(2 rows)

postgres=# analyze page;

ANALYZE

postgres=# select relpages,reltuples from pg_class where relname='page';

 relpages | reltuples 

----------+-----------

        1 |       100

(1 row)

postgres=# insert into page values(generate_series(101,1000));

INSERT 0 900

postgres=# analyze page;

ANALYZE

postgres=# select relpages,reltuples from pg_class where relname='page';

 relpages | reltuples 

----------+-----------

        5 |      1000

(1 row)

postgres=# \dt+

                      List of relations

 Schema | Name | Type  |  Owner   |    Size    | Description 

--------+------+-------+----------+------------+-------------

 public | emp  | table | postgres | 8192 bytes | 

 public | page | table | postgres | 64 kB      | 

(2 rows)

postgres=# 

postgres=# show shared_buffers;

 shared_buffers 

----------------

 128MB

(1 row)

postgres=# show checkpoint_timeout;

 checkpoint_timeout 

--------------------

 5min

(1 row)

TABLESPACE -> SUB DIRECTORY, IT IS AT INSTANCE (CLUSTER)

10 DATABASE -> 10 DIRECTORY

10 TABLES -> 10 DATAFILES

10 INDEX -> 10 DATAFILES