Alpine Linux, Oracle Java JDK and musl?! – why it does not work…

Uncategorized
| Nov 17, 2019
Sometime ago i did some work with Alpine Linux (s. here) and i was impressed how tiny this Linux distro was and how fast it was running.


So i decided after nearly 6 years of running an aircraft noise measuring station (for dfld.de) with Ubuntu to change to Alpine Linux.

This station runs a software on Java and needs RXTX, because the microphone is connected via USB and is read over (more...)

Reading External File

Uncategorized
| Nov 13, 2019

I’m working on items for migrating my database class from Oracle to PostgreSQL. I ran into an interesting limitation when I tried using the COPY command to read an external CSV file.

I had prepared the system by creating a new directory hierarchy owned by the postgres user on top of a /u01/app mount point. I set the ownership of the directories and files with the following command from the /u01/app mount point:

chown -R  (more...)

PostgreSQL and LPAD

Uncategorized
| Nov 12, 2019

While porting my Oracle code to PostgreSQL I encountered a little quirk. It’s probably not a quirk except for the fact that I’ve worked in Oracle so long. Oracle implicitly type casts so well that we seldom notice.

PostreSQL doesn’t work like Oracle. It does type cast sometimes but not very often. I tried porting the following segment from my Oracle stored procedure to PostgreSQL:

/* Add account number with zeros. */
FOR j IN  (more...)

Ultra fast, ultra small Kubernetes on Linux – K3S beating minikube

Uncategorized
| Nov 12, 2019

imageThe easiest way I knew for running a local Kubernetes cluster was minikube. It installs like a breeze and creates a fresh clean cluster with minimal effort on my part. Sure, it takes a while to get going and uses quite a bit of system resources, but it performs quite a feat. I am quite fond of it really.

