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.
- A Linux (Ubuntu/Debian) server installed with PostgreSQL 13.
- A Postgres role and role password for accessing from the local.
- Having access to the server via command line/terminal.
- pgadmin4 for accessing from the local machine (GUI)
- 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
Now find the PostgreSQL configuration file by typing:
# psql -U mina -c 'SHOW config_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:
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.