PpostgreSQL

Installation

We will do the installation in a linux machine (ubuntu-20.04, x86-64).

sudo apt install postgresql postgresql-contrib

Postgresql was already installed in my machine, and according to the postgresql website, it is included by default with ubuntu. After the above step completes, following can be used to check the status of the service.

:~# sudo service postgresql status ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Mon 2023-05-22 17:23:09 UTC; 8 months 0 days ago Main PID: 790509 (code=exited, status=0/SUCCESS) Tasks: 0 (limit: 77195) Memory: 0B CGroup: /system.slice/postgresql.service systemd[1]: Starting PostgreSQL RDBMS... systemd[1]: Finished PostgreSQL RDBMS.

Getting Started

Now that we installed postgresql and made sure that its running, let us create a first toy database to play around with.

PostgreSQL has deafult user named postgres . Let us start the PostgreSQL prompt as the postgres . But first, let us change the current user to postgres so that whatever we do will be in the scope of the postgres account.

root@xxxx:~# su - postgres postgres@xxxx:~$ psql psql (12.17 (Ubuntu 12.17-0ubuntu0.20.04.1)) Type "help" for help. postgres=#

Now that we are inside the prompt of the postgresql, we can list databases using the command \l as follows:

We can delete (or DROP) a database as follows:

postgres=# drop database my_toy_database; DROP DATABASE

We can update password for the default user postgres as follows: This will be necessary when we want to allow remote TCP connections to the postgres database that requires password for authentication. By default, postgres version in Ubuntu does not have any password.

postgres=# ALTER USER postgres PASSWORD 'postgres'; ALTER ROLE

We can restore a database from a .tar file using the following command.

This can also be done with the pgadmin tool which provides a graphical interface for managing postgresql database.

We can exit out of postgres terminal using either exit command or \q in the terminal.

Creating a user, database and table

Let us create a new user named tom_postgres so that we do not mess up with the original default postgres user.

postgres=# create user tom_postgres with password 'tom_postgres'; CREATE ROLE

Now, let us create a new database for which the owner is tom_postgres .

postgres=# create database tom_postgres_toy1 owner tom_postgres; CREATE DATABASE

We can list the existing databases as follows:

Now we can exit out of the current terminal, and connect open a new postgresql terminal so that we are logged in as user tom_postgres , and working against database tom_postgres_toy1 .

root@xxxx:~# psql -U tom_postgres -d tom_postgres_toy1 -h localhost Password for user tom_postgres:

After entering the passowrd in the command above, we can access the terminal as follows:

Password for user tom_postgres: psql (12.17 (Ubuntu 12.17-0ubuntu0.20.04.1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) Type "help" for help. tom_postgres_toy1=>

Let us list tables in the database

tom_postgres_toy1=> SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; table_name ------------ (0 rows)

Now let us create our first table in the database, with some columns

CREATE TABLE address_book ( id SERIAL PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100) );

If we list the tables, we can see the table.

tom_postgres_toy1=> SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; table_name -------------- address_book (1 row)

Using python client to access

Let us install a python client and write a small code to access the database.

First install python client library psycopg2-binary . Here, I first create a virtual environment and install the library in the virtual environment.

root@xxxx:/dataVol/20240121_postgresql# python3 -m venv postgres root@xxxx:/dataVol/20240121_postgresql# source postgres/bin/activate (postgres) root@xxxx:/dataVol/20240121_postgresql# python3 -m pip install psycopg2-binary Collecting psycopg2-binary Downloading psycopg2_binary-2.9.9-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (3.0 MB) |████████████████████████████████| 3.0 MB 25.3 MB/s

Then using the following code, we can list the table in the database. OR, pretty much run any command.

import psycopg2 from psycopg2 import sql db_params = { 'dbname':'tom_postgres_toy1', 'user':'tom_postgres', 'password':'tom_postgres', 'host':'localhost', 'port':'5432' } def create_connection(): try: connection = psycopg2.connect(**db_params) return connection except psycopg2.Error as e: print(f"Error: Unable to connect to the database\n{e}") return None def execute_query(connection, query): try: with connection.cursor() as cursor: cursor.execute(query) result = cursor.fetchall() return result except psycopg2.Error as e: print(f"Error: Unable to execute query\n{e}") return None if __name__=="__main__": connection = create_connection() if connection: outcomes = execute_query(connection, "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';") print(outcomes)

The output of which looks as follows:

(postgres) root@xxxx:/dataVol/20240121_postgresql# python3 1.py [('address_book',)]

Using pgAdmin

Now let us use pgAdmin , which provides a web based UI for managing postgresql database. It can be run as a container using following command. I usually put them in a script and execute the script to run the container.

root@xxxx:/dataVol/20240121_postgresql# cat run_container.sh docker run -network="host"\ -e PGADMIN_DEFAULT_EMAIL=user@domain.com \ -e PGADMIN_DEFAULT_PASSWORD=Supersecret \ -d dpage/pgadmin4

This configuration uses the same networking as the remote-host; and makes it easy to forward the port to our local machine.

Once the container runs, we can connect to the link localhost:5050 in our browser. Since I am running the container inside the virtual machine in a remote server, I need to add one more argument (-L 5050:localhost:80) when ssh-ing into the virtual machine to forward port 80 from the virtual machine (which is essentially used by the pgadmin ) to 5050 of my local computer.

The first page that pops up asks for the username and passowrd. These are the same entries that are set up in the command above that we used to run the container.

Once inside, you can set up a server. This is providing the connection info (hostname, port, username, password) to the postgres database to connect to. You can also have multiple servers connected from the pgadmin web UI.

Playing with database using pgAdmin

Since we have already seen 2 ways to interact with the database (python client and postgres terminal), we are going to see the third and the most convenient way to interacting with the database. This third technique is using the pgadmin UI.

In the pgadmin, once the server set up is complete and database imported from the server, you can right click on the database name to expose Query Tool option. This will open a new sub-tab where you can type out the SQL query and also inspect the output in the output window below.