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

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

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

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

PostgreSQL – CPU Utilization and Index

One of the Production Aurora PostgreSQL instance running on db.r4.16xlarge instance (64 vCPU and 488 GB ) was reporting high CPU Utilization spiking upto 100%.

Screen Shot 2018-12-09 at 11.14.49 AM

With such issues, one of the first thing is to look for the SQLs with high buffers shared hit. I have built a small tool called pgsnap which is something similar to AWR respostory in Oracle maintaining the SQL stat history.  So, with pg_stat_statements and hist_pg_stat_statements(that’s what (more...)

Why document databases are old news…

We’re going to store data the way it’s stored naturally in the brain.

This is a phrase being heard more often today. This blog post is inspired by a short rant by Babak Tourani (@2ndhalf_oracle) and myself had on Twitter today.

How cool is that!!

This phrase is used by companies like MongoDB or Graph Database vendors to explain why they choose to store information / data in an unstructured format. It is (more...)

Column And Table Redefinition With Minimal Locking

TLDR: Note to future self… (1) Read this before you modify a table on a live PostgreSQL database. If you do it wrong then your app might totally hang. There is a right way to do it which avoids that. (2) Especially remember the lock_timeout step. Many blog posts around the ‘net are missing this and it’s very important.

Yesterday I was talking to some PostgreSQL users (who, BTW, were doing rather large-scale cool stuff (more...)

List of Bank Holidays For England in SQL Format

First off, create a table to store the bank holiday values. You may need to adjust this slightly depending on your SQL server technology being used (this was tested on MySQL Server).  [crayon-5bb4e0b3b1755979540269/] Insert the bank holiday values below. This table is currently for 2012 up to 2019 for England and Wales.  [crayon-5bb4e0b3b175d512369946/] For a

How to change the listening port for PostgreSQL Database

The default TCP port for PostgreSQL is usually 5432, however this can easily be changed in the postgresql.conf configuration file, which is the main configuration file for the database server. In addition to a TCP listening port, PostgreSQL will also a local socket if the server is running in a Linux/ Unix environment. A local socket is

Postgres in the Enterprise, Real World Reasons for adoption

The lead-in

Friend and colleague Bruce Momjian already shared in his presentation “Will Postgres live forever?” (https://attendee.gotowebinar.com/register/8793191803818201345) an overview of reasons why companies adopt open source software (OSS) in general. This overview was based on a survey done by Black Duck Software in 2016 (https://www.slideshare.net/mobile/blackducksoftware/2016-future-of-open-source-survey-results). The survey ranks databases on the second place of technologies that companies are moving towards OSS in.

In this series of (more...)

Swiss pgday 2018

The cool thing about zooming out… is that your world appears to get bigger.
Being personally now no longer bound to Oracle, having the opportunity to work with PostgreSQL, also gives the opportunity to go new places and explore new possibilities. One of the cooler things is: participating in Postgres conferences.

Conference vibe

Where Oracle conferences, although having some deep technical aspects, tend to lean towards the business aspects of technology, especially today with Cloud (more...)

AWS DMS – Target TableName Differs

AWS DMS is a tool that supports both homogenous and heterogeneous migration, helping to migrate to aws cloud.

During most of the migrations, the source and target table names remain the same, in which case the Mappings.json file is pretty simple. As an example (Oracle to PostgreSQL)

 {
    "rules":
    [
        {
            "rule-type": "selection",
            "rule-id": "1",
            "rule-name": "1",
            "object-locator":
            {
                "schema-name": "DEVO",
                "table-name": "TEST_DEMO"
            },
            "rule-action": "include"
        },
        {
            "rule-type": "transformation",
            "rule-id": "2",
            "rule-name": "2",
             (more...)

This Week in PostgreSQL – May 31

Since last October I’ve been periodically writing up summaries of interesting content I see on the internet related to PostgreSQL (generally blog posts). My original motivation was just to learn more about PostgreSQL – but I’ve started sharing them with a few colleagues and received positive feedback.  Thought I’d try posting one of these digests here on the Ardent blog – who knows, maybe a few old readers will find it interesting? Here’s the (more...)

PostgreSQL – Unique constraint and null value

An important behavior in PostgreSQL to know about is the duplicate null values do not violate unique constraints.

Oracle

SQL> create table test (id number (2,0), 
                        country varchar(20) not null, 
                        state varchar(20)
                       );

Table created.

SQL> alter table test add constraint pk_test_id primary key (id);

Table altered.

SQL> alter table test add constraint uniq_test_cs unique (country, state);

Table altered.

PostgreSQL

admin@test # create table test (id numeric(2,0), 
                                country character varying(20) not null, 
                                state character varying(20)
                                 (more...)

Beware (Sort-Of) Ambiguous Column Names In Sub-Selects

This morning I received an UPDATE statement from a developer that I was testing. It ran without errors but then I saw that it updated 5 rows when it should have only updated 3. The reason gave me a little shock so I whipped up a simple test-case to reproduce the problem.

First we create two tables:

CREATE TABLE foo (
    id int
    , name varchar(30)
);
CREATE TABLE
CREATE TABLE (more...)

Querying RDS PostgreSQL logs using Amazon Athena

Original Post can be viewed at Querying RDS PostgreSQL logs using Amazon Athena

PostgreSQL logs contains useful information which can help in troubleshooting performance issues,auditing and performing historical analysis. Some of items which can be logged are listed below Client connection and disconnection messages Failed DB connection request Database sql statement errors Database Fatal Errors Auditing Information Database Lock history Long running queries and Query plan logging High…

AskDba.org Weblog

FillFactor for UPDATE

What is FillFactor in PostgreSQL?

As per offical Doc —

fillfactor (integer)

The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the (more...)

A week of PostgreSQL

One of the attractive things of my job is this… Just a bit more often than every now and then, you get the opportunity to get out and meet people to talk about Postgres. I don’t mean the kind of talk I do every day, which has more of a commercial touch to it. – Don’t get me wrong, that is very important too! – But I mean, really talk about PostgreSQL, be part of (more...)

How (Not) to Change Passwords in PostgreSQL

A few months ago I was doing some testing on a development PostgreSQL database and watching the postgresql server log (akin to Oracle's alert log). I was skimming for lines relevant to my testing when I noticed something chilling:

LOG:  statement: alter user john password 'IloveDBAs';

The username and password have obviously been changed but the point is plain: PostgreSQL printed the password value in plain text to the log file. A few tests of (more...)

Oracle to Postgres — Index Skip Scan

I have been working on Oracle to Postgres migration and it has been a fun ride.

One of the recent encounters during migration was performance issue in Postgres for a query, actually quite a simple query.

SELECT count(*) AS num_incidents FROM audit_cs acs INNER JOIN audit_changes ac ON acs.audit_change_id = ac.audit_change_set WHERE acs.object_id=$1 AND ac.path = 'Flag' AND ac.new_value = 'sign' 

Let’s look at the execution plan in Postgres

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 (more...)

The Transition (or: How I Learned to Stop Worrying and Love PostgreSQL)

Note: Yes I re-(ab)used the title.



As some of you may know (if you follow me on twitter), after 16 years as an Oracle DBA, I made a career shift last summer. I hung up the Oracle spurs (and that sweet Oracle Ace vest) and threw on a pair of PostgreSQL chaps. I had always been a fan and very casual user of PostgreSQL for many years, even gently lobbying the folks at Pythian to (more...)