How to Manage PostgreSQL Databases and Users from the Command Line

Most webmasters prefer MySQL for web projects requiring simple relational databases. PostgreSQL is most suitable for large systems requiring complex query executions and high volume data operations.

This guide details how to manage PostgreSQL databases and users from the command line. We’ll be guiding you on how to:

  • Create Postgre users
  • Delete a Postgre user
  • Create a PostgreSQL database
  • Delete a PostgreSQL database
  • Add existing users to a PostgreSQL database

Follow this quick guide to manage your PostgreSQL databases and users remotely.

Creating PostgreSQL Users

To create a PostgreSQL database, you’d need to create a Postgre user that’ll own the database. By default, a Postgres installation creates a superuser.  You’ll have to connect to PostgreSQL as the superuser to create other users.

Follow these steps to get started.

Install PostgreSQL on your local computer. Go to the PostgreSQL download page to install the latest version of the software.

How to Manage PostgreSQL Databases and Users from the Command Line, Creating PostgreSQL Users

Open PostgreSQL’s command line

Type the command below to run commands on PostgreSQL as a superuser.

su – postgres

Use this command to create a new user (role): 

createuser –interactive –pwprompt

The program will prompt you to enter the name of the role. Type the user’s name and press Enter.

The next two prompts will request you to create and confirm a password for the new role. Type the password, then retype to confirm it.

Now assign privileges to the user in the next three prompts.

  • You can grant superuser access to the new role in the first prompt. Type y in the command line to assign the privilege, or type n to withhold this access.
  • In the next prompt, type y to permit the new user to create databases or n to deny.
  • In the last prompt, type y if you want the new user to create new roles; otherwise, type n to withhold the privilege.

PostgreSQL will create the new users with the credentials you specified.

Deleting a Postgre User

You can delete a user in PostgreSQL by using the dropuser command.

However, you can’t drop a user that owns a database. You could consider changing the database owner or dropping the database before deleting the user.

When you sort this out, use this simple command to delete a Postgre user.

dropuser username

Of course, replace the username with the name of the user you intend to delete. Press Enter to execute the command.

Creating a PostgreSQL Database

Like mentioned earlier, PostgreSQL is suitable for large and complex projects.

It’s a free and open-source database known for its flexibility, versatility, reliability and scalability. These attributes make Postgres compatible with various specialized use cases — making it a “one-size-fits-all” solution for database management.

Postgre supports both relational and non-relational data types. And here’s how to create a Postgres database.

To create one, type the command below as the server’s root user.

su – postgres

Executing the command will enable you to run Postgres commands as a superuser. Now, as a superuser, type this command to create a database.

createdb -O user dbname

Replace “user” and “dbname” with the name of the user you want to own the database and the database’s name you intend to create, respectively.

Only Postgres users with the privilege to create a database can create one. The user can create one from their account by running this command:

createdb dbname

Replace “dbname” with the name of the database you intend to create.

Deleting a PostgreSQL Database

You can delete a Postgres database by running the dropdb command. Of course, you’ll need to have superuser privileges or own the database to be able to delete it.

Use this command to delete a Postgres database:

dropdb dbname

“dbname” should be the name of the database you want to delete.

Adding Existing Users to a PostgreSQL Database

Database owners and superusers can add existing users to a Postgres database, and assign privileges to them.

You can do that by executing this grant command:

GRANT permissions ON DATABASE dbname TO username;

Replace “permissions” with the privilege you intend to grant the user, while “dbname” should be the name of the database you want to add the user to.

Also, replace “username” with the Postgres user’s name you intend to add to the database.

You can learn about all the privileges you grant existing users by visiting Postgres’ main website. 

Wrapping It Up

PostgreSQL is abundant with rich features and extensions that make it easier to create highly scalable and easy-to-administer databases. The command line allows you to manage the databases and users remotely from anywhere.

Thankfully, this guide can get you started in an instant.

Was this helpful?

What’s your goal today?

1. Find the right Managed VPS solution

If you’re looking for industry-leading speed, ease of use and reliability Try ScalaHosting with an unconditional money-back guarantee.

2. Make your website lighting fast

We guarantee to make your WordPress site load in less than 2 seconds on a managed VPS with ScalaHosting or give your money back. Fill out the form, and we’ll be in touch.

Please enter a valid name
Please enter a valid website
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

3. Streamline your clients’ hosting experience

If you’re a web studio or development agency hosting more than 30 websites, schedule a call with Vlad, our co-founder and CTO, and see how we can deliver unmatched value to both your business and your clients.

Photo

Need a custom cluster or professional advice?

Book a meeting and get a free 30-minute consultation with Vlad, co-founder & CTO of Scala Hosting, who will help you select, design and build the right solution - from a single data center cluster to a multi-region & multi-datacenter high availability cluster with hundreds of servers.

Book a free consultation

4. Learn how to grow your website in 2024

An all-star team of SEO and web influencers are sharing their secret knowledge for the first time in years. Learn about the future of SEO, Web Design best practices and the secrets to getting the foundation for your website to thrive. Watch the exclusive webinar.

An Exclusive Insiders Look Behind The SEO and Web Development Curtain

Rado
Author

Working in the web hosting industry for over 13 years, Rado has inevitably got some insight into the industry. A digital marketer by education, Rado is always putting himself in the client's shoes, trying to see what's best for THEM first. A man of the fine detail, you can often find him spending 10+ minutes wondering over a missing comma or slightly skewed design.