How to Manage Databases in PostgreSQL: A Complete Guide for Linux VPS Users

PostgreSQL (commonly called Postgres) is one of the most powerful, feature-rich, open-source relational database management systems available today. Trusted by startups and enterprise teams alike, it excels at handling complex queries, large datasets, and scalable application architectures. Whether you're building a SaaS platform, an e-commerce store, or a data-intensive API backend, PostgreSQL delivers the reliability and performance your project demands.

Running PostgreSQL on a high-performance VPS Hosting environment — with NVMe SSD storage, full root access, and built-in DDoS protection — gives you complete control over your database infrastructure without the overhead of managed cloud solutions. This guide walks you through every essential PostgreSQL management task, from initial access and database creation to user permissions, backups, and security hardening.

Prerequisites

Before proceeding, ensure you have:

• A Linux VPS running Ubuntu, Debian, or CentOS with PostgreSQL installed
• Root or sudo access to your server
• Basic familiarity with the Linux command line

If you haven't yet set up your server environment, AlexHost's VPS Control Panels make it straightforward to get your stack configured quickly.

1. Accessing the PostgreSQL Command-Line Interface

All PostgreSQL management tasks begin at the command-line interface (CLI), also known as psql. By default, PostgreSQL creates a system user called postgres during installation, and this account is used to authenticate with the database engine.

Step 1: Switch to the PostgreSQL System User

sudo -i -u postgres

Step 2: Launch the PostgreSQL CLI

psql

Once connected, you will see the interactive prompt:

postgres=#

This confirms you are inside the PostgreSQL environment and ready to execute SQL commands and meta-commands.

> Pro Tip: You can also connect directly without switching users by running sudo -u postgres psql from your regular shell session.

2. Creating a New Database

Databases are the top-level containers for all your tables, indexes, and stored data. Use the CREATE DATABASE statement to provision a new database.

Syntax

CREATE DATABASE database_name;

Example

CREATE DATABASE my_database;

This creates a database named my_database owned by the currently active PostgreSQL role.

Verify the Database Was Created

Use the l meta-command to list all databases on the server:

l

You will see a table showing database names, owners, encodings, and access privileges.

3. Creating and Managing Database Users

Proper user management is critical for database security. Rather than granting all applications access under the postgres superuser account, you should create dedicated users with scoped permissions.

Create a New User

CREATE USER username WITH PASSWORD 'your_secure_password';

Example

CREATE USER dbuser WITH PASSWORD 'StrongP@ssword123';

Grant Full Access to a Specific Database

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Example

GRANT ALL PRIVILEGES ON DATABASE my_database TO dbuser;

This grants dbuser complete read and write access to my_database, including the ability to create and drop tables.

List All Users

To view all existing roles and users:

du

4. Connecting to a Database

Once a database exists, you need to connect to it before you can create tables or run queries against it.

Switch to a Database

c my_database

The prompt will update to reflect the active database:

my_database=#

You are now operating within my_database and can execute all DDL and DML statements against it.

5. Creating and Managing Tables

Tables are the core structural unit of any relational database. Each table defines a schema — a set of named columns with specific data types and constraints.

Create a Table

CREATE TABLE table_name (
    column1 data_type PRIMARY KEY,
    column2 data_type,
    column3 data_type
);

Practical Example: Employee Records Table

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    department  VARCHAR(50),
    salary      NUMERIC(10, 2)
);

Here, SERIAL auto-increments the employee_id on each new row, and NOT NULL enforces that every employee must have a name.

6. Inserting, Querying, Updating, and Deleting Data

Insert a Record

INSERT INTO employees (name, department, salary)
VALUES ('Jane Smith', 'Engineering', 72000);

Query All Records

SELECT * FROM employees;

Filter Results with a WHERE Clause

SELECT name, salary FROM employees WHERE department = 'Engineering';

Update an Existing Record

UPDATE employees
SET salary = 78000
WHERE name = 'Jane Smith';

Delete a Specific Record

DELETE FROM employees
WHERE name = 'Jane Smith';

> Best Practice: Always use a WHERE clause with UPDATE and DELETE statements. Omitting it will affect every row in the table.

7. Managing Database Access and Security

