Infrastructure at your Service

Category Archives: Database Administration & Monitoring

Daniel Westermann

Setting up default parameters for roles in PostgreSQL

By | Database Administration & Monitoring | No Comments

As you might know you can set various parameters on the session level in PostgreSQL by using the “set” command: postgres=# \h set Command: SET Description: change a run-time parameter Syntax: SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | ‘value’ | DEFAULT } SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT } This allows a session to adjust parameters at runtime and…

 
Read More
Daniel Westermann

Can I do it with PostgreSQL? – 17 – Identifying a blocking session

By | Database Administration & Monitoring | No Comments

One single blocking session in a database can completely halt your application so identifying which session is blocking other sessions is a task you must be able to perform quickly. In Oracle you can query v$session for getting that information (blocking_session, final_blocking_session). Can you do the same in PostgreSQL? Yes, you definitely can, lets go.  

 
Read More
William Sescu

OUD 11.1.2.3 – Patch 25840309: OUD BUNDLE PATCH 11.1.2.3.170718

By | Database Administration & Monitoring | 2 Comments

A new OUD bundle patch was release at the 18th of July 2017, and this gives me the opportunity to show how fast an OUD can be patched, if configured correctly. In case you have an OUD multi master replication with a load balancer virtual IP on top, then zero downtime patching is possible. This is the configuration that I usually recommend. Not only for high availability, but also for maintenance. A typical OUD installation…

 
Read More
Clemens Bleile

Recommended DB Parameters on the Oracle Database Appliance (ODA)

By | Database Administration & Monitoring, Database management, Hardware & Storage | No Comments

When creating a DB on the ODA using # oakcli create database -db <db-name> a template is used to set a couple of DB parameters like e.g. _datafile_write_errors_crash_instance=FALSE _db_writer_coalesce_area_size=16777216 _disable_interface_checking=TRUE _ENABLE_NUMA_SUPPORT=FALSE _FILE_SIZE_INCREASE_INCREMENT=2143289344 _gc_policy_time=0 _gc_undo_affinity=FALSE db_block_checking=’FULL’ db_block_checksum=’FULL’ db_lost_write_protect=’TYPICAL’ filesystemio_options=’setall’ parallel_adaptive_multi_user=FALSE parallel_execution_message_size=16384 parallel_threads_per_cpu=2 use_large_pages=’ONLY’ In recent projects I saw a couple of DBs running on ODA, which did not have (all) those parameters set, because the DBs were migrated from a non-ODA-platform and the customer took over…

 
Read More
Daniel Westermann

Can I do it with PostgreSQL? – 16 – DDL triggers

By | Database Administration & Monitoring | No Comments

A question I received recently from a customer: Oracle gives you the possibility to create DDL triggers. DDL triggers fire (as the name implies) when DDL events occur. Can we do that in PostgreSQL? Yes, this feature is called event triggers. There is a nice list in the documentation which lists for which DDLs an event trigger can fire: Event Trigger Firing Matrix. Lets go …  

 
Read More
Daniel Westermann

What are typed tables in PostgreSQL?

By | Database Administration & Monitoring | No Comments

While reading the PostgreSQL documentation for “CREATE TABLE” I came across this: “OF type_name: Creates a typed table, which takes its structure from the specified composite type (name optionally schema-qualified). A typed table is tied to its type; for example the table will be dropped if the type is dropped (with DROP TYPE … CASCADE).” Sounds interesting, lets have a look.  

 
Read More
Daniel Westermann

Did you ever wonder what PostgreSQL is executing in the background when you use the psql shortcuts?

By | Database Administration & Monitoring | No Comments

When you work with PostgreSQL you are probably using psql and when you use psql you are probably using one or more of the shortcuts psql is providing. These shortcuts provide a quick and convenient way to get meta data out of the PostgreSQL catalog which safes you from a lot of typing and typos. The only issue with that is that it hides the statements which are executed to get the meta data so…

 
Read More
Eric Simbozel

Azure Compare Total Cost of Ownership (TCO) with comparable Azure deployment

By | Database Administration & Monitoring | No Comments

Microsoft Azure allows you to quickly deploy infrastructures and services to meet all of your business needs. You can run Windows and Linux based applications in 36 Azure datacenter regions, delivered with enterprise grade SLAs. Microsoft Azure offer you to Quickly compare the total cost of ownership (TCO) of your on-premises infrastructure with a comparable Azure deployment using the  TCO Calculator and estimate savings you can realize by moving to Azure. In this article i…

 
Read More
Daniel Westermann

Can I do it with PostgreSQL? – 15 – invisible indexes

By | Database Administration & Monitoring | 2 Comments

It has been quite a while since the last post in this series. Today we’ll look at what you know from Oracle as: Invisible indexes. In case you wonder what they might be useful for: Imagine you want to test if an index would benefit one or more queries without affecting the production workload. In other words: Wouldn’t it be cool to create an index but somehow tell the optimizer not to use it for…

 
Read More