Contents

Postgres - installation

I’ve just started reading: Anthony De Barros “Practical SQL: A Beginner Guide to Storytelling with Data”. All examples in the book are using Postgresql db system. Therefore I decided it’s time to instal postgres on my machine.

I want to take a closer look to postgresql itself, and during reading I want to play a bit with with different data sets. I need to recall how to use SQL. So this is my plan for October.

Installation: postgresql

Steps needed to install postgress on ubuntu are simple. It’s one step, actually. All systemctl calls below are used to check the status of postresql service on my system (postgres server is started with some additional housekeeping processes):

1
2
3
4
5
sudo apt install postgresql postgresql-client

systemctl status postgresql
systemctl status postgresql@13-main.service
systemctl is-enabled postgresql

Administration

Adminitration of databases (creation, deletion, users management, adding/revoking priviledges) is done after logging in as postgres user:

1
sudo su - postgres

For interaction with database system one can use psql client program which can be executed for single command, for example:

Change password for user postgres

You can change the password for postgres user: psql -c "alter user postgres with password 'Che7Thi!'"

Get connection information

You can get information about current connection:

psql -c "\conninfo"

You can also use psql as interctive client; after running psql “shell” you can use both:

  • “internal” commands (with “" prefix), eg.:
    • psql -c "\?" to hest internal commands help
    • psql -c "\help create" to get sql command help
  • sql commands, eg: select * from my_table'

Create database table

After starting psql I create analysis database and analysisuser with encrypter password

CREATE DATABASE analysis; CREATE USER analysisuser WITH ENCRYPTED PASSWORD 'hae0Eis]';

… and grant all privileges to this user with regard to analysis database:

GRANT ALL PRIVILEGES ON DATABASE analysis TO analysisuser;

  • This is how I create alice user with s3cr3tp@ss and database app1 which is owned by alice. I also decide which encryption algorithm to use for password storing:

    • sudo -u postgres psql postgres
    • SET password_encryption = 'scram-sha-256';
    • CREATE USER alice PASSWORD 's3cr3tp@ss';
    • CREATE DATABASE app1 OWNER alice;

Connect to database

When trying to connect to analysis database:

psql -W -U analysisuser analysis I get following error:

1
psql: error: FATAL:  Peer authentication failed for user "analysisuser

This is because default authentication mechanizm uses current client process user (it is retrieved from the kernel) and if I am user karma, I won’t be able to log in as analysisuser.

I change (thanks, SO!) an entry in /etc/postgresql/13/main/pg_hba.conf file from:

1
local   all             all                                peer

to:

1
local   all             all                                md5

and restart postgres service:

1
sudo systemctl restart postgresql

Installation: pgadmin

Since ubuntu does not have a package with pgadmin, so in order to install, I do several steps:

  1. I add public key for apt to verify packages:
1
 sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
  1. I add appropriate package from postgres package repository to my apt sources (an by the way I learn how to check the version of my ubuntu system: lsb_release) and update my repository of packages:
1
sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
  1. The I install pgadmin desktop version:
1
sudo apt install pgadmin4 pgadmin4-desktop
  1. In order to execute pgadmin, I first checked wnat files got installed and where (dpkg -L pgadmin4-desktop), and I execute:
1
/usr/pgadmin4/bin/pgadmin4

… all right, let’s play!

Pgadmin usage

Pgadmin asks for master password so that it can use it to encrypt database credentials and not store them on disk.

pgadmin4 masterpassword dialog

Then I can connect to my database and view dashboard: pgadmin dashboard

I create some tables and check generated DDL: pgadmin dashboard

And I can run queries: pgadmin dashboard

Setup complete!

Everything is ready. Let’s dig a bit into postgres.


Ten wpis jest częścią serii sql.

Wszystkie wpisy w tej serii: