Configuring Remote Access to PostgreSQL Database from Your Local Server

What is PostgreSQL?

PostgreSQL, often referred to as Postgres, is a powerful, open-source relational database management system (RDBMS) known for its reliability, extensibility, and advanced features. It has gained popularity among developers and organizations for its ability to handle complex data and support various data types, making it a versatile choice for a wide range of applications.

Key Features of PostgreSQL

• Open Source: PostgreSQL is open-source software, which means it is free to use and has a vibrant community of contributors and users.

• ACID Compliance: It adheres to the ACID (Atomicity, Consistency, Isolation, Durability) properties, ensuring data integrity and reliability.

• Extensibility: PostgreSQL allows you to define custom data types, operators, functions, and procedural languages, making it highly extensible.

• Support for JSON and JSONB: It provides robust support for storing and querying JSON data, making it suitable for modern web and mobile applications.

• Advanced Indexing: PostgreSQL offers various indexing techniques, including B-tree, Hash, GiST, GIN, and SP-GiST, to optimize query performance.

• Full-Text Search: It includes powerful full-text search capabilities, enabling efficient searching of text documents.

• Geospatial Support: PostgreSQL supports geospatial data types and functions, making it suitable for geographic information system (GIS) applications.

• Highly Customizable: You can configure PostgreSQL to suit your specific needs, whether you're running a small-scale application or a large enterprise system.

• Now that we have a basic understanding of PostgreSQL let's proceed with the step-by-step guide on configuring remote access to a PostgreSQL database from your local server.

In today's data-driven world, remote database access is a crucial requirement for many businesses and developers. PostgreSQL, an open-source relational database management system, offers robust features and security. In this guide, we will walk you through the steps to configure remote access to a PostgreSQL database hosted on a remote server from your local server.

Prerequisites

Before we begin, ensure you have:

• Administrative access to both the remote and local servers.

• PostgreSQL installed on both servers.

Step 1: Configuring the Remote PostgreSQL Server

1.1. Update postgresql.conf

On the remote server, locate the postgresql.conf file. Typically, it's found in the PostgreSQL data directory, often /etc/postgresql/{version}/main/ or /var/lib/pgsql/{version}/data/.

Open the postgresql.conf file using your preferred text editor. You may need superuser privileges.

Locate the listen_addresses directive and set it to '*':

listen_addresses = '*'

This allows PostgreSQL to listen on all available network interfaces. If you want to allow only a specific address, put this IP address instead of ‘*’.

1.2. Update pg_hba.conf

Open the pg_hba.conf file located in the same directory as postgresql.conf.

Add an entry to allow your local server's IP address to connect to the remote database. This line permits access to all databases (all) and users (all) from your local server's IP address with password authentication (md5):

host    all    all    LOCAL_SERVER_IP/32    md5

Replace LOCAL_SERVER_IP with your local server's IP address. If you don’t know the IP your server, you can visit https://whatismyipaddress.com/. It will show the IP address of your server. Use IPv4 as the local server IP. If you want to allow it for all of the public users, you can put ‘0.0.0.0/32’. It allows to acces your databases from any of local machines that means anyone can access your database with the credentials you set initially.

1.3. Restart PostgreSQL

After making these changes, restart the PostgreSQL server on the remote machine to apply the configuration changes:

On Linux, use a command like sudo systemctl restart postgresql or sudo service postgresql restart.

Step 2: Configuring the Local Server

Option (A): Using Command Line Tool

2.1. Install PostgreSQL Client

If you haven't already, install the PostgreSQL client on your local server. On most Linux distributions, you can do this with:

sudo apt-get install postgresql-client   # For Debian/Ubuntu

2.2. Connect to the Remote Database

Use the psql command-line tool to connect to the remote PostgreSQL database. Replace USERNAME, REMOTE_SERVER_IP, and DATABASE_NAME with the appropriate values:

psql -h REMOTE_SERVER_IP -U USERNAME -d DATABASE_NAME

You'll be prompted to enter the password for the specified username.

Option (B): Using Graphical Interface

PpgAdmin 4 is a popular open-source administration and management tool for PostgreSQL, which is a powerful open-source relational database management system (RDBMS). pgAdmin 4 is designed to provide a user-friendly interface for users to interact with PostgreSQL databases and perform various administrative tasks.

pgAdmin 4 is a web-based application, meaning it can be accessed through a web browser, making it platform-independent and accessible from different devices. It provides both a GUI (Graphical User Interface) and SQL command-line interfaces for interacting with PostgreSQL.

You can follow the official documentations of pgAdmin4 (https://www.pgadmin.org/docs/) instructions to install pgAdmin4 on your machine. If your are using windows and install your PostgreSQL via EDB, you can use stackbuilder of EDB to install pgAdmin4 directly.

Open your pgAdmin4 and provide the password (setup during the installation) to access the GUI interface. Now, you will see Servers in the leaflet panel. Right click it and then Register > Server.

A popup window will appear. Now, provide the name of your server. Any name you can use. Now, click on Connection Tab. Here you need to put the details of your remote server that you want to access.

In the "Host name/address" field, enter the IP address or domain name of the remote server where PostgreSQL is running.

In the "Port" field, specify the PostgreSQL server port (the default is 5432).

In the "Maintenance database" field, enter the name of the PostgreSQL database you want to connect to. This can be the default database, such as "postgres."

In the "Username" field, enter your PostgreSQL username.

In the "Password" field, enter your PostgreSQL password.

SSL Settings (optional): Depending on your server's configuration, you may need to configure SSL settings under the "SSL" tab. This is especially important for secure connections.

Click the "Save" button to save the server configuration. After saving, you can right-click on the server name you just created in the left sidebar and select "Connect Server" to establish a connection to the remote PostgreSQL server.

You may be prompted to enter your PostgreSQL password again for authentication.

Once successfully connected, you should see your remote server listed under "Servers" in pgAdmin 4, and you can expand it to view the databases, tables, and other objects on the remote server.

Conclusion

Configuring remote access to a PostgreSQL database from your local server is essential for collaboration and application development. By following these steps, you can securely connect to a remote PostgreSQL database and harness its power for your projects.

Remember to consider security best practices, such as firewall rules and encryption, to safeguard your data during remote access.

Now, you're ready to access your remote PostgreSQL database and unlock its potential for your applications!

Share To