Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.
As I could not find any information about this error when I faced it this is just a short hint: If you try to use pg_dump or pg_dumpall out of a PPAS installation to dump or dumpall a community version of PostgreSQL you probably get this: pg_dump: [archiver (db)] query failed: ERROR: schema “sys” does not exist pg_dump: [archiver (db)]…
It didn’t made it into the upcoming PostgreSQL 9.5 release but it will probably be coming in 9.6: Parallel sequential scans. If you take a look at the development documentation some new parameters showed up: max_parallel_degree: The maximum degree of parallelism for an individual parallel operation. parallel_tuple_cost: Sets the planner’s estimate of the cost of transferring a tuple from a…
The last posts introduced the logging system, pg_stat_statements, pg_activity and pgcluu. This post will look at POWA: PostgreSQL Workload Analyzer. For getting the most out of POWA the following extension should be installed in the PostgreSQL instance you want to monitor: pg_stat_statements (see last post) pg_stat_kcache: gathers statistics about reads and writes done by the file system layer pg_qualstats: gathers…
The last posts introduced the logging system, pg_stat_statements and pg_activity. All of these can be used to monitor sql statements the PostgreSQL server is executing. In this post I’ll look into pgcluu: PostgreSQL Cluster utilization! This is a more complete monitoring solution as it is not only focused on sql statements but gives you information about the database cluster itself…
The last posts looked at how the logging system and the pg_stat_statements extension can be used to monitor sql statements in PostgreSQL. This post will introduce pg_activity which is very similar to htop. There are some dependencies which need to be installed before we can start installing pg_activity. The first one is python. As I am on a redhat based…
The last post looked into how you can monitor queries using the logging system. This post will introduce pg_stat_statements. pg_stat_statements is a module that needs to be loaded and is not available in the default configuration. Loading it is quite easy. Create the extension as usual: postgres@oel7:/home/postgres/ [PG6] sqh Null display is “NULL”. Timing is on. psql (9.5alpha2) Type “help”…
When developing an application as well as when the application is in production there is the need to identify long running queries. In Oracle one tool you might use for that is the SQL Monitor. In this post I’ll look into what PostgreSQL provides in this area. PostgreSQL has a very strong logging system. This system can be used to…
The last post looked into how to upgrade PostgreSQL to a new minor version. In this post I’ll look into how to upgrade PostgreSQL to a new major version. This is not as simple as just installing the binaries and start the instance from there. For major upgrades there are two possibilities: dump the old version and restore into the…
If you are used to patch Oracle databases you probably know how to use opatch to apply PSUs. How does PostgreSQL handle this? Do we need to patch the existing binaries to apply security fixes? The answer is: No. Lets say you want to patch PostgreSQL from version 9.4.1 to version 9.4.5. What do you need to do? For this…
This post takes a look on how to migrate the Oracle 12cR1 sample schemas to PPAS 9.4 (PostgreSQL Plus Advanced Server 9.4). I’ll not dig into how to install PPAS as this was described in detail some time ago. Just follow this post if you need a setup guide. If you wonder why I am doing this there are two…
A lot of people use the graphical way to upgrade Oracle software from one version to another. While there is nothing to say against that the same can be done without any graphical tools. This post outlines the steps to do so. Currently my cluster is running Grid Infrastructure 12.1.0.1 without any PSU applied. The node names are racp1vm1 and…
I promised to upload the scripts of my session to our blog. So, here they are: create_12102_gi_rac_project.sh: This one creates the 12.1.0.2 GI/RAC project on the cobbler server finish_ora_12102_gi_rac_project.sh: This one does the configuration of the GI/RAC on the last node ora12102_gi_rac.ks: The 12.1.0.2 GI/RAC kickstart file pg940.ks: Template kickstart file for PostgreSQL 9.4 create_940_postgres_project.sh: Template project file for PostgreSQL…
