Ask Me

Postgres Remove connectivity -Where Postgres database running on Cloud compute node.

  

 Step 1: You can install postgres using source code  under any compute running on any cloud services (OCI,AZURE,AWS,GCS) .Below link can be used .

https://deepakoracledba.blogspot.com/p/postgres-binary-based-installation-v14.html

 Step 2: As per above link installation my location on all postgres related datafiles under below dir.

postgres@nftserver ~> cd $PGDATA

postgres@nftserver /u01/pdata> ll

total 64

drwx------. 6 postgres postgres    54 Sep  3 07:02 base

drwx------. 2 postgres postgres  4096 Sep  3 07:02 global

drwx------. 2 postgres postgres     6 Aug 29 04:52 pg_commit_ts

drwx------. 2 postgres postgres     6 Aug 29 04:52 pg_dynshmem

-rw-------. 1 postgres postgres  4960 Sep  3 06:56 pg_hba.conf

-rw-------. 1 postgres postgres  1636 Aug 29 04:52 pg_ident.conf

drwx------. 4 postgres postgres    68 Sep  3 07:07 pg_logical

drwx------. 4 postgres postgres    36 Aug 29 04:52 pg_multixact

drwx------. 2 postgres postgres     6 Aug 29 04:52 pg_notify

drwx------. 2 postgres postgres     6 Aug 29 04:52 pg_replslot

drwx------. 2 postgres postgres     6 Aug 29 04:52 pg_serial

drwx------. 2 postgres postgres     6 Aug 29 04:52 pg_snapshots

drwx------. 2 postgres postgres     6 Sep  3 06:52 pg_stat

drwx------. 2 postgres postgres    84 Sep  4 08:18 pg_stat_tmp

drwx------. 2 postgres postgres    18 Aug 29 04:52 pg_subtrans

drwx------. 2 postgres postgres     6 Aug 29 04:52 pg_tblspc

drwx------. 2 postgres postgres     6 Aug 29 04:52 pg_twophase

-rw-------. 1 postgres postgres     3 Aug 29 04:52 PG_VERSION

drwx------. 3 postgres postgres    60 Aug 29 04:52 pg_wal

drwx------. 2 postgres postgres    18 Aug 29 04:52 pg_xact

-rw-------. 1 postgres postgres    88 Aug 29 04:52 postgresql.auto.conf

-rw-------. 1 postgres postgres 28781 Sep  3 06:52 postgresql.conf

-rw-------. 1 postgres postgres    27 Sep  3 06:52 postmaster.opts

-rw-------. 1 postgres postgres    77 Sep  3 06:52 postmaster.pid

postgres@nftserver /u01/pdata>


 Step 3: You need to update 2 files

postgresql.conf :-- Postgres Sever configuration like (SPFILE in ORACLE) , Be default it locahost , so database will be accessible in local host only, Please change it to 0.0.0.0

postgres@nftserver /u01/pdata> grep listen postgresql.conf

#listen_addresses = 'localhost'         # what IP address(es) to listen on;

listen_addresses = '0.0.0.0'

postgres@nftserver /u01/pdata>


pg_hba.conf :-- Postgres client authentication file . Please add below line so it will accept connection from any IP addresses remotely. You can use the client IP address for limited usage or security purposes.

postgres@nftserver /u01/pdata> grep 0.0.0.0/0 pg_hba.conf
host    all             all             0.0.0.0/0               password
postgres@nftserver /u01/pdata>


 Step 3: Now open port in firewall using root access.

Example here i have already open the port.

[root@nftserver opc]# netstat -lntu | grep 5435

tcp        0      0 0.0.0.0:5435            0.0.0.0:*               LISTEN

[root@nftserver opc]#

To open port.

firewall-cmd --add-port=5435/tcp

firewall-cmd --add-port=4000/tcp --permanent


Step 4: Similarly update same port under OCI console security list ingress rule.




  Step 5: Now install PGADMIN for postgres in your client system running on windows operating system

 Step 6 : Configure in PGADMIN to connect remote database.

add new server , give a name any name i have give ip address as name, then in connection tab . please update IP address or hostname , port , username and password







 

Popular Posts