Common Postgres Tasks on CentOS 7

This guide walks you through some common tasks surrounding a Postgres server. In this tutorial, we’ll cover installing Postgres, creating new databases and users, backing up databases, and more! Let’s dig in!

Installing PostgreSQL

Installing Postgres is simple in our CentOS VPS server, thanks to the YUM package manager. Run the following command to install Postgres on your system:

yum install -y postgres-server

Configuring PostreSQL

Now that Postgres is installed, it is time to configure the service. First, Postgres requires you to initialize a database before it can be used. Fortunately, they provide a command to make this step easy for us:

postgres-setup initdb

We will set Postgres to start automatically when your server is booted. Run the following command to enable the service at boot:
systemctl enable postgres

Next, we will start the service. The following command will start Postgres if it is not already running:
systemctl start postgres

Finally, we check to make sure Postgres is running after the restart:
service postgres status

The output should display active (running) which indicates the service is up and we’re ready to proceed!

Creating a New Database and Adding a New User

Postgres runs as the “postgres” user on your CentOS Linux system, not as “root”. This is important to note because you can not interact with Postgres as root.

First, let’s switch to the “postgres” user. This user was automatically created in your system when we installed Postgres.
su - postgres

Next, we’ll create a new database. We’re calling it “mydatabase” in our example, but feel free to name this whatever you like.
createdb mydatabase

Now, we’ll add a new user for that database. In this example, we’re using the username “myusername”, but you can name this whatever you like.
createuser myusername

Granting Permissions to the PostgreSQL Database

So far, we’ve installed Postgres, created a new database, and created a new user for accessing the database. There is only one step remaining, and that is to grant permissions so that our new user will be allowed to access the database. To start this process, we must first enter the “postgres shell”. Enter the following command:
psql

After you hit enter, you should notice your command prompt has changed, indicating you are now in the Postgres shell.
psql (9.2.24)
Type “help” for help.
postgres =#

From here, we can now add permissions for our new user to access the database. Enter the following command to set a password for our user. There are few things to note. First, the single quotes around the password are required! Secondly, be sure to substitute the example my_secure_password for a legitimate secure password! Finally, be sure to include the semicolon at the end of the statement. It is easy to miss!
alter username myusername with encrypted password ‘my_secure_password’;
Postgres should reply with the following text letting you know it succeeded.
ALTER ROLE

Now that the password has been created, we will set permissions on the database. This will allow myusername to access mydatabase.
grant all privileges on database mydatabase to myusername;
Postgres will reply with the following text if the command succeeded.
GRANT

Let’s double check our work and verify the database shows up in Postgres. From the shell, run the \list command and observe the output carefully. You should see “mydatabase” in the list of databases.

We’re done! Enter the following command to leave the Postgres shell.
\quit

How to Backup a PostgreSQL Database

There are several ways to back up a Postgres database. For our example today, we will show you how to use the excellent “pg_dump” command, which outputs your entire database as a single file. Creating backups is a snap! Run the following command to backup our database. Note that we used the name “mydatabase.bak” in our example, but you can name your backup file whatever you like.
pg_dump mydatabase > mydatabase.bak

You can now easily copy or move this file wherever you like to store backups. As a matter of best practice, we strongly suggest storing your backups on a dedicated backup disk, separate from your operating system drive. This way your backups are safe and sound, even in the unlikely event of a system crash.

How to Delete a PostgreSQL Database

Deleting a database is a straightforward process. It requires you first to access the Postgres shell. A quick reminder that you can access the Postgres shell with the following command:
psql
From here, it is a single command to delete the database. Let’s delete our sample database.
drop database mydatabase
Postgres will confirm success with the following message:
DROP DATABASE

How to Restore a PostgreSQL Database from Backups

Now that we have deleted our database called “mydatabase”, let’s see how to restore that database from the backup we created. First, we need to use an empty template to house the restored database. Be sure to run the following command as your Postgres user, and from the BASH shell, (not the Postgres shell!)
createdb -T template0 mydatabase

Now that we have our template placeholder, we can import data.
psql mydatabase < /path/to/mydatabase.bak
The database has now been restored. If you run \list again from the Postgres shell, you will see “mydatabase” again appears in our list of databases!

Congratulations on making it to the end of this installment. We covered a lot of ground in this tutorial! We’ve only scratched the surface of this topic, and you can already begin to see why database administrators hold dedicated positions in many companies! Fortunately, Liquid Web is here to help you with all your database needs. Our team of pros can help advise, and manage your databases. Drop us a line if you have any questions, and thanks for reading!