PostgreSQL is scalable, reliable and accompanied by advanced optimization features. In most cases, people assume advanced optimization and data types are supported by only commercial databases like SQL Server and Oracle.<\/p>\n\n\n\n
The CentOS repository contains PostgreSQL. Note that it may not have the latest version of PostgreSQL.<\/p>\n\n\n\n
Remember, before starting to install PostgreSQL on CentOS, we need to access our virtual private server with SSH. Check out our PuTTY tutorial<\/a> if you\u2019re having trouble<\/p>\n\n\n\n It is simple to install PostgreSQL from CentOS 7 repositories. Start with the following command:<\/p>\n\n\n\n This might take some time to complete.<\/p>\n\n\n\n Once the installation is done, you can initialize the database using the below command:<\/p>\n\n\n\n After initializing the database, you can start the database using:<\/p>\n\n\n\n This completes our database installation and initialization. If required you can configure PostgreSQL to start on every system reboot automatically.<\/p>\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\n You 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 To change the password, use the below command where you add your new password instead of the NewPassword<\/strong>:<\/p>\n\n\n\n You can switch to the PostgreSQL client shell using:<\/p>\n\n\n\n Here 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\n The 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\n You 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 Here 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 In 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 In case you want to use a specific user or role to log in, use the command as shown below:<\/p>\n\n\n\n This 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\n You 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\n To delete a database, you can use:<\/p>\n\n\n\n PostgreSQL similar to other databases allows:<\/p>\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","protected":false},"excerpt":{"rendered":" PostgreSQL is scalable, reliable and accompanied by advanced optimization features. In most cases, people assume advanced optimization and data types are supported by only commercial databases like SQL Server and Oracle. Install PostgreSQL on CentOS using the standard repositories The […]<\/p>\n2. Install PostgreSQL on CentOS<\/h3>\n\n\n\n
sudo yum install postgresql-server postgresql-contrib<\/pre>\n\n\n\n
3. Initialize the Database<\/h3>\n\n\n\n
sudo postgresql-setup initdb<\/pre>\n\n\n\n
4. Start the Database<\/h3>\n\n\n\n
sudo systemctl start postgresql<\/pre>\n\n\n\n
5. (Optional) Enable PostgreSQL<\/h3>\n\n\n\n
sudo systemctl enable postgresql<\/pre>\n\n\n\n
PostgreSQL Basic Setup<\/h2>\n\n\n\n
sudo passwd postgres<\/pre>\n\n\n\n
su - postgres<\/pre>\n\n\n\n
psql -d template1 -c \"ALTER USER postgres WITH PASSWORD 'NewPassword';\"<\/pre>\n\n\n\n
psql postgres<\/pre>\n\n\n\n
createdb testDB<\/pre>\n\n\n\n
createuser samplerole \u2013pwprompt<\/pre>\n\n\n\n
createdb testDB -O samplerole<\/pre>\n\n\n\n
psql testDB<\/pre>\n\n\n\n
psql testDB -U samplerole<\/pre>\n\n\n\n
dropdb testDB<\/pre>\n\n\n\n