> Quizzes > Quizzes 2019 -- Oracle / Postgresql -- Best Pratices / How to

In the page Prices and Donations, you can find how to have the solutions of the quizzes.

Nella pagina Prezzi e Contatti potete vedere come avere le soluzioni dei quiz.

If it is not specified, the commands must be executed connected as a database "superuser".
About the solutions:
- could be more than one. Here is one solution that often takes into account of the performance tuning
- they could change because of the database version
- it is not warranted to be error-free in all the database environments

December - Postgres Unused Indexes

Level (*) : 3
Topic (*) : Postgres

You are connected in a Postgres 11.
Write a query reading the system catalog in order to get all the indexes matching these conditions:
- they must be never used since their creation
- not unique
- existing on the schema public in the database where you are connected

The query must return their SQL definition (you want it in case you have to re-create these indexes).
Result set example:
 CREATE INDEX t1_idx ON public.t1 USING btree (c1);
 CREATE INDEX model_gin ON public.dbelement USING gin (data jsonb_path_ops);

November - Postgres SQL Tuning

Level (*) : 3
Topic (*) : Postgres/Progr

You are connected in a postgres 11. In it, you have a partitioned table called JOU.
The partitioning is done by range on column DT_INSERT (its data type is timestamp and the partitioning is done monthly).

You have these 2 queries (today is 30th of October):
  SELECT col_1, count(*)
  FROM jou
  WHERE to_char(dt_insert,'yyyymm') >= '201910'
  GROUP BY col_1;

  SELECT col_1, count(*)
  FROM jou
  WHERE dt_insert >= (date_trunc('month', current_date))
  GROUP BY col_1;
How can you check which of these SELECT is faster?
Which database parameter you should check in order to discover if the "partition pruning" is enabled in your database?
How can you check, which of these SELECT is using the "partition pruning"?

October - Postgres connections

Level (*) : 2
Topic (*) : Postgres

You are connected in a machine where are 2 postgres installations:
one postgres 10 and one postgres 11:
  $ ps -ef |grep "bin/postg"
  postgres  1003   1  0 14:42 ?   00:00:00 /usr/lib/postgresql/11/bin/postgres ...
  postgres  3866   1  0 15:34 ?   00:00:00 /usr/lib/postgresql/10/bin/postgres ...
How can you do a connection to these databases using psql ?

September - Postgres connection

Level (*) : 2
Topic (*) : Postgres

You are connected to a Postgres database thru psql.
You are connected with the database user postgres.
How can you check which user are you using?
And how can you create a new table with the owner called user_eco (without knowing its password).

August - Postgres data files

Level (*) : 2
Topic (*) : Postgres

You are connected to a database called db_lor.
In it, you have 20 tables and you want to know all the data files (and their timestamps) associated to these tables.

Then find a "metadata query" that return an output like this (to be executed on Linux command line):
 ls -ld /var/lib/postgresql/10/main/base/13014/19441
 ls -ld /var/lib/postgresql/10/main/base/13014/19805
 ls -ld /var/lib/postgresql/10/main/base/13014/19797
 ls -ld /var/lib/postgresql/10/main/base/13014/19800

July - Postgres sequence

Level (*) : 2
Topic (*) : Postgres

In your database there is a sequence called seq_test.
Using the postgresql dictionary, which are 2 queries useful to get some information of this sequence ?

And find 2 other queries in order to get the current value of this sequence.

June - Postgres connections

Level (*) : 2
Topic (*) : Postgres

You want to know how many server processes are currently in your database cluster.
And you want to see these processes divided by database name and username.
So you have to find a query to get this information (in the "result set" you don't want to consider the server process you are using).

For istance, you want a result like this :
  datname   | usename  | count 
 db_lassi   | lassi    |    10
 db_lassi   | loris    |     4
 db_pippo   | lassi    |     1
            | postgres |     1

May - Postgres Data Files

Level (*) : 1
Topic (*) : Postgres

You want to know where are the data files of your database cluster.
You see this:
  postgres=# SELECT * FROM pg_tablespace;
    spcname   | spcowner | spcacl | spcoptions 
   pg_default |       10 |        | 
   pg_global  |       10 |        | 
So which database parameter can you see to discover where are the data files?


You should check the data_directory parameter.
postgres=# show data_directory;

Then in this case, the data files will be inside the directories in:


April - Postgres Dump & Restore

Level (*) : 2
Topic (*) : Postgres

You have 2 db cluster:
- machine_1 with postgres 10.7
- machine_2 with postgres 11.2

In machine_1, there is this database (it is quite big):
 postgres=# \l db_test_def
         List of databases
    Name     |     Owner     | ...
 ----------------------------+ ...
 db_test_def | user_test_def | ...
In the server machine_2, you have the user called user_test_def but these is no the database db_test_def.

You want to move that database from machine_1 to machine_2.
Which steps (and commands) you should do?

March - Postgres Barman

Level (*) : 2
Topic (*) : Postgres

You have a barman server version 2.4 in a debian machine.
You get two problems:
 1. You want to upgrade it to the latest version (2.6).
    How can you do it?

 2. You have a new postgres cluster database, version 11, to be backupped (until now, you
    have backupped only postgresql 10 databases).

    You are not able to backup the new cluster database and in the barman logs there is:
     2019-03-07 09:51:01,825 [30468] barman.server ERROR: ArchiverFailure:pg_receivexlog
     not present in $PATH

    Which can be the problem and How can you backup it?

February - Postgres data order

Level (*) : 1
Topic (*) : Postgres/Progr

In your postgres db, you have this table:
 (id VARCHAR(6),
  description VARCHAR(100));

 INSERT INTO t_order VALUES ('1','product 1');
 INSERT INTO t_order VALUES ('11','product 11');
 INSERT INTO t_order VALUES ('2','product 2');

You must find a query in order to extract all the data ordered as if the ID column was a NUMBER (instead of VARCHAR).
Then you want a result like this:
  id | description 
  1  | product 1
  2  | product 2
  11 | product 11


To have the order using ID column as an INTEGER, you could launch this query :
 FROM t_order

January - Postgres partition table creation

Level (*) : 2
Topic (*) : Postgres

In your postgres db, you have this partitioned table:
 # \dt
             List of relations
  Schema |   Name     | Type  |  Owner   
  public | tb         | table | postgres
  public | tb_2018_4q | table | postgres
  public | tb_2019_1q | table | postgres

 # \d+ tb
                                                           Table "public.tb"
  Column |            Type             | ... | Nullable |             Default             
  id     | integer                     |     | not null | nextval('tb_id_seq'::regclass)
  dt_ins | timestamp without time zone |     | not null |                                 
  code   | character varying(255)      |     | not null |                                 
  detail | character varying(255)      |     |          |                                 
 Partition key: RANGE (dt_ins)
 Partitions: tb_2018_4q FOR VALUES FROM ('2018-10-01 00:00:00') TO ('2019-01-01 00:00:00'),
             tb_2019_1q FOR VALUES FROM ('2019-01-01 00:00:00') TO ('2019-04-01 00:00:00')

You must create the SQL scripts in order to create a table like this.

1: easy solution
2: medium solution
3: hard (or long) solution

quiz usually suitable for Oracle DBAs
Progr: quiz usually suitable for PL/SQL or SQL Programmers
DBA/Progr: quiz suitable for Oracle DBAs and for Programmers
Postgres: regarding the Postgresql database


This website is not connected with Oracle Corporation or the official Oracle website.
Some words used here are registered trade marks of their owners.