PostgreSQL

How to Install PostgreSQL on Debian

Debian is one of the most successful and independent linux operation system distributions and PostgreSQL is the same for relational database management systems (RDBMS). Both are independent of large corporate control and will allow you to have a free and powerful user experience to host a server and a relational database running on it securely.

In this article I will demonstrate how to install PostgreSQL on Debian. We will use the latest stable versions of both Postgres and Debian at the time of this article, and I expect the process to not vary widely for several years making this tutorial still accurate. We will use the native repo of Debian and not any custom process to have a fully Debian experience. The current Debian version is 9.6 and the current PostgreSQL version is 9.6, released in 2016. Yes that is old, but we are going with the stable versions natively provided by Debian. Also its just a complete coincidence that both Debian and PostgreSQL have the same version number at this time, please don’t read anything into that other than pure coincidence. This will ensure the most stability which is recommended for mission critical usage. I will start with a fresh install of Debian on Digital Ocean in order to ensure the system is clean and the process is reproducible.

Prepare the System

Firstly, lets do a quick apt-get update and apt-get upgrade to ensure that the system has all packages already installed up to date.

$  apt-get update
$  apt-get upgrade

Install PostgreSQL

There are numerous PostgreSQL packages you can see by running apt-cache search. The package we want to install is called just postgresql. We will install it with apt-get install.

$ apt-cache search postgres
$ apt-get install postgresql

Run dkpg to verify the install was completed and PostgreSQL related packages are installed:


$ dpkg -l | grep postgre

On Debian platform, there is a convenience service to manage PostgreSQL. So we will not be running initdb or starting and stopping the database using native commands like pg_ctl. Check the help message for the PostgreSQL service by running the command:

Starting and Stopping PostgreSQL

$ service postgresql


$ service postgresql

Before we begin trying to start and stop the processes, lets verify the configuration files. On Debian the configuration files are installed via the postgresql-common package under the location /etc/postgresql.

PostgreSQL Configuration Files

The postgresql.conf is the main database configuration file, where you can set custom settings for your installation. The pg_hba.conf is the access configuration file. These are started with sane and secure defaults for a Debian server. Notice the pg_hba.conf is configured for local access only, so you will need to update this file according to the documentation when you want to grant access to users or applications to connect to the database remotely.

Ok, lets practice stopping and starting the database with the provided service. With the service postgresql command you can provide the arguments stop, start, and status in order to control the server.

service postgresql start
service postgresql stop
service postgresql status

Connecting to the Database

By default PostgreSQL is installed in a fairly secure fashion. A linux user named postgres is created by default and this user has local access to connect to the database without any extra configuration steps. Even root can not login to the database by default. Let’s try to connect to the database as root user.

Root access denied

So rather, let us change linux user to the postgres user id, and then we can access the system.

$ su - postgresq
$ psql -l
$ psql postgres


Login as linux user: postgres

To verify the system is working, let us create a database from the command line with the createdb utility. We will then update the pg_hba.conf, restart the database and connect to this database.

As user postgres, create the new database:

$ createdb linuxhint

As user root, modify the pg_hba.conf to add the authentication for the new database.

Updated pg_hba.conf as root

Then also as user root, reload the configuration files.

$ service postgresql reload

Finally change back to user postgres and test the new database. We will test by logging into linuxhint database, creating a table, adding 2 rows, and then querying the table. As shown below.

create test table

Conclusion

What you will finally want to do, is design your database schema and configure your pg_hba.conf in order to allow external access to your database and then you are off to the races.

About the author

Linux Wolfman

Linux Wolfman is interested in Operating Systems, File Systems, Databases and Analytics and always watching for new technologies and trends. Reach me by tweeting to @linuxhint and ask for the Wolfman.