Preliminaries

In this tutorial, we’ll show you how to install the PostgreSQL packages so that you can run your own database server and interact with it using the Postgres client.

This tutorial assumes that you’ve followed our “Getting Started With Your Stack” tutorial and have set up a non-root user with sudo privileges. Let’s get started.

Installing the Software

We should always update our package repositories before we install any packages. This ensures that we have the latest versions of packages that we install, including security patches.

sudo apt-get update

Now we can install the PostgreSQL package and the necessary development libraries that are required for software to interact with the SQL server.

sudo apt-get install postgresql postgresql-contrib

Finishing Up and Interacting With the Client

Once your installation has completed, you can verify that you have a working installation of PostgreSQL by switching to the postgres user and running the ‘psql’ command. Postgres uses the concept of ‘roles’ to authenticate with the RDBMS. During the installation, PostgreSQL created a Unix user account named ‘postgres’ that is used as the root database user for the server. Right now, we’re logged into the Stack using our own non-root sudo user. To switch to the ‘postgres’ account, run the following command:

su - postgres

Your command prompt should change to reflect that you’re using the ‘postgres’ user to issue commands. Now we can authenticate using the ‘postgres’ role. Enter psql into the command prompt and you should enter the PostgreSQL shell. Enter ‘\l’ to see the list of databases that are included by default with a successful PostgreSQL installation. You’ll see two ‘template’ databases that Postgres uses as a base when building all other databases. Enter ‘\q’ to exit the shell.

It is safer to create a new user for every application that will interact with your Postgres installation than use the ‘postgres’ account for all database tasks. This way, if someone were to gain access to one of your applications, it would not necessarily compromise the rest. To create a new role, enter the following command into your command prompt:

createuser --interactive

Follow the instructions to set a password and create your first Postgres user. You can use the non-root user that you are currently using to execute all of these commands as your postgres user name. Once you’ve created the role, you will have to create a default database for it as well. Postgres assumes that every role has an associated database. Run the following command as the ‘postgres’ user.

createdb [role name here]

Replace ‘[role name here]’ with the name of the role you created in the previous step. Your user will use this database to store and retrieve data. All you have to do now is restart the PostgreSQL server and enter ‘psql’ into the command prompt and specifying the database you wish to use.

sudo service postgresql restart
psql [database name here]

Final Words

Congratulations! You’re ready start explore the power of PostgreSQL as a RDBMS. You can start stacking existing packages on top of PostgreSQL that require a database system or just use this Stack as a remote database server and benefit from the power of separating your application and database servers. Check our our community section for more tutorials on PostgreSQL and other server & development tasks. From all of us at Stack Harbor, ahoy!