Postgresql 使用Vscode開發指南

Postgresql 使用Vscode開發指南

depends libraries

sudo apt install -y libsystemd-dev libxml2-dev libssl-dev libicu-dev zlib1g-dev libreadline-dev pkg-config

編譯安裝

adduser postgres

<!-- cd postgresql目錄 -->
mkdir build
mkdir pgsql
cd build
<!-- --prefix=絕對路徑 -->
/configure --prefix=/home/postgres/repos/postgresql-15.1/pgsql --with-icu --with-openssl --with-systemd --with-libxml --enable-debug
make -j12
make install
cd ..
mkdir -p pgsql/data
chown postgres pgsql/data
su - postgres
pgsql/bin/initdb -D pgsql/data

前臺執行

pgsql/bin/postgres -D pgsql/data

後臺執行

pgsql/bin/pg_ctl -D pgsql/data -l logfile start

命令互動工具

pgsql/bin/psql

Vscode 組態檔

.vscode/launch.json

{
    "configurations": [
        {
            "name": "Debug PG SRV",
            "type": "cppdbg",
            "request": "launch",
            "program": "${workspaceFolder}/pgsql/bin/postgres",
            "args": [
                "-D",
                "pgsql/data"
            ],
            "stopAtEntry": false,
            "cwd": "${workspaceFolder}",
            "environment": [],
            "externalConsole": false,
            "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
                },
                {
                    "text": "-gdb-set follow-fork-mode child",
                    "ignoreFailures": true
                },
                {
                    "text": "-gdb-set detach-on-fork on",
                    "ignoreFailures": true
                }
            ],
            "preLaunchTask": "rebuild_db",
            "miDebuggerPath": "/usr/bin/gdb"
        },
        {
            "name": "Attach PG SRV",
            "type": "cppdbg",
            "request": "attach",
            "processId": "${command:pickProcess}",
            "program": "${workspaceFolder}/pgsql/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
                }
            ]
        },
    ],
    "version": "2.0.0"
}

.vscode/tasks.json

{
    "tasks": [
        {
            "type": "shell",
            "label": "install_depends",
            "command": "sudo apt install -y libsystemd-dev libxml2-dev libssl-dev libicu-dev zlib1g-dev libreadline-dev pkg-config",
            "options": {
                "cwd": "${workspaceFolder}"
            },
            "detail": "Task install depends."
        },
        {
            "type": "shell",
            "label": "build_env",
            "command": "mkdir build && mkdir -p pgsql/data",
            "options": {
                "cwd": "${workspaceFolder}"
            },
            "detail": "Task add folders."
        },
        {
            "type": "shell",
            "label": "build_config",
            "command": "../configure",
            "args": [
                "--prefix=${workspaceFolder}/pgsql",
                "--with-icu",
                "--with-openssl",
                "--with-systemd",
                "--with-libxml",
                "--enable-debug"
            ],
            "options": {
                "cwd": "${workspaceFolder}/build"
            },
            "detail": "Task Build MakeFile."
        },
        {
            "type": "shell",
            "label": "make",
            "command": "make",
            "args": [
                "-j12"
            ],
            "options": {
                "cwd": "${workspaceFolder}/build"
            },
            "detail": "Task build."
        },
        {
            "type": "shell",
            "label": "make_install",
            "command": "make",
            "args": [
                "install"
            ],
            "options": {
                "cwd": "${workspaceFolder}/build"
            },
            "detail": "Task install database."
        },
        {
            "type": "shell",
            "label": "init_db",
            "command": "pgsql/bin/initdb",
            "args": [
                "-D",
                "pgsql/data"
            ],
            "options": {
                "cwd": "${workspaceFolder}"
            },
            "detail": "Task init default database."
        },
        {
            "type": "shell",
            "label": "clean_db",
            "command": "make uninstall && make clean && rm -rf ../pgsql && rm -rf ../build",
            "options": {
                "cwd": "${workspaceFolder}/build"
            },
            "detail": "Task clean database."
        },
        {
            "type": "shell",
            "label": "build_db_conf",
            "dependsOn": [
                "build_env",
                "build_config"
            ],
            "dependsOrder": "sequence",
            "detail": "Task add folders."
        },
        {
            "type": "shell",
            "label": "build_db",
            "dependsOn": [
                "make",
                "make_install",
                "init_db"
            ],
            "dependsOrder": "sequence",
            "detail": "Task add folders."
        },
        {
            "type": "shell",
            "label": "rebuild_db",
            "dependsOn": [
                "make",
                "make_install"
            ],
            "dependsOrder": "sequence",
            "detail": "Task add folders."
        },
    ],
    "version": "2.0.0"
}

安裝目錄修改必要的設定

pgsql/data/postgresql.conf

listen_addresses = '*'

pgsql/data/pg_hba.conf

host    all             all             0.0.0.0/0               md5

Vscode 偵錯操作

F1 選擇 「Tasks：Run Task」 繼續選擇 「build_db_conf」
F1 選擇 「Tasks：Run Task」 繼續選擇 「build_db」

DEBUG 工具列選擇 Debug PG SRV，F5 開始偵錯postgres主程序

如果要偵錯其它程序，比如我開啟psql互動工具建立一個連線，ps aux 可以看到新增的程序資訊，DEBUG 工具列繼續選擇 Attach PG SRV，F5 開啟偵錯選擇需要偵錯的程序ID

注意：Vscode 需要使用 postgres 使用者登入才行（非ROOT使用者，其它使用者也行，需要新建使用者，需要額外的操作）。