For those who seek help in different areas of software and hardware platform.

How To Access and Manage PostgreSQL from pgAdmin

By default PostgreSQL is configured to be bound to localhost. Which means any attempt to connect to the PostgreSQL server from outside the machine will be refused. To make the connection possible from outside, you will need to make few changes in PostgreSQL configuration file. 

You will need to set the postgres user password if you are working on a freshly installed PostgreSQL server.

sudo -u postgres psql postgres
On postgres=# prompt, type the following to set the postgres user password:
ALTER USER postgres WITH PASSWORD 'TypeStrongPasswordHere';
Exit from postgres=# prompt:
\q

Configure PostgreSQL

First you will need to locate postgresql.conf file using the following command:

sudo find / -name postgresql.conf
This will return you the absolute path of the file, edit postgresql.conf file with any of your favorite text editor:
sudo vi /var/lib/pgsql/12/data/postgresql.conf
Uncomment the following lines, and add your postgresql server ip like below:
listen_addresses = 'localhost','your_postgresql_ip'
port = 5432

Save and close the file when you are finished.

Next, locate pg_hba.conf file using the following command:

sudo find / -name pg_hba.conf
This will return you absolute path of the file, edit pg_hba.conf file with any of your favorite text editor:
sudo vi /var/lib/pgsql/12/data/pg_hba.conf
Add the following line at the end of the file:
host    all        all             0.0.0.0/0               md5

Save and close the file when you are finished. 

Restart PostgreSQL to take changes into effect:

sudo systemctl restart postgresql-12

Now go to the pgAdmin and access your PostgreSQL database:

Enter your database server name in the Name box, then click Connection tab.

Enter the postgresql_server_ip in the Host name/address box, then enter postgres user's password and click Save button.

Share:

0 comments:

Post a Comment

Comments with links will not be published.

Video Tutorials