Connecting to PostgreSQL using pgAdmin

If you are using SSMS to connect SQL Server, you probably looking for similar tool for PostgreSQL. Well, you are in luck, for that we have pgAdmin GUI tool.

If you launching pgAdmin tool on the server where you installed PostgreSQL, it most probably launch and auto connect to the local server by default.

If it doesn't or connecting from another computer, you will have to register the server in pgAdmin tool. To do that, right click on the "Servers" node you see under object explorer window, and choose Register -> Server...

This will bring you a dialog box with connection settings. First tab you see is "General" tab.

You can give a name to the connection and specify a background color too.

Next tab is the  most important tab where you specify host address, port number and credentials.

In above screenshot I have shown how I connected from remote machine. I have entered the host IP address and port number, which is the default port number. Then I left others as defaults, such as database name (postgres) and default user name (postgres). You can choose to save your password or enter every time you connect.

Other tabs control advanced connection settings, such as SSL parameters etc. which I will not touch in this article.

So once you done click on the "Save" button and your server is registered and connected.

If you want to run a query on pgAdmin tool, you can use the query tool in there. To open it, expand the "Databases" node and select your database, right click on it and choose "Query tool".

This will open up new window/tab on the admin tool. Note that there are few tabs already opened by default.

Like in SSMS, you can run queries on here and see results in the bottom pane.

There are few other components in this admin tool, but I think I will cover them later, in a specific article to them.

Connecting to PostgreSQL using psql

In previous article we have looked into how we can install PostgreSQL on a windows machine.

Today I'm going to see how I can connect to PostgreSQL database.

There are two main ways to connect to a PostgreSQL, they are:

• Using psql -> command line based application similar to sqlcmd for SQL server
• Using pgAdmin tool -> GUI based application similar to SSMS in SQL server

Using psql

You can easily launch psql from start menu, just search for psql.

Once launch it will ask for server to connect, database to connect, port number to connect and user name and password.

Server [localhost]:

Database [postgres]:

Port [5432]:

Username [postgres]:

Password for user postgres:

If you use default in installation, just accept the default values shown in the prompt. Otherwise you will have to type the value. In above I have accepted all defaults, except password.

If you successfully connected, you will see a warning message on windows, just ignore it.

You will be presented with "postgres=#" prompt.

You can type your queries in this prompt.

Connecting Remotely

If you want to connect to postgresql server which is not installed on the machine you are working on (in other words, remote PostgreSQL machine), you need to do some additional work.

Install psql

1. First of all you need to install psql on the machine you are going to access from.
2. Download the (zipped) binaries from the postgre site -> PostgreSQL Windows
3. Extract files to a directory (e.g. C:\pgsql)
4. Inside the directory you un-zipped, you will find several folders (e.g. doc, include, pgAdmin, StackBuilder, symbols), if you prefer you can remove these, but don't remove "bin" folder.
5. You can remove all .exe files except psql.exe from bin folder, but I suggest you keep all of these for future use.
6. For easy access, add the "bin" directory path (e.g. C:\pgsql\bin) to PATH variable.

Ensure remote access

• Make sure PostgreSQL server allows remote access. To do this you need to edit pg_dba.conf file on the server. File is located in data folder of the PostgreSQL installation (e.g. \Program Files\PostgreSQL\16\data)
• Add a line to allow source IP address. Line will be like below

host all all <<your IP>>/32 trust

E.g.: host all all 192.168.2.1/32 trust

• Open postgresql.conf file (which is same folder as above) in a text editor and make sure, server is listening to all IP addresses. There should be a line like below

listen_addresses = '*'

• Restart the PostgreSQL server
• Via Windows Services (GUI)
• There will be a service called "postgresql-x64-<<version>> - PostgreSQL Server <<version>>", which you need to restart using services GUI.

• Via command line
• Stop: net stop postgresql-x64-<version>
• Start: net start postgresql-x64-<version>

• Via pg_ctl
• <<path to postgresql server installation>>\bin\pg_ctl -D "<<path to postgresql server installation>>\\data" restart
• E.g. "C:\Program Files\PostgreSQL\16\bin\pg_ctl" -D "C:\\Program Files\\PostgreSQL\\16\\data" restart

• Connecting
• Run following command on command line of the source computer

psql -h <<ip address>> -p <<port>> -U postgres postgres

psql -h 192.168.2.52 -p 5432 -U postgres postgres

Running Queries

Once you connected, you get postgres=# prompt, where you can type your queries.

In next blog, lets find out how we can use pgAdmin tool to connect to PostgreSQL server.