{"id":29934,"date":"2022-05-02T15:30:23","date_gmt":"2022-05-02T15:30:23","guid":{"rendered":"https:\/\/www.devopsschool.com\/blog\/?p=29934"},"modified":"2022-12-23T05:53:02","modified_gmt":"2022-12-23T05:53:02","slug":"postgresql-installation-and-configuration-tutorial-guide","status":"publish","type":"post","link":"https:\/\/www.devopsschool.com\/blog\/postgresql-installation-and-configuration-tutorial-guide\/","title":{"rendered":"postgresql Installation and Configuration Tutorial Guide"},"content":{"rendered":"\n

How To Install and Use PostgreSQL on Ubuntu?<\/h2>\n\n\n\n

Step 1 \u2014 Installing PostgreSQL<\/h3>\n\n\n\n
\n\n\n
$ sudo apt update\n$ sudo apt install postgresql postgresql-contrib\n$ sudo systemctl start postgresql.service<\/code><\/span><\/pre>\n\n\n
\n\n\n\n
Step 2 \u2014 Using PostgreSQL Roles and Databases<\/h3>\n\n\n\n
The installation procedure created a user account called postgres <\/strong>that is associated with the default Postgres <\/strong>role. In order to use Postgres, you can log into that account.<\/p>\n\n\n\n
\n\n\n
Switch over to the postgres account on your server by typing:\n\n$ sudo -i -u postgres\n\nYou can now access a Postgres prompt immediately by typing:\n$ psql\n\nThis will log you into the PostgreSQL prompt, and from<\/span> here you are free to interact with<\/span> the database management system right away.\n\nExit out of<\/span> the PostgreSQL prompt by typing:\n> \\q\nThis will bring you back to the postgres Linux command prompt.\n\nAccessing a Postgres Prompt Without Switching Accounts\n$ sudo -u postgres psql\n> \\q<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
\n\n\n\n
Step 3 \u2014 Creating a New Role<\/h3>\n\n\n\n
Currently, you just have the postgres role configured within the database. You can create new roles from the command line with the createrole command. The –interactive<\/strong> flag will prompt you for the name of the new role and also ask whether it should have superuser permissions.<\/p>\n\n\n\n
\n\n\n
Currently, you just have the postgres role configured within the database. You can create new<\/span> roles from the command line with the createrole command. \n\nThe --interactive flag will prompt you for<\/span> the name of the new<\/span> role and<\/span> also ask whether it should have superuser permissions.\n\nIf<\/span> you are logged in as<\/span> the <strong>postgres <\/strong>account, you can create a new<\/span> user by typing:\n$ createuser --interactive\n\nIf<\/span>, instead, you prefer to use<\/span> sudo<\/span> for<\/span> each<\/span> command<\/span> without<\/span> switching<\/span> from<\/span> your<\/span> normal<\/span> account<\/span>, type<\/span>:\n$ sudo<\/span> -u<\/span> postgres<\/span> createuser<\/span> --interactive<\/span>\nOutput<\/span>\nEnter<\/span> name<\/span> of<\/span> role<\/span> to<\/span> add<\/span>: sammy<\/span>\nShall<\/span> the<\/span> new<\/span> role<\/span> be<\/span> a<\/span> superuser<\/span>? (y<\/span>\/n<\/span>) y<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
\n\n\n\n
\"\"<\/figure>\n\n\n\n
Step 4 \u2014 Creating a New Database<\/h3>\n\n\n\n
Another assumption that the Postgres authentication system makes by default is that for any role used to log in, that role will have a database with the same name which it can access.<\/strong><\/p>\n\n\n\n
This means that, if the user you created in the last section is called sammy, that role will attempt to connect to a database which is also called \u201csammy\u201d by default. <\/strong>You can create the appropriate database with the createdb command.<\/p>\n\n\n\n
If you are logged in as the postgres account, you would type something like:
$ createdb sammy.<\/p>\n\n\n\n
If, instead, you prefer to use sudo for each command without switching from your normal account, you would type:
$ sudo -u postgres createdb sammy<\/p>\n\n\n\n
Step 5 \u2014 Opening a Postgres Prompt with the New Role<\/h3>\n\n\n\n
To log in with ident based authentication, you\u2019ll need a Linux user with the same name as your Postgres role and database.<\/p>\n\n\n\n
If you don\u2019t have a matching Linux user available, you can create one with the adduser command. You will have to do this from your non-root account with sudo privileges (meaning, not logged in as the postgres user):<\/p>\n\n\n\n
\n\n\n
$ sudo adduser sammy\n\nOnce this new<\/span> account is available, you can either switch<\/span> over and<\/span> connect to the database by typing:\n\n$ sudo -i -u sammy\npsql\nOr<\/span>, you can do<\/span> this inline:\n$ sudo -u sammy psql\n\n<strong>If<\/span> you want your user to connect to a different database, you can do<\/span> so by specifying the database like this:<\/strong>\n\npsql -d postgres\nOnce logged in, you can get check your current connection information by typing:\n\n\\conninfo\nOutput\nYou are connected to database \"sammy\"<\/span> as<\/span> user \"sammy\"<\/span> via socket in \"\/var\/run\/postgresql\"<\/span> at port \"5432\"<\/span>.\nThis is useful if<\/span> you are connecting to non-default<\/span> databases or<\/span> with non-default<\/span> users.<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
\n","protected":false},"excerpt":{"rendered":"
How To Install and Use PostgreSQL on Ubuntu? Step 1 \u2014 Installing PostgreSQL Step 2 \u2014 Using PostgreSQL Roles and Databases The installation procedure created a user account called postgres… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_joinchat":[],"footnotes":""},"categories":[2],"tags":[],"class_list":["post-29934","post","type-post","status-publish","format-standard","hentry","category-uncategorised"],"_links":{"self":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/29934","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/comments?post=29934"}],"version-history":[{"count":5,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/29934\/revisions"}],"predecessor-version":[{"id":29943,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/posts\/29934\/revisions\/29943"}],"wp:attachment":[{"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/media?parent=29934"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/categories?post=29934"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.devopsschool.com\/blog\/wp-json\/wp\/v2\/tags?post=29934"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}