Installation: Bad way

First install postgress from ubuntu repos (note: this will lag behind the latest version directly from postgreSQL official page but far easier to install and uninstall).

sudo apt update
sudo apt install postgresql postgresql-contrib

Installation complete. contrib package contains additional postgres utilities.

Installation: Latest Version : Right Way

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/ bionic-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
Then install Postgres-11
sudo apt -y install postgresql-11 postgresql-client-11 libpq-dev postgresql-server-dev-11

Verify installation

You can check the version installed by going here:
ls /etc/postgresql

It will show you the version installed.

The postgresql config files are located here (mine is version 11):
ls /etc/postgresql/11/main

The main config file is postgresql.conf

To check what commands to use to start/stop the postgresql service, use this:
service postgresql
The above will should you the basic usage.

For example, to start the service,
service postgresql start

to know the status:
service postgresql status

Verification of installation

To check what postgres packages got installed:
dpkg -l | grep postgres
I got something like this:

$ dpkg -l | grep postgres
ii  pgdg-keyring                                                2018.2                                                      all          keyring for apt.postgresql.org
ii  postgresql-11                                               11.7-2.pgdg18.04+1                                          amd64        object-relational SQL database, version 11 server
ii  postgresql-client-11                                        11.7-2.pgdg18.04+1                                          amd64        front-end programs for PostgreSQL 11
ii  postgresql-client-common                                    213.pgdg18.04+1                                             all          manager for multiple PostgreSQL client versions
ii  postgresql-common                                           213.pgdg18.04+1                                             all          PostgreSQL database-cluster manager
ii  postgresql-server-dev-11                                    11.7-2.pgdg18.04+1                                          amd64        development files for PostgreSQL 11 server-side programming

Uninstall

To uninstall postgres, first list all the packages installed using the above command.. then remove them all. Look at Verification of Installation section above to see what packages got installed.

To remove all those packages in one command, I do this on console:

sudo apt-get --purge remove postgresql-11 postgresql-client-11 postgresql-client-common postgresql-common postgresql-server-dev-11

Verification of Un-installation

To check what postgres packages got installed:
dpkg -l | grep postgres
This should return nothing.

Basic Config

Default user

Right after installation, use the default postgres user to create users or to check initial database
sudo su postgres
The above command will log you into postgresql as default user postgres. Now you need to start the postgresql cli to do stuff:
so type psql
psql is the command line utility that comes with postgresql

Tip: check the man pages for psql by:
man psql
Note that the command for man page has to be launched from a terminal but NOT inside the cli itself. This man page will briefly describe all the commands that are discussed later (there are meta commands too and these start with \)

Note: to exit cli: just type \q inside the cli

Inside the cli:

To list all the databases present:
\l

To list all users on the postgresql database:
\du

To alter the password for the logged in user (in this case postgres):
ALTER USER postgres WITH PASSWORD 'my_pass';
the above command will return “ALTER ROLE”. This means that password change was successful.

To create a new user:
CREATE USER helios_su WITH PASSWORD 'helios';
The above command will return “CREATE ROLE”. This means that user created successfully.

The user created in the above step will not be having any privilages. To give the user superuser privilages:
ALTER USER helios_su WITH SUPERUSER;
This will return “ALTER ROLE” which means that command has been successful. You can check the privilages by doing \du

To remove the user:
DROP USER helios_su;
This will return “DROP ROLE” which means success. You can list all users again to check.

Postgres GUI client

Bad Way First

Luddite way is to goto Ubuntu softwre center, search for pgadmin and install. Like Below.

As with all databases, its very helpful to also have a gui client. You can install directly from postgres official page for latest. I use ubuntu software center to install it. Downside is that this usually does not have the latest version. Its something I am fine with since it makes uninstallation/reinstallation very easy.

Open ubuntu software center and search for pgadmin. Then install the client.

Now, click on the electric plug symbol to create a connection (very similar to creating a connection string in SQL Server).

Fill in these values:

Name: localhost

This is the name for the connection string (or the connection itself). Since, by default the server listens on localhost, I am just creating a connection with that name.

Host: 127.0.0.1

Port:5432

Leave this one to default. 54323 is the default port the server listens to

MaintenanceDB: postgres

The above is the default database installed. Leave it to default value

Username: helios_su

The user we created above.

Password: helios

The password we set above

Leave the store password checked. And press OK. All done.

Good way .. You will get latest version of pgadmin -> pgadmin4

First add the postgres repo to Ubuntu like so:

sudo vim /etc/apt/sources.list.d/pgdg.list

This will open pgdg.list file in vim. Copy and paste these into it and save and exit:

deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main

Then on the terminal, type:

sudo apt update -y && sudo apt upgrade -y

Now you are ready to install pgadmin4 like so:

sudo apt install pgadmin4 pgadmin4-apache2

You will be prompted to enter email and password during installation.

The way to build connection string for a database is very similar to one described for “Bad way” above.

Restoring a database from a dump

The easiest way is to use the pg_restore tool. Note that restore also depends on how the dump was created. In this case, lets restore the database into a database called example_database.

I have already uncompressed the dump into a tarball. Its here ~/postgres/temp/example_database.tar

I also do cd ~/postgres/temp

Log into postgres: sudo su postgres

then enter cli: psql

first create a database. CREATE DATABASE example_database;

you can do \l to check the created database.

Exit from cli: \q

pg_restore -U postgres -d example_database example_database.rar

Note that I am in the same directory as example_database.rar. It should be extracted and restored.

To check:psql

then connect to database:\c example_database