I always considered myself SQL advanced however when I came to the following sql code, I couldn’t figure out the correct answer: The below table has 5 rows(from on the first sql ), what will be the result of the
Google Cloud SQL(Postgresql) to enable private IP
Due to security requirement, most companies don’t allow Google Cloud SQL (Postgresql) to be accessible via public IP. This article shows how to configure private IP for the db so that a web service built on App engine can communicate
Postgresql identity column “no owned sequence”
Postgresql 10 introduced identity column which offers better manageability than the old fashioned serial. Refer to this post for more detail I followed the post above and converted most of serial columns into identity, worked all good until one day
PostgreSQL logical replication
Logical replication was introduced in PostgreSQL 10. It offers better flexibility than stream replication in a number of ways: Publisher and subscriber can be on different major versions Replication can be done at table level, sharing a subset of the
Building a PostgreSQL docker container with debugger (pldebugger)
Further to my previous post, I wanted to enable debugging in PostgreSQL Docker container so that it can be used together with PGAdmin4. The challenge is it is not enabled by default in any of available Docker images; I also
Deploy pgAdmin as a web service under venv environment
pgAdmin can be deployed as a web server therefore user doesn’t need to install any client to access DB. This also provides a securer way of accessing your database: suppose your web server is on the same network as your
Postgresql upgrade from 9.6 to 10.0
The lasted Postgresql 10 brings in a number of exciting new features: parallel queries, partitions, new sequence and logical replication. Below are steps to upgrade v9.6 to v10. It has similar steps as described in my previous post “Postgresql upgrade
Enable debug in PostgreSQL
PgAdmin comes with built-in support for debugging your Pl/PgSQL programs. However in order to enable this, you’ll have to compile and install extra plug-in to PostgreSQL. I Googled a lot but didn’t find one single post with all steps so
Postgresql upgrade from 9.3 to 9.6
Have been using Postgresql 9.3 for a while until recently after upgrading to PgAdmin4 1.3, I cannot import my backup due to compatibility issue, so I decided to upgrade Postgresql to the current version 9.6. Below are my notes on steps:
Postgresql debugger
It took me a while to figure out how to enable debugger for Postgresql server on Ubuntu. Initially I thought it was pre-packaged by Ubuntu therefore apt-get installed a few packages such as pgadmin3, pgadmin3-dev, postgresql-version.dbg. Here is a post