PostgreSQL is a relational database system. It is available for free under an open source BSD license. A company does not control PostgreSQL, but it bases its development on a globally distributed community, as well on several developers.
PostgreSQL allows the use of procedural languages to execute code blocks directly in the database server. It can be used to create user-defined functions (subroutines, triggers, aggregates and window functions) or to create ad-hoc blocks “DO”. Standard PostgreSQL installation allows you to use the following languages:
PL / pgSQL, a language similar to PL / SQL in Oracle.
PL / Tcl, provides Tcl
PL / Perl, makes Perl available
PL / Python, provides Python version 2 or 3.
Other languages available outside of the basic package include: PL / Java, PL / PHP, PL / Ruby, etc. A database consists of an organized data collection for one or more uses, usually in digital form. A database management system (DBMS) comprises of software that operates databases, providing storage, access, security, backup, and other facilities.
Postgresql installation
Although outside the scope of this document, Mac users can install PostgreSQL using Homebrew.
Windows users can use the graphical installer here: http://www.postgresql.org/download/windows/.
Please note that the documentation assumes that users are running the QGIS application on Ubuntu.
How to install PostgreSQL on Ubuntu
1. Connect to your root user Ubuntu server with your preferred SSH client
2. Update the operating system first with all the latest command packages
sudo apt-get update
3. Install PostgreSQL with the command and wait for the installation process to be done.
apt-get -y install PostgreSQL PostgreSQL-contrib
4. You have finished installing Postgresql.
5. Restart the service with the command.
service PostgreSQL restart
Once the installation is complete, run this command to become the Postgres user, and create a new user for the database:
sudo su – Postgres
Enter the password when prompted (you need sudo rights).
Now, at the Postgres user’s prompt, create the user for the database. Make sure the user name is the same as the UNIX authentication: it will make your life a lot easier because Postgres will automatically sign you in when you are authenticated as that user:
create user -d -E -i -l -P -r -s qgis
Enter a password when prompted. You should use a different password for your account password.
What are these options?
-d, –createdb role can create new databases
-E, -encrypted encrypt stored password
-i, –inherit role inherits privileges of roles it is a member of (default)
-l, –login role can login (default)
-P, –pwprompt assign a password to a new role
-r, –createroll roles can create new roles
-s, –superuser role will be superuser
Now you should exit the Postgres bash console by typing:
exit
If we want to see all existing databases, we execute the prompt Postgres = # command \ list
If we want to see all the users and their roles, we do
If we want to leave the psql shell we run \ q. We are back to the bash shell.