Unlock the Power of Remote Postgres Database Servers
Postgres is a robust relational database management system capable of handling massive workloads, from a single machine to an entire data center. Its scalability and popularity make it an ideal choice for projects of all sizes. In this article, we’ll guide you through setting up a remote Postgres database server, allowing connections from any IP address.
Getting Started
Before we dive in, ensure you have:
- Familiarity with the command line interface
- An Ubuntu server (quickly provision one from DigitalOcean or any cloud provider)
- A lot of patience
- Postgres installed on a local machine
Installing Postgres
First, SSH into your server using the command ssh server_user@server_ip. Update your server packages and dependencies by running sudo apt update && sudo apt full-upgrade. Then, install Postgres with sudo apt install postgresql. This will install Postgres along with its associated dependencies.
Switch to the Postgres user by running sudo su - postgres and access the Postgres shell with psql. You’ll be presented with a shell similar to this:
Creating a New User
Exit the Postgres shell and create a new user with createuser --interactive --pwprompt. Assign a role, name, password, and superuser status as desired. For example, I created a user named “cleopatra” with superuser privileges.
Assigning the User to a Database
Create a new database and assign the user with createdb egypt -O cleopatra. This command creates a new database named “egypt” and assigns “cleopatra” as the database user.
Allowing Remote Access
Open the configuration file with your preferred editor: sudo nano /etc/postgresql/13/main/pg_hba.conf. Modify the file to allow connections from anyone by changing the value to ‘*’.
Next, open the pg_hba.conf file and modify the section to grant all database users access to all databases with any IP address range.
Finally, allow port 5432 through the firewall with sudo ufw allow 5432 and restart Postgres with sudo service postgresql restart.
Connecting to Postgres Remotely
Connect to your remote Postgres database with psql -h server_ip -U cleopatra egypt. You’ll be prompted to enter your user password. If the credentials match, you’ll be logged into the Postgres shell for “cleopatra” and database “egypt”.
Creating a New Table and Adding Records
Create a new table named “pharaohs” with CREATE TABLE pharaohs (id SERIAL PRIMARY KEY, name VARCHAR(50));. Add a record to the table with INSERT INTO pharaohs (name) VALUES ('Tutankhamun');.
Visualizing Data with TablePlus
Open TablePlus and create a new connection. Select Postgres from the dropdown, input your credentials, and ignore the SSL regions. If your credentials are correct, you’ll be shown a GUI panel to view your database records.
As shown in the image, we can see the “pharaohs” table and the record we added to it. Our remote database is now ready for use!
Security Considerations
In production, remember to implement security measures such as allowing only specified IP addresses and restricting root access to your server.