How to Install and Remote PostgreSQL in Nat VPS

NAtvps.id – PostgreSQL is Relational Database Management System (RDBMS) which is Open-source And famous for its ability to handle data on a large scale and reliability. Compared to MySQL, which is simpler and often used for lightweight web applications, PostgreSQL offers more sophisticated and flexible features, such as support for acid transactions, JSON data processing, and stronger replication. This makes postgreSQL better choice for applications that require high performance and more complexity in data management.

In this article, we will discuss how to install and configure PostgreSQL in Ubuntu 22.04 to start using this database system. For other OS, please adjust the steps listed.

PostgreSQL Server Installation

First, do the Ubuntu repository update using the command:

apt update

Then, install the package Postgresql postgresql-contrarib nano By using the following command:

apt install postgresql postgresql-contrib nano -y

After the installation process is complete, check again using the command:

systemctl status postgresql

Make sure that PostgreSQL has been successfully installed and has status active.

PostgreSQL configuration to receive Long distance connection

To allow the PostgreSQL server to be accessed from an external connection, there are several configuration parameters that need to be done.

First, edit the file postgreSQL.conf By using orders:

nano /etc/postgresql/14/main/postgresql.conf

In the 60th row, change the following parameters:

Uncomment by removing the sign # and change to the section ‘Localhost’ become ‘*’ To allow connections from all IP addresses.

Save the file, then configure the file pg_hba.conf

nano /etc/postgresql/14/main/pg_hba.conf

Go to the 97th row, or in the section #IPv4 Local Connection

Change the IP address 127.0.0.1/32 become 0.0.0.0/0

Save the file.

By default, PostgreSQL will make the user by name “Postgres”. In order to access the database remotely, it is necessary to add a password for this user. This is important to ensure that only legitimate users can connect to the PostgreSQL server from outside, increase security in database management.

To change the user password “Postgres”, the first step is to log in to the user.

sudo -i -u postgres

Then enter remind PostgreSQL with the following command:

psql

Change the password by executing question change:

ALTER USER postgres WITH ENCRYPTED PASSWORD 'password';

If the query is successfully executed, a response will appear Change role from PostgreSQL

Kelura from the postgreSQL prompt by entering \ Q.. Then return to the user by executing the command GO OUT.

After all the configuration is completed, Restart serve PostgreSQL on command:

systemctl restart postgresql

Make sure there is no error message that appears after doing Restart.

Port forwarding configuration

Considering we use NAT VPS, Port Forwarding configuration needs to be added to the port 5432 In order to access PostgreSQL from outside the server. Create a Port Forwarding (TCP) configuration that exposes the port 5432 (destination port).

Enter the source port as you wish. For more details, please read:

Remote Database Access PostgreSQL

For example this time using the Dbeaver Database Remote application. For other remote database applications can adjust.

Fill in the host column with the Public IP address of the NAT-VPS service, then enter the port according to the port at the previous forwarding configuration.

Do Connection test And when the status has Connected then the database remote connection is successful.

Cover

Thus this article is about the Installation Steps and Remote PostgreSQL Database in NAT VPS. If you are confused or doubtful, don’t hesitate to ask questions in the telegram group @ipv6indonesia. Thank You!