How to Connect to PostgreSQL and Create a Database, User, and Tables

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 [e-mail]

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!

Comments

[Dancan Ngare]

Great article. I'm keeping this for future reference.

[Emmanuel Kiriinya]

Saving this as my cheatsheet