PostgreSQL is a powerful, enterprise-grade relational database. Your server comes with PostgreSQL 16.13 pre-installed and active. This guide covers how to access your database, manage users, and perform essential database operations.
1. Verifying Your Installation
To ensure your database server is running correctly, use the verification command in your terminal:
# Check version and status
psql --version && systemctl is-active postgresql
Expected Output: psql (PostgreSQL) 16.13 and active.
2. Accessing the PostgreSQL Shell
PostgreSQL uses its own internal command-line interface called psql. To access it, you must switch to the postgres system user.
Log in to the database shell:
sudo -u postgres psql
Once logged in, your terminal prompt will change to postgres=#.
3. Essential PostgreSQL Cheat Sheet
Use these commands while inside the postgres=# prompt to manage your database.
| Goal | Command |
| List all databases | \l |
| Switch to a database | \c database_name |
| Create a database | CREATE DATABASE my_database; |
| Create a user | CREATE USER my_user WITH PASSWORD 'secure_password'; |
| Grant permissions | GRANT ALL PRIVILEGES ON DATABASE my_database TO my_user; |
| Exit psql shell | \q |
Important: Every command inside the
psqlshell must end with a semicolon (;).
4. Security Best Practices
By default, PostgreSQL is configured to allow connections only from the local server (localhost). This is a security feature to protect your data.
Secure Access
-
Never use the
postgressuperuser for your actual application. Create a dedicated user for each application (as shown in the table above). -
Strong Passwords: Always use randomly generated, long passwords for your database users.
External Access (Use Caution)
If you need to connect to your database from a remote computer (e.g., using pgAdmin or DBeaver), do not open the port to the entire internet.
-
Use an SSH Tunnel instead of opening the database port.
-
If you must open the port, edit
/etc/postgresql/16/main/postgresql.confandpg_hba.confto restrict access strictly to your specific IP address.
5. Connecting via GUI Tools (Recommended)
While the terminal is powerful, most users prefer graphical interfaces for easier data management. We recommend:
-
pgAdmin 4: The most popular open-source tool for PostgreSQL.
-
DBeaver: A universal database tool that works perfectly with Postgres.
To connect:
-
Open your GUI tool.
-
Use SSH Tunnel mode (requires your server IP, SSH username, and password).
-
Set the Host to
localhostand Port to5432. -
Enter the database credentials you created in Step 3.
6. Troubleshooting
-
"Command not found": Ensure
psqlis installed by runningsudo apt update && sudo apt install postgresql. -
"Peer authentication failed": This happens when you try to log in as a database user without proper permissions. Ensure you are using
sudo -u postgres psql. -
Database not starting: Check logs with:
sudo tail -n 50 /var/log/postgresql/postgresql-16-main.log.
Need Further Assistance?
If you are having trouble connecting your application to the database, or need help with complex SQL migrations, our support team is here to help.
[Open a Support Ticket at HostGraber] | [Official PostgreSQL Documentation]
Pro-Tip: If your application is running on the same server, always use localhost as the database host. It is faster and more secure than connecting via the server's public IP address.
