How to know PostgreSQL is running or installed

Question: How do I know if PostgreSQL is running or installed on a Linux server ? Answer: There are some ways to check whether PostgreSQL is running or installed, but it will depend on how PostgreSQL was installed . It pays to ask the user , how PostgreSQL was installed. Some different methods of PostgreSQL installation & configuration could include: -Installed from rpm packages and started by init script -Installed \ running but the installation is not associated to the default PATH or even the default port -Installed from rpm packages but not set to start As a starter these... Read more →

Notes on MongoDB BI Connector

These are my notes on installing the MongoDB BI Connector. The MongoDB Connector for Business Intelligence (BI) supports users to generate SQL queries against MongoDB databases. Typical client programs include Qlik,Tableau and Excel Details will depend on the overall architecture required. Typically I install MongoDB Connector on a dedicated server - to support wider usage ==Place the executable in /usr/bin mongodrdl mongosqld mongotranslate ==Place the cfg file in /usr/bin/mongobi - mongosqld.conf ===To start it from the command line mongosqld --mongo-uri "mongodb://MY_SERVER:27017" --auth --mongo-username MY_USER --mongo-password MY_PASSWORD --mongo-authenticationSource MY_AUTH_DB ===To start mongosqld with a configuration file (see example of basic configuration... Read more →

Top 10 Yoda tips for the Linux command line

Master these Linux commands and you will feel the Force or become the Linux Yoda . (or even just your job done more efficiently) I don't know anything about myprogram - how can I find out? 10 "info myprogram" or 9 "man myprogram" How can I find myprogram ? 8 "locate -i foo" or 7 "whereis foo" How can I view myprogram ? 6 "less foo" or 5 "emacs foo" How can I view the program's help ? This applies to most programs 4 myprogram "-h" or "--help" . How can I navigate further into a directory? 3 "cd ./mydirectory"... Read more →

How to use pgbench for PostgreSQL benchmark testing

Pgbench is used for Postgresql benchmark testing. Pgbench iterates through a bunch of SQL statements . There are various configurables such as sessions (single or multiple) and clients. The pgbench approach is based on TPC-B . The TPC-B benchmark focuses on benchmarking as opposed to OLTP type testing. The measurement is based on transactions per second. In other words - you'll define how many transactions to execute per client and this will result in a rate per second. There is a default test framework – which involves 5 SELECT , UPDATE, INSERT commands per transaction- there are options to customise... Read more →

How to clear Postgresql database cache

Question: In SQL Server there are two commands which clear down the data and query caches - DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS. Are there similar commands in PostgreSQL hosted on Linux? I want to clear down datasets from memory cache to allow query performance tuning. Answer: If you'd like to view what is in the buffer cache - pg_buffercache will present to you what is in the cache. Of course , pg_buffercache needs to be installed. It's a complicated data set and requires some knowledge to interpret. Unfortunately - there isn't a straightforward way of clearing down the database cache... Read more →

How to calculate the PostgreSQL database size

Question : How can I calculate the size of a PostgreSQL database using psql?I want to use the command line and execute a sql statement for various checks? Answer: An easy way to derive the Postgresql database size is to use the pg_database_size() function. The pg_database_size() reads the database name and returns the database size in bytes. For example : SELECT pg_database_size('my_db_name') This returns in bytes - which unless you enjoy calculating byte sizes - there is another function you can use which will make these numbers human readable - pg_size_pretty() Combining these two functions will return the database size... Read more →

ODBC Data Source Administrator Connection Type for IBM i Access for Windows

Question: I'm setting up a User DSN on a ODBC Data Source Administrator - and configuring the IBM i Access for Windows ODBC Setup. The default is Use ODBC access mode , but should we use Limit to Read Only (Select Statements) . btw - the intention for this DSN config is to read only. Which option should I choose? Answer: Before we get into connection types - I want to establish the basic principle - which is that the main security focus should be on securing the data rather than access to the data . Of course , it... Read more →

HCI and Database servers

There are constant infrastructure technology changes. There are regular infrastructure changes - for various reasons , such as hardware refresh , hardware not supported by vendor any longer , data centre simplification , changing data centres , on-prem versus off prem. What remains permanent is the database servers that remain on the OS. So it's incumbent upon the DBA to approach the problem with a risk analysis mindset. One of the first is start to understand the underlying infrastructure. Hyper Converged Infrastructure (HCI) utilizes storage residing in servers and distributing the storage treating it like a storage array. This consolidation... Read more →

Which one to use - Apache Kafka versus RabbitMQ

Best case scenario (use case) for Apache Kafka 1) Requirement for partitioned order at least once write. Requirement for A to B streaming with high throughput. 2) Durable message store . Why would this be important? Consumers can get a replay of the event stream - whereas limited in message brokers - where once message is delivered , removed from queue 3) If a solution requires data stream processing. 4) Event sourcing required. Event sourcing persists the state of a business entity e.g maintaining a chain of event at different states in a sequential order for a Customer 5) More... Read more →

How to fix mongo odbc 1.1 access denied for user in Challenge and Response

When supporting developers during setting up BI Connector and the ODBC System DSN for connection to a MongoDB , a common problem is the incorrect application of the username format , leading to a "Access is Denied" error message. What should the correct username format be for the Challenge and Response method? Step 1 is setting up the BI Connector. Typically the developer initiated the command line utility mongosqld . The mongosqld command line utility accepts incoming requests from a SQL client (or ODBC DSN setup used by the SQL Client) and acts as a proxy for the incoming requests.I... Read more →