DEV Community: Yuya Shinde

Using VS Code Tasks for PostgreSQL Development

Yuya Shinde — Fri, 21 Mar 2025 05:11:27 +0000

This post builds upon the content presented in our previous two articles, providing further guidance with additional configurations.

When developing with PostgreSQL, it's often necessary to perform various operations such as compiling, installing, initializing the database, starting and stopping the server. In this article, we'll show you how to add these tasks to Visual Studio Code (VS Code) using the Task feature.

What are Tasks in VS Code?

Tasks in VS Code allow you to automate build scripts and external operations by creating a configuration file called tasks.json. This file is located in the .vscode directory of your workspace. By defining tasks, you can easily run them from within VS Code, making it easier to manage your development workflow.

Creating Tasks for PostgreSQL Development

To create tasks for PostgreSQL development, we'll need to add a new configuration file named tasks.json to our workspace. Here's an example of what the file might look like:

{
    "version": "2.0.0",
    "tasks": [
        {
            "label": "Configure",
            "type": "shell",
            "command": "./configure",
            "args": [
                "--prefix=${PG_BUILD_DIR}",
                "--enable-depend",
                "--enable-debug",
                "--enable-cassert",
                "--enable-tap-tests",
                "CFLAGS=-O0"
            ],
            "options": {
                "cwd": "${workspaceFolder}",
                "env": {
                    "PG_BUILD_DIR": "${workspaceFolder}/../build/pgsql/master"
                }
            },
            "group": {
                "kind": "build",
                "isDefault": false
            },
            "problemMatcher": [],
            "presentation": {
                "reveal": "never",
                "revealProblems": "onProblem",
                "showReuseMessage": false,
                "panel": "shared"
            }        
        },
        {
            "label": "Build",
            "type": "shell",
            "command": "make",
            "args": [],
            "options": {
                "cwd": "${workspaceFolder}"
            },
            "group": {
                "kind": "build",
                "isDefault": false
            },
            "dependsOn": [
            ],
            "problemMatcher": [],
            "hide": true
        },
        {
            "label": "Build & Install",
            "type": "shell",
            "command": "make",
            "args": [
                "install"
            ],
            "options": {
                "cwd": "${workspaceFolder}"
            },
            "group": {
                "kind": "build",
                "isDefault": true
            },
            "dependsOn": [
                "Build"
            ],
            "problemMatcher": [],
            "presentation": {
                "reveal": "never",
                "revealProblems": "onProblem",
                "showReuseMessage": false,
                "panel": "shared"
            }        
        },
        {
            "label": "Clean",
            "type": "shell",
            "command": "make",
            "args": [
                "clean"
            ],
            "options": {
                "cwd": "${workspaceFolder}"
            },
            "group": {
                "kind": "build",
                "isDefault": false
            },
            "problemMatcher": [],
            "presentation": {
                "reveal": "never",
                "revealProblems": "onProblem",
                "showReuseMessage": false,
                "panel": "shared"
            }
        },
        {
            "label": "CleanDataFolder",
            "type": "shell",
            "command": "rm",
            "args": [
                "-rf", "${PG_BUILD_DIR}/data/*"
            ],
            "options": {
                "cwd": "${workspaceFolder}",
                "env": {
                    "PG_BUILD_DIR": "${workspaceFolder}/../build/pgsql/master"
                }
            },
            "group": {
                "kind": "build",
                "isDefault": false
            },
            "problemMatcher": [],
            "hide": true
        },
        {
            "label": "CreateDataFolder",
            "type": "shell",
            "command": "mkdir -p ${PG_BUILD_DIR}/data",
            "args": [
            ],
            "options": {
                "cwd": "${workspaceFolder}",
                "env": {
                    "PG_BUILD_DIR": "${workspaceFolder}/../build/pgsql/master"
                }
            },
            "group": {
                "kind": "test",
                "isDefault": false
            },
            "dependsOn": [
            ],
            "problemMatcher": [],
            "hide": true
        },
        {
            "label": "InitDB",
            "type": "shell",
            "command": "${PG_BUILD_DIR}/bin/initdb -D ${PG_BUILD_DIR}/data",
            "args": [
            ],
            "options": {
                "cwd": "${workspaceFolder}",
                "env": {
                    "PG_BUILD_DIR": "${workspaceFolder}/../build/pgsql/master"
                }
            },
            "group": {
                "kind": "test",
                "isDefault": false
            },
            "dependsOn": [
            ],
            "problemMatcher": [],
            "hide": true
        },
        {
            "label": "Init & Run",
            "type": "shell",
            "command": "sudo",
            "args": [
                "-u", "${USER}",
                "${PG_BUILD_DIR}/bin/pg_ctl",
                "-D", "${PG_BUILD_DIR}/data",
                "-l", "${PG_BUILD_DIR}/data/logfile",
                "start"
            ],
            "options": {
                "cwd": "${workspaceFolder}",
                "env": {
                    "PG_BUILD_DIR": "${workspaceFolder}/../build/pgsql/master"
                }
            },
            "group": {
                "kind": "test",
                "isDefault": false
            },
            "dependsOn": [
                "CleanDataFolder",
                "CreateDataFolder", 
                "InitDB",
            ],
            "problemMatcher": [],
            "presentation": {
                "reveal": "never",
                "revealProblems": "onProblem",
                "showReuseMessage": false,
                "panel": "shared"
            }        
        },
        {
            "label": "Run",
            "type": "shell",
            "command": "sudo",
            "args": [
                "-u", "${USER}",
                "${PG_BUILD_DIR}/bin/pg_ctl",
                "-D", "${PG_BUILD_DIR}/data",
                "-l", "${PG_BUILD_DIR}/data/logfile",
                "start"
            ],
            "options": {
                "cwd": "${workspaceFolder}",
                "env": {
                    "PG_BUILD_DIR": "${workspaceFolder}/../build/pgsql/master"
                }
            },
            "group": {
                "kind": "test",
                "isDefault": false
            },
            "dependsOn": [
            ],
            "problemMatcher": [],
            "presentation": {
                "reveal": "never",
                "revealProblems": "onProblem",
                "showReuseMessage": false,
                "panel": "shared"
            }        
        },
        {
            "label": "Stop",
            "type": "shell",
            "command": "sudo",
            "args": [
                "-u", "${USER}",
                "${PG_BUILD_DIR}/bin/pg_ctl", "stop",
                "-D", "${PG_BUILD_DIR}/data",
                "-m", "smart"
            ],
            "options": {
                "cwd": "${workspaceFolder}",
                "env": {
                    "PG_BUILD_DIR": "${workspaceFolder}/../build/pgsql/master"
                }
            },
            "group": {
                "kind": "test",
                "isDefault": false
            },
            "dependsOn": [],
            "problemMatcher": [],
            "presentation": {
                "reveal": "never",
                "revealProblems": "onProblem",
                "showReuseMessage": false,
                "panel": "shared"
            }
        }
    ]
}

