A complete guide to PostgreSQL
PostgreSQL is nowadays all over the place — major open-source projects use PostgreSQL. So what is PostgreSQL?
PostgreSQL is a powerful, open-source object-relational database system that uses and extend the SQL language combined with many features that safely store and scale the most complicated data workloads. An object-relational database system means is a database management system similar to a relational database, but with an object-oriented database model: objects, classes, and inheritance are directly supported in database schemas and in the query language.
This feature of PostgreSQL, coupled with its stable and highly scalable architecture, makes it the go-to database for various projects.
Installing PostgreSQL
According to the official website, there are two ways to install PostgreSQL — using PostgreSQL apt repository, or using the package already included in apt. Either way works fine.
PostgreSQL apt repository
First, note your Ubuntu version. You can find it by
lsb_release -a
Then, create the file /etc/apt/sources.list.d/pgdg.list
and add a line for the repository.
deb http://apt.postgresql.org/pub/repos/apt/ YOUR_UBUNTU_VERSION_HERE-pgdg main
Import the repository signing key, and update the package lists
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
Included in the distribution
Ubuntu includes PostgreSQL by default. To install PostgreSQL on Ubuntu, use the apt-get (or other apt-driving) command:
sudo apt-get install postgresql-10
Using PostgreSQL
PostgreSQL uses the concept of ‘roles’. Roles are like the user accounts in your OS, with each having a different type of access and rights, like superuser and such. By default, the user account postgres is associated with the default role.
Now, switch over to the postgres account on your server/system by typing
sudo -i -u postgres
Now, you will be able to see that you have switched to the postgres account. Run the Postgres prompt by typing
psql
You will now be in the postgres shell which will look something like
postgres=# \q
You can exit the shell by typing ‘\q’, as shown above
Congratulations! You now have PostgreSQL database successfully installed in your system.
Some of the basic postgress shell commands that will come in handy are:
\q - to exit the postgres shell
\l - to list all the created datbases
\du - to list all the roles
To create a database and delete a database, you can do
postgres=# CREATE DATABASE db_name WITH OWNER postgres;
postgres=# DROP DATABASE db_name
Don’t forget to put the semicolon after every postgres shell statement.
Installing pgAdmin
pgAdmin is a popular and feature rich Open Source administration and development platform for PostgreSQL. It provides you with a GUI to interact with your data tables in PostgreSQL and a bunch of other stuff that would normally require a CLI.
In this post, I’m going to use Python3.6 to install pgAdmin, but the same commands will work fine with Python2.x, just change ‘pip3’ to ‘pip’ and ‘python3’ to ‘python’.
Open a terminal and type
sudo apt-get install virtualenv python3-pip libpq-dev python3-dev
cd
virtualenv -p python3 pgadmin4
cd pgadmin4
source bin/activate
pip3 install https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v3.3/pip/pgadmin4-3.3-py2.py3-none-any.whl
Now you need to configure default paths and set it to a single-user mode in the local configuration file
vim lib/python3.6/site-packages/pgadmin4/config_local.py
Here I’m using vim but you can use any text editor you like, like nano or atom for instance.
Now in config_local.py, write and save and close
import os
DATA_DIR = os.path.realpath(os.path.expanduser(u'~/.pgadmin/'))
LOG_FILE = os.path.join(DATA_DIR, 'pgadmin4.log')
SQLITE_PATH = os.path.join(DATA_DIR, 'pgadmin4.db')
SESSION_DB_PATH = os.path.join(DATA_DIR, 'sessions')
STORAGE_DIR = os.path.join(DATA_DIR, 'storage')
SERVER_MODE = False
Now, run this command while in the pgadmin4 directory
python3 lib/python3.6/site-packages/pgadmin4/pgAdmin4.py
You can now access pgAdmin4 at http://localhost/5050. You can exit the server by Ctril+C.
Now to run pgAdmin again, you will have to type
cd ~/pgadmin4
source bin/activate
python3 lib/python3.6/site-packages/pgadmin4/pgAdmin4.py
To reduce this tedious typing work, we will make an executable bash file for running pgAdmin.
touch ~/pgadmin4/pgadmin4
chmod +x ~/pgadmin4/pgadmin4
vim ~/pgadmin4/pgadmin4
Now write in the file
#!/bin/bash
cd ~/pgadmin4
source bin/activate
python lib/python3.6/site-packages/pgadmin4/pgAdmin4.py
Now you simply run pgAdmin by
~/pgadmin4/pgadmin4
Some general tweaks
Sometimes, you might get an error that postgres isn’t able to connect to the port 5432 (its default port). There are two workarounds that I found useful.
First method
Navigate to /etc/postgresql/10/main
where 10 is the version name of postgres. It can be different depending on the version you have installed. To find the version, do
psql --version
Now open the postgresql.conf
file with root access
sudo vim postgresql.conf
Here, find the port, and make sure its binded to 5432, and localhost is ‘*’.
Second method
Start pgAdmin
~/pgadmin4/pgadmin4
Here, create a new server according to the requirements of your project and enter the port accordingly. By default, postgres run on port 5432. So if nothing is mentioned, then make sure you enter 5432 as the port.
Viewing data in PostgreSQL
Again, pgAdmin to our help.
Run pgAdmin, start your database server in the background, run the application, and do database queries to alter the tables and view changes.
Now in pgAdmin, expand your database tab and look under ‘Schemas’ for your tables. Select the table you want to view, and then right click, then view data. That’s it!