How to Host PostgreSQL on a Dedicated Server: A Step-by-Step Guide

Learn how to set up and configure PostgreSQL on a dedicated Ubuntu server, and then connect it to a Node.js application. This guide walks you through each step, from creating a server to installing PostgreSQL, setting up user access, and connecting with Node.js.

PostgreSQL Setup Steps

1. Create a Ubuntu Server on any cloud platform.

2. Log in to the server using SSH:

ssh root@server_ip

3. Update the server:

sudo apt update
sudo apt upgrade

4. Install PostgreSQL:

sudo apt install postgresql postgresql-contrib

5. Switch to the PostgreSQL user:

sudo su - postgres

6. Open the PostgreSQL shell:

psql

7. Set up the database and user:

• Set a password for the PostgreSQL user: \password postgres
• Create a new database: CREATE DATABASE mydb;
• Create a new user: CREATE ROLE myuser WITH PASSWORD 'mypassword';
• Grant privileges to the new user: GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
• Give superuser privileges to the new user: ALTER ROLE myuser WITH SUPERUSER;

8. Configure PostgreSQL to accept connections:

Find the configuration file:

sudo find / -name "postgresql.conf"

Then, edit it:

sudo nano /path/to/postgresql.conf

Find the line #listen_addresses = 'localhost', uncomment it, and change it to listen_addresses = '*'.

9. Update access settings:

Find the file:

sudo find / -name "pg_hba.conf"

Edit it to allow controlled access:

sudo nano /path/to/pg_hba.conf

At the end of the file, add this line:

host    all             all             0.0.0.0/0               md5

10. Restart PostgreSQL

sudo service postgresql restart

Connecting with Node.js

11. Create a new directory for your Node.js project and navigate into it:

mkdir pg-db-connect
cd pg-db-connect

12. Initialize a new Node.js project:

npm init -y

13. Install the pg package:

npm install pg

14. Create a database.js file:

Create a file named database.js with the following content:

const { Pool } = require('pg');

const pool = new Pool({
    user: 'username',
    host: 'server_ip',
    database: 'db_name',
    password: 'password',
    port: 5432,
});

module.exports = pool;

15. Create an index.js file:

Create a file named index.js with the following content:

const pool = require('./database');

async function createUserTable() {
    try {
        await pool.query(`CREATE TABLE IF NOT EXISTS users (
            id SERIAL PRIMARY KEY,
            name VARCHAR(50),
            age INTEGER
        )`);

        // Uncomment the following line to insert a user
        // await pool.query(`INSERT INTO users (name, age) VALUES ($1, $2)`, ['Kunal', 18]);

        const result = await pool.query('SELECT * FROM users');
        console.log('Users:', result.rows);

        // console.log('User created successfully!');
    } catch (err) {
        console.error('Error creating user:', err);
    } finally {
        pool.end(); // Close the pool when done
    }
}

createUserTable();

16. Run the Node.js application:

node index.js

This will create the users table if it doesn’t exist, and then display all users in the database.

Security Notes

Remember to keep your server updated and secure, and never share sensitive information like database credentials in public repositories.

Conclusion

You now have a fully functional PostgreSQL server running on your dedicated Ubuntu server, with a Node.js application that can connect to it. This setup provides a solid foundation for building scalable web applications with reliable database storage.