Friday 20 January 2017

Configure multiple Postgres instances on the server and connect via PgAdmin

Configure multiple Postgres instances on the server and connect via PgAdmin::
=======================================================
Server : 192.168.1.1
Postgresuser1 : postgres1
Postgresuser2 : postgres2

login as postgres1
#wget https://ftp.postgresql.org/pub/source/v9.6.1/postgresql-9.6.1.tar.gz
#tar -zxvf postgresql-9.6.1.tar.gz
#cd  postgresql-9.6.1
#./configure   --prefix=/home/postgres1/pgsql/
#make
#make install
Now we need to create the database
# /home/postgres1/pgsql/bin/initdb  -D /home/postgres1/pgsql/data/
 
Run this command as root 
#/sbin/ldconfig /home/postgres1/pgsql/lib


You will now be able to connect to the database with the below command from the localhost shell
# /home/postgres1/pgsql/bin/psql -U postgres1 postgres
# /home/postgres1/pgsql/bin/psql -U postgres1 postgres -p 5432
psql (9.6.1)
Type "help" for help.

postgres=# alter user postgres password '123456';
postgres=# GRANT ALL ON DATABASE postgres  TO postgres1;


Check the version of postgresql installed
# /home/postgres1/pgsql/bin/psql --version

Now we need to access the database from outside network. For that we need to edit two configuration file under /home/postgres1/pgsql/data/

1.)Edit postgresql.conf
listen_addresses = '*'
port = 5432

2.)Edit  pg_hba.conf(Host Based Authentication File) based on your network ipaddress. In my case it is as folows

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             192.168.200.0/24            trust

Now restart postgresql service as follows::
/home/postgres1/pgsql/bin/pg_ctl -D /home/postgres1/pgsql/data/ -l logfile start

Repeat the same steps for the second user 'postgres2'
Give another port for the second user in  postgresql.conf


Once the installation is completed. Try installing pgAdmin on your Desktop and connect to the postgres server.

Download pgadmin from https://ftp.postgresql.org/pub/pgadmin3/release/v1.22.2/win32/pgadmin3-1.22.2.zip

Install it and give the server ip  port address  and password to connect.

You should be able to connect to the server. You can use the same credentials to connect from different applications to the database server.


We can create table space via pgAdmin.
Once the table space is created, check it in the backend under the directory '/home/postgres1/pgsql/data/pg_tblspc'

[postgres1@ind pg_tblspc]$ ll
total 0
lrwxrwxrwx 1 postgres1 postgres1 23 Jan 20 17:23 16389 -> /home/postgres1/tablespace1
lrwxrwxrwx 1 postgres1 postgres1 23 Jan 20 17:23 16390 -> /home/postgres1/tablespace2
lrwxrwxrwx 1 postgres1 postgres1 23 Jan 20 17:24 16391 -> /home/postgres1/tablespace3
drwx------ 2 postgres1 postgres1  6 Jan 20 16:54 tablespace1
drwx------ 2 postgres1 postgres1  6 Jan 20 16:54 tablespace2
drwx------ 2 postgres1 postgres1  6 Jan 20 16:54 tablespace3

No comments:

Post a Comment