PostgreSQL – Gokhan Atil's Blog

Oracle Enterprise Manager Plugin for PostgreSQL

Oracle Enterprise Manager supports 3rd party plugins which can help you to extend its monitoring and manageability capabilities. There are already developers and companies specialized in plugin development. Blue Medora is one of them (and probably the most famous one). In this blog post, I’ll show you how we can use Blue Medora’s EM PostgreSQL plugin, which is part of their True Visibility Suite for Oracle. I’ll download and deploy the plugin on the OMS (management servers), then deploy the plugin on the agent(s) and at the end, I’ll add a PostgreSQL target to our Enterprise Manager system.

The PosgteSQL plugin is not available on Self-Update, so we need to download and register it manually to our Enterprise Manager. To download the trial version of the plugin, I go to their website, click “download trial”, and fill a form to request trial version. Blue Medora will see our form and send us an email with download link of the plugin. After I download the zip file to my OMS server (ie into the temp folder), I issue the following commands:

cd /tmp
unzip bm.em.xpgs_12.1.0.2.0_2.0.3.zip -d postgre_plugin
emcli login -user=SYSMAN
emcli import_update -omslocal -file=/tmp/postgre_plugin/bm.em.xpgs_12.1.0.2.0_2.0.3.opar

cd /tmp

unzip bm.em.xpgs_12.1.0.2.0_2.0.3.zip -d postgre_plugin

emcli login -user=SYSMAN

emcli import_update -omslocal -file=/tmp/postgre_plugin/bm.em.xpgs_12.1.0.2.0_2.0.3.opar

August 22, 2016Gokhan Atil4 CommentsOracle, PostgreSQLem13c, postgreSQL

PostgreSQL Partition Check Script

New year is coming, if you want to check date based partitions in PostgreSQL , you may use the following query:

select 
    schema_name,
    table_name,
    max( highvalue )
FROM (    
SELECT
    namespace.nspname AS schema_name,
    parent.relname AS table_name,
    cons.consrc, 
    to_date(substr((cast (regexp_matches(
    cons.consrc,'[[:digit:]]*-[[:digit:]]+-[[:digit:]]*','g') 
    as varchar)),2,10),'YYYY-MM-DD') highvalue
FROM pg_inherits
    JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child ON pg_inherits.inhrelid = child.oid
    JOIN pg_namespace namespace ON namespace.oid = parent.relnamespace
    JOIN pg_constraint cons ON cons.conrelid = child.oid
    WHERE cons.contype='c' ) ss
    group by schema_name, table_name
    order by 1,2,3 desc;

select

schema_name,

table_name,

max( highvalue )

FROM (

SELECT

namespace.nspname AS schema_name,

parent.relname AS table_name,

cons.consrc,

to_date(substr((cast (regexp_matches(

cons.consrc,'[[:digit:]]*-[[:digit:]]+-[[:digit:]]*','g')

as varchar)),2,10),'YYYY-MM-DD') highvalue

FROM pg_inherits

JOIN pg_class parent ON pg_inherits.inhparent = parent.oid

JOIN pg_class child ON pg_inherits.inhrelid = child.oid

JOIN pg_namespace namespace ON namespace.oid = parent.relnamespace

JOIN pg_constraint cons ON cons.conrelid = child.oid

WHERE cons.contype='c' ) ss

group by schema_name, table_name

order by 1,2,3 desc;

December 30, 2014Gokhan Atil6 CommentsPostgreSQLpartitioning

How to Backup and Restore PostgreSQL 9.3 Databases

In my previous blog post, I explained how to install PostgreSQL 9.3 on Oracle Linux. As a DBA, one of the first things we want to learn is how we can recover the database in case of a failure. So in this blog post, I’ll show how to backup and restore PostgreSQL databases in a server. We’ll backup PGDATA directory, so it’ll contain all databases in the instance (remember that PostgreSQL is a multitenant database).

PostgreSQL supports Write Ahead Log (WAL) mechanism like Oracle. So everything will be written to (redo)logs before they written into actual datafiles. So we will use a similar method to Oracle. We need to start “the backup mode”, copy the (data) files, and stop the backup mode, and add the archived logs to our backup. There are SQL commands for starting backup mode (pg_start_backup) and for stopping backup mode (pg_stop_backup), and we can copy the files using OS commands. Good thing is, since 9.1, PostgreSQL comes with a backup tool named “pg_basebackup”. It’ll do everything for us.

December 1, 2014Gokhan Atil22 CommentsPostgreSQLbackup, restore

How to Install PostgreSQL 9.3 on Oracle Linux

For whom are not familiar with PostgreSQL, it is an open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. Like all other enterprise companies, we use different databases beside Oracle to lower the costs. We prefer PostgreSQL instead of Microsoft SQL Server, because it supports PL/SQL for some degree.

In this blog post, I’ll try to show how to install PostgreSQL using YUM. YUM (Yellowdog Updater, Modified) is an open-source command-line package-management utility for Linux operating systems using the RPM Package Manager. It handles library dependencies, so it’s the easiest and maybe the best way to install any RPMs. In this blog post, I’ll install PostgreSQL on Oracle Linux because I use Oracle Linux when I need a Linux OS, and its support costs are lower than Redhat Enterprise Linux. In next weeks, I’m also gonna blog about backup and replication for PostgreSQL, and maybe (not promising) blog about some key concepts of PostgreSQL such as MVCC, vacuum and vacuum freeze.

I assume that you can install Redhat or Oracle Linux. You may read my old post about installing Oracle Linux, hopefully it would be useful. Anyway, if our Linux server is ready, we can download and install Repository RPM for our server. Visit http://yum.postgresql.org.

November 27, 2014Gokhan Atil4 CommentsPostgreSQLinstall, linux

PostgreSQL Conference Europe 2014

I attended to PostgreSQL Conference Europe 2014. It was held October 21-24 at Madrid. I do not know the exact number of attendees but there were some sessions that you can not find seat and have to listen while standing up on a corner. As an Oracle DBA, PostgreSQL is a different world to me. I tried to follow the DBA track and gather as much information as I can. Unfortunately the level of DBA tracks are not deep enough for me. The most topics are designed for developer or system admins who also manage PostgreSQL Databases as a side-mission. I feel like most attendees are developers and see that a database management system should manage itself with almost zero-maintenance. This is the same approach of Microsoft SQL Server users/developers. They see the database as a part of their development environment, and expect it to behave like a answering machine. Anyway there were still interesting sessions and watching the presentations of PostgreSQL Developers was a unique opportunity.

October 27, 2014Gokhan AtilPostgreSQLconference