Set Up Remote Database Connection in PostgreSQL

[aaron]

   

   

After configuring the database, set up remote access to PostgreSQL using the psql command. To do so, use this command syntax on the client machine:

   

psql -h ip_address -p port -d database -U username

   

For example, run this command to connect to the TheNewUser database hosted on a VPS with the IP address 185.185.185.185 using the TheNewUser account:

   

psql -h 185.185.185.185 -p 5432 -d TheNewUser -U TheNewUser

   

Remember that the default PostgreSQL port is 5432. To verify if the connection is successful, run this command:

   

\conninfo

   

Once connected, you can send database queries to retrieve data. To enable PostgreSQL remote access in your application, install a database driver and import the module into your code.

   

For this tutorial, we will show you how to do so in a Python application hosted on an Ubuntu 20.04 VPS. After connecting via SSH, follow these steps:

   

       
1. Install the pip package manager using this command. If you already have it, skip this step:
       

sudo apt-get install python-pip

       
2. Download and install the psycopg2 database driver using pip:
       

pip install psycopg2-binary

       
3. Add the following line to your application code to import the module and establish a connection with the PostgreSQL database. Remember to change the values accordingly:
       

import psycopg2

# Connect your app to the remote database.

conn = psycopg2.connect(host="182.182.182.182", port="5432", dbname="TestNewUser", user="TestNewUser", password="UserPassword")

# Open a cursor for database operations

cur = conn.cursor()

# Execute a query. Replace table with your own. 

cur.execute("SELECT * FROM table")

# Retrieve data

records = cur.fetchall()

   

   

    Join ellohost