Security is a first-class concern in any production PostgreSQL deployment. The principle of least privilege — granting users only the permissions they actually need — dramatically reduces your attack surface.

Revoke All Privileges from a User

REVOKE ALL PRIVILEGES ON DATABASE my_database FROM dbuser;

Grant Read-Only Access to a Specific Table

GRANT SELECT ON TABLE employees TO dbuser;

This allows dbuser to query the employees table but prevents any inserts, updates, or deletions.

Grant Specific DML Permissions

GRANT SELECT, INSERT, UPDATE ON TABLE employees TO dbuser;

Additional Security Recommendations

• Use strong, unique passwords for every database user
• Disable remote access for the postgres superuser in pg_hba.conf
• Enable SSL connections to encrypt data in transit — pair this with a trusted SSL Certificate on your server
• Regularly audit user privileges using du and dp
• Keep PostgreSQL updated to patch known vulnerabilities

8. Backing Up and Restoring Databases

Regular backups are non-negotiable for any production database. PostgreSQL provides the pg_dump and psql utilities for straightforward backup and restoration workflows.

Back Up a Database to a SQL File

pg_dump my_database > my_database_backup.sql

This exports the entire database schema and data as a plain-text SQL script.

Back Up in Compressed Format (Recommended for Large Databases)

pg_dump -Fc my_database > my_database_backup.dump

The custom format (-Fc) produces a compressed binary file and supports parallel restoration.

Restore a Database from a SQL Backup

psql my_database < my_database_backup.sql

Restore from a Custom-Format Backup

pg_restore -d my_database my_database_backup.dump

Automate Backups with a Cron Job

Schedule daily backups by adding a cron entry:

crontab -e

0 2 * * * pg_dump my_database > /var/backups/postgres/my_database_$(date +%F).sql

This runs a backup every day at 2:00 AM and appends the date to the filename for easy versioning.

9. Dropping Tables and Databases

When a table or database is no longer needed, use the DROP command to permanently remove it.

Drop a Table

DROP TABLE table_name;

Example

DROP TABLE employees;

Drop a Table Only If It Exists (Safer Syntax)

DROP TABLE IF EXISTS employees;

Drop a Database

You must disconnect from the target database before dropping it. Switch to the default postgres database first:

c postgres

Then drop the target:

DROP DATABASE my_database;

> Warning: DROP DATABASE is irreversible. Always verify you have a current backup before executing this command in production.

10. Useful PostgreSQL Meta-Commands Reference

The psql CLI includes a rich set of meta-commands (prefixed with ) that simplify navigation and inspection:

11. Exiting the PostgreSQL CLI

When you are finished with your session, exit cleanly using:

q

This returns you to the Linux shell prompt.

Why Run PostgreSQL on an AlexHost VPS?

Performance and control are the two biggest reasons to self-host PostgreSQL on a dedicated VPS rather than relying on a shared or managed database service. With AlexHost's infrastructure, you get:

• NVMe SSD storage for ultra-fast read/write operations on large datasets
• Full root access to configure PostgreSQL exactly as your application requires
• DDoS protection to keep your database server available under adverse network conditions
• Scalable resources — upgrade your CPU, RAM, and storage as your data grows

For teams managing multiple applications or client projects, Dedicated Servers offer even greater isolation, raw performance, and predictable latency for database-heavy workloads.

If you're hosting web applications alongside your PostgreSQL databases, pairing your VPS with Shared Web Hosting for static assets or front-end delivery can further optimize your architecture and reduce costs.

Conclusion

Mastering PostgreSQL database management on a Linux VPS gives you full ownership of your data infrastructure — from schema design and user permissions to automated backups and security hardening. The commands and workflows covered in this guide represent the core skill set every developer and systems administrator needs to operate PostgreSQL confidently in production.

Quick Reference Summary:

• Use CREATE DATABASE and CREATE USER to provision new resources
• Apply the principle of least privilege with GRANT and REVOKE
• Always back up with pg_dump before making destructive changes
• Use l, dt, and du to inspect your environment at a glance
• Schedule automated backups via cron to protect against data loss

Ready to put these skills into practice? Spin up a fully managed Linux environment on an AlexHost VPS Hosting plan and take complete control of your PostgreSQL deployment today.