Everything our consultants learn while running databases for customers: Oracle, PostgreSQL, SQL Server, MySQL, MariaDB, MongoDB and Delphix. Expect hands-on posts about installation, day-to-day administration, backup and recovery, performance tuning, replication and upgrades.
By Franck Pachot . This is the start of a series on PostgreSQL execution plans, access path, join methods, hints and execution statistics. The approach will compare Postgres and Oracle. It is not a comparison to see which one is better, but rather to see what is similar and where the approaches diverge. I have a long experience of reading…
By Franck Pachot . I run my laptop with Windows 10 for office programs, and VirtualBox machines with Linux for the big stuff (Oracle databases). I have also Cygwin installed on Windows for GNU programs. I wanted to quickly install PosgreSQL and rather than installing it in a Linux VM, or as a Windows program, I installed the Cygwin version…
By Franck Pachot . In the previous post we have seen the Index Scan, where a where clause condition uses the index structure to find rows and then fetches remaining columns from the table. The case was an optimal one where the correlation/clustering factor was good. Now I’ll do the opposite: the worst clustering factor. Index with good correlation /…
From time to time it might be required to move objects (tables, indexes, whatever) from one user to another user in a database system. You could do that by dumping all the objects with pg_dump and then load it to the target user. But there are other solutions which are faster than that. Lets go. Obviously we need two users…
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…
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. As usual we’ll start by…
By William Sescu [vc_row][vc_column width=”1/1″][vc_column_text]Security in OUD plays a very important role. Keeping your patch level up to date is just one of those topics, like explained in my recent blog. https://www.dbi-services.com/blog/oud-11-1-2-3-patch-25840309-oud-bundle-patch-11-1-2-3-170718/ Another one are privileges. You don’t need to do all of your work with the cn=Directory Manager. The cn=Directory Manager is the most powerful user in an Oracle…
By William Sescu 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…
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…
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…
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…
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…
