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


February - Postgres jsonb and Unix time stamp

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


In your db, there is a table called CUST.
It is like this:
                    Table "public.cust"
    Column   |  Type   | Collation | Nullable | Default 
  -----------+---------+-----------+----------+---------
   id        | integer |           | not null | 
   name      | text    |           |          | 
   birthinfo | jsonb   |           |          | 
  Indexes:
     "cust_pk" PRIMARY KEY, btree (id)
In it, there is data like this:
   id |   name     |             birthinfo                        
  ----+------------+------------------------------------------------------
    1 | Loris Assi | {"city": "Augusta", "date": 203644800000, "pr": "SR"}
The date information is in Unix time stamp format (milliseconds from January 1st, 1970).
You have to find a query in order to convert that date in 'yyyymmdd' format.

Then the query result should be like this:
   id |    name    | birthdate 
  ----+------------+-----------
    1 | Loris Assi | 19760615




January - Postgres backup error

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


Using Barman, we launch a backup of a Postgres cluster database called "c3".
We receive the following error:
 barman@dblor:~$ barman backup c3
 Starting backup using postgres method for server c3 in /u1/barman/c3/base/20191219T105926
 Backup start at LSN: 16/FF34BB88 (0000000100000016000000FF, 0034BB88)
 Starting backup copy via pg_basebackup for 20191219T105926
 ERROR: Backup failed copying files.
 DETAILS: data transfer failure on directory '/u1/barman/c3/base/20191219T105926/data'
 pg_basebackup error:
 pg_basebackup: initiating base backup, waiting for checkpoint to complete
 pg_basebackup: could not initiate base backup: SSL SYSCALL error: EOF detected
 pg_basebackup: removing contents of data directory "/u1/barman/c3/base/20191219T105926/data"
Which could be the reason of that error?
And what you can do in order to avoid it?



(*)
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.