Postgresql is default port 5432/tcp
1: Disable Old version using yum
[root@dev ~]# yum-config-manager --disable pgdg10 pgdg94 pgdg95 pgdg96
2: Download Postgresql 11 New Version
[root@dev ~]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
root@server# yum install postgresql-server postgresql-contrib
3: Initialize PostgreSQL database instance
root@server# sudo postgresql-setup initdb
4: Restart or enable postgresql service
[root@dev ~]# sudo systemctl start postgresql
[root@dev ~]#sudo systemctl enable postgresql
5:Check version
[root@dev ~]# sudo -u postgres psql -c "SELECT version();"
could not change directory to "/root"
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 11.2.24 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)
6:Connect as postgres user and set admin password
[root@dev ~]# su - postgres
Last login: Fri Jan 29 16:46:48 IST 2021 on pts/1
-bash-4.2$ psql
psql (11.4)
Type "help" for help.
postgres=# ALTER USER postgres WITH PASSWORD '123';
ALTER ROLE
postgres=# \q
-bash-4.2$ exit
logout
8.Configuring PostgreSQL Remote Access on CentOS 7
[root@dev ~]# vi /var/lib/pgsql/11/data/postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
9 # Allow network clients to access PostgreSQL service in pg_hba.conf file.
[root@dev ~]# vim /var/lib/pgsql/11/data/pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres peer
#host replication postgres 127.0.0.1/32 ident
#host replication postgres ::1/128 ident
host all all 192.168.105.119/24 md5
10. [root@dev ~]# echo "host all all 192.168.116.0/24 md5" >> /var/lib/pgsql/11/data/pg_hba.con
!!!!!!@Pass ip address in pg_hba.conf file and enter ip address which it is access by remote user .
11.[root@dev ~]# systemctl restart postgresql
12. Allow postgresql service in linux
[root@dev ~]#firewall-cmd --permanent --add-service=postgresql
[root@dev ~]#firewall-cmd --reload
If you are configure ip address in pg_hba.con if other machine is connect give error like
Give error
13. I will allow all host so all client will connect remote side machine .
[root@dev ~]# echo "host all all 0.0.0.0/0 md5" >> /var/lib/pgsql/11/data/pg_hba.con
14.[root@dev ~]# systemctl restart postgresql
15. Check default postgresql post run
[root@dev ~]#ss -nlt | grep 5432
16.Create DATABASE in postgres
[root@dev ~]# su - postgres
Last login: Sat Jan 30 17:50:32 IST 2021 on pts/1
-bash-4.2$ psql postgres
psql (9.2.24)
Type "help" for help.
postgres=# create database mytestdb;
CREATE DATABASE
postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
johndb | postgres | UTF8 | en_IN | en_IN | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | john=CTc/postgres
mytestdb | postgres | UTF8 | en_IN | en_IN |
postgres | postgres | UTF8 | en_IN | en_IN |
template0 | postgres | UTF8 | en_IN | en_IN | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_IN | en_IN | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
postgres-# \l+ (show extra information)
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
johndb | postgres | UTF8 | en_IN | en_IN | =Tc/postgres +| 6501 kB | pg_default |
| | | | | postgres=CTc/postgres+| | |
| | | | | john=CTc/postgres | | |
mytestdb | postgres | UTF8 | en_IN | en_IN | | 6501 kB | pg_default |
postgres | postgres | UTF8 | en_IN | en_IN | | 6501 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | en_IN | en_IN | =c/postgres +| 6385 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | en_IN | en_IN | =c/postgres +| 6393 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(5 rows)
Click here...... Create user and role and grant privileges on Database
969 ss -nlt | grep 5432
970 sudo su - postgres
971 clear
972 sudo passwd postgres
973 su - postgres
974 psql postgres
975 sudo su - postgres
976 systemctl restart postgresql
977 su --shell /bin/bash postgres
978 su - postgres
979 psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'Om';"
980 su - postgres
981 sudo passwd postgres
982 su - postgres
983 psql postgres
984 createdb testDB
985 psql postgres
986 su - postgres
987 su -postgres -c "createdb test_database"
988 psql
989 sudo su -postgres
990 su - postgres
991 vi /var/lib/pgsql/11/data/postgresql.conf
992 vi /var/lib/pgsql/11/data/postgresql.conf
993 vim /var/lib/pgsql/data/postgresql.conf
996 systemctl restart postgresql
997 echo "host all all 192.168.105.119/24 md5" >> /var/lib/pgsql/data/pg_hba.conf
998 systemctl restart postgresql
999 vim /var/lib/pgsql/data/pg_hba.conf
1000 exit
No comments:
Post a Comment