PostgreSQL Creating Schema

Uncategorized
| Dec 10, 2019

The process of creating a schema requires you grant the CREATE ON DATABASE privilege to the user as the postgres user. You use the following syntax:

GRANT CREATE ON DATABASE videodb TO student;

As the student user, you create the app schema with the following syntax:

CREATE SCHEMA app;

Then, you can query the result as follows:

SELECT   * 
FROM     pg_catalog.pg_namespace
ORDER BY nspname;

You should see the following:

      nspname       | nspowner |               nspacl                
 (more...)

PostgreSQL WITH to INSERT

Uncategorized
| Dec 3, 2019

This demonstrates how you insert results from a common table expression (CTE) in a leading WITH clause. I thought it would be a nice add since the existing tutorials didn’t have an example.

Create the message table, like this:

CREATE TABLE message
( message_id    SERIAL
, message_text  VARCHAR );

Now, here’s a CTE with a two fabricated rows:

WITH cte AS
( SELECT 'x-ray' AS msg
  UNION ALL
  SELECT 'MRI' AS msg )
INSERT INTO  (more...)

PostgreSQL Upsert Advanced

Uncategorized
| Nov 25, 2019

Nine years after writing how to use the MERGE statement in Oracle, I am writing how you implement an UPSERT statement in PostgreSQL. I wrote an initial post going over the basics of PostgreSQL’s upsert implementation of the INSERT statement with an DO UPDATE clause and a DO NOTHING clause.

I thought it was interesting that the PostgreSQL Upsert Using INSERT ON CONFLICT Statement web page didn’t cover using a subquery as the source for (more...)

PostgreSQL Upsert Intro

Uncategorized
| Nov 24, 2019

Oracle and SQL Server use the MERGE statement, MySQL uses the REPLACE INTO statement or ON DUPLICATE KEY, but PostgreSQL uses an upsert. The upsert isn’t a statement per se. It is like MySQL’s INSERT statement with the ON DUPLICATE KEY clause. PostgreSQL uses an ON CONFLICT clause in the INSERT statement and there anonymous block without the $$ delimiters.

The general behaviors of upserts is covered in the PostgreSQL Tutorial. It has the (more...)

How to Use IAM authentication for RDS PostgreSQL with Glue ETL Jobs

Uncategorized
| Nov 21, 2019

Amazon RDS enables you to use AWS Identity and Access Management (IAM) to manage database access for Amazon RDS for PostgreSQL DB instances. It’s possible use the IAM authentication with Glue connections but it is not documented well, so I will demostrate how you can do it. In your Glue job, you can import boto3 library to call “generate_db_auth_token” method to generate a token and use it when connecting.

Here’s a simple Glue (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...)

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

How to do a quick health check of AWS RDS database

Uncategorized
| Sep 24, 2019
Just because the database is on AWS RDS, it doesn't mean that it won't run slow or get stuck. So when your users complain about the slowness of your RDS database, do the following quick health check:
1- From AWS console, in RDS section, go to your database and then go to Logs and Events tab. From Logs, in case of Oracle check alert log, in case of SQL Server check Error log, for PostgreSQL (more...)

Seattle PostgreSQL Meetup This Thursday: New Location

Uncategorized
| Jun 18, 2019

I’m looking forward to the Seattle PostgreSQL User Group meetup this Thursday (June 20, 2019) at 5:30pm! We’re going to get an early sneak peek at what’s coming later this year in PostgreSQL’s next major release. The current velocity of development in this open source community is staggering and this is an exciting and valuable opportunity to keep up with where PostgreSQL is going next.

One thing that’s a bit unusual about this meetup is (more...)

Identifying PostgreSQL Bloat and fixing it without downtime

Uncategorized
| Apr 20, 2019

Original Post can be viewed at Identifying PostgreSQL Bloat and fixing it without downtime

MVCC (Multi-Version Concurrency Control) feature allows databases to provide concurrent access to data. This allows each SQL statement to see a snapshot of data as it was some time ago, regardless of the current state of the underlying data. This prevents statements from viewing inconsistent data produced by concurrent transactions performing updates on the same…

AskDba.org Weblog

named tuple to JSON – Python

Uncategorized
| Apr 5, 2019

In pgdb – PostgreSQL DB API, the cursor which is used to manage the context of a fetch operation returns list of named tuples. These named tuples contain field names same as the column names of the database query.

An example of a row from the list of named tuples –


Row(log_time=datetime.datetime(2019, 3, 20, 5, 41, 29, 888000, tzinfo=), user_name='admin', connection_from='72.20.208.64:21132', command_tag='INSERT', message='AUDIT: SESSION,1,1,WRITE,INSERT,TABLE,user.demodml,"insert into user.demodml (id) values (1),(2),(3),(4),(5),(6),(7),(8),(9),(11);",',  (more...)

PostgresConf 2019 Summary

Uncategorized
| Mar 27, 2019

PostgresConf 2019 in New York City is a wrap! Now that I’ve had a few days to recover a little brain capacity, I think I’m ready to attempt a summary. I love conferences for the learning opportunities… inside of sessions and also outside of them. Being the largest PostgreSQL-centered conference in the United States, PostgresConf offers unique opportunities to:

  1. Watch seasoned technical presenters go deep on PostgreSQL internals.
  2. Hear serious users present the latest about (more...)

PostgresConf 2019 Training Days

Uncategorized
| Mar 19, 2019

It feels like PostgresConf in New York is in full swing, even though the main tracks haven’t even started yet!

(Oh, and by the way, as of this morning I heard there are still day-passes available for those who haven’t yet registered for the conference… and then you can come hear a great session about Wait Events in PostgreSQL this Thursday at 4:20pm!)

The first two days of PostgresConf are summits, tutorials and training (more...)

Speeding up Initial data load for Oracle to PostgreSQL using Goldengate and copy command

Uncategorized
| Mar 18, 2019

Original Post can be viewed at Speeding up Initial data load for Oracle to PostgreSQL using Goldengate and copy command

Oracle Goldengate supports Oracle to PostgreSQL migrations by supporting PostgreSQL as a target database, though reverse migration i.e PostgreSQL to Oracle is not supported. One of the key aspect of these database migrations is initial data load phase where full tables data have to copied to the target datastore. This can be a time (more...)

PostgreSQL – LWLock:multixact_offset

Uncategorized
| Feb 24, 2019

On one of the PostgreSQL db , the active sessions were waiting on IO:SLRURead and LWLock:multixact_offset causing application latency.

Multioffset

As per PostgreSQL doc, SLRURead is waiting for a read of an SLRU page and multixact_offset is waiting for I/O on a multixact offset buffer.

Diagnosing the issue took some time so cut to the chase

1. Identified the SQLs, relation (and its size) for which these wait events were high. These table had ~1500 DMLs (more...)

Step by Step Installation of PostgreSQL 11 on Oracle Linux 7

Uncategorized
| Jan 11, 2019
This is quick spit-out of commands, I used on my test virtual machine to install PostgreSQL 11 on Oracle Enterprise Linux 7.



[root@docker ~]# curl -O https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-oraclelinux11-11-2.noarch.rpm
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  4952  100  4952    (more...)