Uncategorized Archives -

How to diagnose and fix the 39000 external_routine_invocation_exception error code in Postgres.

31/01/2024 by Philip

The 39000 error code in PostgreSQL, known as external_routine_invocation_exception, indicates that there is an exception related to the invocation of an external routine. This error can occur in various situations, such as when an external function is called with incorrect parameters or when there is an issue with the external environment that the function depends …

How to diagnose and fix the 1004 string_data_right_truncation error code in Postgres.

09/01/2024 by Philip

The 1004 error code does not correspond to a string_data_right_truncation error in Postgres. The string_data_right_truncation error typically has the SQLSTATE code 22001. This error occurs when an attempt is made to store a string in a column that is longer than the column’s maximum length. For example, trying to insert a 300-character string into a …

Why Am I Getting the Error “Is the PostgreSQL Server Running Locally and Accepting Connections on Unix Domain Socket”? – Troubleshooting Guide for Server Connectivity Issues

04/01/202404/01/2024 by Philip

If you’re encountering the “Is the PostgreSQL server running locally and accepting connections on Unix domain socket?” error, this typically indicates an issue with the initialization or setup of your PostgreSQL server. When attempting to connect to a PostgreSQL database, the client software verifies whether the PostgreSQL service is active and reachable through the Unix …

All about Logical Replication in Postgres.

04/01/202406/12/2022 by Philip

What is Logical Replication

Logical replication in PostgreSQL is a feature that allows you to replicate data from one database to another, in a flexible and customizable way. Unlike physical replication, which replicates the entire database and its structure, logical replication allows you to specify which tables and columns you want to replicate, and how you want the data to be replicated. This means that you can replicate only the data that is relevant to your application, and you can apply custom transformations or filters to the replicated data to meet your specific needs.

All about Table Partitioning in PostgreSQL

04/01/202406/12/2022 by Philip

What is Partitioning in PostgreSQL

Partitioning in PostgreSQL is a technique for dividing a large table into smaller, more manageable pieces called partitions. Each partition contains a subset of the data in the table, and the data in each partition is organized and stored in a way that is optimized for the specific subset of data it contains.

Overall, partitioning is a useful technique for dividing large tables into smaller, more manageable pieces in PostgreSQL. It can improve the performance and manageability of the table, and can make it easier to work with large datasets in your database.

The Quick Redshift AWS Tutorial – Everything you need to get started.

28/02/2020 by Philip

AWS Redshift is a very powerful database, but it is quite different from traditional a RDBMS in terms of architecture and also the sorts of decisions that you need to make and planning that you need to perform before building and operating a Amazon Redshift cluster.

This Redshift AWS tutorial aims to give you everything that you need to get started with AWS Redshift in as little time as possible, by covering the basics of architecture and the structure of the database, optimizations that you can make and some advice on performance tuning and maintaining a AWS Redshift cluster.

AWS Well-Architected Framework – The 5 Pillars

26/02/2020 by Philip

The AWS Well-Architected Framework is a set of Documents and Tools that sets out best practices and procedures to follow to build high quality applications in the cloud on AWS

The core of the well architected framework is 5 pillars that group the important considerations and practices that you need to stick to when building in AWS.

The 5 Pillars of the AWS Well-Architected Framework

Security
Reliability
Performance Efficiency
Operational Excellence
Cost Optimization

AWS IAM Notes – AWS Solutions Architect

25/02/202025/02/2020 by Philip

This is some rough notes on IAM in AWS, covering IAM Roles, IAM Users and IAM Policies. The notes are aims at studying for the AWS Solutions Architect Certification and should cover the main points required for that exam.

AWS IAM can be summed up by saying that it authenticates and authorities you to and in AWS.

Root user is first user and has all permissions to everything on the account. It can’t be removed.

Should you migrate to Postgres from Oracle?

03/01/2020 by Philip

The Oracle database has been the gold standard for enterprise applications for a long time now. It has great performance, solid reliability and most of the features that you could want are available. The big problem is that it is expensive. And I mean REALLY expensive. That’s just for the base product as well. All of the extra features that you might want are chargeable extras which means that wench developing for Oracle, you often have to work without some of the more advanced features because they would cost too much.

What is the pg_clog and the clog

30/08/201929/08/2019 by Philip

There are several directories named log in a Postgres installation.

You have pg_xlog, pg_log and pg_clog.

These are all important but I’ll talk about the others another time.

Pg_clog is the commit log. It is generally a small folder that you should never have a reason to look at. (note that from version 10 of postgres the pg_clog directory is being renamed to pg_xact I will continue to refer to it as pg_clog in this document but the functioning of both is the same)

Importantly, you can never delete anything from that directory. If you do your database will become unusable and you will need to recreate it from a backup.