ManualiOracle.it
  
 
> 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


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?

Solution

You should check the data_directory parameter.
Example:
postgres=# show data_directory;
	       data_directory
	-----------------------------
	 /var/lib/postgresql/10/main

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

	/var/lib/postgresql/10/main/base/



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


In your postgres db, you have this table:
 CREATE TABLE t_order
 (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

Solution

To have the order using ID column as an INTEGER, you could launch this query :
 SELECT * 
 FROM t_order
 ORDER BY id::INT;



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.



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

Topic:
DBA:
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 authorized by Oracle Corporation and it is not connected to the official Oracle website.
Some words used here are registered trade marks of their owners.