postgresql – Shekhar Gulati

How does FerretDB work?

In recent weeks, I have come across FerretDB on multiple occasions, and I thought why not just get a closer look on the topic. I took a particular interest in it (FerretDB) as it is a MongoDB implementation, on top of my favourite database PostgreSQL.

While I do have high-level thoughts on how I would go about building MongoDB on top of Postgres, I wanted to confirm, validate, and learn how the FerretDB team has been doing it.

FerretDB (previously MangoDB) was founded to become the de-facto open-source substitute to MongoDB. FerretDB is an open-source proxy, converting the MongoDB 5.0+ wire protocol queries to SQL – using PostgreSQL as a database engine.

At a high level, FerretDB is a proxy, which implements MongoDB Wire Protocol that MongoDB clients speak. After establishing the connection with MongoDB clients, it translates any query sent by MongoDB clients to the SQL queries Postgres understands.

In the recent release(0.5.0) of FerretDB, it is also possible to use it as a library rather than as a proxy. FerretDB as a library helps in reducing one network hop, which leads to better performance. It is only possible for applications that are built in Go since FerretDB is implemented in Go.

Below are some of the tweets from people on this article. If you find this article useful please share and tag me @shekhargulati

TIL #9: PostgreSQL SQL query to find column count for all the tables in a PostreSQL schema

Today, I wanted to find column count for all the tables in a PostreSQL schema. The following query could be used.

select table_name, count(*) as column_count 
from information_schema."columns" 
where table_schema = 'public'  
GROUP by table_name order by column_count desc;

TIL #8: Installing and Managing PostgreSQL with Brew

This post documents how to install and manage PostgreSQL using brew package manager for macOS.

To install PostgreSQL using brew, run the following command.

$ brew install postgresql

If you wish to start and stop PostgreSQL, then you do that using following brew commands.

$ brew services stop postgresql
$ brew services start postgresql

Create a database for your username

createdb `whoami`

To fix the error role "postgres" does not exist, run the following command

createuser -s postgres

Now, you will be able to log into psql using both your username and postgres

psql

psql -U postgres

To get information about your PostgreSQL installation, you can run following brew command.

brew info postgresql

There are time you might forget if you installed PostgreSQL using brew. You can check if PostgreSQL was installed by brew by running following command.

brew list |grep postgres

The brew package manager installs PostgreSQL under following location.

/usr/local/Cellar/postgresql/

The configuration files as inside following directory.

/usr/local/var/postgres

How to Rename and Change Boolean column to Integer in PostgreSQL

Today, I faced a situation where I had to change a column type from Boolean to Integer. Also, I wanted to rename the column. Let’s suppose, we have tasks table that has a boolean column done. I want to rename done to status and change all false values to 0 and all true values to 1. To do that, you have to run following SQL query using PostgreSQL client.

ALTER TABLE tasks ALTER done SET DEFAULT null;
ALTER TABLE tasks
ALTER done TYPE INTEGER
USING
CASE
WHEN f THEN 0 ELSE 1
END;
ALTER TABLE tasks RENAME done TO status;

PostgreSQL learning

To find out the location of PostgreSQL configuration files, just connect with your PostgreSQL database and run the following command.

SELECT name, setting FROM pg_settings WHERE category = 'File Locations';

To describe a table, you should use

\d+ table_name

To list all the databases,you should run following command.

\list
\l

To view all the tables in a schema

\dt+ pg_catalog.pg_t*

To view core settings of a PostgreSQL server, you should use

SHOW ALL;

To view value of a specific setting like effective_cache_size

SHOW effective_cache_size;

To reload the configuration changes

SELECT pg_reload_conf();

To create a new LOGIN user

CREATE ROLE shekhar LOGIN PASSWORD 'p@ssw0rd';

To create a new database

CREATE DATABASE myappdb;

Schema are logical compartments in a database. You can divide your database into small logical schemas.

CREATE SCHEMA etcs;

To view all the available extensions

SELECT * from pg_available_extensions;

To view details about an extension
To view all the available extensions

\dx+ plpgsql;

To install an extension and view its details

postgres=# CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION
postgres=# \dx+ fuzzystrmatch;
                    Objects in extension &quot;fuzzystrmatch&quot;
                             Object Description
----------------------------------------------------------------------------
 function difference(text,text)
 function dmetaphone(text)
 function dmetaphone_alt(text)
 function levenshtein(text,text)
 function levenshtein(text,text,integer,integer,integer)
 function levenshtein_less_equal(text,text,integer)
 function levenshtein_less_equal(text,text,integer,integer,integer,integer)
 function metaphone(text,integer)
 function soundex(text)
 function text_soundex(text)
(10 rows)

To view the active running processes

postgres=# SELECT pid, usename from pg_stat_activity;
 pid  | usename
------+----------
 1982 | postgres
 2742 | postgres
 2236 | postgres
 2723 | shekhar
 2744 | postgres
(5 rows)

To kill a connection

postgres=# SELECT pg_terminate_backend(2723);
 pg_terminate_backend
----------------------
 t
(1 row)

To kill all the connection by user shekhar

SELECT pg_terminate_backend(pid) FROM pg_stat_activity where usename = 'shekhar';

To run a script file, run the following command.

psql -f &lt;path_to_filefile&gt;

You can also run commands with psql non-interactively

psql -d mydb -c &quot;CREATE SCHEMA test;&quot;

The \set command can be used to create user defined shortcuts like as shown below.

\set connections_check 'SELECT pid, usename from pg_stat_activity;'

The .psqlrc file can be used to define configurations for a session. You can use PSQLRC environment variable to control the location of the startup file.

\pset null 'Null'
\encoding latin1
\set PROMPT1 '%n@%M:%&gt;%x %/#'
\set PROMPT2
\timing on
\pset pager always

To turn on timing for the queries execution time

\timing

You can call OS shell commands from within psql as shown below.

admin2pnvtk8:myapp#\! env|grep POSTGRES

To view uptime of your PostgreSQL database in minutes, run the following query.

myapp=# select date_trunc('minute',current_timestamp - pg_postmaster_start_time()) as &quot;postgresql_uptime&quot;;
 postgresql_uptime 
-------------------
 00:12:00
(1 row)

How to Host your Java EE Application with Auto-scaling

OpenShift is an auto-scalable Platform as a Service. Auto-scalable means OpenShift can horizontally scale your application up or down depending on the number of concurrent connections. OpenShift supports the JBoss application server, which is a certified platform for Java EE 6 development. As an OpenShift user, you have access to both the community version of JBoss and JBoss EAP 6(JBoss Enterprise Application Platform) for free. In this blog post, we will learn how to host a scalable Java EE 6 application using a JBoss EAP 6 server cluster running on OpenShift. Read the full blog here https://www.openshift.com/blogs/how-to-host-your-java-ee-application-with-auto-scaling

DevFest Austria 2013 Talk : Thinking Beyond RDBMS — Building Polyglot Persistence Java Applications

Couple of weeks back I gave a talk at DevFest Austria event on how to use different nosql datastores with OpenShift. You can view the video here.