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 CentOS repository contains PostgreSQL. Note that it may not have the latest version of PostgreSQL.

1. Access Your Server

Remember, before starting to install PostgreSQL on CentOS, we need to access our virtual private server with SSH. Check out our PuTTY tutorial if you’re having trouble

2. Install PostgreSQL on CentOS

It is simple to install PostgreSQL from CentOS 7 repositories. Start with the following command:

sudo yum install postgresql-server postgresql-contrib

This might take some time to complete.

3. Initialize the Database

Once the installation is done, you can initialize the database using the below command:

sudo postgresql-setup initdb

4. Start the Database

After initializing the database, you can start the database using:

sudo systemctl start postgresql

5. (Optional) Enable PostgreSQL

This completes our database installation and initialization. If required you can configure PostgreSQL to start on every system reboot automatically.

sudo systemctl enable postgresql

PostgreSQL Basic Setup

In Linux by default, a user named postgres is created once PostgreSQL is installed. You can change the user’s password with the following command:

sudo passwd postgres

You will be prompted to enter the new password twice.

Next, you can switch to the PostgreSQL prompt and change the password for the PostgreSQL postgres user using:

su - postgres

To change the password, use the below command where you add your new password instead of the NewPassword:

psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'NewPassword';"

You can switch to the PostgreSQL client shell using:

psql postgres

Here you can check the list of available commands by typing \h. You can use \h followed by the command for which you need more information. To exit the environment you can type \q.

The createdb command lets you create new databases. Suppose we want to create a new database named testDB using the postgres Linux user. The command we would use would look like this:

createdb testDB

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.

createuser samplerole –pwprompt

Here you will be prompted to set a password for the user.

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:

createdb testDB -O samplerole

In the above command, replace samplerole with the role you want to use.

You can connect to this new database using the command bellow:

psql testDB

In case you want to use a specific user or role to log in, use the command as shown below:

psql testDB -U samplerole

This will prompt you to enter the password.

You can use \l or \list commands to show all the databases. To know the current database you’re using, you can use \c. In case you want more information about connections such as the socket, port, etc.  then you can use \conninfo.

You can also drop or delete a database using the dropdb command. However, remember to verify what you’re deleting before doing it. Deleted databases cannot be retrieved.

To delete a database, you can use:

dropdb testDB

PostgreSQL similar to other databases allows:

• Table creation
• Table deletion
• Table Updates
• Column Addition
• Drop column
• Query a table
• Alter commands
• Grant Privileges

The syntax for all of these commands is similar to most database commands. You can list all the tables by using the \dt command. To list all roles, you use the \du command. To learn more, we encourage you to read the official documentation!