Last week I learned about k3s (https://k3s.io/) – light weight Kubernetes, for Linux (more...)

PostgreSQL Invalid Page and Checksum Verification Failed

Uncategorized
| Nov 8, 2019

At the Seattle PostgreSQL User Group meetup this past Tuesday, we got onto the topic of invalid pages in PostgreSQL. It was a fun discussion and it made me realize that it’d be worth writing down a bunch of the stuff we talked about – it might be interesting to a few more people too!

Invalid Page In Block

You see an error message that looks like this:

ERROR: invalid page in block 1226710  (more...)

Postgres Foreign Keys

Uncategorized
| Nov 6, 2019

Just sorting out how to query the information_schema to discover the magic for a query of a table’s foreign key constraints. This query works to return the foreign key constraints:

SELECT   conrelid::regclass::text AS table_from
,        conname AS foreign_key
,        pg_get_constraintdef(oid)
FROM     pg_constraint
WHERE    contype = 'f'
AND      connamespace = 'public'::regnamespace 
AND      conrelid::regclass::text = 'rental_item'
ORDER BY conrelid::regclass::text
,        conname;

It returns the following for the rental_item table:

 table_from  |   foreign_key    |                           pg_get_constraintdef                            
-------------+------------------+---------------------------------------------------------------------------
 rental_item |  (more...)

Postgres Overloaded Routines

Uncategorized
| Nov 5, 2019

Earlier I showed how to write an anonymous block in PostgreSQL PL/pgSQL to drop routines, like functions and procedures. However, it would only work when they’re not overloaded functions or procedures. The following lets you drop all routines, including overloaded functions and procedures. Overloaded procedures are those that share the same name but have different parameter lists.

Before you can test the anonymous block, you need to create a set of overloaded functions or procedures. (more...)

DBeaver for PostgreSQL

Uncategorized
| Nov 3, 2019

I’m migrating my database classes from the Oracle database to the PostgreSQL database. Using the Oracle Express Edition has always required a virtualized image because students use Windows and Mac OS. Also, the university doesn’t like my use of a virtualized image. Virtualization imposes incremental cost on students to have high end laptops.

The available Docker images don’t typically support the Oracle Express Edition. That means there are licensing implications tied to Oracle.

As a (more...)

Java and Postgres

Uncategorized
| Nov 2, 2019

I wanted to get Java working with PostgreSQL to test some GUI interfaces on Linux. Figuring out the necessary JAR file for the JDBC was my first hurdle. I found it was postgreSQL-42-2.5.jar file.

You can download it with the following command line:

wget https://jdbc.postgresql.org/download/postgresql-42.2.5.jar

I downloaded it to a Java directory off the home/student directory. Then, I added the following CLASSPATH to local java.env environment file.

 (more...)

PASS Summit 2019 is NEXT WEEK!

Uncategorized
| Oct 31, 2019

I’m getting ready for SQL Saturday Oregon this weekend, (taking the train up from Eugene to Portland…) but its about time I talked about the exhilarating and exhausting event we call PASS Summit.

This year I was lucky enough to be chosen as a speaker and a blogger.  Bloggers at the conference get the opportunity to sit in special zones during keynotes and we write about the event.  We also get special access to Microsoft (more...)

What’s new with Oracle database 18.8 versus 18.7

Uncategorized
| Oct 30, 2019

The amount of changes between version 18.7 and 18.8 is truly minimal. There’s one spare parameter renamed to an underscore parameter, which seems to be a back port of functionality created version 19:

DATABASE_VERSION                                                                                     NAME
---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------
18.8                                                                                                 _ldap_password_oneway_auth
19.2                                                                                                 _ldap_password_oneway_auth
19.3                                                                                                 _ldap_password_oneway_auth
19.4                                                                                                 _ldap_password_oneway_auth
19.5                                                                                                 _ldap_password_oneway_auth

And it seems the DBA view DBA_REGISTRY_BACKPORTS is corrupted with 18.8, there only is a synonym with that name, which (more...)

Changing Your Bash Shell Prompt and Colors

Uncategorized
| Oct 29, 2019

I enjoyed the opportunity to test out my PASS Summit shell scripting session at SQL Saturday Denver.  It was the first run on it and Glenn Berry sat in my session.  Afterwards, this wise and long-standing member of the community had a very valuable piece of constructive criticism-  change the background on my bash terminal for Azure Cloud Shell, as the black background and colors could be troublesome for some attendees.

I agree (more...)

What’s new with Oracle database 19.5 versus 19.4

Uncategorized
| Oct 29, 2019

It seems that the most eye-catching difference between Oracle database versions 19.5 and 19.4 is three underscore parameters spare parameters being changed to specifically named underscore parameters, two of them have a bug number in them which is not publicly visible.

In v$sysstat/v$sesstat, a group of statistics are renamed from ‘OS %’ to ‘Server %’. All these statistics are about networking. One changed parameter directly points to networking (tcpinfo).

One DBA view was (more...)

Postgres Drop Tables

Uncategorized
| Oct 27, 2019

While building my PostgreSQL environment for the class, I had to write a couple utilities. They do the following:

  1. Drops all the tables from a schema.
  2. Drops all the sequences from a schema that aren’t tied to an _id column with a SERIAL data type.
  3. Drops all the functions and procedures (qualified as routines) from a schema.
  4. Drops all the triggers from a schema.

The following gives you the code for all four files: drop_tables. (more...)

Getting started with Windows Subsystem for Linux, Ubuntu and Docker

Uncategorized
| Oct 23, 2019

Starting with a vanilla Windows 10 environment, it took just a few simple steps to get going with Linux on my Windows machine in the Windows Subsystem for Linux (WSL). Note: this is not yet Version 2 of wsl which is currently in (limited) preview

  • install Ubuntu App from Windows App Store
  • enable Windows Linux Subsystem feature
  • run Ubuntu (in elevated mode – as Windows Admin)
  • create Linux user
  • update Ubuntu (optional)
  • do your Linux (more...)
  • Solving accountability for ssh/linux servers without creating personal accounts

    Uncategorized
    | Oct 14, 2019

    One of the continuing issues that I see is accountability (who did what on what server) on Linux or other operating systems that are using ssh.

    The traditional solution for this problem is personal accounts, which provides logon and logoff registration.
    Personal accounts often do have the least amount of rights which means typical administration tasks can not be done with them, in order to actually do something you need to sudo to an application (more...)

    Postgres SQL Nuance

    Uncategorized
    | Oct 12, 2019

    I ran across an interesting nuance between Oracle and Postgres with the double-pipe operator. I found that the following query failed to cross port from Oracle to Postgres:

    COL account_number  FORMAT A10  HEADING "Account|Number"
    COL full_name       FORMAT A16  HEADING "Name|(Last, First MI)"
    COL city            FORMAT A12  HEADING "City"
    COL state_province  FORMAT A10  HEADING "State"
    COL telephone       FORMAT A18  HEADING "Telephone"
    SELECT   m.account_number
    ,        c.last_name || ', ' || c.first_name
    ||       CASE
               WHEN  (more...)

    Hashicorp vault and ansible: using certificate based authentication for playbooks

    Uncategorized
    | Oct 11, 2019

    In first steps with with hashicorp vault and ansible I explained how to setup Hashicorp vault for use with Ansible.

    The authentication of the playbook with Hashicorp vault in the playbooks was done in two ways:
    – using a username and password in the playbook itself (which I discourage; then the authentication is readable).
    – using a “authentication token” in the playbook.

    The “authentication token” is obtained from vault using a username and password, and (more...)

    First steps with Hashicorp Vault and Ansible

    Uncategorized
    | Oct 9, 2019

    This post is about using using hashicorp vault and ansible.

    Everyone that has used ansible knows you sometimes can’t get around storing secrets (passwords mostly) in an ansible playbook because for example an installer requires them. Or even simpler, because authentication must be done via a username and password.

    The ansible embedded solution is to use ansible vault. To me, ansible vault is a solution to the problem of storing plain secrets in an ansible (more...)

    Postgres Foreign Constraints

    Uncategorized
    | Oct 8, 2019

    You can’t disable a foreign key constraint in Postgres, like you can do in Oracle. However, you can remove the foreign key constraint from a column and then re-add it to the column.

    Here’s a quick test case in five steps:

    1. Drop the big and little table if they exists. The first drop statement requires a cascade because there is a dependent little table that holds a foreign key constraint against the primary key column (more...)