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) :
sudo apt-get install postgresql postgresql-contrib phppgadmin
Change the postgres user’s Linux password:
sudo passwd postgres
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 :
su - postgres
psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'yourpassword';"
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:
su - postgres
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
Search the line
$conf['extra_login_security'] and change the value to
false. Restart the services :
systemctl restart postgresql
systemctl restart apache2
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 :
# "local" is for Unix domain socket connections only
local all all peer
md5 to activate password authentication. We need to restart PostgreSQL.
sudo service postgresql restart
su - postgres
Obviously there is huge guides, tips on official site :