{"id":1064,"date":"2021-03-17T10:50:07","date_gmt":"2021-03-17T08:50:07","guid":{"rendered":"https:\/\/www.scalahosting.com\/kb\/?p=1064"},"modified":"2025-11-27T11:50:38","modified_gmt":"2025-11-27T09:50:38","slug":"how-to-manage-postgresql-databases-and-users-from-the-command-line","status":"publish","type":"post","link":"https:\/\/www.scalahosting.com\/kb\/how-to-manage-postgresql-databases-and-users-from-the-command-line\/","title":{"rendered":"Manage PostgreSQL via CLI Full Guide"},"content":{"rendered":"<p><span style=\"font-weight: 400;\">Most webmasters prefer MySQL for web projects requiring simple relational databases. PostgreSQL is <\/span><b>most suitable for large systems<\/b><span style=\"font-weight: 400;\"> requiring complex query executions and high volume data operations.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">This guide details how to manage PostgreSQL databases and users from the command line. <\/span><b>We\u2019ll be guiding you on how to:<\/b><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Create Postgre users<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Delete a Postgre user<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Create a PostgreSQL database<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Delete a PostgreSQL database<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Add existing users to a PostgreSQL database<\/span><\/li>\n<\/ul>\n<p><b>Follow this quick guide<\/b><span style=\"font-weight: 400;\"> to manage your PostgreSQL databases and users remotely.<\/span><\/p>\n<h2><b>Creating PostgreSQL Users<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">To create a PostgreSQL database, you\u2019d need to create a Postgre user that\u2019ll own the database. By default, <\/span><b>a Postgres installation creates a superuser<\/b><span style=\"font-weight: 400;\">.\u00a0 You\u2019ll have to connect to PostgreSQL as the superuser to create other users.<\/span><\/p>\n<p><b>Follow these steps<\/b><span style=\"font-weight: 400;\"> to get started.<\/span><\/p>\n<p><b>Install PostgreSQL<\/b><span style=\"font-weight: 400;\"> on your local computer. Go to <\/span><a href=\"https:\/\/www.enterprisedb.com\/downloads\/postgres-postgresql-downloads\" rel=\"nofollow\"><span style=\"font-weight: 400;\">the PostgreSQL download page<\/span><\/a><span style=\"font-weight: 400;\"> to install the latest version of the software.<\/span><\/p>\n<p><img decoding=\"async\" class=\"alignnone size-full wp-image-1065\" src=\"https:\/\/www.scalahosting.com\/kb\/wp-content\/uploads\/2021\/03\/2021-03-17_10h48_10.png\" alt=\"Manage PostgreSQL via CLI Full Guide, Creating PostgreSQL Users\" width=\"545\" height=\"263\" srcset=\"https:\/\/www.scalahosting.com\/kb\/wp-content\/uploads\/2021\/03\/2021-03-17_10h48_10.png 545w, https:\/\/www.scalahosting.com\/kb\/wp-content\/uploads\/2021\/03\/2021-03-17_10h48_10-300x145.png 300w\" sizes=\"(max-width: 361px) 660px, (max-width: 767px) 89vw, (max-width: 1000px) 54vw, (max-width: 1071px) 910px, 1140px\" \/><\/p>\n<p><b>Open PostgreSQL\u2019s command line<\/b><span style=\"font-weight: 400;\">.\u00a0<\/span><\/p>\n<p><b>Type the command below<\/b><span style=\"font-weight: 400;\"> to run commands on PostgreSQL as a superuser.<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">su &#8211; postgres<\/span><\/i><\/p>\n<p><b>Use this command to create a new user (role):<\/b><span style=\"font-weight: 400;\">\u00a0<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">createuser &#8211;interactive &#8211;pwprompt<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">The program will prompt you to enter the name of the role. <\/span><b>Type the user\u2019s name<\/b><span style=\"font-weight: 400;\"> and press <\/span><b>Enter<\/b><span style=\"font-weight: 400;\">.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">The next two prompts will request you <\/span><b>to create and confirm a password<\/b><span style=\"font-weight: 400;\"> for the new role. Type the password, then retype to confirm it.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Now <\/span><b>assign privileges to the user<\/b><span style=\"font-weight: 400;\"> in the next three prompts.<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">You can grant superuser access to the new role in the first prompt. Type <\/span><i><span style=\"font-weight: 400;\">y<\/span><\/i><span style=\"font-weight: 400;\"> in the command line to assign the privilege, or type <\/span><i><span style=\"font-weight: 400;\">n<\/span><\/i><span style=\"font-weight: 400;\"> to withhold this access.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">In the next prompt, type <\/span><i><span style=\"font-weight: 400;\">y<\/span><\/i><span style=\"font-weight: 400;\"> to permit the new user to create databases or <\/span><i><span style=\"font-weight: 400;\">n<\/span><\/i><span style=\"font-weight: 400;\"> to deny.<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">In the last prompt, type <\/span><i><span style=\"font-weight: 400;\">y<\/span><\/i><span style=\"font-weight: 400;\"> if you want the new user to create new roles; otherwise, type <\/span><i><span style=\"font-weight: 400;\">n<\/span><\/i><span style=\"font-weight: 400;\"> to withhold the privilege.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400;\">PostgreSQL will create the new users with the credentials you specified.<\/span><\/p>\n<h2><b>Deleting a Postgre User<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">You can delete a user in PostgreSQL <\/span><b>by using the <\/b><b><i>dropuser<\/i><\/b><b> command<\/b><span style=\"font-weight: 400;\">.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">However, you can&#8217;t drop a user that owns a database. You could consider changing the database owner or dropping the database before deleting the user.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">When you sort this out, use this simple command to delete a Postgre user.<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">dropuser username<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">Of course, <\/span><b>replace the username<\/b><span style=\"font-weight: 400;\"> with the name of the user you intend to delete. Press <\/span><b>Enter<\/b><span style=\"font-weight: 400;\"> to execute the command.<\/span><\/p>\n<h2><b>Creating a PostgreSQL Database<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">Like mentioned earlier, PostgreSQL is suitable for large and complex projects.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">It\u2019s a <\/span><b>free and open-source database<\/b><span style=\"font-weight: 400;\"> known for its flexibility, versatility, reliability and scalability. These attributes make Postgres compatible with various specialized use cases \u2014 making it a \u201cone-size-fits-all\u201d solution for database management.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Postgre <\/span><b>supports both relational and non-relational data types<\/b><span style=\"font-weight: 400;\">. And here\u2019s how to create a Postgres database.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">To create one, type the command below as the server\u2019s root user.<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">su &#8211; postgres<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">Executing the command will <\/span><b>enable you to run Postgres commands as a superuser<\/b><span style=\"font-weight: 400;\">. Now, as a superuser, type this command to create a database.<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">createdb -O user dbname<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">Replace <\/span><i><span style=\"font-weight: 400;\">\u201cuser\u201d<\/span><\/i><span style=\"font-weight: 400;\"> and <\/span><i><span style=\"font-weight: 400;\">\u201cdbname\u201d<\/span><\/i><span style=\"font-weight: 400;\"> with the name of the user you want to own the database and the database\u2019s name you intend to create, respectively.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Only Postgres users with the privilege to create a database can create one. The user can create one from their account <\/span><b>by running this command:<\/b><\/p>\n<p><i><span style=\"font-weight: 400;\">createdb dbname<\/span><\/i><\/p>\n<p><b>Replace \u201cdbname\u201d<\/b><span style=\"font-weight: 400;\"> with the name of the database you intend to create.<\/span><\/p>\n<h2><b>Deleting a PostgreSQL Database<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">You can delete a Postgres database <\/span><b>by running the <\/b><b><i>dropdb<\/i><\/b><b> command<\/b><span style=\"font-weight: 400;\">. Of course, you\u2019ll need to have superuser privileges or own the database to be able to delete it.<\/span><\/p>\n<p><b>Use this command<\/b><span style=\"font-weight: 400;\"> to delete a Postgres database:<\/span><\/p>\n<p><i><span style=\"font-weight: 400;\">dropdb dbname<\/span><\/i><\/p>\n<p><i><span style=\"font-weight: 400;\">\u201cdbname\u201d<\/span><\/i><span style=\"font-weight: 400;\"> should be the name of the database you want to delete.<\/span><\/p>\n<h2><b>Adding Existing Users to a PostgreSQL Database<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">Database owners and superusers can add existing users to a Postgres database, and assign privileges to them.<\/span><\/p>\n<p><b>You can do that by executing this <\/b><b><i>grant<\/i><\/b><b> command:<\/b><\/p>\n<p><i><span style=\"font-weight: 400;\">GRANT permissions ON DATABASE dbname TO username;<\/span><\/i><\/p>\n<p><b>Replace <\/b><b><i>\u201cpermissions\u201d<\/i><\/b><span style=\"font-weight: 400;\"> with the privilege you intend to grant the user, while <\/span><i><span style=\"font-weight: 400;\">\u201cdbname\u201d<\/span><\/i><span style=\"font-weight: 400;\"> should be the name of the database you want to add the user to.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Also, <\/span><b>replace <\/b><b><i>\u201cusername\u201d<\/i><\/b><span style=\"font-weight: 400;\"> with the Postgres user\u2019s name you intend to add to the database.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">You can learn about all <\/span><b>the privileges you grant<\/b><span style=\"font-weight: 400;\"> existing users by visiting <\/span><a href=\"https:\/\/www.postgresql.org\/docs\/9.1\/sql-grant.html\"><span style=\"font-weight: 400;\">Postgres\u2019 main website.<\/span><\/a><span style=\"font-weight: 400;\">\u00a0<\/span><\/p>\n<h2><b>Wrapping It Up<\/b><\/h2>\n<p><span style=\"font-weight: 400;\">PostgreSQL is <\/span><b>abundant with rich features<\/b> <b>and extensions<\/b><span style=\"font-weight: 400;\"> that make it easier to create highly scalable and easy-to-administer databases. The command line allows you <\/span><b>to manage the databases and users remotely<\/b><span style=\"font-weight: 400;\"> from anywhere.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Thankfully, this guide can get you started in an instant.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>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\u2019ll be guiding you on how to: Create Postgre users Delete a Postgre user [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_seopress_robots_primary_cat":"none","_seopress_titles_title":"Manage PostgreSQL via CLI Full Guide | ScalaHosting KB","_seopress_titles_desc":"","_seopress_robots_index":"","_seopress_analysis_target_kw":"","footnotes":""},"categories":[27],"tags":[],"class_list":["post-1064","post","type-post","status-publish","format-standard","hentry","category-vps-hosting"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts\/1064","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/comments?post=1064"}],"version-history":[{"count":6,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts\/1064\/revisions"}],"predecessor-version":[{"id":5850,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/posts\/1064\/revisions\/5850"}],"wp:attachment":[{"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/media?parent=1064"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/categories?post=1064"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.scalahosting.com\/kb\/wp-json\/wp\/v2\/tags?post=1064"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}