PostgreSQL is a powerful open-source relational database system that's popular for web applications, data analytics, and more. In this guide, I'll walk you through connecting to a PostgreSQL server, creating a database and user, setting up tables, and connecting via DBeaver.

Prerequisites

  • Access to a Linux server with PostgreSQL installed
  • SSH client on your local machine
  • Basic command line knowledge

Step 1: Connect to Your Server via SSH

First, connect to your remote server using SSH:

ssh [email protected]

Enter your password when prompted. Once logged in, you'll need to access the PostgreSQL command line interface.

Step 2: Access the PostgreSQL CLI

PostgreSQL creates a default postgres user during installation. Switch to this user:

sudo -i -u postgres

Now access the PostgreSQL interactive terminal:

psql

You should now see the PostgreSQL prompt: postgres=#

Step 3: Create a New User

Let's create a dedicated user for your database operations:

CREATE USER navas WITH PASSWORD 'your_secure_password';

For development purposes, you might want to grant superuser privileges:

ALTER USER navas WITH SUPERUSER;
  • Note: In production, grant only the necessary privileges following the principle of least privilege.

Step 4: Create a Database

Create a new database owned by your user:

CREATE DATABASE navasdb OWNER navas;

Step 5: Connect to Your New Database

Connect to your newly created database:

\c navasdb

Step 6: Create Tables

Now let's create a sample table. Here's an example users table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
);

Step 7: Verify Your Setup

Check the existing tables in your database:

\dt

View the schemas:

\dn

Step 8: Exit PostgreSQL

When you're done, exit the PostgreSQL CLI:

\q

Then exit the postgres user session:

exit

Step 9: Restart PostgreSQL (If Needed)

If you've made configuration changes that require a restart:

sudo systemctl restart postgresql

Connecting with DBeaver

DBeaver is a popular database GUI tool. Here's how to connect to your PostgreSQL database:

  1. Install DBeaver if you haven't already (available at dbeaver.io)
  2. Open DBeaver and click on "New Database Connection"
  3. Select PostgreSQL from the database list
  4. Enter connection details:
    • Host: 172.184.XXX.XXX
    • Port: 5432 (default PostgreSQL port)
    • Database: navasdb
    • Username: navas
    • Password: your_secure_password
  5. Test Connection to verify everything works
  6. Click Finish to save the connection

Troubleshooting Tips

If you encounter connection issues:

  • Verify PostgreSQL is running: 
sudo systemctl status postgresql

Conclusion

You've now successfully:

  • Connected to your PostgreSQL server
  • Created a new database user
  • Established a new database
  • Created tables
  • Connected via DBeaver for graphical management

This setup gives you a solid foundation for developing applications with PostgreSQL.

Remember to always:

  • Use secure passwords
  • Follow proper privilege management in production environments
  • Regularly backup your databases

Happy databasing!