RedHat Package manager<\/a> along with EPEL (Extra Packages Enterprise Linux) repositories. This is required for additional PostgreSQL dependencies.<\/p>\n\n\n\n3. Install PosgreSQL on CentOS 7 with the Downloaded Package<\/h3>\n\n\n\n
This can be installed using the below command:<\/p>\n\n\n\n
sudo yum install pgdg-centos96-9.6-3.noarch.rpm epel-release<\/pre>\n\n\n\n4. Update Yum<\/h3>\n\n\n\n
Update yum so that your changes get reflected. Use the below command for this:<\/p>\n\n\n\n
sudo yum update<\/pre>\n\n\n\n5. Complete the PostgreSQL Install Process for CentOS 7<\/h3>\n\n\n\n
This completes our prerequisites for PostgreSQL installation. Next, we can install this using the below command:<\/p>\n\n\n\n
sudo yum install postgresql96-server postgresql96-contrib<\/pre>\n\n\n\nThis completes our PostgreSQL installation.<\/p>\n\n\n\n
6. Initialize the Database<\/h3>\n\n\n\n
Next, you can initialize the database using:<\/p>\n\n\n\n
sudo \/usr\/pgsql-9.6\/bin\/postgresql96-setup initdb<\/pre>\n\n\n\n7. Restart PostgreSQL<\/h3>\n\n\n\n
You can restart PostgreSQL using:<\/p>\n\n\n\n
sudo systemctl start postgresql-9.6<\/pre>\n\n\n\n8. (Optional) Enable PostgreSQL Launch on Reboot<\/h3>\n\n\n\n
In case you want PostgreSQL to start at system reboot automatically then you can optionally use the below command:<\/p>\n\n\n\n
sudo systemctl enable postgresql-9.6<\/pre>\n\n\n\nPostgreSQL Basic Setup<\/h2>\n\n\n\n
In Linux by default, a user named postgres<\/strong> is created once PostgreSQL is installed. You can change the user\u2019s password with the following command:<\/p>\n\n\n\nsudo passwd postgres<\/pre>\n\n\n\nYou will be prompted to enter the new password twice.<\/p>\n\n\n\n
Next, you can switch to the PostgreSQL prompt and change the password for the PostgreSQL postgres user using:<\/p>\n\n\n\n
su - postgres<\/pre>\n\n\n\nIf you receive an error, you can set a valid shell on the user with the following command:<\/p>\n\n\n\n
su --shell \/bin\/bash postgres<\/pre>\n\n\n\nAfterwards, perform the same command:<\/p>\n\n\n\n
su - postgres<\/pre>\n\n\n\nTo change the password, use the below command where you add your new password instead of the NewPassword<\/strong>:<\/p>\n\n\n\npsql -d template1 -c \"ALTER USER postgres WITH PASSWORD 'NewPassword';\"<\/pre>\n\n\n\nYou can switch to the PostgreSQL client shell using:<\/p>\n\n\n\n
psql postgres<\/pre>\n\n\n\nHere you can check the list of available commands by typing \\h<\/strong>. You can use \\h<\/strong> followed by the command for which you need more information. To exit the environment you can type \\q<\/strong>.<\/p>\n\n\n\nThe createdb command lets you create new databases. Suppose we want to create a new database named testDB<\/strong> using the postgres<\/strong> Linux user. The command we would use would look like this:<\/p>\n\n\n\ncreatedb testDB<\/pre>\n\n\n\nYou can create a new role using the createuser command. Below is an example where we are creating a role named samplerole using the postgres Linux user.<\/p>\n\n\n\n
createuser samplerole \u2013pwprompt<\/pre>\n\n\n\nHere you will be prompted to set a password for the user.<\/p>\n\n\n\n
Optionally you can assign the ownership of our newly created database to a specific postgres user or role. This can be done with a command like this one:<\/p>\n\n\n\n
createdb testDB -O samplerole<\/pre>\n\n\n\nIn the above command, replace samplerole with the role you want to use.<\/p>\n\n\n\n
You can connect to this new database using the command bellow:<\/p>\n\n\n\n
psql testDB<\/pre>\n\n\n\nIn case you want to use a specific user or role to log in, use the command as shown below:<\/p>\n\n\n\n
psql testDB -U samplerole<\/pre>\n\n\n\nThis will prompt you to enter the password.<\/p>\n\n\n\n
You can use \\l<\/strong> or \\list<\/strong> commands to show all the databases. To know the current database you\u2019re using, you can use \\c<\/strong>. In case you want more information about connections such as the socket, port, etc. then you can use \\conninfo<\/strong>.<\/p>\n\n\n\nYou can also drop or delete a database using the dropdb<\/strong> command. However, remember to verify what you\u2019re deleting before doing it. Deleted databases cannot be retrieved.<\/p>\n\n\n\nTo delete a database, you can use:<\/p>\n\n\n\n
dropdb testDB<\/pre>\n\n\n\nPostgreSQL similar to other databases allows:<\/p>\n\n\n\n
- Table creation<\/li>
- Table deletion<\/li>
- Table Updates<\/li>
- Column Addition<\/li>
- Drop column<\/li>
- Query a table<\/li>
- Alter commands<\/li>
- Grant Privileges<\/li><\/ul>\n\n\n\n
The syntax for all of these commands is similar to most database commands. You can list all the tables by using the \\dt<\/strong> command. To list all roles, you use the \\du<\/strong> command. To learn more, we encourage you to read the official documentation<\/a>!<\/p>\n\n\n\nConclusion<\/h2>\n\n\n\n
With this, we have shown you PostgreSQL installation in CentOS 7 and also walked you through the basic PostgreSQL setup. There are several things that can still be learned in PostgreSQL.<\/p>\n","protected":false},"excerpt":{"rendered":"
PostgreSQL is a powerful and open source relational database platform. It\u2019s scalable across multiple platforms and is a widely used and well-loved tool. In this tutorial, we\u2019ll show you how to install PostgreSQL on CentOS 7! How Does PostgreSQL work? PostgreSQL uses SQL for relational queries and JSON for non-relational queries. One advantage of using […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13],"tags":[],"class_list":["post-2982","post","type-post","status-publish","format-standard","hentry","category-linux"],"_links":{"self":[{"href":"https:\/\/microsoftgeek.com\/index.php?rest_route=\/wp\/v2\/posts\/2982","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/microsoftgeek.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/microsoftgeek.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/microsoftgeek.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/microsoftgeek.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2982"}],"version-history":[{"count":1,"href":"https:\/\/microsoftgeek.com\/index.php?rest_route=\/wp\/v2\/posts\/2982\/revisions"}],"predecessor-version":[{"id":2983,"href":"https:\/\/microsoftgeek.com\/index.php?rest_route=\/wp\/v2\/posts\/2982\/revisions\/2983"}],"wp:attachment":[{"href":"https:\/\/microsoftgeek.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2982"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/microsoftgeek.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2982"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/microsoftgeek.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2982"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}