In this example, we've defined several tasks for PostgreSQL development, including:

Configure: runs the ./configure script with various options
Build & Install: runs both make and make install
Clean: runs make clean to delete built objects
Init & Run: initializes the database and starts the server
Run: starts the server
Stop: stops the server

Each task has a unique label, type, command, and arguments. Details of each option are described on the following page. The JSON schema definition is also useful to understand what you can set in the each attribute.

Running Tasks in VS Code

To run a task in VS Code, follow these steps:

Open the Command Palette by pressing Cmd+Shift+P (Mac)
Type "Task: Run Task" and select the task you want to run
Press Enter to run the task

Alternatively, you can also run tasks from the Terminal in VS Code by typing task .

By adding these tasks to your VS Code configuration, you'll be able to easily manage your PostgreSQL development workflow and automate repetitive tasks.

Setting up IntelliSense for PostgreSQL Development

Yuya Shinde — Thu, 20 Mar 2025 04:56:48 +0000

This post builds upon the content presented in our previous two articles, providing further guidance with additional configurations.

As a developer working on an open source project, it's essential to have a seamless development experience. One way to achieve this is by setting up IntelliSense in Visual Studio Code (VS Code). IntelliSense is a Microsoft-developed set of code-completion features, primarily known for its use in Visual Studio and Visual Studio Code, that provides real-time suggestions, auto-completion, and context-aware documentation for various programming languages.

