Managing PostgreSQL databases and users from the command line

PostgreSQL includes powerful command‑line tools that allow administrators to create, modify, and delete databases and users. On ruachost.com, you can manage PostgreSQL directly from the terminal for advanced control and automation.

Why Use the Command Line?

• Faster than using a web interface.

• Enables scripting and automation.

• Provides full access to PostgreSQL features.

• Essential for developers and system administrators.

Creating PostgreSQL Users

1. Log in to your server via SSH.

2. Switch to the PostgreSQL superuser:

   Bash
   
   su - postgres
   

    

3. Create a new user interactively:

   Bash
   
   createuser --interactive --pwprompt
   

   • Enter the username.

   • Enter and confirm the password.

   • Choose whether the user is a superuser.

   • Choose whether the user can create databases.

   • Choose whether the user can create other roles.

Creating PostgreSQL Databases

1. Log in as the PostgreSQL superuser:

   Bash
   
   su - postgres
   

2. Create a database owned by a specific user:

   Bash
   
   createdb -O username dbname
   

   • Replace username with the database owner.

   • Replace dbname with the database name.

???? Tip: Users with permission to create databases can run:

Bash

createdb dbname

Granting User Privileges

1. Connect to PostgreSQL:

   Bash
   
   psql -U postgres
   

2. Grant privileges:

   GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
   

   • Replace dbname with the database name.

   • Replace username with the user.

Deleting PostgreSQL Databases

1. Log in as the PostgreSQL superuser.

2. Run:

   Bash
   
   dropdb dbname
   

    

   ⚠️ Warning: This deletes the database and all its data immediately without confirmation.

Deleting PostgreSQL Users

1. Log in as the PostgreSQL superuser.

2. Run:

   Bash
   
   dropuser username
   

   ⚠️ Important: You cannot drop a user if they own any databases or objects.

   • First, reassign ownership or drop the database.