How To Install PostgreSQL on Ubuntu 16.04 LTS

Old readers can recall that we had guides for PostgreSQL with WordPress. New and old users can recall our recent guide on how to install MongoDB. PostgreSQL is time tested and often compared head to head with MongoDB. Here is how to install PostgreSQL on Ubuntu 16.04 LTS Server via SSH. In this article we will avoid the comparison but only discuss the steps to install, configure and secure PostgreSQL.

 

Steps of How To Install PostgreSQL on Ubuntu 16.04 LTS

 

SSH to the intended server and become root. In case it is public server for long term use, definitely you should follow some steps like we described before in this article to secure the server. First update, upgrade the instance :

Install PostgreSQL from the Ubuntu package repository with phppgadmin (PHP based web administration tool like PHPMyAdmin used for MySQL) :

Change the postgres user’s Linux password:

PostgreSQL uses role for user authentication and authorization in Unix-Style. By default, PostgreSQL creates a new user called “postgres” for basic authentication. The following commands are for setting a password for the postgres database user :

yourpassword is example our password. This user is distinct from the postgres Linux user which is used to access the database, and the PostgreSQL user is used to perform administrative tasks. This password set in this step will be used to connect to the database.

Alternatively, you can access the PostgreSQL prompt with the command:

And then change the password for postgres user by typing:

We can add user in this way :

We can create a sample database called testdb:

Connect to the test database:

We need to configure apache for phpPgAdmin. Follow the steps on our separate guide to install Apache2 correctly (do not install MySQL from that guide, unless needed).

Now, edit the file /etc/apache2/conf-available/phppgadmin.conf :

Search the line $conf['extra_login_security'] and change the value to false. Restart the services :

Now access phpPgAdmin with your browser http://yourip/phppgadmin/. User will be postgres, password is what you set above.

pgAdmin, PSequel, Valentina Studio are known GUI tools for desktop.

PostgreSQL uses peer authentication which means database connections will be granted to local system users that own or have privileges on the database being connected to. There is a file like :

Open that file, there will be lines like :

Replace peer with md5 to activate password authentication. We need to restart PostgreSQL.

Obviously there is huge guides, tips on official site :