In this post, we'll explore how to configure IntelliSense to work with PostgreSQL source code and exclude unnecessary files from Git changes.

Step 1. Configuring IntelliSense

To set up IntelliSense, create a c_cpp_properties.json file in the .vscode directory of your project. This file will contain settings for the compiler and include paths. Here's an example configuration:

{
    "env": {
        "myIncludePath": ["${workspaceFolder}/src/include", "${workspaceFolder}/src/interfaces/**"],
        "myDefines": []
    },
    "configurations": [
        {
            "name": "Linux (GCC ARM64)",
            "compilerPath": "/usr/bin/gcc",
            "compilerArgs": ["-D_GNU_SOURCE"],
            "intelliSenseMode": "linux-gcc-arm64",
            "includePath": [ "${myIncludePath}", "/usr/include", "/usr/local/include" ],
            "defines": [ "${myDefines}" ],
            "cStandard": "${default}",
            "configurationProvider": "ms-vscode.cmake-tools",
            "mergeConfigurations": true
          }
    ],
    "version": 4,
    "enableConfigurationSquiggles": true
}

This configuration sets up the compiler path, include paths for PostgreSQL and standard libraries, and defines. The predefined argument workspaceFolder points the path of the folder opened in VS Code. VS Code provides some other predefined variables, whose definitions can be found here.

intelliSenseMode needs to be set appropriately for your platform. This link has a list of platforms where you can set it.

Since PostgreSQL depends on GNU functions, setting -D_GNU_SOURCE is necessary to get appropriate code-completion.

Step 2. Excluding Files from Git Changes

When working on a project, you may not want to commit certain files, such as those in the .vscode directory or log files. To exclude these files from Git changes without adding them to the .gitignore file (which would require committing), you can use the exclude file in the .git/info directory.

To exclude the .vscode directory, run the following command:

