Debugging a PostgreSQL backend in one click with WezTerm

Introduction PostgreSQL has a process-based architecture. A single running PostgreSQL instance contains several types of processes: a single postgres server process, a backend process per each client connection and various maintenance background processes. During the development of an extension it is often necessary to debug a backend process using gdb (or a different debugger). Along with debugging logs it helps to understand how to work with the extension API since it is often not clear and the PostgreSQL documentation might not be enough....

Creating new PostgreSQL enum data types using pg_type_template

Introduction In Adjust we are creating new data types for PostgreSQL, in order to make handling of data easier for us, but also sometimes to save some space. Many of them are written using the C language. Here are some of the more important extensions for us: istore: it is an integer based hstore. In istore both keys and values are represented and stored as integers. pg-base36: a base36 extension, which implements a base36 binary-to-text encoding algorithm....

Checking PostgreSQL replication lag with pg_last_xact_replay_timestamp()

Recently we got alerted by our NetOps team about a replication lag in one PostgreSQL database. That by itself is not a major problem, nevertheless something which deserves investigation. NetOps is using Zabbix, and the replication lag in the database system in question is measured using a plpgsql function provided by an open source template. In the replica, not in the primary. DO LANGUAGE plpgsql $$ DECLARE ver integer; res text; BEGIN SELECT current_setting('server_version_num') INTO ver; IF (ver >= 100000) THEN SELECT * INTO res from ( SELECT CASE WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0 ELSE COALESCE(EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())::integer, 0) END ) T; ELSE SELECT * INTO res from ( SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE COALESCE(EXTRACT(EPOCH FROM now() - pg_last_xact_replay_timestamp())::integer, 0) END ) T; END IF; perform set_config('zbx_tmp....