How to

Access PostgreSQL Database From Local Computer

You may want to access your PostgreSQL server database from your local computer using a GUI for example pgadmin or using a database extension installed in a text editor like VS Code. In this tutorial, We will learn How to access PostgreSQL database from the local computer.

We will access the database from the pgadmin4 GUI. If you don’t have pgadmin4 setup on your computer then follow this guide to install pgadmin4 on your local machine.

Prerequisites

  1. You can also access your PostgreSQL server database using Visual Studio Code. Read this guide to access from VS Code.

Configuring the Server

First access your database server through a command line. Then switch to your database account user account (mina in my case).

root@server-ip:~# su - mina
mina@server-ip:~#

Now find the PostgreSQL configuration file by typing:

# psql -U mina -c 'SHOW config_file'
PostgreSQL configuration file

Now we have to edit the config file and change the allowed hosts to all.

# sudo nano /etc/postgresql/13/main/postgresql.conf

Note: The installation directory might be different based on your PostgreSQL installation and version.

Now find the “listen_addresses” section and change to listen_addresses = ‘*’. Save and exit the file. Now we have to edit one more file in the same directory called  pg_hba.conf.

# sudo nano /etc/postgresql/13/main/pg_hba.conf

And edit the following line to match exact:

host        all           all             0.0.0.0/0         md5
host        all           all              ::/0             md5

Save and close the file. And restart your PostgreSQL server:

$ sudo systemctl restart postgresql

Now the server is ready to connect with the pgadmin4 installed on your local computer.

Coccenting Database to pgadmin4

Start the pgadmin server from your local computer. And access pgadmin through the browser. The default link is: http://127.0.0.1:5050/.

From the left panel of pgadmin dashboard. Right click on the server and click Create > Server.

Now a configuration popup window will appear. In the general tab enter the server name (ex: PostgreSQL Remote).

Now go to the Connection tab and enter your server Ip, the desired database that you want to manage, username/role, and finally password. Click on the save.

Now connect the server from the pgadmin. You should see all the database with active database that you selected under the server.

Now you can manage, delete, update, backup, restore and perform SQL query from inside your pgadmin.

Posted in