echo .vscode/* >> .git/info/exclude

You can verify the excluded files by running:

cat .git/info/exclude

By following these steps, you'll have IntelliSense set up for your PostgreSQL project and unnecessary files excluded from Git changes.

Happy coding!

Debugging PostgreSQL with GDB + Visual Studio Code

Yuya Shinde — Wed, 19 Mar 2025 05:13:43 +0000

In this article, we will explore how to debug PostgreSQL using Visual Studio Code (VS Code). We will assume that you have a client machine (macOS) and a remote Linux environment where PostgreSQL is built and running. Please refer to this article for how to build PostgreSQL from source code in a Linux environment.

When doing remote development in Visual Studio, it's common to use SSH public key authentication instead of password authentication. An SSH key consists of a pair of public and private keys, where you keep the private key in a safe place and register the public key with the remote host, allowing you to connect securely without entering a password.

Step 1: Set up SSH keys

First, we need to create an SSH key. We will do it on the client machine (macOS). We can use the ssh-keygen command to generate a new key pair. The -C option specifies the comment for the key, which can be any string (e.g., an email address). The location where the generated key is saved is specified with the -f option. In this article, we save the key in the ~/.ssh directory with the name id_rasp5, but you can specify a different location and/or name.

ssh-keygen -t ed25519 -C "<YOUR_COMMENT>" -f ~/.ssh/id_rasp5

Next, we need to copy the public key to the remote host's ~/.ssh/authorized_keys file.

ssh-copy-id -i ~/.ssh/id_rasp5.pub yuyash@<REMOTE_HOST_IP_ADDRESS>

If you cannot access the remote host via SSH, manually add the contents of ~/.ssh/id_rasp5.pub to the ~/.ssh/authorized_keys file on the remote host.

Step 2: Configure SSH

We need to configure our SSH client to connect to the remote host. We can do this by adding an entry to our ~/.ssh/config file.

Host rasp5
  HostName <REMOTE_HOST_IP_ADDRESS>
  User <USERNAME>
  IdentityFile ~/.ssh/id_rasp5

Replace and with your remote host IP address and username. This configuration tells SSH to connect to the remote host with the specified hostname, username, and identity file.

Step 3: Install VS Code and extensions

Install VS Code. You can either download the package from the website or use the brew command to install it.

brew install --cask visual-studio-code

We also need to install VS Code extension: Remote - SSH into your client. This extension allows us to connect to a remote host using SSH.

To install these extensions, open the Extensions view in VS Code by clicking on the Extensions icon in the left sidebar or pressing Cmd+Shift+X (macOS). Search for the extensions and click the Install button to install them.

Step 4: Connect to the remote host

Once we have installed the Remote - SSH extension, we can connect to our remote host. Open the Command Palette in VS Code by pressing Cmd+Shift+P (macOS) and type "Connect to Host..." in the input field. Select the host we configured earlier.

Step 5: Install C/C++ extensions on the remote host

After connecting to the remote host, we need to install the C/C++ extension pack on the remote host as well. Follow the same step to open Extensions view and install C/C++ extension. This extension provides C/C++ language support for VS Code.

Step 6: Create a launch configuration

To debug PostgreSQL, we need to create a launch configuration. Open the Run view in VS Code by clicking on the Run icon in the left sidebar or pressing Cmd+Shift+D (macOS). Click on the "create a launch.json file" link.

In the launch.json file, add the following configuration:

{
    "version": "0.2.0",
    "configurations": [
        {
            "name": "(gdb) Attach",
            "type": "cppdbg",
            "request": "attach",
            "program": "${workspaceFolder}/../build/pgsql/master/bin/postgres",
            "MIMode": "gdb",
            "setupCommands": [
                {
                    "description": "Enable pretty-printing for gdb",
                    "text": "-enable-pretty-printing",
                    "ignoreFailures": true
                },
                {
                    "description": "Set Disassembly Flavor to Intel",
                    "text": "-gdb-set disassembly-flavor intel",
                    "ignoreFailures": true
                }
            ]
        }
    ]
}

This configuration tells VS Code to attach to the PostgreSQL process using GDB.

Step 7: Start PostgreSQL

Start PostgreSQL on the remote host. The location of the pg_ctl command depends on the --prefix setting when you built it. This article specifies the same location as in this setup steps.

export PG_DIR_PREFIX=${HOME}/Workplace/build/pgsql/master
${PG_DIR_PREFIX}/bin/pg_ctl -D ${PG_DIR_PREFIX}/data -l logfile start

Step 8: Create a backend process

In this article we will debug a PostgreSQL session. Since PostgreSQL forks a new process for each session, we first make a connection and identify the session's process. Create a backend process by running psql on the remote host:

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${PG_DIR_PREFIX}/lib
${PG_DIR_PREFIX}/bin/psql -d postgres

In the psql prompt, run the following command to get the PID of the backend process:

SELECT pg_backend_pid();

Note down the PID (e.g., 444662). If you run the following command in another terminal without closing the psql session, you will see that the process exists. In the next step, we will debug this process using GDB.

ps -ef | grep postgres | grep 444662
yuyash    444662  444569  0 22:20 ?        00:00:00 postgres: yuyash postgres [local] idle

Step 9: Set a breakpoint

Set a breakpoint in the xact.c file at the first line of StartTransactionCommand function.

Step 10: Attach to the process

Attach to the backend process using the launch configuration we created earlier. In the Run view, select the "(gdb) Attach" configuration and click on the "Start Debugging" button. VS Code will attach GDB to the process.

Next, go back to the psql prompt and execute BEGIN; as shown below. This will create a transaction on the PostgreSQL side and go through the code path where you set the breakpoint above.

postgres=# BEGIN;

VS Code's GDB stop at the breakpoint we set earlier. We can now step through the code, inspect variables, and debug PostgreSQL as needed.

That's it! With these steps, you should be able to debug PostgreSQL using Visual Studio Code.

Building and Running PostgreSQL from Source Code

Yuya Shinde — Tue, 18 Mar 2025 00:42:08 +0000

In this article, I'll walk you through the steps to build and run PostgreSQL database server on Linux machine.

Preface

This article is intended for use on Debian-based Linux systems.
The steps is intended for PostgreSQL development. For general use, I recommend installing a pre-compiled package from your Linux distribution's repository.

Step 1: Install Git and Clone the PostgreSQL Repository

The first step is making sure that Git is installed on your system. If it's not already installed, you can install it using the following command:

sudo apt-get install git

Next, clone the official PostgreSQL repository. Note: You can clone the repository anywhere; I'm using ~/Workplace in this article.

mkdir -p ~/Workplace
cd Workplace/
git clone git://git.postgresql.org/git/postgresql.git

Step 2: Install Required Packages

Before building PostgreSQL database server, you need to install some required packages.

sudo apt-get install -y build-essential git gdb lcov bison flex \
libkrb5-dev libssl-dev libldap-dev libpam0g-dev python3-dev \
tcl-dev libperl-dev gettext libxml2-dev libxslt1-dev \
libreadline-dev libedit-dev uuid-dev libossp-uuid-dev \
libipc-run-perl perl

The configure script running in the next step will check for these packages and prompt you to install them if you are missing any other packages.

Step 3: Build and Install PostgreSQL

Now it's time to build and install PostgreSQL. Create a new directory for the build files, set the PG_DIR_PREFIX environment variable and set it as a value of the --prefix option. You can set any prefix location, but avoid building inside the cloned repository directory for better organization.

We also enable debugging and disable optimization to prepare for GDB debugging in the next step.

cd postgresql
mkdir -p ${HOME}/Workplace/build/pgsql/master/
export PG_DIR_PREFIX=${HOME}/Workplace/build/pgsql/master
./configure --prefix=${PG_DIR_PREFIX} --enable-depend --enable-debug --enable-cassert --enable-tap-tests CFLAGS=-O0
make
make install

Step 4: Initialize and Start PostgreSQL

After building and installing PostgreSQL, initialize the database and start the server:

mkdir -p ${PG_DIR_PREFIX}/data
${PG_DIR_PREFIX}/bin/initdb -D ${PG_DIR_PREFIX}/data
${PG_DIR_PREFIX}/bin/pg_ctl -D ${PG_DIR_PREFIX}/data -l logfile start

Step 5: Verify that PostgreSQL is Running

To verify that PostgreSQL server is running, check the process list:

ps -ef | grep postgres
yuyash     54508       1  0 11:32 ?        00:00:00 /home/yuyash/Workplace/build/pgsql/master/bin/postgres -D /home/yuyash/Workplace/build/pgsql/master/data
yuyash     54509   54508  0 11:32 ?        00:00:00 postgres: checkpointer 
yuyash     54510   54508  0 11:32 ?        00:00:00 postgres: background writer 
yuyash     54512   54508  0 11:32 ?        00:00:00 postgres: walwriter 
yuyash     54513   54508  0 11:32 ?        00:00:00 postgres: autovacuum launcher 
yuyash     54514   54508  0 11:32 ?        00:00:00 postgres: logical replication launcher 
yuyash     54524   39226  0 11:32 pts/2    00:00:00 grep --color=auto postgres

Step 6: Connect to PostgreSQL using psql

Before using psql command, set shared library path:

export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${PG_DIR_PREFIX}/lib

Finally, connect to the PostgreSQL database using psql command:

${PG_DIR_PREFIX}/bin/psql -d postgres

To test the connection, execute a simple query to retrieve the version number:

postgres=# select version();
                                              version                                               
----------------------------------------------------------------------------------------------------
 PostgreSQL 18devel on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

Step 7: Disconnect from PostgreSQL

Enter \q to exit.

postgres=# \q

Step 8: Stop PostgreSQL

To stop the PostgreSQL server, execute following command:

${PG_DIR_PREFIX}/bin/pg_ctl stop -D ${PG_DIR_PREFIX}/data -m smart

That's it! In my next article, I'll explore how to debug the PostgreSQL process.